Contextures

Find and Fix Formula Cells

How to use specific colours for data entry and formula cells, to make troubleshooting easier. Find and fix the problem cells

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.

excel sheet with colours

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

Home Tab No Fill Color

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

  4. 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. find constants

  4. 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. find data validation

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

    click Go To Special

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

    Go To Special dialog box

  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.

troubleshooting colour code

Then, get started with the troubleshooting. For example, in the screen shot below, the circled cells are constants, but they should contain formulas.

troubleshooting worksheet formatting

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

Search Contextures Sites

 

Excel Tools Add-in

 

 

 

Excel Data Entry Popup List

 

 

 

Excel UserForms for Data Entry

 

Last updated: December 31, 2016 11:27 AM
Contextures RSS Feed