Pivot tables are both incredibly simple and increasingly complex as you learn to master them. They’re great at sorting data and making it easier to understand, and even a complete beginner in Excel can find value using them.
We help you get started with pivot tables in a Microsoft Excel spreadsheet.
First, we label the top row so that we can organize our data better once we apply the pivot tables in a later step.
Before we proceed, this is a good opportunity to delete blank rows in your workbook. Pivot tables work with empty cells, but they can’t quite understand how to proceed with an empty row. To delete, highlight the row, right-click, choose “Delete” and then “Scroll Cells Up” to combine the two sections.
Click in a cell in the dataset. On the “Insert” tab, click the “Pivot Table” button.
Click “OK” when the dialog box appears. You can change the settings in the Create PivotTable dialog box, but this is usually not necessary.
We have many options here. The simplest of these is to group our products by category, with a total of all purchases at the bottom. To do this, we just click next to each box in the “Pivot Table Fields” section.
To make changes to the PivotTable, click a cell in the dataset to open the “PivotTable Fields” sidebar again.
Once opened, we’re going to clean up the data a bit. In our example, we don’t need our product ID to be a sum, so we’ll move it from the ‘Values’ field at the bottom to the ‘Filters’ section. Just click and drag it to a new field and feel free to experiment here to find the format that works best for you.
To view a specific product ID, click the arrow next to “All” in the header.
This drop-down list is a sortable menu that allows you to view each product ID individually or in combination with another product ID. To choose a product, click on it and then click “OK”, or check the “Select multiple items” option to choose more than one product ID.
This is better, but still not ideal. Let’s try dragging the product ID to the “Rows” field.
We’re getting closer. Now the product ID appears closer to the product making it a little easier to understand. But it still isn’t perfect. Instead of placing the product ID below the product, we drag the product ID above the item in the “Rows” field.
This seems much more useful, but we may want to take a different view of the data. For that, we are going to move Category from the “Rows” field to the “Columns” field for a different look.
We don’t sell many dinner rolls, so we decided to discontinue them and remove the product ID from our report. To do that, we’ll click on the arrow next to ‘Row Labels’ to open a drop-down menu.
Remove the check mark at “45” from the list of options. This is the product ID for dinner sandwiches. If you uncheck this box and click “OK”, the product will be removed from the report.
As you can see, there are a number of options to play with. How you display your data is completely up to you, but with pivot tables, there really is no shortage of options.
