Home > Format > Tips > Troubleshoot Find and Fix Formula Cells How to use specific colours for data entry and formula cells, to make Excel troubleshooting easier. Find and fix the problem cells, so you can leave the office on time!

Introduction

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:

• formulas
• text
• numbers
• data validation (drop down lists)

The written instructions are below the video.

Clear All Formatting

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.

1. To select all the cells on the worksheet, click the Select All button at the top left of the worksheet
2. On the Ribbon's Home tab, click the Fill Color arrow, and click on No Fill

Colour the Formula Cells

The next step is to find and format the cells that contain formulas.

1. On the Ribbon's Home tab, click the Find & Select command
2. Click on Formulas, to select all the formula cells



3. Then, use the Fill command on the Ribbon to format the Formula cells. I usually use grey for the formula cells, which indicates that they shouldn't be changed.

Colour the Constant Cells

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.

1. On the Ribbon's Home tab, click the Find & Select command
2. Click on Constants, to select all the cells that contain constants



3. Then, use the Fill command on the Ribbon to format the Constant cells.

Colour the Data Validation 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.

1. On the Ribbon's Home tab, click the Find & Select command
2. Click on Constants, to select all the cells that contain data validation



3. Then, use the Fill command on the Ribbon to format the data validation cells.

Colour the Constant Cells With Numbers

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..

1. On the Ribbon's Home tab, click the Find & Select command
2. There is no command for Constants - Numbers, so click the Go To Special command.

   

3. Select Constants, and check the box for Numbers. Remove the check mark in the Text, Logicals and Errors boxes, then click OK.

   

4. Then, use the Fill command on the Ribbon to format the Constants - Numbers cells

Finish the Worksheet Formatting

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.

More Tutorials

Conditional Formatting Introduction

Conditional Formatting Examples

Conditional Formatting Documentation

Conditional Formatting Data Bars