EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Excel Tips Auditing Tools in Excel
 

Auditing Tools in Excel

Madhuri Thakur
Article byMadhuri Thakur

Updated June 8, 2023

Auditing Tools in Excel

 

 

Excel Auditing Tools (Table of Contents)

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

  • Introduction to Auditing Tools in Excel
  • Examples of Auditing Tools in Excel

Introduction to Auditing Tools in Excel

We all know why Excel is so popular throughout the world. Because of its wide range of formulae, functions, and macros, it can almost surely handle every task of the world with ease. All these formulae are easy to use, but it becomes a tough job when using multiple formulae in combination. It sometimes does not give the result as expected. It becomes easy to trace back the errors if formulae are quite simple. However, when we start creating some complex formulae, it becomes a hectic job to track the errors with such formulae. Thus, it becomes an important job for the user to be able to trace back the errors. Fortunately, Excel provides a variety of built-in tools that allow you to audit the errors with formulae and correct the same.

We have six main tools under Excel for formulae auditing listed below:

  1. Trace Precedents
  2. Trace Dependents
  3. Remove Arrows
  4. Show Formulas
  5. Error Checking (includes Error Checking, Trace Error, Circular References)
  6. Evaluate Formula

Let’s explore each of these formula auditing tools under Excel.

Examples of Auditing Tools in Excel

Lets us discuss examples of Auditing Tools in Excel.

You can download this Auditing Tools Excel Template here – Auditing Tools Excel Template

Example #1 – Trace Precedents

Trace precedents are the cells affecting the values/formulae under the selected cell. Suppose we have five principle amounts that we can invest with a rate of interest of 12% and a period of 6 years. We have calculated the interest amounts for all five principles using a simple formula Principle*Interest Rate*Period(in years). See the screenshot below:

Auditing Tools in Excel example 1-1

Now, if we click on cell A6 and press F2, we can see the formula we used to capture Interest Amount 1.

Auditing Tools in Excel example 1-2

This clearly shows us that Interest Amount 1 is based on Principle Amount 1 Value (A2) and Interest Rate (H2) as well as Period (I2). However, Excel has its way of showing this relationship through cell precedents under the formula auditing group.

Step 1: Select cell A6 from the current worksheet and click on the Formulas tab at the Excel ribbon.

Auditing Tools in Excel example 1-3

Step 2: Once you click on the Formulas tab, you can see the Formula Auditing group under it with various formula auditing options available.

Auditing Tools in Excel example 1-4

Step 3: Click on the Trace Precedents option under the Formula Auditing group, and it will make a connection between all the cells that are affecting the currently selected cell through blue-colored arrows as shown below:

Auditing Tools in Excel example 1-5

Trace Precedents help you find out what cells are associated with the formula using which the values are obtained.

Example #2 – Remove Arrows

Trace Precedents were nice since they let us see the relationship between different cells in our Excel sheet. However, we don’t want those here in our file. We wanted those to remove. Is it Possible? Well, possible!

Go again to the Formula Auditing group (Select the Formulas tab through the ribbon if you are not there and you can see this group within it). Click on the Remove Arrows dropdown.

Remove Arrows

Once you click on the Remove Arrows dropdown, you can see a list of options as shown below:

Remove Arrows 1-1

You can either hit the first option, which removes all the arrows (precedents and dependents), or you can select particular options that remove the precedents or the dependents.

Example #3 – Trace Dependents

Suppose we click on any cell and want to check the cells that depend on the currently selected cell. Can we do that in Excel? Surely, we can do this. Trace Dependents is the function that shows a relationship between the selected cell and all other cells that depend on the selected cell.

Suppose we select the cell H2 (Interest Rate). And wanted to check what are all the other cells which have a dependency on H2. We can do this in Excel.

Step 1: Select cell G2 in your Excel sheet.

Auditing Tools in Excel 3-1

Step 2: Now click the Formulas tab on the Excel ribbon and select Trace Dependents to see all the cells dependent on G2.

Auditing Tools in Excel 3-2

Once you click on Trace Dependents, you’ll see all the cells that have a dependency on G2, and they will be connected with blue arrows.

Example #4 – Show Formula

See Formulas is an exciting feature within Excel that shows you the formulas under all cells (formulated) instead of the values.

Go to the Formulas tab through the Excel ribbon and click the Show Formulas button under the Formula Auditing group to see all the formulas in the current Excel sheet.

Auditing Tools in Excel 4-1

Once you click on the Show Formulas button, you can see all the formulated cells with formulas instead of values, as shown below:

Auditing Tools in Excel 4-2

Example #5 – Error Checking

Suppose we have two numbers, Number 1 and Number 2, respectively, as shown in the image below:

Example 6-1

Now, we call a function that takes these two cell references and should give some output. I will be using the ROUNDUP function in Excel. See cell B3 for more references.

Function Called

I used the ROUNDUP function to divide 120 by zero and round it up to the nearest integer. Hit enter to see the output of this formula.

Function Called 1

Oh, Crap! I am getting an error here. How can I check the error? Well, Excel has an Error Checking option with it.

  • Go to the Formula Auditing group and click on the Error Checking button.

formula auditing

Error Checking dialogue box will open up as shown below:

Error Checking 1

You can see online help on this error by clicking Help on this Error button, or you can see the calculation steps involved in this error by hitting Show Calculation Steps, or you can ignore the error or Edit the formula.

Click on Help on this Error button, and you’ll be redirected to Office Support online page as shown below:

Error Checking 2

From here, I get that the #DIV/0! An error occurs when we try to divide some number with zero. I will change the value of Number 1 under cell B1 and see how the formula works in B3.

Error Checking 3

This is how error checking helps you in Excel.

Example #6 – Evaluate Formula

You can also evaluate any formula in Excel to see how that works. Just click on the Evaluate Formula button under the Formula Auditing group. Then you’ll see a new window where you can click the evaluate button and see the actual evaluation of the formula step by step.

Evalute Formula

Evalute Formula 1

This is how the Auditing Excel formula works under Excel. Now, let’s wrap things up with some points to be remembered:

Things to Remember

  • The formula Auditing tool can be found under the Formula tab and has a separate group assigned to it.
  • There is a keyboard shortcut for the Show Formula option. You can directly hit “Ctrl + `” simultaneously from your keyboard to Show Formulas in the entire sheet.
  • You can also use the F9 button to Evaluate the Formula within that option.

Recommended Articles

This has been a guide to Auditing Tools in Excel. Here we discuss How to use Auditing Tools in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

  1. Excel Export to PDF
  2. Divide in Excel Formula
  3. Interactive Chart in Excel
  4. SUM Cells in Excel

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download Auditing Tools Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download Auditing Tools Excel Template

EDUCBA

डाउनलोड Auditing Tools Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW