Ravindra BagaleCourses & study guides मराठी Track your progress

14. Protection, Sharing and Printing

14.2 Hiding Formulas and Allow Edit Ranges

By Last updated:

Steps in Excel

  1. 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).
  2. 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.)
  3. 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.

Practice tasks (10+)

Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.

1. Rebuild the smallest example of hiding formulas and allow edit ranges on a blank sheet for Skyline Clinics.

Hint: Use only the steps on this page. Keep numbers numeric.

2. Change one input for Aamir Khan and confirm the result updates.

Hint: If the result is stuck, check absolute vs relative references.

3. Repeat the same steps with a £ or € value for Oliver Bennett.

Hint: Formatting must not break SUM.

4. Write the result meaning in one plain sentence next to the cell.

Hint: Imagine explaining it to a new joiners.

5. Add a second row and fill the method down.

Hint: Double-click the fill handle if the pattern is consistent.

6. Break the example on purpose, then fix it using this lesson.

Hint: Common break: text instead of a number.

7. Apply hiding formulas and allow edit ranges to a 5-row made-up Skyline Clinics table.

Hint: Keep column headers clear.

8. Check your answer with a simple total or COUNT.

Hint: A wrong total usually means a format or key mismatch.

9. Swap ₹ and $ on one column and re-check totals.

Hint: Stored values stay the same; only the display changes.

10. List two mistakes beginners make with hiding formulas and allow edit ranges.

Hint: Look back at the tip box on this page.

11. Teach the steps out loud in under one minute.

Hint: Why → What → How.

12. Save the sheet as SkylineClin_practice.xlsx.

Hint: Use a clear file name for class.

Loading practice tasks...

  1. Input cells to unlock (Easy): How many cells are in the Monthly target column?
  2. Total target (Easy): Add up the Monthly target column.
  3. Total actual (Easy): Add up the Actual column.
  4. Highest target (Easy): What is the largest value in the Monthly target column?
  5. Overall achievement (Medium): Divide the total Actual by the total Monthly target. Round to 3 decimals.
  6. Cities below target (Medium): How many cities have a Gap below 0?
  7. Manager for a city (Medium): Find the Manager for City "Mumbai".
  8. Pages to print (Medium): If one page holds 4 data rows, how many pages do the rows need?
  9. Gap for the first city (Medium): For the first row, find Actual minus Monthly target.
  10. Best performer (Hard): Which City has the largest Actual?
  11. Status for the first city (Hard): For the first row, return "Hit" if Actual is at least Monthly target, else "Miss".
  12. Total shortfall (Hard): Add up the Gap column only where it is below 0, and show it as a positive number.

See all practice tasks in the Task Hub →

Quick revision cards

Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.

Revision card 1: What is Hiding Formulas And Allow Edit Ranges?
Card 1: What is Hiding Formulas And Allow Edit Ranges? · Download PNG
Revision card 2: Tiny example — Hiding Formulas And Allow Edit Ranges
Card 2: Tiny example — Hiding Formulas And Allow Edit Ranges · Download PNG
Revision card 3: Checklist — Hiding Formulas And Allow Edit Ranges
Card 3: Checklist — Hiding Formulas And Allow Edit Ranges · Download PNG