• Home  / 
  • Excel
  •  /  Advanced Excel Formula Auditing

Advanced Excel Formula Auditing

Microsoft Excel has a lot of features. It has a variety of powerful tools that allows checking what is happening in your spreadsheet. Today we will discuss on formula auditing in Excel. 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.

Let’s go through examples one by one for formula auditing.

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.

Sheet Contains lot of formula and you are not sure how the formula linked - 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%.

 

  1. Trace Precedents – 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).

    Trace Precedents help you to show the arrows that indicate which cells affect the value of the current selected cell - Formula 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%

    click on trace precedents it will show you the arrows pointing cell while Formula Auditing

 

Get advanced excel training online -Learn at your own pace

  1. Trace Dependents – 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).

    Opposite of trace precedents is trace dependents while formula auditingAs 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.

    trace dependents show the arrows to all the cells which are connected with that particular cell in Formula Auditing

    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.

    Formula Auditing - 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.

    Learn more about Excel formulas with FREE Excel Handbook covering tips and tricks including formula nesting and lookups

  2. Show All Formula – 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 Auditing - you want to see all the formulas at a time (show all formulas)
    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.
    Formulas Auditing - Show all formulas it will show all the formulas in your sheet and if there is any date in the sheet show as number
    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.

    Show formula - Formula Auditing

  3. 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:

    • Go to file and then click on Options
    • Then go to Formulas Tab
    • Check the box named as “Enabled Background Error Checking”.
      Go to file and then click on options then go to formulas tab and Enabled error checking - Formula Auditing
      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.

    There is an error in your formula excel will prompt you for correction - Formula Auditing
    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.

    Whenever is an error in your formula and you are not sure about then you can 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.
    Formula Auditing - You want to see the calculation steps and 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.
    Excel formula Auditing - Go to trace error to check which cells are linked with the cell you have got the errorAs 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 tab it show which cells are linked if you are references are wrong you can edit the Formula from formula bar with correct referenceIn my example I ma 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.

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

    Formula Auditing - Go to Formulas tab and then remove arrows

  5. 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
    You entered formula in excel and it is not working Instead you are getting an error on Circular reference - formula AuditingAfter 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.Formula Auditing - move the formula another cellTo 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 Online excel course for free

About the author

Rishabh Pugalia and Niladri Sekhar Biswas

Author CA. Rishabh Pugalia has 10+ yrs. of work experience in Audit, Interest Rates Trading & I-Banking & Author Niladri Sekhar Biswas has 9 years of experience working in Microsoft Excel, as Excel and VBA corporate trainer

  • Hi admin, i see your blog needs fresh posts. If you are
    too lazy to write unique posts everyday you should search in google for:
    Ightsero’s Essential Tool

  • Kandace

    yodalearning.com has potential, you can make your page go viral easily using one tricky method.
    Just search in google:
    Irsrod’s Method To Go Viral