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.
- Select all the cells on the worksheet
- 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.
- On the Ribbon’s Home tab, click the Find & Select command
- Click on Constants, then format the Constant cells.
- 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.
___________________
Good tutorial Debra!
Thanks
Paul
I love your blog and website! The tuts are so easy to follow!
Do you have anything posted that might address this question:
Is there a way to do a modified amortization schedule in Excel that also takes into account payments in excess of the monthly required payment? I have a schedule for my mortgage in Excel, but also pay a certain amount over each month, and would like to see the impact of the extra principal applied (in regards to interest charged for the next payment, etc), and be able to do ‘what-if’ scenarios..
Thanks!