Excel Functions Tutorials
- Excel Tools
- Excel Ribbons and Tabs
- Quick Access Toolbar in Excel (QAT)
- "Save As" Shortcut in Excel
- Accounting Number Format in Excel
- Add-Ins in excel
- Add Filter in Excel
- Advanced Filter in Excel
- Auto Filter In Excel
- Auto Format Excel
- AutoFill in Excel
- Analysis ToolPak in Excel
- ANOVA in Excel
- Border in Excel
- Checkbox in Excel
- Check Mark in Excel (? Tick Symbol)
- Combo Box in Excel and VBA
- Conditional Formatting in Excel
- Conditional Formatting with Formulas
- Conditional Formatting for Blank Cells
- Conditional Formatting Based on Another Cell Value
- Conditional Formatting in Pivot Table
- Consolidate Data in Excel
- Comma Style in Excel
- CSV vs Excel
- Data Bars in Excel
- Data Table in Excel
- Data Validation Excel
- Data Model in Excel
- Developer Tab in Excel
- Descriptive Statistics in Excel
- Dynamic Named Range in Excel
- Drawing in Excel
- Excel Fill Handle
- Excel Fill Down
- Error Bars in Excel
- Excel Forms for Data Entry
- Excel Tables
- Excel Power View
- Exponential Smoothing in Excel
- Filters in Excel
- Flash Fill in Excel
- Freeze Panes in Excel
- Freeze Columns in Excel
- Freeze Cells in Excel
- Format Painter in Excel
- Shortcut for Format Painter in Excel
- F-Test in Excel
- Goal Seek in Excel
- Gridlines in Excel
- Heat Map in Excel
- 3D Maps in Excel
- Header and Footer in Excel
- Insert Button in Excel
- Insert / Draw Line in Excel
- Insert Function in Excel
- List Box in Excel VBA
- Lock Cells in Excel
- Macros in Excel
- Enable Macros in Excel
- Merge and Center in Excel
- Merge Cells in Excel
- Merge Tables in Excel
- Name Box in Excel
- Name Range in Excel
- Null in Excel
- One Variable Data Table in Excel
- OneDrive Excel
- Protect Workbook in Excel
- Pivot Table in Excel
- Pivot Table Examples
- Pivot Table Filter
- Pivot Table Slicer
- Paste Special in Excel (With Top 10 Shortcuts)
- Quick Analysis Tools in Excel
- Radio Button in Excel
- Recording Macros in Excel
- Regression Analysis in Excel
- Scenario Manager in Excel
- Scroll Bars in Excel
- Scroll Lock in Excel
- Slicers in Excel
- Solver in Excel
- Sort by Color in Excel
- Sort by Number in Excel
- Sort Data in Excel
- Sparklines in Excel
- Spell Check in Excel
- Split Panes in Excel (Horizontally, Vertically, Cross Split)
- Status Bar in Excel
- Text to Columns in Excel
- Timeline in Excel
- Toolbar on Excel
- Track Changes in Excel
- Trend Line in Excel
- Two-Variable Data Table in Excel
- Watch Window in Excel
- Wrap Text in Excel
- XML in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tips (178+)
- VBA (162+)
We lock cells in excel to protect them so that there isn’t any unwanted changes in the cell, by default all the cells in excel are locked which can be seen by right click on the cell and then click on format options which will open a wizard box for us, now click on protection tab and we can see the option for locked cells, to protect a cell after locking it we need to put a protection by protect worksheet option.
Lock cells in Excel (Table of Contents)
How to Lock Cells and Protect Formulas in Excel?
Formulas in Excel are easy to create and edit. A single formula in an Excel worksheet can be very important. Though it is easy to edit a formula, any accidental change in the formula can lead to completely different and erroneous results. When the Excel sheets travel from one person to another, the chances that a person hit a deleterious key (such as backspace, delete, alphabet or number key) on formula while going through it also increases. Thus, it is important to secure your Excel worksheet from any change. To address such issues, Excel has incorporated Lock and protect functions which prevent other users to do any further change (deleting or overwriting) in an Excel worksheet.
How to Lock Cells in Excel?
Let us learn how to lock cells in excel 2016 with some examples.
Lock Cells in Excel – Example #1
Suppose you have a worksheet in which there are three values v1, v2, and v3 and you to calculate the average of the three values.
Cell E5 contains the formula for calculating the average. Now you want to lock the cell in excel and protect this formula.
Since all the cells in Excel are locked by default, let us first unlock all the cells in excel.
To do this, first, select all the cells using Control + A (or Command + A).
Then open the Format Cells dialog box by pressing Control + 1 (or Command + 1).
Under the Protection Tab, uncheck the Locked option and click OK.
All the cells in the excel worksheet will be unlocked.
Now, select the cell containing the formula and press Control + 1 to open the Format Cells dialog box. Under the protection tab, check Locked option.
Then, click OK to lock cell formula in excel. Now, you need to protect the formula. To protect the cell in excel, go to Review Tab and click on Protect Sheet or Sheet (Highlighted below).
You can optionally give a password. Click OK.
Now the formula cell in excel is locked and protected. If you try to change the cell, a window will pop-up as shown below.
You will find that only this particular cell is protected. If you try to overwrite any other cell, no message or warning appears. This is because only the locked cells in excel are protected.
A locked cell in excel or formula cannot secure the cell from further overwriting unless it is protected. So, to protect a formula from any change, you need to lock the cell formula in excel and then protect it. It is important to note that by default all cells in a worksheet are locked. Therefore, if you want to protect only the formula cells, you need to unlock all the other cells and then protect the formula cells.
Lock Cells in Excel – Example #2
Suppose you have the sales data for different products obtained in different zones as shown below. You are supposed to calculate the total sales of each product separately and identify the product with the highest sales. You are also supposed to lock a cell in excel and also protect the formula for calculating the highest sales, however, the sales data and its sum can be editable.
Let us first calculate the total sales for each product. To do this, use the syntax:
=SUM(C3:G3) in cell H3.
Press ENTER and then drag it to rest of the cells.
To identify the product with the highest sales, use the index formula:
This will give the product with the highest sales in the cell K6.
Now, to protect this formula cell from any further change, you need to first unlock all the cells in excel sheet. To do this, press Control + A and then press Control + 1. This will open the Format Cells dialog box.
Now, uncheck the Locked option under the Protection Tab. Now, select the cell K9 containing the formula and again press Control + 1. Under the Protection Tab check the Locked option and click OK. This will lock the cell formula.
Now, go to the Review Tab and select Sheet option to protect the formula and select OK. You can optionally select a password.
The formula for identifying the product with the highest sales has been locked.
How to Protect Formulas in Excel by Locking Cell?
Let us take an example to see how to protect formulas in Excel by using Cell Lock.
Suppose you have the oil prices for different cities for August and September. You need to calculate the median oil prices for August and September, and see in which month the oil prices were higher.
To calculate the median prices, use the syntax:
Median Prices for Aug
= MEDIAN(C5:C18) for Aug
Median Prices for Sep
= MEDIAN(D5:D18) for Sep
To identify in which month the prices were higher, use the syntax:
This will give the month (Aug here).
Now, you are supposed to protect all the cells containing the formula.
To do this, follow the following steps:
Step 1 – Unlock cells in Excel
Select all the cells by pressing Control + A. Then press Control + 1 to open the Format Cells dialog box and go to the Protection tab. Then, uncheck the Locked option. This will unlock all the cells.
Step 2 – Select and lock the cells containing the formula
Click Find & Select in Home and select Go to Special. If you are using Mac, then in the Edit Tab, select Go To…
and press Special.
In the Go To Special dialog box, select Formulas and click OK.
All the cells containing the formulas will be selected. Now, to lock these cells, press Control + 1 and go to Protection Tab in the Format Cells dialog box. Check Locked and click OK.
Step 3 – Protect the formula cells.
To protect the cells with formulas, go to Review Tab and click on Sheets.
You can optionally provide a password. Let us provide a password “12345” and then click OK. The formulas are now protected.
Things to Remember
- Only locking the cells in excels does not provide security to the cells from any further change.
- You need to lock a cell in excel and protect the formulas to secure them.
- All the cells in a worksheet are locked by default.
- Only protecting formula cells without locking also does not secure them.
This has been a guide to Lock Cells and Protect Formulas in Excel. Here we discuss how to lock cells in excel and how to protect formulas in Excel along with practical examples and downloadable excel templates.