1 / 23

Excel Chapter 9

Excel Chapter 9. Part 3: Creating and Formatting Pivot Charts and PivotTables. Creating a PivotTable. Select cell A3 and then click the Insert tab on the Ribbon Click the PivotTable button arrow on the Ribbon to display the PivotTable menu

nijole
Download Presentation

Excel Chapter 9

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 Chapter 9 Part 3: Creating and Formatting Pivot Charts and PivotTables

  2. Creating a PivotTable • Select cell A3 and then click the Insert tab on the Ribbon • Click the PivotTable button arrow on the Ribbon to display the PivotTable menu • Click PivotTable to display the Create PivotTable dialog box • Click the OK button to create the PivotTable on a new worksheet and display the PivotTable Field List pane Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  3. Creating a PivotTable Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  4. Adding Data to the PivotTable • Drag the Shop Type field from the ‘Choose fields to add to report’ list to the Row Labels area to add the Shop Type field to the PivotTable • Drag the State field from the ‘Choose fields to add to report’ list below the Shop Type button in the Row Labels area to add the State field to the PivotTable • Drag the Revenue per Repair Person 2007 field to the Values area to add the field to column B of the PivotTable • Drag the Revenue per Repair Person 2008 field to the Values area to add the field to column C of the PivotTable Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  5. Adding Data to the PivotTable Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  6. Changing the View of a PivotTable • Drag the Shop Type button in the Row Labels area below the State button to group revenue by shop type, rather than by state • Click the Sum of Revenue per Repair Person 2007 button arrow in the Values area to display the shortcut menu • Click Remove Field on the shortcut menu to remove Sum of Revenue per Repair Person 2007 from the PivotTable • Click the Undo button on the Quick Access Toolbar to undo the removal of Sum of Revenue per Repair Person 2007 column from the PivotTable • Click the Row Labels AutoFilter button in cell A4 to display the Row Labels AutoFilter menu Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  7. Changing the View of a PivotTable • When Excel displays the RowLabels AutoFilter menu, click the check boxes for Kansas and North Dakota to clear them • Click the OK button to display only the Nebraska and South Dakota data in the PivotTable • Click the Undo button on the Quick Access Toolbar to display the data for all states Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  8. Changing the View of a PivotTable Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  9. Filtering a PivotTable Using a Page Field • Drag the State button in the Row Labels area to the Report Filter area in the PivotTable Field List pane to create a new page field in the PivotTable • Click the State AutoFilter button in cell B1, click South Dakota on the AutoFilter menu, and then click the OK button to display totals for South Dakota Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  10. Filtering a PivotTable Using a Page Field Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  11. Formatting a PivotTable • Double-click the Sheet1 tab • Type PivotTable and then press the ENTER key. • Select cell A4, right-click the PivotTable tab, and then point to Tab Color on the shortcut menu. • Click Olive Green, Accent 3 (column 7, row 1) in the Theme Color area • If necessary, select a cell in the PivotTable and then click the Design tab on the Ribbon Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  12. Formatting a PivotTable • Click the PivotTable Styles More button to display the PivotTable Styles gallery • Point to the Pivot Style Medium 11 PivotTable style in the PivotTable Styles gallery to view a preview of the style in the PivotTable • Click the Pivot Style Medium 11 PivotTable style to apply the style to the PivotTable • Right-click anywhere in cell B4 to display the shortcut menu • Click Number Format on the shortcut menu to display the Format Cells dialog box Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  13. Formatting a PivotTable • When Excel displays the Format Cells dialog box, if necessary, click Currency in the Category list, type 0 in the Decimal places box, and then click the OK button • Right-click anywhere in cell C4 to display the shortcut menu • Click Number Format on the shortcut menu to display the Format Cells dialog box • When Excel displays the Format Cells dialog box, click Currency in the Category list, type 0 in the Decimal places box, and then click the OK button to format the numeric values in the PivotTable with the Currency style • Select cell A9 to deselect the worksheet • Click the Save button on the Quick Access toolbar to save the workbook Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  14. Formatting a PivotTable Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  15. Switching Summary Functions in a PivotTable • Select cell B4 and then drag the State button in the Report Filter area above the Shop Type button in the Row Labels area • Right-click cell B4 to display the shortcut menu • Point to Summarize Data By on the shortcut menu • Click Average on the Summarize Data By submenu to change the numbers in column B from sums to averages • Right-click cell C4 to display the shortcut menu Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  16. Switching Summary Functions in a PivotTable • Point to Summarize Data By on the shortcut menu • Click Average on the Summarize Data By submenu to change the numbers in column C from sums to averages • Click cell A19 to deselect the PivotTable and close the PivotTable Field List Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  17. Switching Summary Functions in a PivotTable Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  18. Creating a PivotChart, Changing the PivotChart Type, and Formatting the Chart • Select cell A4 • Click the Options tab on the Ribbon and then click the PivotChart button on the Ribbon to display the Insert Chart dialog box • Click Clustered Cylinder (column 1, row 2) in the ‘Column chart type’ gallery • Click the OK button to add the chart to the PivotTable worksheet • With the chart selected, click the Move Chart button on the Ribbon to display the Move Chart dialog box • When Excel displaysthe Move chart dialog box, click New sheet to select it and then type PivotChart in the New sheet text box Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  19. Creating a PivotChart, Changing the PivotChart Type, and Formatting the Chart • Click the OK button • Right-click the PivotChart tab and then point to Tab Color on the shortcut menu • Click Purple, Accent 4 (column 8, row1) to change the color of the PivotChart sheet tab • Click the Layout tab on the Ribbon and then click the Chart Title button on the Ribbon to display the Chart Title menu • Point to Above Chart on the Chart Title menu • Click Above Chart on the Chart Title menu • When the title is placed above the chart, select the title text. Type FasToast Ovens Analysis and then click anywhere on the chart to add the title to the chart Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  20. Creating a PivotChart, Changing the PivotChart Type, and Formatting the Chart Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  21. Changing the View of a PivotChart • Drag the Shop Type button in the Row Labels above the State button to group the data by state within shop type • Click the PivotTable sheet tab Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  22. Changing the View of a Pivot Chart Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  23. Closing the Workbook • Click the workbook Close button • When Excel displays the Microsoft Office Excel dialog box, click the Yes button Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

More Related