How do I lock a number in an Excel formula?

Tips, Tips & ShortcutsHow to Lock Cells for Editing and Protect FormulasApril 4, 2019Jon Acampora35 commentsBottom Line: Learn how to lock individual cells or ranges in Excel s

How do I lock a number in an Excel formula?

Tips, Tips & Shortcuts

How to Lock Cells for Editing and Protect FormulasApril 4, 2019Jon Acampora35 comments

Bottom Line: Learn how to lock individual cells or ranges in Excel so that users cannot change the formulas or contents of protected cells. Plus a few bonus tips to save time with the setup.

Skill Level: Beginner

Video TutorialWatch on YouTube & Subscribe to our Channel

Download the Excel File

You can download the file that I use in the video tutorial by clicking below.Lock-Cells-and-Sheet-Protection-Quote-Example.zipDownload

Protecting Your Work from Unwanted Changes

If you share your spreadsheets with other users, you've probably found that there are specific cells you don't want them to modify. This is especially true for cells that contain formulas and special formatting.

The great news is that you can lock or unlock any cell, or a whole range of cells, to keep your work protected. It's easy to do, and it involves two basic steps:

  1. Locking/unlocking the cells.
  2. Protecting the worksheet.

Here's how to prevent users from changing some cells.

Step 1: Lock and Unlock Specific Cells or Ranges

Right-click on the cell or range you want to change, and choose Format Cells from the menu that appears.

Format Cells Right Click Menu

This will bring up the Format Cells window (keyboard shortcut for this window is Ctrl + 1.). Choose the tab that says Protection.

Next, make sure that the Locked option is checked.

Format Cells Window to Lock Cells

Locked is the default setting for all cells in a new worksheet/workbook.

Once we protect the worksheet (in the next step) those locked cells will not be able to be altered by users.

If you want users to be able to edit a particular cell or range, uncheck the Locked box so they are unlocked. Since cells are locked by default, most of the job will be going through the sheet and unlocking cells that can be edited by users.

Uncheck the Locked Box to Unlock Cells or Range

I share some shortcuts to make this process faster in the Bonus section below.

Step 2: Protect the Worksheet

Now that you've locked/unlocked the cells that you want users to be able to edit, you want to protect the sheet. Once you protect the sheet, users cannot change the locked cells. However, they can still modify the unlocked cells.

To protect the sheet, simply right-click on the tab at the bottom of the sheet, and choose Protect Sheet from the menu.

Protect Sheet Right Click Menu

This will bring up the Protect Sheet window. If you want your sheet to be password protected, you have the option of entering a password here. Adding a password is optional. Click OK.

Protect Sheet Window

If you've chosen to enter a password, then you will be prompted to verify your entry after you've clicked OK.

With the sheet protected, users will be unable to change the cells that are locked. If they try to make changes, they will get an error/warning message that looks like this.

Error message for Protected Sheet

You can unprotect the sheet in the same way that you protected it, by right-clicking on the sheet tab. An alternative way to protect and unprotect sheets is by using the Protect Sheet button in the Review tab of the Ribbon.

Protect Sheet Button on Review Tab

The button text displays the opposite of the current state. It says Protect Sheet when the sheet is unprotected, and Unprotect Sheet when it is protected.

It's important to note that all cells can be edited when the sheet is unprotected. After making changes you must protect the sheet again and Save the workbook before sending or sharing with other users.

3 Bonus Tips for Locking Cells and Protecting Sheets

As you can see, it is fairly simple to protect your formulas and formatting from being changed! But I'd like to leave you with three tips to help make it faster & easier for both you and your users.

1. Prevent Locked Cells From Being Selected

This tip will help make it faster and easier for your users to input data in the sheet.

Turning off the Select locked cells option prevents the locked cells from being selected with either the mouse or keyboard (arrow or tab keys). This means users will only be able to select the unlocked cells that they need to edit. They can quickly hit the Tab, Enter, or arrow keys to move to the next editable cell.

To make this change, you just uncheck the option that says Select locked cells on the Protect Sheet window.

Uncheck box in Protect Sheet Window

After pressing OK, you will only be able to select the unlocked cells.

2. Add a button for locking cells to the Quick Access Toolbar

This allows you to quickly see the locked setting for a cell or range.

Lock Button on QAT to See Change Locked Property for Cell Range

From the Home tab on the Ribbon, you can open the drop-down menu under the Format button and see the option to Lock Cell.

Lock Cells Ribbon Menu Option

If you right-click on the Lock Cell option, another menu appears giving you the option to add the button to the Quick Access Toolbar.

Add to Quick Access Toolbar

When you select this option, the button will be added to the Quick Access Toolbar at the top of the workbook. This button will remain each time you use Excel. You can easily lock and unlock specific cells on your sheet by clicking on this button.

Add Lock Cell Button to Quick Access Toolbar

You can also see if the active cell locked or unlocked. The button will have a dark background if the selection is locked.

It's important to note that this only shows the locked state of the active cell. If you have multiple cells selected, the active cell is the cell you selected first and appears with no fill shading.

Mixed Lock State

If you select a range that contains both locked and unlocked cells, you will see a solid box for the Locked checkbox in the Format Cells window. This denotes the mixed state.

You can click the checkbox to lock or unlock ALL cells in the selected range.

3. Use different formatting for locked cells

By changing the formatting of cells that are locked, you give your users a visual clue that those cells are off limits. In this example the locked cells have a gray fill color. The unlocked (editable) cells are white. You can also provide a guide on the sheet or instructions tab.

Formatting locked cells

You might be wondering where I found this template for a quote. I got it from the template library. You can access the library by going to the File tab, choosing New, and using the search word quote.

You can find all sorts of useful templates there, including invoices, calendars, to-do lists, budgets, and more.

Conclusion

By locking your cells and protecting your sheet, you can keep your formulas safe from tampering by other users, and prevent mistakes.

I hope this simple tutorial proves helpful to you. Please leave a comment below if you have any tips or questions about locking cells, protecting sheets with passwords, or preventing users from changing cells.

Thank you! Previous Gridlines & Freeze Panes Settings Lost in New Window  How to Fix ItNext View Two Sheets Side-by-Side in the Same Workbook

You may also like

Automatic Data Conversion

New Excel Feature: Automatic Data Conversion for Numbers

XLOOKUP Shortcuts

Shortcuts for Writing XLOOKUP and VLOOKUP Formulas

File Explorer Shortcuts

12 Keyboard Shortcuts for Windows File Explorer

Numbered Lists

4 Ways to Create Numbered Lists in Excel

Wanda says:April 5, 2019 at 6:54 am

Loving the Elevate course. Very helpful. Have a question that I suspect I cant get around, but doesnt hurt trying.  I have headers for our columns in a very lengthy spreadsheet. I have locked cells, which works fabulous. I cant lock the headers, because the participants need to be able to Sort and Filter columns when required. I want to lock the headers, because it affects an automated Power Query report we do. Headers (name of column) cant be changed, but they do need to sort/filer columns. Is there any way I can do this? Thank you in advance for any help you can provide.Reply

Jon Acampora says:April 5, 2019 at 11:45 am

Hi Wanda,
Thanks for the nice feedback. Im happy to hear youre enjoying Elevate.

Great question! Yes, this is possible. Its a few steps to setup.

Youll need to use the Allow Edit Ranges feature on the Review tab.
Add the entire table as a new range.
Then protect the sheet with the following settings:

Uncheck Select Locked Cells
Check Sort
Check Use AutoFilter

Here is a good article I just found that explains more about it.

I hope that helps. Thanks again and have a nice weekend! Reply

Erik says:April 5, 2019 at 8:13 am

Great article. Two questions:

What software do you use for the ripped effect in your screenshots?

How does cell protection work with tables, i currently have a table with several columns that have formulas. Whenever i lock the columns, unlock the rest of the sheet and allow inserting rows, the formulas do not populate down in the new row?

Cheers

ErikReply

Jon Acampora says:April 5, 2019 at 11:50 am

Thanks Erik!

I use Snag-it by Techsmith for the screenshots, and it has that ripped effect in the Editor. Its called Cut Out and they have quite a few different effects/patterns to choose from.

Great question on Tables. This is still a limitation of Excel and the only way around it that I know of is to use a macro to add the rows and copy the formulas down. Here is a suggestion by my good friend Zack Barresse on Excel Uservoice about the issue.

https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/16452913-get-tables-working-on-protected-sheets-add-rows

You can add a vote there to help get this fixed.

I hope that helps. Thanks again and have a nice weekend! Reply

Erik says:April 8, 2019 at 1:03 pm

Thank for the response Jon, will have a look at Snag-IT and of course the url provided!

Keep up the good work!Reply

Anowarul Alam says:April 13, 2019 at 12:33 am

You have described the steps nicely and effectively.
Every week I learn new new steps about MS excel operation through your website and email notifications.
Many thanks.Reply

Maya says:April 18, 2019 at 8:44 am

I just wanted to take a minute and say thank you for all you share.
All your videos are very easy to understand and follow. I always come back for more.
Cheers
MayaReply

Mark says:April 28, 2019 at 3:37 pm

Hey Jon  I like your tip of adding the Locked Cells setting to the QAT, thats a good little time saver.

Ive written a macro to automatically select all the unlocked cells, it is a useful final check to ensure the right cells are locked or unlocked.
https://exceloffthegrid.com/how-to-select-all-unlocked-cells-in-excel/

Thanks,
MarkReply

Venkanna says:May 12, 2019 at 6:07 am

Dear sir,

I am VENKANNA.thank you very much sir for lock cell.
But I want to remaining cells working but PROPERTIES not working sirI want enable remaining cells properties.please sir help me
Thanking you sirReply

Robert M. Ward says:September 5, 2019 at 2:58 am

These tips are very important. I prefer to Protect the Worksheet from all tips. Carry on.Reply

Pushpanjalee says:October 17, 2019 at 5:58 am

Really helpful this tutorial.,,,Reply

Frank Edward says:November 29, 2019 at 8:10 pm

If you want to share your excel sheet for the many people, you can use the lock formula in excel. Because, if you share your excel file with the lock formula, other user does not changing anything in your excel file.Reply

xavier fernandes says:December 21, 2019 at 8:20 am

I had prepared a workbook with password, but after two years I forgot the password.
How to unlock the workbook. At present I cannot insert a new worksheet or rename the worksheet.Reply

xavier fernandes says:December 21, 2019 at 8:21 am

Its excel 2007Reply

Opio Ronny says:April 7, 2020 at 5:53 am

Thanks for this tutorial it really helped me.Reply

Maria says:July 23, 2020 at 6:31 pm

Thanks. I looked at a couple of tutorials and didnt manage to follow. This was very clear and I got the job done in a minute!Reply

Dennis says:August 12, 2020 at 11:28 am

This is among the VERY BEST tutorials Ive ever seen! Explanation, graphics, and progression are exceptionally clear. THANK YOU!Reply

Sam says:August 31, 2020 at 3:31 am

Hi your blog is nice and well explain can you answer on this, I am trying to add an IF & AND formula and showing the value in % since my data is coming from other sheet (Test) for some value instead of % show round numbers it is show value i.e .3333

How to use round function together with this formula?

Here is my formula.
=IF(AND(Test!B5=),0%,(SUMPRODUCT((Test!$A$4:$A$153=Sam),Test!B$4:B$153))*100&%&IF(Test!B152=,, & &(1+ABS(((RIGHT(Test!B152,LEN(Test!B152)-SEARCH(-,Test!B152)))-LEFT(Test!B152,SEARCH(-,Test!B152)-1))))&AL))Reply

Lya says:October 7, 2020 at 2:35 pm

Very HelpfulReply

milly says:October 27, 2020 at 9:13 am

hi i have tried locking the cells that u dont want anyone to edit & unlocked the cells that people are okay to edit, however when i protect the sheet it wont let me hide or unhide columns? how do i lock the formula cells but still hide & unhide all columns please?Reply

Nate says:October 29, 2020 at 12:29 pm

Is there a way to keep a workbook protected each time it is opened? Without having to protect it each time before saving and closing it?Reply

JERIN says:January 6, 2021 at 1:04 am

thnks, very useful this videoReply

Stanley Thomas says:January 24, 2021 at 10:05 pm

I like that you quickly go over the concept within excel as well as the steps. change a lock
Reply

Samuel Sisungo says:February 2, 2021 at 11:25 pm

This has greatly helped me to know how to keep my hard work safe and complete my project in time.Reply

Elouise Burton says:February 5, 2021 at 6:05 pm

Very good video, I have been trying to figure this for a good while!Reply

Emmanuel Odota says:February 19, 2021 at 10:22 am

When I use tables and lock one cell, all cells are locked. How do i solve thisReply

Ahmed says:March 30, 2021 at 1:36 am

Thanks indeed. that was so helpful. Subbed to the YouTube Channel.Reply

lim says:April 14, 2021 at 1:10 am

goodReply

Muhammad Shahab Yousaf says:April 30, 2021 at 5:00 am

Thanks !! Really helpfulReply

SATHI says:June 22, 2021 at 12:15 am

BUT WITH THE MERGED HEADER CELLS WE CANT DO LIKE THISReply

sukron jazuli says:September 27, 2021 at 3:19 am

thank you, very help meReply

lucky says:November 23, 2021 at 6:22 am

wow, thanks to you. This article is helpful, God bless you.Reply

bell says:April 6, 2022 at 10:31 am

I am reading this article about really too good information and help us infromation thanks sharing this amazing and lovely blogReply

Gill says:May 7, 2022 at 2:18 am

Thank you  very helpful, without sounding too technical Reply

Brian says:June 15, 2022 at 9:02 am

Thanks!Reply

More results...   Generic filters Hidden labelExact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.Join Our Free Newsletter

Free Excel Training Webinar Modern Power Tools

Video liên quan