Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Count rows with at least n matching values
  • Multi-criteria lookup and transpose in Excel
  • Create hyperlink with VLOOKUP in Excel
  • Vlookup Examples in Excel
  • Excel Advanced Lookup using Index and Match Functions

Data Analysis Examples

  • How to create running total in an Excel Table
  • How to sum a total in multiple Excel tables
  • Use Data Form to input, edit and delete records in Excel
  • How to conditionally sum numeric data in an Excel table using SUMIFS
  • How to create Checklist in Excel

Data Validation Examples

  • Prevent invalid data entering in specific cells
  • Excel Data validation specific characters only
  • Excel Data validation allow uppercase only
  • How To Create Drop-down List in Excel
  • Excel Data validation unique values only

Find, Trace and Correct Errors in Excel Formulas using ‘Formula Auditing’

by

Formula auditing in Excel allows you find and correct errors in formulas. Also, displays the relationship between formulas and cells using Trace Precedents, Remove Arrows, Trace Dependents, Show Formulas, Error Checking and Evaluate Formula.

The example below helps you master Formula Auditing quickly and easily.

Trace Precedents

You have to pay $96.00. To show arrows that indicate which cells are used to calculate this value, execute the following steps.

1. Select cell C13.

2. On the Formulas tab, in the Formula Auditing group, click Trace Precedents.

Result:

As expected, Total cost and Group size are used to calculate the Cost per person.

Worked Example:   Common Errors in Excel

3. Click Trace Precedents again.

As expected, the different costs are used to calculate the Total cost.

Remove Arrows

To remove the arrows, execute the following steps.

1. On the Formulas tab, in the Formula Auditing group, click Remove Arrows.

Trace Dependents

To show arrows that indicate which cells depend on a selected cell, execute the following steps.

1. Select cell C12.

2. On the Formulas tab, in the Formula Auditing group, click Trace Dependents.

Worked Example:   How to create simple Time Sheet in Excel

Result:

As expected, the Cost per person depends on the Group size.

Show Formulas

By default, Excel shows the results of formulas. To show the formulas instead of their results, execute the following steps.

1. On the Formulas tab, in the Formula Auditing group, click Show Formulas.

Result:

Note: instead of clicking Show Formulas, you can also press CTRL + (`). You can find this key above the tab key.

Error Checking

To check for common errors that occur in formulas, execute the following steps.

Worked Example:   How to get original price from percentage discount in Excel

1. Enter the value 0 into cell C12.

2. On the Formulas tab, in the Formula Auditing group, click Error Checking.

Result. Excel finds an error in cell C13. The formula tries to divide a number by 0.

Evaluate Formula

To debug a formula by evaluating each part of the formula individually, execute the following steps.

1. Select cell C13.

2. On the Formulas tab, in the Formula Auditing group, click Evaluate Formula.

3. Click Evaluate four times.

Excel shows the formula result.

Post navigation

Previous Post:

Floating Point Errors in Excel

Next Post:

Circular Reference Problem Solved

Leave a Reply Cancel reply

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

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • BAHTTEXT function: Description, Usage, Syntax, Examples and Explanation
  • Remove line breaks in a cell in Excel
  • TEXT function: Description, Usage, Syntax, Examples and Explanation
  • Find most frequent text within a range with criteria in Excel
  • DBCS function: Description, Usage, Syntax, Examples and Explanation
  • Count holidays between two dates in Excel
  • Check If Two Dates are same month in Excel
  • Get days between dates in Excel
  • How to calculate percent of year complete in Excel
  • Extract date from a date and time in Excel
  • SLN function: Description, Usage, Syntax, Examples and Explanation
  • RECEIVED function: Description, Usage, Syntax, Examples and Explanation
  • CUMPRINC function: Description, Usage, Syntax, Examples and Explanation
  • XIRR function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate annuity for interest rate in excel
© 2022 xlsoffice . All Right Reserved. | Teal Smiles