Spreadsheets can be vast, often containing thousands of rows of repetitive data that makes them impossible to parse at a glance. Fortunately, Excel offers two powerful features — PivotTables and PivotCharts — for summarizing data sets and presenting them visually.
What is a PivotTable?
A PivotTable allows you to take an extensive data set with multiple columns and rows and summarize that data in a compact, easy-to-read table. You can create multiple PivotTables from the same data set, each highlighting different aspects of the data. And PivotTables are interactive — you can easily manipulate them to filter or rearrange the data shown in one.
What is a PivotChart?
A PivotChart is a chart visualization based on the summarized information in a PivotTable. You can choose from a wide variety of chart types to best display a PivotTable’s data. The combinations you can create using these tools are countless.
In this tutorial, we will give you step-by-step instructions on how to get started with PivotTables and PivotCharts, and you can apply these steps to any data set you work with in Excel. We’ll demonstrate in Excel for Windows under a Microsoft 365 subscription; if you’re using a different version of Excel, your interface might look a little different and the steps might vary slightly, but things work more or less the same way.
How to create a PivotTable in Excel
We will use the data set shown below as our starting point:
The starting data set for our PivotTable examples.
Shimon Brathwaite / IDG
To get started, select any cell in the data set, then go to the Ribbon toolbar at the top of the spreadsheet and select Insert. At the far left of the toolbar, select the PivotTable button.
A pop-up appears that lets you select the range of data you want to analyze and where to place the PivotTable. Make sure the whole data set is selected and that the PivotTable will be placed in a new worksheet, then click OK.
Starting a PivotTable in Excel.
Shimon Brathwaite / IDG
Now we are brought to the starting page for creating a PivotTable. From here, we can begin constructing our first data summary.
Your blank canvas for PivotTable creation.
Shimon Brathwaite / IDG
First, we will look at the total quantity of each ordered product. To do this, let’s check the checkbox next to Quantity in the PivotTable Fields sidebar on the right. This will move Quantity into the Values area at the bottom right of the sidebar. Next, drag Product_# into the Rows area to sort by Product_#. The screenshot below shows the result.
This PivotTable shows the quantity of each product type sold.
Shimon Brathwaite / IDG
Here we see a summary of the quantity of products sold by product number and the total quantity of all products sold. You can do this sort of simple analysis with any two variables, but you can also do more fine-grained summaries.
Next, we will add another layer to our analysis by displaying quantity of products by product number and categorizing them by order category. To do this, drag Order_Category into the Rows section of the sidebar and make sure that Order_Category is on top. (You can reorder the items in any area of the sidebar by dragging and dropping them.)
In this version of the PivotTable, another element is shown: Order_Category.
Shimon Brathwaite / IDG
It’s important to understand that you can manipulate how information is shown in the table by the order in which you place the items in any section of the PivotTable. Since we put Order_Category on top of the Rows area, the PivotTable is summarized by that first and then by Product_# inside. To show the opposite sorting, move Product_# to the top in the Rows section and see the result.
Reversing how Product_# and Order_Category are displayed in the PivotTable.
Shimon Brathwaite / IDG
So far, we have only used the Rows section of the PivotTable builder, but we can show even more information using the Rows and Columns sections together. To demonstrate, we will display the total quantity of products sold at different unit prices. To do this, uncheck the Order_Category checkbox at the top of the sidebar, keep Product_# in the Rows section, and then drag Unit_Price into the Columns section.
The PivotTable now has columns for different unit prices.
Shimon Brathwaite / IDG
We have created a summary showing the amount of each product sold at a particular unit price. Now, let’s say we don’t want to view all of the products at the same time. We can limit the products shown using the filtering tools built into PivotTables.
First, let’s filter our results by Products 1, 2, and 3. Click the downward triangle icon next to Row Labels. In the filtering pop-up that appears, select Products 1, 2, and 3. The PivotTable will change to show only those three products.
Filtering the PivotTable to show only Products 1, 2, and 3.
Shimon Brathwaite / IDG
Once you are done, select the Clear Filter button in the pop-up, and the full PivotTable reappears.
Next, let’s filter by unit price using the Column Labels filter option. Select that filter and select the $4.00, $5.00, & $7.00 options to change your PivotTable.
Filtering the PivotTable to show only items that cost $4.00, $5.00, and $7.00.
Shimon Brathwaite / IDG
You can also use the pop-up to sort the items in the PivotTable by various fields, and to filter using conditions such as “Greater Than” or “Contains.” It’s worth spending a little time playing with the options to see what happens; just remember to click Clear Filter when you’re done.
Before we move on to PivotCharts, let’s discuss the Filters area of the sidebar. This can be used to filter out specific items from the PivotTable, but you may find it simpler to remove the field altogether or use the filtering and sorting options that we discussed earlier for more granular control. However, you can see how this box functions by moving the “Product_#” field to the Filters area.
Another way to filter PivotTable data is by using the Filters area in the PivotTable Fields sidebar.
Shimon Brathwaite / IDG
How to create a PivotChart in Excel
Now, let’s move on to how to create data visualizations using PivotCharts. To add a PivotChart to the main data set, go back to the worksheet that contains the main data set, place your cursor in a cell that contains data, and select Insert> PivotChart in the Ribbon.
Starting a PivotChart in Excel.
Shimon Brathwaite / IDG
Hit OK on the dialog box that pops up, and the familiar PivotTable builder interface appears, with an additional placeholder for a PivotChart.
Your blank canvas for PivotChart creation.
Shimon Brathwaite / IDG
We will summarize the quantity of items sold by order category and unit price. In the sidebar, check Quantity to add it to the Values area, then drag Order_Category and Unit_Price to the Axis (Categories) area, with Order_Category on top. This will create a PivotTable and a column chart displaying the information we have selected.
The PivotChart graphically displays the information from the PivotTable at left.
Shimon Brathwaite / IDG
But you’re not limited to column charts; there are multiple types of charts to choose from. Right-click the column chart, select Change Chart Type, and select Pie > 3-D Pie to see a different chart example.
Choosing a different chart type for the PivotChart.
Shimon Brathwaite / IDG
The result will look like the screenshot below.
The PivotChart in 3-D pie chart form.
Shimon Brathwaite / IDG
You can filter or sort the data in the PivotTable that a PivotChart is based on, and those changes will be reflected in the PivotChart. To see what this looks like, click the minus sign to the left of Large Order in the PivotTable to the left of the chart. The Large Order section of the PivotTable collapses and shows only the large order total, without breaking it down by unit price. The same thing happens in the PivotChart to the right.
The PivotChart with Large Orders collapsed into a single slice of pie.
Shimon Brathwaite / IDG
Now you see how using PivotTables and PivotCharts lets you create data summaries and visualizations to display specific data quickly and easily. These options can be used on data sets of almost any size and easily customized to show only very specific information. The combinations that you can create using PivotTables and PivotCharts are almost endless, and we encourage you to test them out on any data sets that you work with in Excel.
Microsoft 365, Microsoft Excel, Microsoft Office, Office Suites, Productivity Software
Spreadsheets can be vast, often containing thousands of rows of repetitive data that makes them impossible to parse at a glance. Fortunately, Excel offers two powerful features — PivotTables and PivotCharts — for summarizing data sets and presenting them visually.
What is a PivotTable?
A PivotTable allows you to take an extensive data set with multiple columns and rows and summarize that data in a compact, easy-to-read table. You can create multiple PivotTables from the same data set, each highlighting different aspects of the data. And PivotTables are interactive — you can easily manipulate them to filter or rearrange the data shown in one.
What is a PivotChart?
A PivotChart is a chart visualization based on the summarized information in a PivotTable. You can choose from a wide variety of chart types to best display a PivotTable’s data. The combinations you can create using these tools are countless.
In this tutorial, we will give you step-by-step instructions on how to get started with PivotTables and PivotCharts, and you can apply these steps to any data set you work with in Excel. We’ll demonstrate in Excel for Windows under a Microsoft 365 subscription; if you’re using a different version of Excel, your interface might look a little different and the steps might vary slightly, but things work more or less the same way.
How to create a PivotTable in Excel
We will use the data set shown below as our starting point:
The starting data set for our PivotTable examples.
Shimon Brathwaite / IDG
To get started, select any cell in the data set, then go to the Ribbon toolbar at the top of the spreadsheet and select Insert. At the far left of the toolbar, select the PivotTable button.
A pop-up appears that lets you select the range of data you want to analyze and where to place the PivotTable. Make sure the whole data set is selected and that the PivotTable will be placed in a new worksheet, then click OK.
Starting a PivotTable in Excel.
Shimon Brathwaite / IDG
Now we are brought to the starting page for creating a PivotTable. From here, we can begin constructing our first data summary.
Your blank canvas for PivotTable creation.
Shimon Brathwaite / IDG
First, we will look at the total quantity of each ordered product. To do this, let’s check the checkbox next to Quantity in the PivotTable Fields sidebar on the right. This will move Quantity into the Values area at the bottom right of the sidebar. Next, drag Product_# into the Rows area to sort by Product_#. The screenshot below shows the result.
This PivotTable shows the quantity of each product type sold.
Shimon Brathwaite / IDG
Here we see a summary of the quantity of products sold by product number and the total quantity of all products sold. You can do this sort of simple analysis with any two variables, but you can also do more fine-grained summaries.
Next, we will add another layer to our analysis by displaying quantity of products by product number and categorizing them by order category. To do this, drag Order_Category into the Rows section of the sidebar and make sure that Order_Category is on top. (You can reorder the items in any area of the sidebar by dragging and dropping them.)
In this version of the PivotTable, another element is shown: Order_Category.
Shimon Brathwaite / IDG
It’s important to understand that you can manipulate how information is shown in the table by the order in which you place the items in any section of the PivotTable. Since we put Order_Category on top of the Rows area, the PivotTable is summarized by that first and then by Product_# inside. To show the opposite sorting, move Product_# to the top in the Rows section and see the result.
Reversing how Product_# and Order_Category are displayed in the PivotTable.
Shimon Brathwaite / IDG
So far, we have only used the Rows section of the PivotTable builder, but we can show even more information using the Rows and Columns sections together. To demonstrate, we will display the total quantity of products sold at different unit prices. To do this, uncheck the Order_Category checkbox at the top of the sidebar, keep Product_# in the Rows section, and then drag Unit_Price into the Columns section.
The PivotTable now has columns for different unit prices.
Shimon Brathwaite / IDG
We have created a summary showing the amount of each product sold at a particular unit price. Now, let’s say we don’t want to view all of the products at the same time. We can limit the products shown using the filtering tools built into PivotTables.
First, let’s filter our results by Products 1, 2, and 3. Click the downward triangle icon next to Row Labels. In the filtering pop-up that appears, select Products 1, 2, and 3. The PivotTable will change to show only those three products.
Filtering the PivotTable to show only Products 1, 2, and 3.
Shimon Brathwaite / IDG
Once you are done, select the Clear Filter button in the pop-up, and the full PivotTable reappears.
Next, let’s filter by unit price using the Column Labels filter option. Select that filter and select the $4.00, $5.00, & $7.00 options to change your PivotTable.
Filtering the PivotTable to show only items that cost $4.00, $5.00, and $7.00.
Shimon Brathwaite / IDG
You can also use the pop-up to sort the items in the PivotTable by various fields, and to filter using conditions such as “Greater Than” or “Contains.” It’s worth spending a little time playing with the options to see what happens; just remember to click Clear Filter when you’re done.
Before we move on to PivotCharts, let’s discuss the Filters area of the sidebar. This can be used to filter out specific items from the PivotTable, but you may find it simpler to remove the field altogether or use the filtering and sorting options that we discussed earlier for more granular control. However, you can see how this box functions by moving the “Product_#” field to the Filters area.
Another way to filter PivotTable data is by using the Filters area in the PivotTable Fields sidebar.
Shimon Brathwaite / IDG
How to create a PivotChart in Excel
Now, let’s move on to how to create data visualizations using PivotCharts. To add a PivotChart to the main data set, go back to the worksheet that contains the main data set, place your cursor in a cell that contains data, and select Insert> PivotChart in the Ribbon.
Starting a PivotChart in Excel.
Shimon Brathwaite / IDG
Hit OK on the dialog box that pops up, and the familiar PivotTable builder interface appears, with an additional placeholder for a PivotChart.
Your blank canvas for PivotChart creation.
Shimon Brathwaite / IDG
We will summarize the quantity of items sold by order category and unit price. In the sidebar, check Quantity to add it to the Values area, then drag Order_Category and Unit_Price to the Axis (Categories) area, with Order_Category on top. This will create a PivotTable and a column chart displaying the information we have selected.
The PivotChart graphically displays the information from the PivotTable at left.Shimon Brathwaite / IDG
But you’re not limited to column charts; there are multiple types of charts to choose from. Right-click the column chart, select Change Chart Type, and select Pie > 3-D Pie to see a different chart example.
Choosing a different chart type for the PivotChart.
Shimon Brathwaite / IDG
The result will look like the screenshot below.
The PivotChart in 3-D pie chart form.
Shimon Brathwaite / IDG
You can filter or sort the data in the PivotTable that a PivotChart is based on, and those changes will be reflected in the PivotChart. To see what this looks like, click the minus sign to the left of Large Order in the PivotTable to the left of the chart. The Large Order section of the PivotTable collapses and shows only the large order total, without breaking it down by unit price. The same thing happens in the PivotChart to the right.
The PivotChart with Large Orders collapsed into a single slice of pie.
Shimon Brathwaite / IDG
Now you see how using PivotTables and PivotCharts lets you create data summaries and visualizations to display specific data quickly and easily. These options can be used on data sets of almost any size and easily customized to show only very specific information. The combinations that you can create using PivotTables and PivotCharts are almost endless, and we encourage you to test them out on any data sets that you work with in Excel.
Microsoft 365, Microsoft Excel, Microsoft Office, Office Suites, Productivity Software Read More Computerworld
+ There are no comments
Add yours