Pivot Tables

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

20. 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

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

22. 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

23. 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