More Related Content
What's hot (19)
Viewers also liked (18)
Similar to Excel ch08 (15)
More from Kristin Harrison (20)
Recently uploaded (20)
Excel ch08
- 1. Microsoft Excel 2013 Chapter 8 Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers
- 2. Objectives • Analyze worksheet data using a trendline • Create a PivotTable report • Format a PivotTable report • Apply filters to a PivotTable report • Create a PivotChart report • Format a PivotChart report • Apply filters to a PivotChart report Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 2
- 3. Objectives • Analyze worksheet data using PivotTable and PivotChart reports • Create calculated fields • Create slicers to filter PivotTable and PivotChart reports • Format slicers • Analyze PivotTable and PivotChart reports using slicers Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 3
- 4. Project – On Your Feet Sneakers Sales Analysis Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 4
- 5. Roadmap • Create the line chart and trendline • Create the PivotTable • Change the layout and view of the PivotTable • Filter the PivotTable • Format the PivotTable • Create the PivotChart • Change the PivotChart view and contents • Add slicers Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 5
- 6. Creating a 2-D Line Chart • Select the range of cells to be charted • Tap or click the Insert Line Chart button on the INSERT tab to display the Line Chart gallery • Tap or click the Line with Markers chart type in the 2-D Line area of the Insert Line Chart gallery to insert a 2-D line chart with data markers • Tap or click the Move Chart button on the CHART TOOLS DESIGN tab to display the Move Chart dialog box Working with Trendlines, PivotTable Reports, PivotChart Reports and Slicers 6
- 7. Creating a 2-D Line Chart • If necessary, double-tap or double-click the default text in the New sheet text box to select the text • Type the desired chart name • Tap or click the OK button to move the chart to a new sheet • Tap or click the default title to select it • Type the desired chart title and then press the ENTER key Working with Trendlines, PivotTable Reports, PivotChart Reports and Slicers 7
- 8. Creating a 2-D Line Chart Working with Trendlines, PivotTable Reports, PivotChart Reports and Slicers 8
- 9. Adding a Trendline to a Chart • Tap or click the chart to select it • Tap or click the Add Chart Elements button on the CHART TOOLS DESIGN tab to display the Add Chart Element menu • Tap or click the More Trendline Options to display the Format Trendline dialog box • If necessary, tap or click the Trendline Options button • Tap or click the desired trendline type • Tap or click the desired options in the Format Trendline dialog box • Tap or click the Close button to add the trendline with the selected options • Tap or click a desired theme on the PAGE LAYOUT tab Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 9
- 10. Adding a Trendline to a Chart Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 10
- 11. Changing the Format of a Data Point • Tap or click the desired data point twice to select the single point • Right-click or press and hold (touchscreen) the selected data point do display the shortcut menu • Tap or click Format Data Point on the shortcut menu to display the Format Data Point task pane • Tap or click the Fill & Line button, then tap or click MARKER • If necessary, tap or click MARKER OPTIONS to expand the section and select the desired options • Tap or click the Close button Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 11
- 12. Changing the Format of a Data Point Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 12
- 13. Creating a Blank PivotTable • Tap or click the desired cell containing the data for the PivotTable • Tap or click the PivotTable button on the INSERT tab to display the Create PivotTable dialog box • Click the OK button to create a blank PivotTable report on a new worksheet and display the PivotTable Fields task pane Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 13
- 14. Creating a Blank PivotTable Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 14
- 15. Adding Data to the PivotTable • Drag the desired field from the Choose fields to add to report area to the desired locations (rows or columns) • Drag the desired fields to the VALUES area Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 15
- 16. Adding Data to the PivotTable Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 16
- 17. Changing the Layout of a PivotTable • Tap or click the Report Layout button on the PIVOTTABLE TOOLS DESIGN tab to display the Report Layout menu • Click the desired layout Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 17
- 18. Changing the View of a PivotTable Report • Change the order of the fields in the ROWS or COLUMNS area to change the view Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 18
- 19. Filtering a PivotTable Report Using a Report Filter • Drag the desired field from the ‘Choose fields to add to report’ area to the Report Filter area in the PivotTable Fields task pane • Tap or click the field’s Filter button to display the Filter menu • Click to select the desired criterion • Click the OK button to update the display Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 19
- 20. Filtering a PivotTable Report Using a Report Filter Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 20
- 21. Filtering a PivotTable Report Using Multiple Selection Criteria • Drag the desired button from the ROWS or COLUMNS area to the FILTERS area • Drag the desired button from the FILTERS area to the ROWS or COLUMNS area • Tap or click the filter button to display the filter menu • Tap or click the desired check boxes to prepare to select multiple criteria • Tap or click the OK button Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 21
- 22. Filtering a PivotTable Report Using Multiple Selection Criteria Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 22
- 23. Removing a Report Filter from a PivotTable Report • Tap or click the filter button and tap or click the All check box to include all criteria in the PivotTable report • Tap or click the OK button • Drag the desired button out of the PivotTable Fields task pane to remove it from the report Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 23
- 24. Removing a Report Filter from a PivotTable Report Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 24
- 25. Filtering a PivotTable Report Using the Row Label Filter • Drag the desired button out of the ROWS area in the PivotTable Fields task pane to remove the field from the report • Tap or click the desired check box in the Choose fields to add to report list to add the desired field to the ROWS area • Tap or click the filter button to display the filter menu for the desired field • Tap or click the desired check boxes on the filter menu • Tap or click the OK button to update the display Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 25
- 26. Filtering a PivotTable Report Using the Row Label Filter Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 26
- 27. Removing a Row Label Filter from am PivotTable Report • Tap or click the Filter button to display the Filter menu • Tap or click the Clear Filter From command to remove the row label filter Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 27
- 28. Formatting a PivotTable Report • Select a cell in the PivotTable • Tap or click the More button in the PivotTable Styles gallery in the PIVOTTABLE TOOLS DESIGN tab to expand the gallery • Click the desired style • Format the cells as desired using the Format Cells dialog box Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 28
- 29. Formatting a PivotTable Report Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 29
- 30. Switching Summary Functions in a PivotTable • Press and hold or right-click the desired cell to display the shortcut menu and prepare for changing the summary function • Point to Summarize Values By on the shortcut menu to display the Summarize Values By menu • Click the desired command Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 30
- 31. Switching Summary Functions in a PivotTable Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 31
- 32. Adding a Second Value Calculation to a PivotTable Report • Drag the desired field from the PivotTable Fields task pane to the VALUES area • Tap or click the desired calculation • Tap or click the Value Field Settings to display the Value Field Settings dialog box • Tap or click the desired settings and then tap or click the OK button Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 32
- 33. Adding a Second Value Calculation to a PivotTable Report Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 33
- 34. Customizing the Display of the Field List and Field Headers in the PivotTable Report • Tap or click the Field list button and the Field Headers button on the PIVOTTABLE TOOLS ANALYZE tab to show or hide the fields • Tap or click the PivotTable Options button on the PIVOTTABLE TOOLS ANALYZE tab to display the PivotTable Options dialog box • Tap or click the desired settings • Tap or click the OK button Working with Trendlines, PivotTable Reports, PivotChart Reports and Slicers 34
- 35. Customizing the Display of the Field List and Field Headers in the PivotTable Report Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 35
- 36. Customizing the Display of the Expand and Collapse Buttons in a PivotTable Report • Tap or click the Collapse button to collapse or expand the desired selection • Press and hold or right-click the desired cell to display the shortcut menu and then point to Expand/Collapse to display the Expand/Collapse submenu • Tap or click the desired settings Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 36
- 37. Customizing the Display of the Expand and Collapse Buttons in a PivotTable Report Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 37
- 38. Updating the Contents of a PivotTable Report • Modify the contents of the desired cell on the desired sheet tab • Tap or click the desired PivotTable sheet tab to make it active • Tap or click any cell in the PivotTable report to make it active • Tap or click the Refresh button on the PIVOTTABLE TOOLS ANALYZE tab to update the PivotTable report Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 38
- 39. Updating the Contents of a PivotTable Report Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 39
- 40. Creating a PivotChart Report from an Existing PivotTable Report • Select a cell in the PivotTable report • Click the Field List button on the PIVOTTABLE TOOLS ANALYZE tab to display the PivotTable Field List pane • Click the PivotChart button on the PIVOTTABLE TOOLS ANALYZE tab to display the Insert Chart dialog box • Click the desired chart type • Click the OK button to add the chart to the worksheet Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 40
- 41. Creating a PivotChart Report from an Existing PivotTable Report Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 41
- 42. Changing the Location of a PivotChart Report and Deleting Data • With the chart selected, click the Move Chart button on the PIVOTTABLE TOOLS DESIGN tab PivotChart Tools to display the Move Chart dialog box • Tap or click the New sheet option button to select it • Type the desired name in the New sheet text box • Tap or click the OK button to move the chart to the new sheet • In the PivotTable Field List pane, drag the desired items out of the Values area Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 42
- 43. Changing the Location of a PivotChart Report and Deleting Data Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 43
- 44. Changing the PivotChart Type and Formatting the Chart • Tap or click the chart to select the data series • Press and hold or right-click to display the shortcut menu • Tap or click Format Data Series on the shortcut menu to open the Format Data Series task pane • Tap or click the desired chart type • Tap or click the Close button • Tap or click the Chart Elements arrow on the PIVOTCHART • TOOLS FORMAT tab to display the Chart Elements menu • Tap or click the desired formatting changes Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 44
- 45. Changing the PivotChart Type and Formatting the Chart Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 45
- 46. Changing the View of a PivotChart • If necessary, click the Field List button on the PIVOTCHART TOOLS ANALYZE tab to display the PivotTable Field List pane • Select and deselect the fields as desired • Add, delete, and reorder the desired fields in the AXIS area • Tap or click the PivotChart Report sheet tab to make it the active tab Working with Trendlines, PivotTable Reports, PivotChart Reports and Slicers 46
- 47. Changing the View of a PivotChart Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 47
- 48. Creating a PivotChart Report Directly from Data • Select a cell containing the data • Tap or click the PivotChart arrow on the INSERT tab to display the PivotChart menu • Tap or click the PivotChart & PivotTable on the PivotChart menu to display the Create PivotTable with PivotChart dialog box • If necessary, tap or click the New Worksheet option button • Tap or click the OK button to add a new worksheet containing a blank PivotTable and blank PivotChart • Add the desired fields to the Axis Fields and Values areas Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 48
- 49. Creating a PivotChart Report Directly from Data Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 49
- 50. Adding a Calculated Field to a PivotTable Report • If necessary, tap or click the PivotTable to make it active • Tap or click the Fields, Items, & Sets button on the PIVOTTABLE TOOLS ANALYZE tab to display the Fields, Items, & Sets menu • Tap or click the Calculated Field command on the Fields, Items, & Sets menu to display the Insert Calculated Field dialog box • Enter the name, and formula, and then click the Add button to add the calculated field to the Fields list • Click the OK button to close the dialog box Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 50
- 51. Adding a Calculated Field to a PivotTable Report Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 51
- 52. Formatting the PivotTable and PivotChart • Tap or click the Field List button on the PIVOTTABLE TOOLS ANALYZE tab to display the PivotTable Fields task pane • Tap or click DESIGN on the ribbon to display the PIVOTTABLE TOOLS DESIGN tab and make the desired formatting changes • Use the Field List button, the +/- Buttons button, and the Field Headers button on the PIVOTTABLE TOOLS ANALYZE to hide the field list, the Expand/Collapse buttons, and the field headers Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 52
- 53. Formatting the PivotTable and PivotChart Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 53
- 54. Formatting the PivotChart • Tap or click in the PivotChart report to select it • Apply the desired styles from the Chart Styles gallery • Use the ‘Chart Quick Colors’ button on the PIVOTCHART TOOLS DESIGN tab to change the colors • Use the ‘Add Chart Element’ button on the PIVOTCHART TOOLS DESIGN tab to position the legend • Tap or click the Field Buttons button on the PIVOTCHART TOOLS ANALYZE tab to show or hide the field button Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 54
- 55. Formatting the PivotChart Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 55
- 56. Copying a PivotTable and PivotChart • Create a copy of the worksheet Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 56
- 57. Formatting the PivotChart • Use the ‘Chart Quick Colors’ button on the PIVOTCHART DESIGN tab to apply the desired color Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 57
- 58. Formatting the PivotChart • If necessary, tap or click to make the PivotChart active • Tap or click the Insert Slicer button arrow on the PIVOTCHART TOOLS ANALYZE tab to display the Insert Slicers dialog box • Click the check boxes to select the desired slicers to insert • Click the OK button to display the selected slicers on the worksheet Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 58
- 59. Formatting the PivotChart Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 59
- 60. Formatting Slicers • Tap or click and then drag the desired slicer to the desired location • Hold down the ctrl key and then tap or click to select multiple slicers • Tap or click the Height box on the SLICER TOOLS OPTIONS tab to set the button height • Tap or click the desired Slicer style on the SLICER TOOLS OPTIONS tab to apply it to the slicers • Tap or click any cell to deselect the slicer Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 60
- 61. Formatting Slicers Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 61
- 62. Using the Slicers to Review Groups in the PivotTable • Hold down the CTRL key and then tap or click the desired slicer to remove the data from the PivotTable and PivotChart calculations. • Release the CTRL key and then tap or click the desired slicer to display that slicer’s data only • Tap or click different slicers slicer to see the data for those slicers only Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 62
- 63. Using the Slicers to Review Groups in the PivotTable Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 63
- 64. Using the Slicers to Review Groups Not in the PivotTable • Tap or click the Clear Filter button on the desired slicers to remove the filters and return the PivotTable and PivotCharts to their unfiltered states • Tap or click Custom in the desired slicer to see the aggregate data options • Tap or click different styles and combinations of styles to see how the aggregate data changes Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 64
- 65. Using the Slicers to Review Groups Not in the PivotTable Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 65
- 66. Chapter Summary • Analyze worksheet data using a trendline • Create a PivotTable report • Format a PivotTable report • Apply filters to a PivotTable report • Create a PivotChart report • Format a PivotChart report • Apply filters to a PivotChart report Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 66
- 67. Chapter Summary • Analyze worksheet data using PivotTable and PivotChart reports • Create calculated fields • Create slicers to filter PivotTable and PivotChart reports • Format slicers • Analyze PivotTable and PivotChart reports using slicers Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers 67
- 68. Microsoft Excel 2013 Chapter 8 Complete