3.4 Introduction to PivotTables
PivotTables
While conditional formatting, which you learned about in the previous section, provides one way to emphasize key points in your data, another way to analyze data is by using PivotTables. PivotTables are a powerful tool that can help make your worksheets more manageable by summarizing your data and allowing you to manipulate it in different ways. PivotTables are inserted directly from the contents of a dataset, linking to the data but using a summary table format while the original dataset remains unchanged. We will do a brief introduction to PivotTables here so you become familiar with this widely used Excel tool.
First, click the link below to view a video on PivotTables:
Introduction to PivotTables Video
Using PivotTables to Summarize Data and Answer Questions
Figure 3.28 below shows a data set of some of the national parks of the Western States, including the park sizes, visitors in the year 2023, and main attractions. One question we might ask about this data set would be, What was the total number of visitors to these parks by state in the year 2023? To answer this question, we would have to sum up the number of visitors for the parks in each state, which could take a bit of time.
However, if we create a PivotTable from this data set, the answer to our question is immediately calculated and displayed in an easy-to-read table. It might look like the PivotTable in the image below. We say “might” because there are many ways you can create a PivotTable.
Not only are there many ways to create a PivotTable, you can also rearrange–or pivot–the information in a PivotTable to answer other questions or look at the data from a different perspective. Another question we could ask about the parks data would be, What were the total visitors by main attraction? To answer this question, we just need to change out the State data from the previous PivotTable to the Attraction data. This simply involves dropping and dragging or selecting and deselecting fields or data. Our adjusted PivotTable would look like this:
Let’s try it:
Open the “CH3-Gradebook and Parks” workbook if it isn’t already open.
Click on the “PivotTable” sheet tab within your “CH3-Gradebook and Parks” workbook. .
On this spreadsheet is data about the national parks in the Western United States as shown in Figure 3.28 above. First, we’ll create the PivotTable in Figure 3.29 above, showing the total number of visitors to the parks by state for the year 2023.
- Select the cells (including column headers) you want to include in your PivotTable, we will select cells A3:F43.
- On the Insert tab click the PivotTable command. See Figure 3.31.
- The PivotTable from table or range dialog box opens, Figure 3.32. You will choose your settings, then click OK. Since we already selected the range A3:F43, it populates into the Table/Range box. For this practice, we will place the PivotTable on our PivotTable sheet beginning in cell H3. Click on the radio button for