Spreadsheets

Allow Users to Edit Ranges in Excel: let family add entries only

Use Allow Edit Ranges in Excel so family can type expenses in one entry area while your formula columns stay locked, and learn what the password protects.

Editorial illustration for Allow Users to Edit Ranges in Excel: let family add entries only

To let family add entries but nothing else, choose Review > Allow Edit Ranges, add the entry area as a range, then switch on Review > Protect Sheet. Every cell is locked by default, so after you protect the sheet only the range you listed can be typed in, and your formula columns stay untouched.

How do I set up an editable entry area?

Say your sheet has Date, Category, Amount and Mode in columns A to D for entries, and formulas in columns F to H. You want family to type in A to D only.

  1. Make sure the sheet is not protected yet. The Allow Edit Ranges button is not usable on a protected sheet.
  2. Select A2:D500, the area where family will type.
  3. Choose Review > Allow Edit Ranges, then New. Give it a title such as Entry area. The Refers to cells box shows your selection.
  4. Leave the range password blank if you do not want one, or set one (the next sections say what it does).
  5. Press OK, then Review > Protect Sheet. Tick only the choices you want to allow, such as selecting unlocked cells, and press OK.

The editing happens in the desktop version of Excel. Excel for the web and the phone apps may not let you create these ranges, so set it up on a computer first, then open it elsewhere to test.

Why are my formula columns already locked?

Every cell in a new sheet has a Locked setting switched on. It does nothing until the sheet is protected. Once you protect the sheet, locked cells cannot be changed, and the range you opened with Allow Edit Ranges is the exception. If you also want one formula cell to stay open, select it, press Ctrl+1, go to the Protection tab, and untick Locked before protecting.

How do I test it as another user?

Do not assume it works; try to break it.

  • Type a number in A2. It should be accepted.
  • Click a formula cell in G2 and type over it. Excel should refuse with a message that the cell is protected.
  • Try to insert a column or delete a row. The result depends on what you ticked in the Protect Sheet box.
  • Ask a family member to open the file on their device and try the same three things. If a range password is set, they will be asked for it when they first type in the range.

What does the password actually protect?

PasswordWhat it asks forWhat it does not do
Range passwordAsked when someone first edits that rangeDoes not stop someone who knows how to remove protection
Sheet protection passwordAsked when someone tries to remove protectionDoes not hide the data; people can still read the sheet
File open passwordAsked when the file is opened, set under File > Info > Protect WorkbookIs a different feature, and a forgotten one may be unrecoverable

The first two are meant to prevent mistakes. They are not strong security, and a protected sheet is not a safe for sensitive figures. If the sheet holds details you would not want a stranger to read, rely on the file open password and on who the file is shared with, and keep a note of that password somewhere a trusted person can find it.

What if the family wants to add rows?

Entries beyond row 500 are outside the range you set. Either make the range large enough, say A2:D2000, or turn the entry area into an Excel table (Insert > Table) before you protect the sheet. Protected sheets can restrict table growth, so test a new row at the bottom before you hand it over.

Does protection stop them deleting the whole file?

No. Protection works inside the sheet. Anyone with access to the file in OneDrive or on a device can still delete the file itself, or save a copy of it. Keep a backup copy somewhere only you control, and if the file is in OneDrive, remember that Version History can bring back earlier versions.

Before you hand it over

  1. Formula columns are locked and look right.
  2. The entry area is the only place a typed value is accepted.
  3. A family member has tested it on their own device.
  4. A copy of the file is saved outside the shared folder.
  5. Everyone knows which cells are for them, perhaps from a shaded colour on the entry area.

Locked sheets need upkeep. If constant manual entry becomes a chore for the family, remember that bank statements can be uploaded to an app instead. SpendixAI takes PDF, CSV or Excel statements and offers manual entry for cash, though it keeps one personal workspace and is not a shared household sheet.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports