Lesson 15 Using Auditing Tools THE PROFESSIONAL APPROACH

Lesson 15 Using Auditing Tools THE PROFESSIONAL APPROACH SERIES © 2008 The Mc. Graw-Hill Companies, Inc. All rights reserved. M I C R O S O F T ® O F F I C E EXCEL 2007

2 Lesson Objectives • Evaluate a formula. • Set a data validation list. • Trace precedents, dependents, and error messages. • Find and correct errors. • Troubleshoot errors. • Use the Watch Window. • Use IFERROR. THE PROFESSIONAL APPROACH SERIES © 2008 The Mc. Graw-Hill Companies, Inc. All rights reserved.

3 Evaluating a Formula • Excel performs background error checking while you work. • A tiny green triangle highlights potential problems in a cell. • Some errors display an error value message in the cell. • When you hover a cell with an error triangle, the Error Checking Options button appears. It opens a menu of commands for handling the error. THE PROFESSIONAL APPROACH SERIES © 2008 The Mc. Graw-Hill Companies, Inc. All rights reserved.

4 Error Value Messages THE PROFESSIONAL APPROACH SERIES © 2008 The Mc. Graw-Hill Companies, Inc. All rights reserved.

5 Evaluating a Formula If you hover a cell with an error message, a Screen. Tip explains the type of error. THE PROFESSIONAL APPROACH SERIES © 2008 The Mc. Graw-Hill Companies, Inc. All rights reserved.

6 The Evaluate Formula Dialog Box The Evaluate Formula command steps through each part of a formula so that you can determine where the error starts. THE PROFESSIONAL APPROACH SERIES © 2008 The Mc. Graw-Hill Companies, Inc. All rights reserved.

7 Setting a Data Validation List • Data validation checks a cell entry to determine if it matches conditions that have been set. • You can set the condition, an input message, and an error message. • A data validation list can be keyed, or it can use an existing range. THE PROFESSIONAL APPROACH SERIES © 2008 The Mc. Graw-Hill Companies, Inc. All rights reserved.

8 Setting a Data Validation List This data validation list is a range named Flavors. THE PROFESSIONAL APPROACH SERIES © 2008 The Mc. Graw-Hill Companies, Inc. All rights reserved.

9 Setting a Data Validation Error Alert A Warning style does allow an invalid entry to be made. THE PROFESSIONAL APPROACH SERIES © 2008 The Mc. Graw-Hill Companies, Inc. All rights reserved.

10 The Formula Auditing Group THE PROFESSIONAL APPROACH SERIES © 2008 The Mc. Graw-Hill Companies, Inc. All rights reserved.

11 The Formula Auditing Group THE PROFESSIONAL APPROACH SERIES © 2008 The Mc. Graw-Hill Companies, Inc. All rights reserved.

12 Tracing Precedents, Dependents, and Error Messages • A precedent is a cell that contributes to the results of a formula. • The Trace Precedents button is in the Formula Auditing group on the Formulas command tab. THE PROFESSIONAL APPROACH SERIES © 2008 The Mc. Graw-Hill Companies, Inc. All rights reserved.

13 Tracing Precedents The error in cell B 19 traces to cell B 9 and to cell C 4 on the Pie. Prices sheet. THE PROFESSIONAL APPROACH SERIES © 2008 The Mc. Graw-Hill Companies, Inc. All rights reserved.

14 Tracing Dependents • A dependent is a cell that relies on another cell. • The Trace Dependents button is in the Formula Auditing group on the Formulas command tab. THE PROFESSIONAL APPROACH SERIES © 2008 The Mc. Graw-Hill Companies, Inc. All rights reserved.

15 Tracing Dependents Cells B 23 and E 19 depend on the value in cell B 19. The error in cell B 19 generates errors in its dependent cells. THE PROFESSIONAL APPROACH SERIES © 2008 The Mc. Graw-Hill Companies, Inc. All rights reserved.

16 Finding and Correcting Errors The Go To Special dialog box is another tool that can aid in locating some types of errors. THE PROFESSIONAL APPROACH SERIES © 2008 The Mc. Graw-Hill Companies, Inc. All rights reserved.

17 Options in the Go to Special Dialog Box THE PROFESSIONAL APPROACH SERIES © 2008 The Mc. Graw-Hill Companies, Inc. All rights reserved.

18 Using Multiple Windows The New Window button is on the View command tab. It opens a second window with the same workbook. You can work in either window; it’s the same file. THE PROFESSIONAL APPROACH SERIES © 2008 The Mc. Graw-Hill Companies, Inc. All rights reserved.

19 Using Multiple Windows When formulas have 3 -D references, using multiple windows can aid in finding problems, too. THE PROFESSIONAL APPROACH SERIES © 2008 The Mc. Graw-Hill Companies, Inc. All rights reserved.

20 Troubleshooting Errors • Excel does not correct errors. It only helps locate them. • You must correct errors yourself. • Excel does not find all types of errors. • Displaying formulas can reveal errors that are otherwise not recognized, too. THE PROFESSIONAL APPROACH SERIES © 2008 The Mc. Graw-Hill Companies, Inc. All rights reserved.

21 Troubleshooting Errors Excel does find errors that refer to empty cells. It does not find the unusually large values in cells E 18 and E 19, however. Those are errors in this case. THE PROFESSIONAL APPROACH SERIES © 2008 The Mc. Graw-Hill Companies, Inc. All rights reserved.

22 Using the Watch Window Use the Watch Window to monitor formula results on a large worksheet or one that uses 3 -D references. THE PROFESSIONAL APPROACH SERIES © 2008 The Mc. Graw-Hill Companies, Inc. All rights reserved.

23 Using IFERROR • The IFERROR function allows you to set your own error value message. • It is in the Logical category. • It has two arguments, Value and Value_if_error. THE PROFESSIONAL APPROACH SERIES © 2008 The Mc. Graw-Hill Companies, Inc. All rights reserved.

24 Using IFERROR Instead of showing the default #VALUE! error message, this formula will show “Check cell/value on the Pie. Prices sheet. ” THE PROFESSIONAL APPROACH SERIES © 2008 The Mc. Graw-Hill Companies, Inc. All rights reserved.

25 Summary • Auditing is the practice of examining cells and formulas for accuracy. Excel provides several tools to help you audit your worksheet for accuracy. • Excel performs background error checking. Possible errors are noted with a tiny green triangle. Some error types also display an error value message. THE PROFESSIONAL APPROACH SERIES © 2008 The Mc. Graw-Hill Companies, Inc. All rights reserved.

26 Summary • When a cell displays an error value message, you can review each element in the formula step-by-step. • One way to help eliminate some types of errors is by setting data validation. This command specifies what data is permissible in a range of cells. THE PROFESSIONAL APPROACH SERIES © 2008 The Mc. Graw-Hill Companies, Inc. All rights reserved.

27 Summary • The Formula Auditing group on the Formulas tab includes commands for tracing and evaluating formula errors. • A precedent contributes or provides data to a formula’s result. • A dependent is a cell that relies on another cell for its value. THE PROFESSIONAL APPROACH SERIES © 2008 The Mc. Graw-Hill Companies, Inc. All rights reserved.

28 Summary • You can use the Go To Special dialog box to select cells with errors, blanks, and other types of data. • Although Excel can help you locate errors, you must manually correct them. THE PROFESSIONAL APPROACH SERIES © 2008 The Mc. Graw-Hill Companies, Inc. All rights reserved.

29 Summary • The Watch Window displays formula cells and related information in a separate window. • The IFERROR function enables you to create your own error messages. THE PROFESSIONAL APPROACH SERIES © 2008 The Mc. Graw-Hill Companies, Inc. All rights reserved.