Excel has a variety of powerful tools in it’s toolbox to help you see what is going on in a spreadsheet with formulas. Which cells are using the information in them and where the information within the cell comes from, they can even help you trace errors.
Obviously ensuring that your formulas a working correctly and only using the appropriate data is vital to the integrity of your calculations across the board from simple sums through to advanced business intelligence tools. We offer courses in London and Guildford appropriate for all levels of Excel user.
This shows where the cell is getting the information from that it is using in the formula
To see this click on the Formulas tab, then click into the cell you want to know about, I did two in the picture below – the first one was cell F7, then click ‘Trace Precedents’ .
This will add the blue boxes and the arrow showing which cells are being used in the formula in that cell. The formula itself can be seen in the function bar above.
For the second one I clicked into cell F13 and then clicked ‘Trace Precedents’.
If you changed a cell, what other cells would it effect? What cells are using this info?
To see this, click on the Formulas tab, click on the cell you want to know about and then click on the ‘Trace Dependants’
In the picture below you can see the the ‘12%’ in cell G2 is used in the calculations in cells B3 – M3
Excel will automatically colour code the arguements in a formula to help you identify its relationship with other cells
Double click on the cell with the formula and it’s contents will show in the formula bar, different colours for different cells. If a cell is referenced twice it will keep the same colour in both places. You are able to drag and drop the colours to a different cell to change the formula.
Excel will show you at the click of a button where your spreadsheet errors are. The example we have is a very small simple worksheet and it’s easy to see the errors, but imagine how useful this will be on large complicated spreadsheets.
With the spreadsheet open that you want to check, click on the ‘Formulas’ tab and then ‘Error Checking’
This is what will open up, it will go to the 1st error on the sheet.
Click on the ‘Edit in formula bar’ and correct it. Whether you make any changes or not you will be given the option to ‘Resume’ in the error box and it will continue looking for errors.
This will help identify which cells are being used for the formula and which ones are causing the errors. When you use this, red arrows show the the linked cells where there is an error and blue cells show linked cells where there is no error. Here is where you find the button:
In the diagram below cell L7 has the error. By clicking on cell L7 and clicking ‘trace error’ It shows in blue the cells that are involved but do not have errors in them.
By clicking on cell L8 and clicking ‘trace error’ the red arrow shows that the error in that cell is caused by an error in cell L7
You will find this button under the formulas tab in the formula auditing section.
It seems a bit confusing at first, but once you understand how it works, it’s a great tool.
This is the spreadsheet I will be using for this example:
We first click on cell F7 and click on evaluate formula, this is what appears:
It shows the two cells that are used in the formula in F7. See how E7 is underlined under the pink dot? By clicking evaluate it will show the value of that cell:
It automatically then moves over to the next cell in the formula, E19
Click evaluate again and it will show the value of E19, in this case zero.
Click evaluate again and is will show the error message from the cell and you have the option to restart.
After you click on restart ‘Step In’ will be highlighted. Think of stepping in and out as moving down and back up levels. Click on that and it will show you the formula that F7 has in it, which is E7/E19 click again and it will show you the formula of the first cell within the cell E7 which is D7*C7
Click step in (drill down to the next level) again and it will show you the value of that cell, the only option then is to ‘Step out’
Remember that the starting cell for all of this was F7
When you click Step Out (move back up a level) you will then have the option to step in to C7 or to ‘step out’ another level. I have ‘Stepped out’ back to where cell E19 is underlined and I have then ‘Stepped In’. You can see from the picture below that cell E19 is empty which is causing the #DIV/0! error as you cannot divide by 0. You can now correct the error and move on to the next one.
This may seem very long from reading this, but in practise is quick.
On a larger worksheet you can use the watch window to see cells that are not in the main view so you don’t need to keep scrolling to see them or moving to different parts of the worksheet.
In this picture the total is in cell C280 which cannot be seen at the top of the worksheet. By clicking it, then pressing the ‘Watch Window’ button, which is greyed out in this image as I have gone past that, it will open another window. Click ‘Add Watch’ and the cell that you had selected in the worksheet will be in the info bar. Click ‘Add’ the top little window will close and the info will appear in the main ‘Watch Window’.
If you want to you can dock this to the top of your workbook by double clicking the title bar of the watch window.
You will now be able to see the effect of any changes that are made to figures in column C
I have just discovered that if you open a worksheet with a circular reference in it, a warning message will pop up. This message explains exactly what a circular reference is:
Excel will display in the bottom left-hand corner that the sheet has this error and where it is:
As you can see cell E18 looks empty but when you click on it you can see the formula in the formular bar. It is not dislaying a total because of the circular reference. Click in the formula bar and change E18 to E16 and everything will work correctly again.
If you want to learn more about this, attend our Excel for beginners training
Want to find out more about Text editing in Excel? Follow this article to find out more.
Want to find out more about Absolute Cell Referencing? Follow this article to find out more.