1 / 23

Pivot Tables

Pivot Tables. Overview. What are Pivot Tables. Pivot tables in Excel are a versatile reporting tool that makes it easy to extract information from large tables of data without the use of formulas Uses

everly
Download Presentation

Pivot Tables

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. Pivot Tables Overview

  2. What are Pivot Tables • Pivot tables in Excel are a versatile reporting tool that makes it easy to extract information from large tables of data without the use of formulas • Uses • Data in the table can be categorized and summarized without making you create formulas to perform the calculations. • Pivot tables also allow user to rearrange the summarized data simply by rotating row and column headings

  3. Enter Worksheet Data • At least three columns of data are needed to create a pivot table. • Columns must have column heading • It is important to enter data correctly. • Errors, caused by incorrect data entry, are the source of many problems related to data management. • Leave no blank rows or columns when entering the data. • This includes NOT leaving a blank row between the column headings and the first row of data.

  4. Leave no blank rows or columns • Leave NOblank row between the column headings and the first row of data.

  5. Before creating Pivot Table • Convert the source data to a table before creating the pivot table • This updates the pivot table to include any data that is added to the data table after pivot table is created • Steps • Click on cell in the source data table • Select insert tab, • Select table • Select Ok

  6. 2. Select Insert Tab 3. Select table • Select a Data Cell • In this example cell B4

  7. Entire data range is selected My table has headers is checked

  8. Data source after conversion to data table

  9. Converting data source to table gives you option to easily sort or filter on a column(field)

  10. Create the Pivot Table • Highlight any cell in the data source table • Select insert tab • Select Pivot Table • Data range will be outlined for you • Default output for pivot table will be to place it into a new worksheet • Select ok

  11. Table range is outlined • New worksheet default is checked

  12. Pivot Table Worksheet

  13. To create the pivot table • Drag field names into the following boxes • Report Filter • Row Labels • Column Labels • Should be text • Values Box • Should only contain numbers to be summarized in some manner

  14. Pivot table example(Sales by Rep) • Drag the field names to these data areas: • Sales Rep to Row Labels area • Total Sales to Values area

  15. Sales by Region by Rep • Drag the field names to these data areas: • Region & Sales Rep to Row Labels area • Total Sales to Values area

  16. Sales by Region by Rep

  17. Other value options • Change Total sales by region to Average Sales by Region • Highlight the drop down area • Select Value Field settings from pop-up menu

  18. Change Total sales by region to Average Sales by Region • Highlight the drop down area • Select Value Field settings from pop-up menu • Select summarize value field by that you wish to use and click on OK

  19. Pivot Table options • Highlight any cell in pivot table • Select options tab • Select options • To have the current Pivot table recalculate when opening the, select • Data tab • Check refresh data when opening file and select ok

  20. 1 2 3 4 Options tab Options Data tab Refresh data Select ok 5

  21. Refresh an open Pivot Table 2 3 1 • Assuming you converted the source data to a table before creating the pivot table • Select any cell in pivot table • Select options tab • Select refresh

  22. Pivot Table Styles 2 3 1 • To apply a pivot table style • Select cell in the pivot table • Select the design tab in pivot table tools • Apply a design

More Related