14. Protection, Sharing and Printing
14.2 Hiding Formulas and Allow Edit Ranges
Steps in Excel
- Hide formulas: select formula cells › Ctrl + 1 › Protection › tick Hidden › protect the sheet. The formula bar now shows nothing for those cells (the result is still visible).
- Allow Edit Ranges (different people edit different ranges): Review › Protect › Allow Edit Ranges › New… › Title
PuneTargets, Refers to=$C$4, optional range password › OK. Repeat for other ranges. Then click Protect Sheet in the same dialog. (In some Microsoft 365 builds this appears under Review › Protect › Allow Edit Ranges or inside the Protection menu – may vary by version.) - In a company network, Permissions… in the same dialog can allow specific Windows users without a password.
Worked example. The Nagpur manager gets the range password for C9 only; the Pune manager (Ravindra Bagale) gets the password for C4. Each can change only their own city's target; all formulas are hidden and locked.
Ravindra Bagale's Tip
Many students think that making a formula Hidden makes it "secret" – but Hidden has no effect unless the sheet is protected, and sheet protection is easy to break. Use hidden formulas only to prevent accidental changes, not to hide confidential logic.
Ravindra Bagale's Tip – मराठी
बऱ्याच students ना वाटतं की formula Hidden केला म्हणजे तो "secret" झाला – पण sheet protect केल्याशिवाय Hidden चा काहीच परिणाम होत नाही, आणि sheet protection तोडणं सोपं आहे. Hidden formulas फक्त चुकून होणारे बदल टाळण्यासाठी वापरा, गुप्त (confidential) logic लपवण्यासाठी नाही.
Ravindra Bagale's Tip – हिंदी
बहुत से students सोचते हैं कि formula को Hidden किया तो वह "secret" हो गया – पर sheet protect किए बिना Hidden का कोई असर नहीं होता, और sheet protection तोड़ना आसान है. Hidden formulas सिर्फ़ गलती से होने वाले बदलाव रोकने के लिए इस्तेमाल करो, गोपनीय (confidential) logic छुपाने के लिए नहीं.
Practice task
Hide the formulas in D4:E9, create two Allow Edit Ranges (Pune C4, Nagpur C9) with different passwords, protect the sheet and test both passwords.