Microsoft Excel has a lot of features one of it is excel formula auditing. It has a variety of powerful tools that allow checking what is happening in your spreadsheet. Today we will discuss on Advanced Excel formula. It helps you to get the relations between formula and your cells. Which cells are using the information in the cell and from which cell the information comes from in the cell? Even this will help you to trace error if there are Errors in your Formula.
Table of Contents
Advanced Excel formula auditing example:
Assume you have a sheet containing lots of formula and you are not sure how the formula is linked up. In my case, I have the financial projection table for ABC Pvt Ltd Company. Now I have created a formula to get the Sales growth of 31-Mar-11 which I got 7.1% and it is coming from Current Year Sales (which is 1,050) minus previous Year Sales (which is 980) and the result is divided by previous year sales (which is 980). And I am getting the result as 7.1%. So my sales growth in 2011 is 7.1%.
Trace Precedents – Excel Formula Auditing:
It will help you to show the arrows that indicate which cells affect the value of the currently selected cell.
Now I select F5 cell which is showing as 7.1%. Then I go to Formula Tab and then in the auditing section, I click on trace precedents (Shortcut Key Alt + M+P).
As soon as I click on Trace Precedents it will show you the arrows pointing to my F5 cell. It means if I change the value of these two cells it will affect the value of F5 cell which is currently showing as 7.1%
Trace Dependents – Excel Formula:
It is just the opposite of Trace Precedents. Let say we have the same table like above picture. Now you want to know the dependent cell of cell E9 which is 980. That means if I change the value of this cell (E11) which are the cells are going to affect. In excel you can see this by clicking on Trace Dependents. To apply the same you need to go Formulas Auditing – Trace Dependents ((Shortcut Key Alt + M+D).
As you click on the cell you will see that from your cell E9, 3 arrows are pointed towards 3 cells. And these 3 cells are F7, E9 and E13. It showing the arrows to all the cells which are connected with this cell E11. And if I change the value of this cell E11 then all the cells which are connected with this cells will be changed.
It is clear from the above picture that my E11 cell (which is 980) is connected with 3 other cells and currently showing the value as 79.6%, 7.1% and 200.0. Now if I change the value of my E11 cell then all the values will be changes.
So before changing any value in your sheet just ensure applying Trace dependents. It will tell you that if you change the value of your selected cells then what are the other cells are going to change.
Another example of Trace dependents:
Let’s look at a very simple example. In you, cell B2 just type 10. And cell D5 just write =$B$2*row (). It will give you the result 50 as the value of B2 is 10 and row () means your currently active cell no which is 5. So 10*5 and it will give you the result 50. And drag it down up to D12. You will get the result like 50, 60,70 ….up to 120. You have dragged down up to 12 no. row so last value will be 120. Then you go to you B2 cell and then go to Formula tab and apply Trace Dependents. It will show like below picture.
From the above picture you can understand that if you change the value of B2 cell all the other cells connected to B2 will also be changed. So before changing any value in your spreadsheet make sure that which of the cells value are also be changing. To check the same you can apply Trace Dependents.
Show All Formula – Excel Formula Auditing:
There are a lot of formulas applied to your sheet. You want to see all the formulas at a time. To get all the formulas at a time you need to go Formulas tab and then click on Show all formulas. You can also apply the shortcut key Ctrl +` (the sign beside 1) As you click on Show all formulas it will show you all the formulas in your sheet and if there is any date in your sheet it will show as a number as you know that each and every date is a number to Excel. Again clicking on the show all formulas tab will help you to get back to your original look. So remember if you want to see all the formulas in your sheet please click on show all formulas tab.
Go to file and then click on Options
Then go to Formulas Tab
Check the box named as “Enabled Background Error Checking”.
If there is an error in your formula excel will prompt you for correction. You may click on “yes” following the rules of Excel or you may click on “No” for editing the formula in the formula bar.
Error checking – Formula Auditing:
The first check is error checking is enabled in your excel file or not. By default, it is enabled. If it is not enabled in your file follow the below steps to enable Error checking in excel
In the above formula, I am trying to get some value and then dividing it by Zero. And you will see the error #DIV/0! And we all know that this is not possible.Now, whenever there is an error in your formula and you are not sure about this then you can apply error checking. And to do the same you need to go to Formulas tab and then click on Error Checking.
As soon as you click on Error checking you will get a box which will guide you what to do next. If you want to see the calculation steps then please click on show calculation steps and then Evaluate. You can also go to trace Error to check which cells are linked with the cell where you have got the error. To do the same you need to go to Trace Error under Formulas tab. As you click on the trace error tab it shows which cells are linked and then if you are references are wrong you can edit the formula from formula bar with correct reference.In my example I am trying to do a vlook up when my table array is blank. Surely I will get #N/A in my result as my lookup table is empty. I have checked the formula with trace error and now I can change the reference to get the appropriate result.
Remove Arrows – Formula Auditing:
If you want to remove arrow for the above the picture you need to go to Formulas tab and then click on remove arrows. Remember undo (Ctrl +Z) will not work here. To remove arrows you need to click on remove Arrows icon. It will help you to remove all the arrows drawn trace precedents and trace dependents.
Circular reference – Formula Auditing:
You have entered a formula in excel and it is not working. Instead, you are getting an error on “Circular Reference”. I have seen a lot of people facing this problem as the formula he/she has entered into the cell is calculating its own cell. For example, I have a series of a number from E1 to E5. Now I want a result which will give me the sum of range (E1 to E5) and then again add the total value.
In E6 cell I am writing the formula =sum(E1: E5)+E6 After entering the formula as soon as I will press enter I will get circular reference error. As because the formula =sum(E1: E5)+E6 breaks as it lives in cell E6 and it is calculating itself.To remove from this you can move the formula in another cell. Don’t be scared of circular reference. Let me tell you – It’s a sword which you need to handle with care 🙂