EDUCBA

EDUCBA

MENUMENU
  • Blog
  • All Courses
  • All in One Bundle
  • Login
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)

Start Your Free Excel Course

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

  • 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
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
MS Excel & VBA for Data Science Course Bundle - 24 Courses in 1 | 10 Mock Tests
87+ Hours of HD Videos
24 Courses
10 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
All-in-One Financial Analyst Masters Training Program Bundle - 550+ Courses | 300+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.9
All-in-One Data Science Bundle - 400+ Courses | 550+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.7
Primary Sidebar
Popular Course in this category
FINANCIAL MODELING & VALUATION Course Bundle - 51 Courses in 1 | 30 Mock Tests | World's #1 Training
 278+ Hours of HD Videos
51 Courses
30 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.6
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA

Download Auditing Tools Excel Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

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

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

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Auditing Tools Excel Template

EDUCBA

डाउनलोड Auditing Tools Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more