Excel Lesson 15 Working with Auditing and Analysis Tools

1. Excel Lesson 15Working with Auditing and Analysis Tools Microsoft Office 2010 Advanced Cable / Morrison

2. Objectives • Use the Trace Precedents feature. • Use the Trace Dependents feature. • Use the Trace Error feature. • Check for errors in functions. • Perform a what-if analysis using the Goal Seek feature. 2 2

3. Objectives (continued) • Create a scenario. • View the scenario summary. • Consolidate data. • Create a one-way data table.

4. Vocabulary • audit • consolidating • dependent • precedent • Scenario Manager • tracer arrow 4 4

5. Introduction • Excel offers many tools to: • Check for accuracy in formulas • Solve problems with formulas • Analyze existing data • Trace Precedents • Trace Dependents • Trace Error

6. Introduction (continued) • Error Checking • Goal Seek • Scenario Manager • Data Table

7. Using Trace Precedents • When you auditsomething, you are checking it for accuracy. • The Trace Precedents feature finds the cells that are used in afunction. • Precedentsrefer to cells that supply the values used in a function.

8. Using Trace Precedents (continued) • Example of trace precedents

9. Using Trace Dependents • The Trace Dependents feature works by locating formulas and/or functions that depend on the value in a selected cell. • If you select a cell containing data and then click the Trace Dependents button: • A tracer arrow is drawn from the selected cell, called the dependent, pointing to the cell with the function.

10. Using Trace Dependents (continued) • Average function depends on cells with tracer arrows

11. Using Trace Error • To locate the source of an error, click the cell with the error and then click the Trace Error command. • Excel displays an information icon next to the cell with an error status. • Click the icon to see a list with options on how to resolve the error.

12. Using Trace Error (continued) • Function error traced

13. Checking for Errors • The Error Checking feature is used to check all of the formulas and functions in a worksheet that has a lot of data. • When an error is located, the Error Checking dialog box gives information about the error. • If no errors are found, a dialog box lets you know that the error check for the worksheet is complete.

14. Checking for Errors (continued) • Error Checking dialog box

15. Performing a What-if Analysis Using the Goal Seek Feature • The Goal Seek feature finds the unknown value you need in order to accomplish your goal. • The Goal Seek feature is referred to as a “what-if” analysis tool. Goal Seek dialog box

16. Creating Scenarios • The Scenario Managerperforms a “what-if” analysis that lets you change several cells of data. • The Scenario feature is another type of “what-if” analysis. • Scenarios are sometimes used to view various changes in expenses.

17. Creating Scenarios (continued) • Scenario Manager dialog box with scenarios

18. Viewing the Scenario Summary • The Scenario Manager lets you view the created scenarios in a formatted report on a separate worksheet. • Excel automatically names the new worksheet Scenario Summary. • Excel also applies formatting to the data.

19. Viewing the Scenario Summary (continued) • Scenario Summary worksheet

20. Consolidating Data • In Excel, consolidatinginvolves bringing data from several worksheets together into one worksheet. • You can choose various functions to use when consolidating. • Examples: Sum, Average, etc.

21. Consolidating Data (continued) • Completed Consolidate dialog box

22. Creating a One-Way Data Table • A one-way data table uses one function to change cells that use this function. Example of a one-way data table

23. Summary In this lesson, you learned: • The Trace Precedents feature finds the cells that are used in the function. • The Trace Error feature locates cells that are used in a formula to assist in identifying the source of an error.

24. Summary (continued) • The Trace Dependents button works by locating formulas or functions that depend on the value in a selected cell. • If a cell containing a formula has an error, the Trace Error feature displays the cells used in the formula. • You can check for errors throughout an entire worksheet, using the Error Checking feature.

25. Summary (continued) • The Goal Seek feature can perform a “what-if” analysis based on a single function. • The Scenario Manager performs a “what-if” analysis and lets you view the results based on changing several cells of data. • You can view scenarios in the scenario summary.

26. Summary (continued) • Data from multiple worksheets can be totaled using the Consolidate feature. • A one-way data table shows various results for a function within a range of cells.