Unlocking coloured cells in Excel

Styles and Find solutions

When you create an Excel file that handles inputs it is best practice to colour code the input cells. The colour you choose isn’t important but making sure you use it consistently is. You may need to unlock the input cells if you plan to add sheet protection to the file. Here’s a couple of ways to do that.

If you know ahead of time that you will be adding a password to the sheets then the easiest way is to use a Style that includes the unlock format.

Unlocking cells

In Excel every cell is locked as standard, when you apply a password to the sheet all cells are locked. For a report this may make sense but when inputs are required you need to allow the user to make or change entries. To do that you have to unlock the input cells before you apply the password.

You unlock a cell through the Format Cells dialog. See image below.

There is also an icon you can add to the Quick Access Toolbar to make it easier. See image below.

Because it is a format you can capture the format in a custom Style or amend an existing Style.

Styles and Custom Styles

Styles are on the Home ribbon tab.

They allow you to apply formats consistently throughout a file. One advantage of Styles is the ability to amend the Style’s format and have all the cells with that Style automatically change.

If you use a Style for input cells then changing the unlock format is easy. You modify the Style and all the cells will update. You can then apply the password.

Modifying a Style

There is a standard Style for Input cells (image below).

You can modify that to include an unlocked format. Right click the Style and choose Modify.

Click the Format button.

In the Protection tab untick the Locked box and click OK. Then click OK again – done.

All of the Input Style cells are now unlocked. In the future when you apply the Input Style you will automatically unlock the cell ready for applying sheet protection.

Existing input cells

If you have applied a consistent colour to your input cells and you now need to unlock all of them before applying a password then you can use the Find dialog.

Steps

  1. Select a single cell in the sheet. When you select a single cell before using Find you are looking in the whole sheet.
  2. Press Ctrl + F to open the Find dialog.
  3. Click the Options button if the full Find dialog doesn’t open – see below.
  4. Click the Format drop down and select Choose Format from Cell – see below.
  5. Click on an input cell that has the format.
  6. Click the Find All button.
  7. Click in the bottom section of the Find dialog where all the cells are listed and press Ctrl + A this will select all the cells in one step.
  8. Close the Find dialog – click the X top right.
  9. Press Ctrl + 1 and click the Protection tab and untick the Locked option and click OK.

Job done – all input cells are unlocked.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply to sandeep kothari Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

4 thoughts on “Unlocking coloured cells in Excel

  1. Dear Neale, When I make a custom style in a particular file, does such custom style become available for use in other files?
    I found that when I opened another file, that custom style is not available in custom style ribbon.
    Please show the way forward so that custom style made in a particular file becomes available for use in other files?