IF YOU ARE USING EXCEL VERSION 2007 OR AFTER: In the Conditional Formatting dialog box, click OK, and you are done. Step 6 (version 2003 or before) - In the Format Cells dialog box:

Enter the formula =CELL('PROTECT',A1)=0.

From the drop down list, select Formula Is.

Step 5 (version 2003 or before) - Press Alt+O+D to show the Conditional Formatting dialog box: IF YOU ARE USING EXCEL VERSION 2003 OR BEFORE: Step 4 - Press Alt+O+E to show the Format Cells dialog box: Step 3 - Select the range(s) where you want to unlock the cells for users to enter data.

Click OK, to make sure all cells in that range are locked.

Step 2 - Press Alt+O+E to show the Format Cells dialog box: This will ensure that users will only have access to the unlocked cells.

The above formula is what is being used in this example, shown in the following pictured steps.Īs a quick aside, you can conditionally format locked cells with this True/False formula:Īlthough not absolutely necessary, it is advisable 99.9% of the time that you protect your worksheet after you have installed the conditional formatting.

You can conditionally format unlocked cells with this boolean (True or False) formula: Users appreciate seeing exactly where they can - and should - enter data. Here is how you want to present your worksheet to your users, so they know where to enter data: You’ll want to protect all the cells containing formulas and static header labels, while allowing certain cells to be unprotected for users to input data. There are times when your users will find it easier to enter data directly into worksheet cells, instead of a userform interface. Tom’s Tutorials For Excel: Conditionally Formatting Locked and Unlocked Cells

Lock cells with formulas in excel 2016