How to Protect an Excel Workbook or Sheet From Editing

Coding Liquids tutorial cover featuring Sagnik Bhattacharya for How to Protect an Excel Workbook or Sheet From Editing, with lock and collaboration visuals.
Coding Liquids tutorial cover featuring Sagnik Bhattacharya for How to Protect an Excel Workbook or Sheet From Editing, with lock and collaboration visuals.

To protect an Excel workbook or sheet so others cannot edit it, clear Locked on the cells people may change, then use Review > Protect Sheet to lock the rest and Review > Protect Workbook to lock the sheet structure. This is an anti-accident layer that helps stop collaborators overwriting formulas or deleting lookup sheets, not a security wall for sensitive data.

The Complete Excel Course with AI Integration

From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.

By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner

Buy now

The trick is to protect the costly parts while leaving the normal collaboration path easy. If every weekly update needs the owner to unprotect three sheets, the team will either stop updating the file or start passing around unofficial copies. A protected workbook should make the intended edit path obvious: this is where you type, this is what recalculates, this is what you should not touch.

This guide uses the modern collaboration model: store the file in OneDrive, SharePoint, or Teams, share it with the right permissions, and let people co-author in Excel for Microsoft 365 or Excel for the web. The older Shared Workbook feature still exists for legacy workflows, but Microsoft recommends co-authoring instead for current cloud files.

Quick answer

To protect an Excel workbook without breaking collaboration, share the file from OneDrive or SharePoint, unlock only the input cells collaborators need, protect each worksheet, then protect the workbook structure only if sheet tabs must not be moved, renamed, hidden, or deleted. Keep file encryption for genuinely private files, because sheet and workbook protection are not security features.

  • Use OneDrive, SharePoint, or Teams sharing for access control and co-authoring.
  • Use worksheet protection to stop edits to formula cells, headings, layout, and helper areas.
  • Use workbook protection to stop sheet-level structure changes, not to protect cell contents.
  • Use file-level encryption only when people should not be able to open the workbook without a password.

What should you decide before protecting an Excel workbook?

Before protecting an Excel workbook, divide it into input, formula, reference, report and structure zones, then pick a protection setting for each zone.

Most protection problems start because the workbook was locked before the owner decided who should edit what. Build a simple zone map first. You can do it on a hidden Admin sheet, in a comment at the top of each sheet, or in a separate handover note.

ZoneExamplesProtection choice
InputsMonthly actuals, staff names, deal stage updates, commentsUnlocked cells or editable ranges
FormulasSUMIFS totals, lookup formulas, variance calculationsLocked cells; optionally hide formulas
Reference tablesCategory lists, tax rates, stage probabilities, department codesLocked for most users; editable only by owners
ReportsDashboard cells, chart ranges, KPI cardsLocked layout; allow filters if needed
StructureSheet order, hidden helper sheets, named tabsWorkbook structure protection if tab changes are risky

Colour the zones before applying protection. For example, pale yellow input cells, white formula cells, grey helper cells, and a short note such as "Edit yellow cells only". That visual design prevents more mistakes than a password does.

How do you share a workbook so several people can edit it?

To let several people edit an Excel workbook at once, save it in OneDrive, SharePoint, or a Teams-backed document library and share it from there instead of emailing copies. Co-authoring works best when everyone edits the same cloud copy with AutoSave on.

  1. Save the workbook as an .xlsx or .xlsm file in OneDrive, SharePoint, or Teams.
  2. Select Share and choose whether collaborators can edit or only view.
  3. Ask desktop users to keep AutoSave on, or have them edit in Excel for the web if their desktop version does not support co-authoring.
  4. Avoid the legacy Shared Workbook feature for new cloud workflows. It has major feature limits and is not the same as co-authoring.

If the file contains sensitive payroll, customer, or finance data, solve access first. A protected worksheet does not stop someone who can open the workbook from copying data, taking screenshots, or working around the file locally. Use share permissions, data minimisation, and file-level encryption where appropriate.

How do you let people edit only certain cells?

To let collaborators edit only certain cells, select those cells, press Ctrl+1, clear Locked on the Protection tab, then protect the sheet.

In Excel, locked cells only matter after the worksheet is protected. Many people miss this and assume that ticking Locked on a cell immediately protects it. The usual workflow is the opposite: decide the input cells first, unlock those cells, then protect the sheet.

  1. Select the input cells collaborators should edit. For a budget sheet, that might be the monthly actuals column and the notes column.
  2. Press Ctrl+1 to open Format Cells.
  3. Go to the Protection tab.
  4. Clear Locked, then select OK.
  5. Use a visible input fill colour so collaborators can see the editable cells before they click.

If your input area is an Excel Table, unlock the cells in the editable columns and test adding a new row. Protected sheets can block row insertion unless you allow it during sheet protection. For recurring collaboration, a Table-backed input area is usually easier to maintain than a fixed rectangle such as B5:H35.

How do you lock formulas and hide them in Excel?

To lock and hide formulas in Excel, select the formula cells, open Format Cells, keep Locked ticked and tick Hidden on the Protection tab, then protect the worksheet. Formula cells should normally stay locked; add Hidden only when you do not want casual users to see the formula text in the formula bar.

A fast way to review formulas before protection:

  1. Press F5 or Ctrl+G.
  2. Select Special.
  3. Choose Formulas and select OK.
  4. Apply a formula-cell style or border so you can inspect what will be locked.
  5. Use formula auditing if the selected cells include unexpected references.

Do not hide formulas as a substitute for real security. Hidden formulas are useful for reducing accidental edits and casual confusion. They are not a safe way to protect proprietary logic if the workbook is distributed outside trusted users.

How do you protect a worksheet in Excel?

To protect a worksheet in Excel, select Review > Protect Sheet, set a password if you need one, and tick only the actions collaborators should be able to perform, sheet by sheet.

Allow optionWhen to tick itWhen to avoid it
Select unlocked cellsAlmost always, because collaborators need to reach input cellsRarely
Select locked cellsWhen users need to copy values or inspect reportsWhen selection itself causes confusion in input-only templates
SortInput tables where users sort their own rowsReports where sort order drives formulas or charts
Use AutoFilterShared trackers, dashboards, review listsSheets where filtering hides rows other collaborators need to see
Insert rowsTable-style logs that grow every weekFixed-form reports and reconciliations
Format cellsOnly for power users maintaining the templateMost shared input sheets

Store protection passwords in a password manager or controlled team note. The password is there to prevent accidental unprotecting, not to carry your security model. If only one person knows it, the workbook becomes fragile when that person is away.

When should you protect the workbook structure?

Excel's Protect Workbook setting is worth using only when the sheet tabs themselves are part of the model; otherwise leave it off and protect individual sheets.

Workbook protection is often misunderstood. It does not lock formulas inside sheets. It protects the workbook structure: adding, deleting, renaming, moving, copying, hiding, and unhiding sheets.

Use Review > Protect Workbook when hidden helper sheets, lookup tabs, or tab order are part of the workbook design. For example, a monthly management pack might have hidden sheets named Lookup_Categories, Calc_Variance, and Admin_Checks. If a collaborator unhides, renames, or deletes one of those sheets, formulas and dashboards can fail.

Do not use workbook structure protection when the team genuinely needs to add tabs during normal work. In that case, document tab-naming rules and protect only the calculation sheets.

The Complete Excel Course with AI Integration

From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.

By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner

Buy now

What does Allow Edit Ranges do in Excel?

Review > Allow Edit Ranges can unlock named ranges inside a protected worksheet. It is useful when different parts of a protected sheet need separate edit permissions, but it can become awkward in broad collaboration.

Use editable ranges when:

  • A finance lead owns assumptions while department leads own monthly inputs.
  • A template has a small maintenance range that only one person should change.
  • You are working in a Windows domain where user permissions can be resolved properly.

Avoid turning every department block into a different password-protected range unless there is a real governance need. Password sprawl makes shared workbooks harder to support. For many teams, clearer input sheets plus OneDrive or SharePoint permissions are easier to operate.

How do you test a protected workbook before sharing it?

Test a protected workbook by opening it from the shared cloud location as a second user and trying every action the team needs.

Protection is not done until you test it like a collaborator. Use a second account or ask a colleague to try the workbook while you watch.

  1. Open the workbook from the shared cloud location, not a downloaded copy.
  2. Confirm two users can edit at the same time without a "file locked" warning.
  3. Type into every intended input area.
  4. Try to overwrite a formula cell and confirm Excel blocks it.
  5. Insert a new row into any protected input Table that is meant to grow.
  6. Use filters, slicers, and dropdowns exactly as the team will use them.
  7. Close and reopen the file to confirm protection and sharing settings persist.

This test catches the painful mistakes: an input column left locked, a filter blocked by sheet protection, a table that cannot grow, or a desktop user opening the file in an old Excel version that locks out everyone else.

Worked example: monthly budget workbook

Imagine a small operations team uses one shared monthly budget workbook. Department leads enter actual spend, finance owns assumptions, and leadership views the dashboard.

SheetWho edits itProtection setup
Inputs_DepartmentDepartment leadsUnlock Actual Spend, Forecast Note, and Owner Comment; lock category names and formulas
Lookup_CategoriesFinance onlyLocked sheet; hidden; workbook structure protected
Calc_VarianceNo one directlyLocked and hidden formulas; sheet protected
DashboardViewers filter onlyLocked layout; allow slicers and filters if used

The input table has formulas such as:

=SUMIFS(tblActuals[Amount],tblActuals[Department],[@Department],tblActuals[Month],BudgetMonth)

Only the actual amount and comment columns are unlocked. The formula columns, category list, month driver cell, and dashboard cards stay locked. Leadership gets a view-only sharing link, department leads get edit access, and finance keeps the protection password in the team password manager.

The result is a workbook people can still use. Department leads do not need to ask finance to unlock the file every month, but they also cannot accidentally overwrite the variance logic that feeds the dashboard.

If you want to practise protecting sheets and locking cells with an instructor, try The Complete Excel Course with AI Integration on Coding Liquids.

Which protection option fits each need?

Use access permissions or file encryption to control who opens the workbook, worksheet protection for formulas, structure protection for tabs, and co-authoring for simultaneous editing.

NeedUse thisDo not confuse it with
Stop unauthorised people opening the fileFile-level encryption or access permissionsWorksheet protection
Stop collaborators editing formulasWorksheet protection after unlocking input cellsWorkbook structure protection
Stop sheet tabs being renamed, moved, deleted, hidden, or unhiddenWorkbook structure protectionFile encryption
Let different people edit different blocksUnlocked areas, Allow Edit Ranges, or separate input sheetsOne shared password for everyone
Let many users work at onceCo-authoring through OneDrive, SharePoint, or TeamsLegacy Shared Workbook

Common mistakes

  • Locking everything. This turns protection into a support queue. Unlock the intended input cells first.
  • Using sheet protection for sensitive data. If the person can open the workbook, assume they can see or copy the data unless stronger access controls are in place.
  • Forgetting Tables need room to grow. If collaborators add rows weekly, test row insertion on the protected sheet.
  • Protecting the workbook structure too early. If the team still changes tabs during normal work, structure protection will slow them down.
  • Keeping the password in one person's head. A shared workbook needs a shared recovery process.

Troubleshooting

1. Collaborators can still edit formula cells

Cause: the cells are unlocked, or the worksheet was never protected after the Locked setting was changed. Fix: select the formula cells, press Ctrl+1, tick Locked on the Protection tab, then use Review > Protect Sheet. Remember that cell locking does nothing until the sheet is protected.

2. Collaborators cannot type into the input cells

Cause: the input cells were left locked, the sharing link is view-only, or the user is clicking a formula column rather than the unlocked input column. Fix: unprotect the sheet, select the input cells, clear Locked, protect the sheet again, and confirm the user has edit permission on the cloud file.

3. Excel says the file is locked instead of co-authoring

Cause: someone opened the workbook in a version that does not support co-authoring, AutoSave is off, the file is on a local/network drive instead of OneDrive or SharePoint, or it is using the old Shared Workbook workflow. Fix: move the workbook to OneDrive or SharePoint, have everyone use Excel for Microsoft 365 or Excel for the web, and ask the locking user to close the unsupported copy.

4. Filters, slicers, or row inserts stopped working

Cause: the Protect Sheet options were too restrictive. Fix: unprotect the sheet, protect it again, and allow the specific actions the workflow needs: Use AutoFilter, Sort, Insert rows, or Edit objects for slicers and controls. Test each action before sharing.

5. The protection password is lost

Cause: the password was never stored in a controlled place. Fix: rebuild from a clean template or backup if needed, then store future protection passwords in a team password manager. Microsoft warns that forgotten file-level passwords cannot be recovered, so treat file encryption passwords with extra care.

The Complete Excel Course with AI Integration

From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.

By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner

Buy now

FAQ

Is workbook protection the same as file encryption?

No. Workbook protection protects the structure of the workbook, such as sheet tabs, from being added, deleted, renamed, moved, hidden or unhidden. File encryption decides who can open the file at all. Worksheet protection controls what users can do inside a sheet. They solve different problems, so you often need more than one.

Can people co-author a protected Excel worksheet?

Yes, if the workbook is stored in a supported cloud location such as OneDrive or SharePoint and users are on a co-authoring-capable version of Excel. Protection still applies inside the worksheet: collaborators can edit unlocked cells and are blocked from locked cells. Test with a second account before sharing the link.

Should I use the old Shared Workbook feature?

Usually no for new workbooks. Microsoft positions co-authoring as the replacement for Shared Workbooks, especially when the file lives in OneDrive or SharePoint. The legacy feature still exists for older workflows, but it has major feature limits and is not the same as co-authoring.

Can sheet protection stop someone seeing my formulas?

Partly. It can hide formulas from casual viewing when the formula cells are marked Hidden and the sheet is protected. It should not be treated as a serious security control for proprietary logic, especially if the workbook is distributed outside trusted users. Use access permissions or encryption for anything sensitive.

What should I protect first in a shared Excel file?

Protect formulas, lookup tables, report layouts and hidden helper sheets first. Leave clearly marked input cells unlocked so collaborators can complete the work they were invited to do. Then test the protected sheet as a collaborator would, including filters and any table that needs to grow, before you share the link.

How do I protect an Excel workbook or sheet so others cannot edit it?

Clear Locked on the cells people may edit, then select Review > Protect Sheet to lock the rest and Review > Protect Workbook to stop sheet tabs being changed. Add a password if you need one, and give view-only sharing access to anyone who should not edit at all. Treat this as protection against accidents, not security.

Related tutorials

Sources