Format Excel Worksheet for Troubleshooting

Do you ever get sent an Excel file, and asked to figure out why it’s not working correctly? The odds are that it was created several years ago, by someone who has left the company, and a few other people have “improved” it over the years.

Now it’s a mess, and nobody is sure exactly how it works. It’s up to you to sort it out. Lucky you!

Here’s how I start the troubleshooting process, and you can see the steps in the video below. There are detailed instructions on my Contextures site.

Make a Copy of the Original File

In the screen shot below, you can see a worksheet that is similar to the ones that I troubleshoot. There is a rainbow of colours on the sheet, but they don’t seem to have any significance – they’re just someone’s favourite colours.

I’ll get rid of that rainbow, and colour the cells that contain:

• formulas
• text
• numbers
• data validation

But the first step is to make a copy (or two) of the original file, and store it in a safe place. Don’t make any changes to the file, until you have that backup file safely stored away – you might want to refer to it later, as you dig deeper into the troubleshooting.

Clear All Colour

The next step is to remove all the colour fill from the worksheet, so you can add your own colour scheme.

1. Select all the cells on the worksheet
2. Choose No Fill as the cell fill colour

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.

Colour the Formula Cells

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

• On the Ribbon’s Home tab, click the Find & Select command
• Click on Formulas, to select all the formula cells

• 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 Remaining Cells

Next, you can find and format the cells that contain constants, and data validation, by using the other options in the Find & Select drop down.

1. On the Ribbon’s Home tab, click the Find & Select command
2. Click on Constants, then format the Constant cells.
3. Repeat the steps to format the Data Validation cells, where drop down lists or special rules apply

Colour Constant Numbers

Finally, find and format the cells that contain constant numbers. These might be data entry cells, that you can use in testing the worksheet.

• On the Ribbon’s Home tab, click the Find & Select command
• Click the Go To Special command.
• Select Constants, and check the box for Numbers. Remove the check mark in the Text, Logicals and Errors boxes, then click OK.

• Then, format the constant number cells

Finish Worksheet Formatting

After all the key cells are colour coded, you can add a legend on the worksheet, to show what the colours mean.

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

Watch the Formatting Video

To see the steps for formatting the different types of cells, you can watch this short video.

___________________