How to protect formulas in Excel but allow input
By
SpreadCheaters
You can watch a video tutorial here.
Excel is often used for gathering data through a form. A form can be designed as a template in Excel and then filled in by users. When creating a form with calculated columns, it is important to protect the formulas in cells so that users do not accidentally change or delete the formulas. At the same time, you need to permit the users to enter data in other cells. In Excel, it is possible to lock cells and protect the entire worksheet. In this example, we will see how to lock certain cells but permit input in others.
Step 1 – Create the formula
– Select the cell where the result is to be displayed
– Type the formula using cell references:
= No. of days worked * Rate
– Press Enter
Step 2 – Copy the formula
– Using the fill handle from the first cell, drag the formula to the remaining cells
OR
a. Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
b. Select the rest of the cells in the column and press Ctrl+V or choose Paste from the context menu (right-click)
Step 3 – Open the Format Cells window
– Select the entire sheet by clicking in the top left corner
– Right-click and select Format Cells from the context menu
OR
Go to Home > Number and click on the arrow to expand the menu
OR
Go to Home > Cells > Format > Format Cells
OR
Press Ctrl+1
Step 4 – Unlock the cells
– Select the Protection tab
– Uncheck the ‘Locked’ box
– Click OK
Step 5 – Lock the cells with formulas
– Select the cells with formulas
– Right-click and select Format Cells from the context menu
OR
Go to Home > Number and click on the arrow to expand the menu
OR
Go to Home > Cells > Format > Format Cells
OR
Press Ctrl+1
– Select the Protection tab
– Tick the ‘Locked’ box
– Click OK
Step 6 – Protect the sheet
– Go to Review > Protect
– Click on Protect Sheet
– In the box that opens click OK
Step 7 – Check the sheet
– Enter values for the ‘No. of days worked’ and ‘Rate’
– The ‘Amount’ is calculated automatically
– Attempt to change the ‘Amount’
– An error message is displayed
