1 / 36

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

Excel 2013 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

maeko
Download Presentation

Excel 2013 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 2013 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 gallery is grouped into three sections: Paste, Paste Values, and Other Paste Options. • 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 source range. • Click Copy button. • Click starting cell in destination range. • Click Paste button arrow in Clipboard group on HOME tab. • Click Transpose option. Transpose option

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

  7. Paste Data Using Paste Special Options -continued • At Paste Special dialog box, click 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 but the 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 to achieve 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 desired cell active. • Click DATA tab. • Click What-If Analysis button. • Click Goal Seek option. continues on next slide… Goal Seek option

  11. Use Goal Seek to Populate a Cell -continued • Enter desired cell address in Set cell text box. • Enter desired target value in To value text box. • Enter dependent cell address in 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 Goal Seek Status dialog box, click OK or Cancel to accept or reject 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 how each set of assumptions affects 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 BestCase or WorstCase 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 DATA tab. • Click What-If Analysis button. • Click Scenario Manager option. • Click Add button. • At Add Scenario dialog box, type a name in Scenarioname text box. • Type or select variable cells in 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 Scenario Values dialog box, enter values for each changing cell. • Click OK. • Click Close button. Scenario Values dialog box

  16. Create Assumptions for What-If Analysis Using Scenario Manager continued To display a scenario: • Click DATA tab. • Click What-If Analysis button. • Click Scenario Manager option. • Click desired scenario name. • Click Show button. • Click Close button. Show button

  17. Create Assumptions for What-If Analysis Using Scenario Manager -continued To create a scenario summary report: • Click DATA tab. • Click What-If Analysis button. • Click Scenario Manager option. • Click Summary button. • If necessary, change cell address in Result cells text box at 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 variable data in column at right of worksheet. • Enter formula one row above and one cell to right of variable data. • Select data range including formula cell. continues on next slide… formula

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

  22. Perform What-If Analysis Using Data Tables -continued • Type cell address for variable data in source formula in 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 variable data at right of worksheet with one input series in a column and another in a row across top of table. • Enter formula in top left cell of table. • Select data table range. • Click DATA tab. • Click What-If Analysis button. • Click Data Table option continues on next slide… formula

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

  25. Use Auditing Tools • The Formula Auditing group on the FORMULAS tab contains buttons that are useful for viewing relationships between cells in formulas. Toggle between formula display and cell display. Draw arrows to cells that provide data to the active cell. Show error checking tools for the active cell. Draw arrows to cells that use the data in the active cell. Open a window in which to place cells for viewing while moving/editing within the worksheet. Clear the arrows to/from the active cell. Work through a formula value by value to determine how the result is calculated.

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

  27. Use Auditing Tools - continued To trace dependent cells: • Open worksheet. • Make desired cell active. • Click FORMULAS tab. • Click Trace Dependents button. • 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 and require that you check a worksheet by entering proof formulas or bymanually checking the accuracy of each formula.

  29. Use Auditing Tools - continued

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

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

  32. Use Auditing Tools - continued To watch a formula cell: • Click FORMULAS tab. • Click Watch Window button. • Click Add Watch button. • At Add Watch dialog box, click desired cell. • Click 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 on 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