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.

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. Financial-projection-Formula-AuditingNow 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).

In-Formulas-trace-precedents-in-auditingAs 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).In-Formulas-trace-dependents-in-Auditing
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.

Examples-of-trace-dependentsFrom 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)Formulas-tab-show-formulas-auditing 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.
Show-all-formulas-in-your-sheetAgain 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

Error-in-formula-excel-will-prompt-correctionIn 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.Apply-error-checking-Formulas-Auditing
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.
Error-checking-what-to-do-next-evaluateYou 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.
Trace-Error-under-Formulas-tabAs 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.Trace-Error-cells-are-linked-AuditingIn 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.

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


  2. 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
    Formulas-Auditing-Circular-referenceAfter 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.Circular-referenceTo 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 🙂

Started your self-paced learning with Free Online Excel Course

You can learn some excel formulas by watching this video:

Leave a Reply

Related Tutorials

Connect to Data in Power Query
February 21, 2019
How to use Text to Column in Excel with 4 Difference Example
February 21, 2019
Things you should learn in Excel Course
February 13, 2019
How to use Clean Function in Excel
February 11, 2019
How to use the Excel Choose Function
February 11, 2019
How to use the Excel Code Function
February 11, 2019
How to use the Excel Column Function
February 11, 2019
How to use Ceiling Function in Excel
January 30, 2019
How to use Char Function in Excel
January 28, 2019
Text To Column in Excel | Split Cells
January 4, 2019
Pivot Tables (Complete Guidelines)
December 27, 2018
Power Bi Dax Deduplication Based On Column
December 17, 2018
Special Character Symbol List with Shortcodes in Excel
December 15, 2018
December 10, 2018
December 10, 2018
How to use ABS Function in Excel
December 10, 2018
How to use AND Function in Excel
December 10, 2018
Delete Duplicate in Excel or Remove Duplicate in Excel
November 9, 2018
Excel Formulas and Functions in PDF
September 6, 2018
How To Lock Cells in Excel | Unprotect Excel
August 13, 2018
4x Faster at Excel
August 6, 2018
Separate Content of One Excel Cells into Separate Columns
August 3, 2018
How to Transpose Excel Columns to Rows | Paste Special Method
July 26, 2018
How to create sparklines in Excel
July 19, 2018
AutoSum in Excel with Shortcut
July 17, 2018