Lock up your Excel worksheet or cells!

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

How to lock your Excel worksheet from editing – the whole thing or some cells.  Allow some people to edit a few cells, leaving the rest of the cells and formulas untouchable by others using the worksheet.

Here’s a very simple example:

lock up your excel worksheet or cells microsoft office 8648 - Lock up your Excel worksheet or cells!

Anyone opening the worksheet can enter a US dollar amount to see the latest equivalents in various currencies.  But they can only enter an amount and see the results, they can’t change the formulas and code behind the visible cells.

Read only x 2

There’s two ways to make a Word document ‘Read only’ – meaning that it can’t be edited, only viewed.

Windows

Any file can be marked as ‘Read only’ at the system level.  This means the file can be opened and viewed but no changes can be made to the file.

To do that, close the document in Office.  In Windows Explorer, right-click on the file, choose Properties | General and check the ‘Read-only’ box.

lock up your excel worksheet or cells microsoft office 8649 - Lock up your Excel worksheet or cells!

If you open a ‘Read only’ document in Office, you can make changes but if you try to save you’ll be presented with a Save As dialog to save under a different name.  In the title bar you’ll see “(Read-Only)” after the document name.

This applies to any file and any program in Windows.

Save As options

If you want people to view the worksheet but not edit it, consider ‘Save As’ to another format, most likely PDF.

This doesn’t work well for large worksheets because the PDF version of a worksheet is harder to navigate.

Inside Excel

The Protection options in Excel are on the right side of the Review tab

lock up your excel worksheet or cells microsoft office 8650 - Lock up your Excel worksheet or cells!

You can protect any part of a workbook:

  • Whole Workbook
  • Individual Sheets

You can unlock selected parts of a worksheet to allow them to be edited but it’s done differently in Excel than in Word.

It’s quite possible to lock an entire workbook then unlock a single cell for editing.  For example, a complex loan calculation where the end-user can enter the loan amount only.  We’ll show how to do that later in this article.

Before you start

Before you lock up a workbook or sheet, make sure the viewing settings are suitable.  In particular, Freeze Panes.

Whole Workbook

Go to Review | Protect Workbook

lock up your excel worksheet or cells microsoft office 8651 - Lock up your Excel worksheet or cells!

The password is optional.  With no password the protected workbook can be unlocked and edited with a click of the mouse.

Sheet

Protecting at the sheet level gives you a lot more options than locking the entire worksheet.

Choose Review | Protect Sheet or right-click on the sheet tab and choose Protect Sheet.

lock up your excel worksheet or cells microsoft office 8652 - Lock up your Excel worksheet or cells!

As you can see, there are options to permit various types of editing on a sheet-wide basis.

lock up your excel worksheet or cells microsoft office 8653 - Lock up your Excel worksheet or cells!

  • Select locked cells
  • Select unlocked cells
  • Format cells
  • Format columns
  • Format rows
  • Insert columns
  • Insert rows
  • Insert hyperlinks
  • Delete columns
  • Delete Rows
  • Sort
  • Use AutoFilter
  • Use PivotTable and PivotChart
  • Edit objects
  • Edit scenarios

Locked and Unlocked cells

Before we continue, we need to talk about cells – locked and unlocked.   This has been in Excel for a long time and the source of much confusion.

It might seem that you can only Protect entire sheets because when you choose Protect for a worksheet, the entire sheet becomes Read-only.  In fact, you can make individual cells editable using the Lock status of each cell.

Every Excel cell has a ‘lock’ status – locked or unlocked.    But the lock status has no effect until you Protect the sheet or workbook.    Only when Protected does the locked/unlocked state make any difference.

All Excel cells start in a ‘locked’ state (which is what confuses people).  With all cells set to ‘locked’ the entire sheet will be read-only if you choose to Protect that sheet.

Change specific cells to ‘Unlocked’ so they are still editable when the sheet is Protected.

Here’s a simple example.  The white cells have been unlocked and so they are editable when the sheet is locked.  All the other cells are locked (the default) and can’t be changed.

lock up your excel worksheet or cells microsoft office 33678 - Lock up your Excel worksheet or cells!

The cell colors don’t effect the locked/unlocked state, that’s just to make it clear to users where they can edit and where they can’t.

There are various places to set or check the lock status of cells.  Right-click on a cell and choose Properties | Protection

lock up your excel worksheet or cells microsoft office 8655 - Lock up your Excel worksheet or cells!

Or go to Home | Cells | Format and check the pull-down list.

lock up your excel worksheet or cells microsoft office 8656 - Lock up your Excel worksheet or cells!

If you’re checking Locked/Unlocked cells a lot, add the Lock Cell button to the Quick Access toolbar.

lock up your excel worksheet or cells microsoft office 8657 - Lock up your Excel worksheet or cells!

Styles – the locked/unlocked property can be set in a Cell Style, along with all the visible cell attributes like color etc.

Putting it all together

To make a worksheet fully ‘read-only’ is simple – just right-click on the sheet tab and choose Protect Sheet.  Add an optional password and it’s done.  Because all cells are ‘locked’ by default, the entire sheet will be protected from editing.

To allow some cells to be editable with others ‘read only’ – choose the cells that you want editable and change the cell to ‘Unlocked’.  Once you have the right mix of locked and unlocked cells then Protect the sheet.

Tip:  put some help text and change the background color of editable/unlocked cells so the cells to change are obvious to people using the worksheet.

subs profile e1563205311409 - Lock up your Excel worksheet or cells!
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address