Unlock the Cells You Want Editors to Change

By default, every cell in Excel is locked with a "locked" property, but that property only takes effect when you switch on sheet protection. So the first job is to unlock the input cells — the ones your team or clients should be able to type into.

Highlight the cells that should stay editable — for example, the cells for entering new sales data. Right-click the selection and choose Format Cells. Go to the Protection tab. Untick Locked. Click OK.

Do this before you protect the sheet. If you try to unlock cells after protection is on, Excel will block you in most cases.

Hide Formulas You Don’t Want Visible

If your worksheet has formulas that calculate totals or margins, you might not want people to see them. Hiding the formula before protection makes them invisible in the formula bar.

Select the cells that contain your formulas. Right-click and choose Format Cells. On the Protection tab, tick both Locked and Hidden. Click OK.

Remember that cells with a formula are locked by default, so you only need to add the Hidden tick. When the sheet is protected, the formula itself stays out of the formula bar and the cell just shows its result.

Open the Protect Sheet Panel

With your editable cells unlocked and your formulas hidden if needed, you are ready to switch on protection. Go to the Review tab on the Excel ribbon and click Protect Sheet.

In the dialog box that appears, you’ll see a password field and a long list of checkboxes. The list tells Excel what actions people can still perform even with the sheet protected — actions like Select unlocked cells, Sort, Use AutoFilter, or Format rows.

Do not tick the very top option that says "Protect worksheet and contents of locked cells" — that box should stay ticked. That’s the core setting that does the protecting.

Choose What Editors Can Still Do

For most sales, planning, or project trackers, you want editors to click into the unlocked cells and type. The default setting — Select locked cells and Select unlocked cells — is usually enough.

If your worksheet has filters or a table with dropdowns, tick Use AutoFilter. If people need to sort rows by date or amount, tick Sort. For formatting changes like font size or column width, tick the specific formatting options.

Keep the list as short as your workflow allows. Every tick you add is another way someone can inadvertently mess up the layout or overwrite a formula.

Set a Strong Password (and Keep It Safe)

At the bottom of the Protect Sheet dialog box, you’ll see the password field. Type a password you haven’t used elsewhere. Excel asks you to enter it twice to confirm.

Password protection on a worksheet is not encryption. It stops casual editing, but an expert can unprotect the sheet without the password, especially in older Excel files. Because of that, don’t rely on this password for serious confidentiality — use file-level encryption on the whole workbook instead.

Click OK and then save your file. Your worksheet is now protected.

Test Your Protected Worksheet

Before you send the file to your team, test it yourself. Click on an unlocked cell and type — it should accept your input. Then click on a locked cell, like a formula total, and Excel should refuse to let you edit.

Try a few actions you ticked, like sorting a column or using a filter dropdown. If you ticked "Select locked cells", you are allowed to click a locked cell to read it — you just can’t type in it. If you did not tick that option, clicking a locked cell gives you a warning message.

If something feels wrong — a needed input cell is locked, or sorting behaves oddly — go to the Review tab and click Unprotect Sheet. Enter your password, fix the setup, and protect again.

Allow Editing for Specific Users (Advanced)

If you want more control, you can allow certain people to edit specific ranges even while the whole sheet is protected. On the Review tab, click Allow Edit Ranges.

In the window that opens, click New. Give the range a name, select the cells, and set a password for that range. Anyone who knows that range’s password can edit those cells without unlocking the rest of the sheet.

This is useful when you have multiple departments sharing one workbook — one range for finance, another for operations — and you want each to edit only its own area.

I Can’t Edit Any Cells After Protecting

This almost always means you forgot to unlock the cells before turning on protection. Unprotect the sheet, revisit each editable cell, open Format Cells, and untick the Locked box. Then protect again.

Another cause: you selected the whole sheet before protecting, which can confuse things. Work on the specific input cells only.

Excel Says the Sheet Is Protected and I Can’t Unprotect It

If you know the password, go to the Review tab and click Unprotect Sheet and type your password. If you don’t know it, you have limited options. In older files (.xls or .xlsx with an older protection scheme), a determined person can remove the protection without the password, but this is not reliable and not something we recommend you rely on.

Your best route is to recover from a backup copy of the file. That’s why the backup advice at the start matters.

Sorting Messes Up My Protected Sheet

Sorting is dangerous on any worksheet, but protection makes it worse because you can’t always see the whole data range. If you ticked Sort when protecting, rows can move and break formula references.

Remove Sort from the allowed actions when you protect the sheet, or use a table (Insert → Table) so Excel always sorts the whole table as one unit. Tables behave better with AutoFilter and sorting than bare ranges.

I Can’t See the Formulas Even Though I Want To

If you ticked Hidden on the cells that contain formulas, they are now invisible in the formula bar. To reverse that, unprotect the sheet, select the formula cells, open Format Cells, go to Protection, untick Hidden, and protect again.

Keep in mind that hiding formulas is a cosmetic difference — it does not make the formula secure. Someone who really wants to see it can copy the workbook or unprotect it, so treat hidden formulas as a convenience, not security.

My AutoFilter Is Not Working on the Protected Sheet

You need to tick Use AutoFilter in the Protect Sheet dialog. If you tick the setting but the filter arrows still look grey, check your data doesn’t have merged cells in the header row. Merged headers break Excel’s filter logic.

Unprotect the sheet, unmerge any header cells, and re-protect with Use AutoFilter ticked.

Use the Hidden Setting on the Whole Sheet as a Layer

Beyond hiding specific formula cells, you can hide entire sheets. Right-click the sheet tab and choose Hide. Then protect the workbook so nobody can unhide it. On the Review tab, click Protect Workbook and tick the structure box.

This is useful for reference data, lookup tables, or price lists that support your main worksheet. It also means those sheets won’t show up in the tab bar at the bottom, keeping the file cleaner.

Record a Macro to Speed Up Re-Protecting

If you regularly make a copy of your master file and need to protect the new copy each time, record a macro. Go to the View tab, click Macros, then Record Macro. Perform your protect-sheet steps, then stop recording.

Give the macro a name like "ProtectReport" and assign it to a button on your Quick Access Toolbar. The next time you create a copy, run that macro and the protection is applied in one click.

Combine with Workbook Protection for Full Structure Lock

Worksheet protection locks the content inside a tab. Workbook protection locks the structure — whether someone can add, move, delete, or rename sheets. Use both together for a fully locked workbook.

On the Review tab, click Protect Workbook and tick the box that says Structure. You can also add a password here. This stops people from duplicating your sheet or inserting a new tab to bypass protections.

• I have a backup copy of the workbook saved before I started.
• I unlocked every cell that users need to edit, using Format Cells → Protection → untick Locked.
• I hid the formulas in the cells I don’t want users to see — if that matters for my workflow.
• I opened the Review tab, clicked Protect Sheet, and ticked only the actions my team needs — no more.
• I entered a strong, memorable password and stored it in a password manager.
• I tested the protected sheet: typed in an unlocked cell, tried to edit a locked cell, and used the filter/sort options I allowed.
• I checked whether Allow Edit Ranges is needed for specific users and added it using per-range passwords.
• I considered whether I also need workbook structure protection so nobody can add, move, or delete my tabs.
• I saved the file and confirmed the protection is active by closing and reopening the workbook.