1 / 26

Excel Lesson 15 Working with Auditing and Analysis Tools

Excel Lesson 15 Working with Auditing and Analysis Tools. Microsoft Office 2010 Advanced. Cable / Morrison. Objectives. Use the Trace Precedents feature. Use the Trace Dependents feature. Use the Trace Error feature. Check for errors in functions.

aldis
Download Presentation

Excel Lesson 15 Working with Auditing and Analysis Tools

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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.

More Related