As data becomes more sophisticated, PivotTables can be used to analyze data and to help you make more wise decisions. A PivotTable report is a flexible report generated from a data source that quickly summarizes, organizes, and compares large amounts of data in a worksheet. The source data can reside in an Excel worksheet or in an external data file. Rows and columns may be rotated in a PivotTable to allow different views of the same data. The ability to "pivot" the dimensions of the table—for example, to transpose column headings to row positions, gives the PivotTable its name.
PivotTables help to answer questions you may have about your data. If your question sounds like "What is the [summary of variable] for each [category]?", then a PivotTable may be the correct tool to help you answer that rapidly.
Here are some other example questions a PivotTable might answer:
- How many employees does each department have?
- How much total salary is paid by each department?
- What's the median salary for each department?
The data on which the PivotTable is based is called the source data. Fields of information can be placed in Row or Column areas to organize the data. The Values area generally contains summarized numeric values. If there are no numeric data fields, a PivotTable can still provide a frequency count of data, for example student ID numbers. The Filter area fields are displayed as top-level report filters at the top of the PivotTable.
Here is an example of a PivotTable:
Tables can be imported from same or different data sources or imported simultaneously from a relational database. In an Excel PivotTable, we can take thousands of rows of data and quickly organize and rearrange the data to see different perspectives. Field drop-down arrows allow us to view all or portions of the data.
Creating a PivotTable
Before creating a PivotTable, the data must be organized into columns and rows, have column headings, and include no blank rows in the data. Since our data is organized in columns and rows with column headings, we can easily turn this information into a PivotTable report that will be useful at a glance. We want to be able to view total expenses and to select any quarter to see expenses for any particular category.
The PivotTable will appear in this final format:
It is easier to maintain source data by first using Excel's Table feature. By converting the data to a table first, data that may be added in new rows or columns later will be automatically included in the PivotTable when it is refreshed or updated. Therefore, this is a real advantage since you won't have to remember to manually adjust the data range every time the data source changes.
To begin, we must first select a cell within the Transactions data.
Step1. Verify that a cell within the data is selected.
Step2. Switch to the Insert tab.
Step3. To open the Create PivotTable dialog box,
Step4. Verify that the New Worksheet radio button is selected.
Step5. To continue,
NOTE: The PivotTable Field List is normally docked on the right side of the window. However, you can drag the title bar to move it anywhere on the worksheet. If you Click a cell outside the PivotTable placeholder area, the PivotTable Field List will be hidden.
Step6. To name the worksheet tab,
Double-Click the Sheet# worksheet tab
Step7. To rename the tab, type:
Banking PivotTable Enter
- Press & Drag the fields from the field list to one of the four boxes in the Areas Section below the field list.
- Right-click a field name and choose its position in the PivotTable from the drop-down list.
- Click the checkbox of each field to be included and allow Excel to give it a default location in the PivotTable. This may require modification later.
Step8. To select Category as the source for Row Labels, from the Field List,
Press & Drag Category to the Rows area
Step9. To make Qtr the source for Column Labels, from the Field List,
Press & Drag Qtr to the Columns area
Step10. To make Date the Report Filter field, from the Field List,
Press & Drag Date to the Filters area
Step11. To provide data about withdrawals as the summarized information in this table, from the Field List,
Press & Drag Withdrawal to the Values area
Step12. To open the Value Field settings window, in the Values box,
Click,Click Value Field Settings...
13. To change the function, in the "Summarize value field by" section,
Step14. To change the name of the field, in the Custom Name field,
Press & Drag the text, type: Expenses
Step15. To display the expense amounts as currency,
Click, Click Currency
NOTE for MacOS Users: The correct button is labeled "Number...".
Step16. To finish modifying the field settings,
Step17. Save the workbook.
Pivoting the PivotTable
Currently, this PivotTable has Category in the Rows area, and Qtr in the Columns area. We can change how this data is = displayed in the PivotTable by "pivoting" or swapping the data in Rows with the data in Columns. This will still use the same data, but present it differently.
Let's see how to do this now.
Step1. To move the location of the Category data, in the Areas section,
Press & Drag Category from the Rows area to the Columns area
Step2. To move the location of the Qtr data, in the Areas section,
Press & Drag Qtr from the Columns area to the Rows area
Step3. To move the location of the Category data, in the Areas section,
Press & Drag Category from the Columns area to the Rows area
Step4. To move the location of the Qtr data, in the Areas section,
Press & Drag Qtr from the Rows area to the Columns area
PivotTables offer much flexibility in viewing data. We can hide data by using the column or row fields to display specific quarters or categories. By using the Report Filter drop-down list, we can further filter the information.
The "A Beginning Balance" and "Paycheck" categories are not withdrawals so we will hide those categories.
Step1. To deselect these categories, in the Row Labels drop-down box, in the PivotTable,
Click, Click the "A Beginning Balance" checkbox,
Click the Paycheck checkbox
Step2. To close the dialog box,
Drilling Into the PivotTable Data
By double-clicking a value, we can see the hidden details behind any one particular value in the PivotTable.
Let's look at the Cable expenses from the second quarter.
Step1. To view the details underlying a value, in the PivotTable,
Double-Click cell C6
NOTE: To learn more on how to create and manipulate PivotTables, consider attending the workshop Excel 2016: Analyzing Data with Pivot Tables.
Linking Data to Another Worksheet
Earlier, we copied and pasted data from one worksheet to a new worksheet, to create a single list of the Titanic passengers. What we pasted was static data; it was a picture of the information at the time we copied it. Excel also allows us to create links between worksheets so that as data in the source worksheet changes, the linked data updates to reflect the changes, as well.
Let's see how to link the expenditures for third and fourth quarters from this PivotTable to cells on the AutoFill worksheet.
Step1. Switch to the Banking PivotTable worksheet.
Step2. To select the cells to copy, in the PivotTable worksheet,
Press & Drag cells D5 through E14
Step3. To copy the displayed cells to the clipboard, on the keyboard, press:
Step4. To activate the AutoFill worksheet tab,
Click the AutoFill worksheet tab
Step5. To indicate where to place the data,
Click cell B19
Step6. To link the data, on the Home tab of the Ribbon,
NOTE for MacOS Users: The options have slightly different icons, and visible labels.
Step7. To view the linked reference in cell B19,
Click cell B19
NOTE: If the worksheet name contains spaces, you will see single quotes around the sheet name. If neither the workbook name nor the worksheet name contains a space, the single quotes are not necessary.
Refreshing PivotTable Data
Outside of this workshop, data often changes, sometimes as we are working to analyze it. Pasting a link from one chunk of data to another will cause that linked data to update and reflect any changes. Because PivotTables are created using a snapshot of the data source, any changes in data values the PivotTable will need to be updated to reflect the new values. This process of updating data is called refreshing.
Let's change some data in the Transactions worksheet, and see how that change may ripple to other worksheets in this workbook.
Step1. Switch to the Transactions worksheet.
Step2. Change the value for the last ATM Withdrawal on 25-Dec to be $600.00.
Step3. Switch to the Autofill worksheet.
Step4. Switch to the Banking PivotTable worksheet.
Step5. To update the data used in the PivotTable,
Right-Click the PivotTable, Click Refresh
Step6. Switch to the Autofill worksheet.
NOTE: When linking data between workbooks, be careful when changing names or locations of related linked files. Also, links are normally updated automatically when the source and destination workbooks are open on the same computer. If they are not both open, you may be alerted by the Trust Bar to update the links.
Step7. Save the workbook.
Step8. If you do not wish to take advantage of this opportunity, exit Excel.