• Skip to primary navigation
  • Skip to main content
  • Skip to footer
WallStreetMojo

Wallstreet Mojo

Wallstreet Mojo

MENUMENU
  • Resources
        • Accounting Basics

          • Income Statement
          • Balance Sheet
          • Cash Flow in Accounting
        • Accounting-Basics
        • Accounting Concepts

          • Assets in Accounting
          • Liabilities in Accounting
          • Shareholders Equity
        • Accounting-Concepts
        • Accounting Careers

          • Accounting Interview Q&A
          • Financial Accounting Careers
          • Cost Accounting
        • Accounting-Careers
        • Financial Statement Analysis

          • Ratio Analysis
          • Profitability Ratios
          • Turnover Ratios
        • Financial-Statement-Analysis
        • Investment Banking Guides

          • Investment Banking
          • What is LBO?
          • What is Pitch Book?
        • Investment-Banking-Guides
        • Others

          • Resources (A to Z)
          • Equity Research
          • Corporate Finance
          • Financial Modeling
          • Financial Certifications
          • Valuation
  • Free Courses
  • All Courses
        • Certification Courses

          Certificate
        • All in One Financial Analyst Bundle

          Financial-Analyst-Bundle
        • Accounting Course

          Accounting-Course
        • US GAAP Course

          US-GAAP-Course
        • Others

          • Cost Accounting Course
          • IFRS Course
          • Equity Research Course
          • Finance for Non finance

          • Financial Modeling Course
          • Business Valuation Course
          • Investment Banking Course
          • View All
  • 250+ Courses All in One Bundle
  • Login

Auditing Tools in Excel

Home » Accounting » Audit » Auditing Tools in Excel

By Sharmila Reddy Leave a Comment

Auditing Tools in Excel

Excel Auditing Tools (Table of Contents)

  1. Trace Precedents
  2. Remove Arrows
  3. Trace Dependents
  4. Show Formulas
  5. Error Checking

Formula Auditing Tools in Excel

As we all know that MS Excel is mainly used and popular for its function, formulas, and macros. But what if we are getting some issue while writing the formula or we are not able to get the desired result in a cell as we have not formulated the function correctly. That is why MS Excel provides a lot of built-in tools for formula auditing and troubleshooting formulas.

The tools which we can use for auditing and formula troubleshooting in excel are:

  1. Trace Precedents
  2. Trace Dependents
  3. Remove Arrows
  4. Show Formulas
  5. Error Checking
  6. Evaluate Formula

Examples of Auditing Tools in Excel

We will learn about each of the above auditing tools one by one using some examples in excel.

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

#1 – Trace Precedents

Suppose we have the following formula in D2 cell for calculating interest for an FD account in a bank.

Auditing Tools Example 1

Now if we want to check the precedents for the formula, then we can press F2 to get into edit mode after selecting the required cell so that precedents cells got bordered with various colors and in the same color, cell reference is written.

Auditing Tools Example 1-1

We can see that A2 is written with blue color in the formula cell and with the same color, A2 cell is bordered.

In the same way,

B2 cell has a red color.

C2 cell has a purple color.

This way is good but we have a more convenient way to check precedents for the formula cell.

To trace precedents, we can use the ‘Trace Precedents’ command in the ‘Formula Auditing’ group under the ‘Formulas’ tab.

Auditing Tools Example 1-2

We just need to select the formula cell and then click on the ‘Trace Precedents’ command. Then you can see an arrow as shown below.

Auditing Tools Example 1-3

We can see that precedent cells are highlighted with blue dots.

#2 – Remove Arrows

To remove these arrows, we can use the ‘Remove Arrows’ command in the ‘Formula Auditing’ group under the ‘Formulas’ tab.

Auditing Tools Example 1-4

#3 – Trace Dependents

This command is used to trace the cell which is dependent on the selected cell.

Popular Course in this category
Cyber Monday Sale
All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) 250+ Courses | 40+ Projects | 1000+ Hours | Full Lifetime Access | Certificate of Completion
4.9 (1,067 ratings)
Course Price

View Course

Related Courses
Accounting CourseUS GAAP CourseCost Accounting Course

Let’s use this command using an example.

Suppose, we have 4 amounts that we can invest. We want to know that how much interest, we can earn if we invest.

Auditing Tools Example 2

We can see that in the above image, we have applied a formula for calculating interest with Amount 1 and specified interest percentage and duration in the year.

We will copy the formula and paste it in the adjacent cells for amount 2, amount 3 and amount 4. It can be noticed that we have used an absolute cell reference for G2 and I2 cells as we do not want to get changed these references while copying and pasting.

Auditing Tools Example 2-1

Now if we want to check if which cells are dependent on G2 cell. Then we will use the ‘Trace Dependents’ command available in the ‘Formula Auditing’ group under the ‘Formulas’ tab.

Auditing Tools (Trace Dependents)

Select the G2 cell and click on the ‘Trace Dependents’ command.

Auditing Tools Example 2-2

In the above image, we can see the arrow lines where arrows indicate which cells are dependent on the cells.

Now we will remove the arrow lines using ‘Remove Arrows’ command.

#4 – Show Formulas

We can use this command to display formulas written in the excel sheet. The shortcut key for this command is ‘Ctrl+~’.

Auditing Tools Example 3

See the below image where we can see the formulas in the cell.

Auditing Tools Example 3-1

We can see that instead of formula results, we are able to see the formula. For amounts, currency format is not visible.

To deactivate this mode, press ‘Ctrl+~’ again or we can click on ‘Show Formulas’ command.

#5 – Error Checking

This command is used to check the error in the specified formula or function.

Let’s take an example to understand this.

See the below image where we have an error in the function applied for the result.

Auditing Tools Example 4

Now to solve this error, we will use the ‘Error Checking‘ command.

Steps would be:

Select the cell where the formula or function is written then click on ‘Error Checking’.

Auditing Tools Example 4-1

As we click on command, we get the following dialog box captioned as ‘Error Checking’.

Auditing Tools Example 4-2

In the above dialog box, it can be seen that there is some invalid name error. The formula contains unrecognized text.

If we are using the function or constructed the formula for the first time, then we can click on ‘Help on this Error’ button which will open the help page for the function in the browser where we can see all the related information online and understand the cause and find all the possible solutions.

As we click on this button now, we will find the following page.

Auditing Tools Example 4-3

On this page, we get to know about the error that this error comes when

  1. The formula refers to a name that has not been defined. It means the function name or named range has not been defined earlier.
  2. The formula has a typo in the defined name. It means that there is some typing error.

If we have used the function earlier and know about the function, then we can click on the ‘Show Calculation Steps’ button to check how the evaluation of the function results in an error.

If we click on this button, the following steps are displayed:

  • The following dialog box displays when we click on the ‘Show Calculation Steps’ button.

show calculations steps Example 4-4

  • After clicking on the ‘Evaluate’ button, the underlined expression i.e., ‘IIF’ gets evaluated and gives the following information as displayed in the dialog box.

underlined expression Example 4-5

As we can see in the above image, the ‘IIF’ expression evaluated to be an error which is ‘#NAME?’.  Now next expression or reference i.e., B2 got underlined. If we click on the ‘Step In’ button then we can check internal details of a step also and come out by pressing the ‘Step Out’ button.

Function error Example 4-6

Evaluate error Example 4-7

  • Now we will click on the ‘Evaluate’ button to check the result of the underlined expression. After clicking, we get the following result.

Evaluate Example 4-8

  • After clicking on the ‘Evaluate’ button, we get the result of the function applied.

Restart Example 4-9

  • We got an error as a result and as we analyzed the function step by step, we got to know that there is some error in ‘IIF’ For this, we can use the ‘Insert Function’ command in ‘Function Library’ group under ‘Formulas’ tab.

Formulas Example 4-10

As we typed the ‘if’, we got a similar function in the list, we need to choose the appropriate function.

Insert Function Example 4-11

After selecting the ‘If’ function, we get the following dialog box with text boxes for argument and we will fill all the details.

Function arguments Example 4-12

After clicking on ‘Ok’, we get the result in the cell. We will copy down the function for all the students.

Auditing Tools Example 4-13.png

Things to Remember

  1. If we activate the ‘Show Formulas’ command, the dates are also shown in the number format.
  2. While evaluating the formula, we can also use F9 as a shortcut key.

Recommended Articles

This has been a guide to Auditing Tools in Excel. Here we discuss 5 different types of auditing tools including Show Formulas, Error Checking, Trace Precedents, etc with some examples and downloadable excel template. You may learn more about excel from the following articles –

  • PERCENTRANK in Excel
  • Excel Infographics
  • Comparison Chart
  • Excel Watch Window
  • Data Model in Excel
  • ISNA in Excel
  • Excel Structured References
0 Shares
Share
Tweet
Share

Filed Under: Accounting, Audit

Reader Interactions
Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Footer
COMPANY
About
Reviews
Blog
Contact
Privacy
Terms of Service
FREE COURSES
Free Finance Online Course
Free Accounting Online Course
Free Online Excel Course
Free VBA Course
Free Investment Banking Course
Free Financial Modeling Course
Free Ratio Analysis Course

CERTIFICATION COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Business Valuation Course
Equity Research Course
CFA Level 1 Course
CFA Level 2 Course
Venture Capital Course
Microsoft Excel Course
VBA Macros Course
Accounting Course
Advanced Excel Course
Fixed Income Course
RESOURCES
Investment Banking
Financial Modeling
Equity Research
Private Equity
Excel
Books
Certifications
Accounting
Asset Management
Risk Management

Copyright © 2019. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

Free Accounting Course

You will Learn Basics of Accounting in Just 1 Hour, Guaranteed!

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

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

WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

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

WallStreetMojo

Free Accounting Course

You will Learn Basics of Accounting in Just 1 Hour, Guaranteed!

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

WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

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

WallStreetMojo

Download Auditing Tools Excel Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.

CYBER WEEK OFFER - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More