How to use specific colours for data entry and formula cells, to make troubleshooting easier. Find and fix the problem cells
Sometimes you get workbooks to troubleshoot, and the cells are filled with colours, that are only there to brighten up the worksheet. To make the troubleshooting easier, you can clear out the existing colours, and use your own set of colours to highlight cells with formulas, or other cell contents.
This short video shows how to remove the old colours, and format the cells that contain:
The written instructions are below the video.
In the screen shot below, you can see a typical worksheet that I get for troubleshooting. Nobody remembers who built the spreadsheet, but it's not working correctly, and needs to be fixed.
The sections are coloured randomly, because someone liked the colours, but they don't help with understanding how to use the worksheet.
The first step is to make a copy of the original file, and store it in a safe place.
Then, follow these steps to remove all the colour fill from the worksheet, so you'll be able to add your own colour scheme.
The next step is to find and format the cells that contain formulas.
The next step is to find and format the cells that contain constants. These are values -- text or numbers -- that have been typed into the cells.
The next step is to find and format the cells that contain data validation rules. These might be data validation drop down lists, or other types of rules.
The next step is to find and format the cells that contain constants that are numbers. These might be data entry cells, that we can use in our testing..
Now that all the key cells are colour coded, you can add a legend on the worksheet, to indicate what the colours mean.
Then, get started with the troubleshooting. For example, in the screen shot below, the circled cells are constants, but they should contain formulas.
When you're finished troubleshooting, and the worksheet is functioning correctly, you can clear the troubleshooting formats.
Then, add colour to the data entry cells, or other key cells, to help people understand how the workbook should be used.
Last updated: July 15, 2021 11:47 AM