1 / 36

Excel 2010 Level 2 Unit 2 Managing and Integrating Data and the Excel Environment

Excel 2010 Level 2 Unit 2 Managing and Integrating Data and the Excel Environment Chapter 5 Using Data Analysis Features. Using Data Analysis Features. Quick Links to Presentation Contents. Paste Data Using Paste Special Options Use Goal Seek to Populate a Cell

audi
Download Presentation

Excel 2010 Level 2 Unit 2 Managing and Integrating Data and the Excel Environment

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 2010 Level 2 Unit 2Managing and Integrating Data and the Excel Environment Chapter 5Using Data Analysis Features

  2. Using Data Analysis Features Quick Links to Presentation Contents • Paste Data Using Paste Special Options • Use Goal Seek to Populate a Cell • Create Assumptions for What-If Analysis Using Scenario Manager • CHECKPOINT 1 • Perform What-If Analysis Using Data Tables • Use Auditing Tools • CHECKPOINT 2

  3. Paste Data Using Paste Special Options • The Paste drop-down gallery contains many options for pasting copied data. The galleryis grouped into three sections: Paste, Paste Values, and Other Paste Options. • In Excel 2010, the Paste gallery includes a live preview of how the data will be pasted to help you choose the best paste option. Paste Special dialog box

  4. Paste Data Using Paste Special Options…continued • The data in a worksheet is not always arranged appropriately for the analysis you want to perform. worksheet

  5. Paste Data Using Paste Special Options…continued To transpose a range: • Select the source range. • Click the Copy button in the Clipboard group in the Home tab. • Click the starting cell in the destination range. • Click the Paste button arrow in the Clipboard group in the Home tab. • Click the Transpose option. Transpose option

  6. Paste Data Using Paste Special Options…continued To perform a mathematical operation during pasting: • Select the source range values. • Click the Copy button in the Clipboard group in the Home tab. • Click the starting cell in the destination range. • Click the Paste button arrow in the Clipboard group in the Home tab. • Click the Paste Special option. continues on next slide… Paste Special option

  7. Paste Data Using Paste Special Options…continued • At the Paste Special dialog box, click the desired mathematical operation. • Click OK. Paste Special dialog box

  8. Paste Data Using Paste Special Options…continued • Other options at the Paste Special dialog box include: • Formulas or Values to paste the source formulas or displayed values only, • Formats to paste only formatting options from the source, • Validation to paste a validation rule, • All using Source theme to apply the theme from the source, • All except borders to paste everything except borders from the source, and • Column widths to adjust the destination cells to the same column width as the source.

  9. Use Goal Seek to Populate a Cell • Goal Seek calculates a value based on a target you specify in another cell. Goal Seek can determine the value that needs to be entered for the final test toachieve an average grade that you specify in B11.

  10. Use Goal Seek to Populate a Cell…continued To use Goal Seek to return a value: • Make the desired cell active. • Click the Data tab. • Click the What-If Analysis button in the Data Tools group. • Click the Goal Seek option. continues on next slide… Goal Seek option

  11. Use Goal Seek to Populate a Cell…continued • Enter the desired cell address in the Set cell text box. • Enter the desired target value in the To value text box. • Enter the dependent cell address in the By changing cell text box. • Click OK. continues on next slide… By changing cell text box

  12. Use Goal Seek to Populate a Cell…continued • At the Goal Seek Status dialog box, click OK or Cancel to accept or reject the results. Goal Seek Status dialog box

  13. Create Assumptions for What-If Analysis Using Scenario Manager • The Scenario Manager allows you to store multiple sets of assumptions about data and then view the impact of those assumptions on your worksheet. • You can switch the display between scenarios to test the various inputs on your worksheet model. • You can save each scenario using a descriptive name such as Best Case or Worst Case to indicate the type of data assumptions you have stored.

  14. Create Assumptions for What-If Analysis Using Scenario Manager…continued To add a scenario: • Click the Data tab. • Click the What-If Analysis button in the Data Tools group. • Click the Scenario Manager option. • Click the Add button. • At the Add Scenario dialog box, type a name in the Scenarioname text box. • Type or select variable cells in the Changing cells text box. • Click OK. continues on next slide… Add Scenario dialog box

  15. Create Assumptions for What-If Analysis Using Scenario Manager…continued • At the Scenario Values dialog box, enter the values for each changing cell. • Click OK. • Click the Close button. Scenario Values dialog box

  16. Create Assumptions for What-If Analysis Using Scenario Manager…continued To display a scenario: • Click the Data tab. • Click the What-If Analysis button in the Data Tools group. • Click the Scenario Manager option. • Click the desired scenario name. • Click the Show button. • Click the Close button. Show button

  17. Create Assumptions for What-If Analysis Using Scenario Manager…continued To create a scenario summary report: • Click the Data tab. • Click the What-If Analysis button in the Data Tools group. • Click the Scenario Manager option. • Click the Summary button. • If necessary, change the cell address in the Result cells text box at the Scenario Summary dialog box. • Click OK. Scenario Summary dialog box

  18. CHECKPOINT 1 • This drop-down gallery contains many options for pasting copied data. • Options • Cut • Copy • Paste • This feature calculates a value using a target that you want to achieve. • Goal Seek • Scenario Manager • Scenario Seek • Value Manager Answer Answer Next Question Next Question • This option will convert columns to rows and rows to columns. • Transpose • Duplicate • Convert • Switch • This feature allows you to store multiple sets of assumptions about data. • Goal Seek • Scenario Manager • Scenario Seek • Value Manager Answer Answer Next Question Next Slide

  19. Perform What-If Analysis Using Data Tables • A data table is a range of cells that contains a series of input values. • Excel calculates a formula substituting each input value in the data table range and places the result in the cell adjacent to the value. • You can create one-variable and two-variable data tables. • A one-variable data table calculates a formula by modifying one input value in the formula. • A two-variable data table calculates a formula substituting two input values.

  20. Perform What-If Analysis Using Data Tables…continued To create a one-variable data table: • Create the variable data in the column at the right of the worksheet. • Enter the formula one row above and one cell to the right of the variable data. • Select the data range including the formula cell. continues on next slide… formula

  21. Perform What-If Analysis Using Data Tables…continued • Click the Data tab. • Click the What-If Analysis button in the Data Tools group. • Click the Data Table option. continues on next slide… Data Table option

  22. Perform What-If Analysis Using Data Tables…continued • Type the cell address for the variable data in the source formula in the Column input cell text box. • Click OK. Column input cell text box

  23. Perform What-If Analysis Using Data Tables…continued To create a two-variable data table: • Create the variable data at the right of the worksheet with one input series in a column and another in a row across the top of the table. • Enter the formula in the top left cell of the table. • Select the data table range. • Click the Data tab. • Click the What-If Analysis button in the Data Tools group. • Click the Data Table option continues on next slide… formula

  24. Perform What-If Analysis Using Data Tables…continued • Type the cell address for the variable data in the source formula in the Row input cell text box. • Press Tab. • Type the cell address for the variable data in the source formula in the Column input cell text box. • Click OK. Column input cell text box

  25. Use Auditing Tools • The Formula Auditing group in the Formulas tab contains buttons that are useful for viewing relationships between cells in formulas. Draw arrows to cells that provide data to the active cell. Toggle between formula display and cell display. Show error checking tools for active cell. Draw arrows to cells that use the data in the active cell. Open a window in which you can place cells that you want to view as you move/edit within the worksheet. Clear the arrows to/from the active cell. Step through a formula value by value to determine how the result is calculated.

  26. Use Auditing Tools…continued To trace precedent cells: • Open the desired worksheet. • Make the desired cell active. • Click the Formulas tab. • Click the Trace Precedents button in the Formula Auditing group. • Continue clicking until all relationships are visible. tracer arrow

  27. Use Auditing Tools…continued To trace dependent cells: • Open the worksheet. • Make the desired cell active. • Click the Formulas tab. • Click the Trace Dependents button in the Formula Auditing group. • Continue clicking until all relationships are visible. tracer arrows

  28. Use Auditing Tools…continued • Formulas in Excel can contain various types of errors. Some errors are obvious because Excel displays an error message such as #VALUE!. • Logic errors are more difficult to find. These errorsrequire that you check a worksheet by entering proof formulas or by individually checking accuracy.

  29. Use Auditing Tools…continued

  30. Use Auditing Tools…continued To trace errors: • Click the cell containing the error message. • Click the Formulas tab. • Click the down-pointing arrow on the Error Checking button in the Formula Auditing group. • Click the Trace Error option. tracer arrow

  31. Use Auditing Tools…continued To circle invalid data: • Open the worksheet containing the validation rules. • Click the Data tab. • Click the down-pointing arrow on the Data Validation button in the Data Tools group. • Click the Circle Invalid Data option. Circle Invalid Data option

  32. Use Auditing Tools…continued To watch a formula cell: • Click the Formulas tab. • Click the Watch Window button in the Formula Auditing group. • Click the Add Watch button in the Watch Window. • At the Add Watch dialog box, click the desired cell. • Click the Add button. Add Watch dialog box

  33. Use Auditing Tools…continued • Checking a worksheet for accuracy using auditing and error checking tools is an important skill to develop. • Worksheets provide critical information to decision-makers who rely on the validity of the data.

  34. CHECKPOINT 2 • This range of cells contains a series of input values. • data table • range table • range data • input table • These cells provide data to a formula cell. • precedent cells • dependent cells • provider cells • formula cells Answer Answer Next Question Next Question • The What-If Analysis button is located in this tab. • Home • Insert • Data • Formulas • These cells contain a formula that refers to other cells. • precedent cells • dependent cells • provider cells • formula cells Answer Answer Next Question Next Slide

  35. Using Data Analysis Features Summary of Presentation Concepts • Switch data arranged in columns to rows and vice versa • Perform a mathematical operation during a paste routine • Populate a cell using Goal Seek • Save and display various worksheet models using Scenario Manager • Create a scenario summary report • Create a one-variable data table to analyze various outcomes • Create a two-variable data table to analyze various outcomes • View relationships between cells in formulas • Identify excel error codes and troubleshoot a formula using formula auditing tools • Circle invalid data • Use the Watch Window to track a value

More Related