If you want to learn what a Pivot table is in Excel and how you can make one, then we’ve got you covered.
Pivot tables in Excel are incredibly useful for data analysis and summarization. They can help you summarize large data sets into more manageable summaries. You can quickly aggregate, count, average, or perform other calculations on your data. On top of that, you can filter and sort your data within a pivot table without altering the original dataset.
Creating a Pivot table in Excel might seem tricky, especially if you’re new to the software. But don’t worry, we’ll show you how to do it in this guide.
What is a Pivot table?
A Pivot table is a data analysis tool that people can create in Excel. It is typically used to share information as it can sort, group, reorganize, count, summarize, average, or total data stored within a table. On top of that, advanced calculations can be used, and columns and rows can be flipped into different formats.
They are also a quick way of seeing any inconsistencies in your data or typos, helping your work to be more exact. Mainly, Pivot tables are used to find significance in big, detailed sets of data.
How to create a Pivot table in Excel
Scenario on hand: We have a supermarket dataset in Excel.
What we want to accomplish: Explore how to create a Pivot table in Excel following the steps:
- Creating a Pivot table
- Creating the fields
- Sorting the table
- Filtering the table
- Changing the summary calculations
- Creating a 2D Pivot table
- Refreshing a Pivot table
- Creating a Pivot chart
Step
Creating a Pivot table
The first step in creating a Pivot table is to open the Excel file with the data. Click on any blank cell in your worksheet and go to Insert > Pivot Table:
A ‘Create PivotTable’ box will appear.
Select the data range, and then select ‘New Worksheet’ at the end of the box before clicking ‘OK.’
Step
Creating the fields
After inserting the Pivot table, you will be redirected to a new worksheet. Here, you will see a box on the left named ‘PivotTable Fields.’
Here, you will see the names of your data labels. You can drag the fields you wish to have in your pivot table from the menu to the areas at the bottom of the menu, indicating where you would like each title to appear on the worksheet.
For our demonstration, we added the data field named “Supermarkets” to rows and “Sales” to values.
You can choose these field settings according to the type of analysis you need to perform.
As you drag the fields to the particular areas, you will see a pivot table forming on the sheet on the left.
You can drag and remove fields by left-clicking on them:
Step
Sorting the table
Now that all of the information you wanted has been transferred onto your pivot table in your chosen format, you will sort the table. Tables will often be sorted by values, such as smallest to largest or vice versa.
We want to sort the table based on the sales. So, we right-click the small arrow on the pivot table and select More Sort Options.
From this option, you can select whether you want the data sorted from smallest to largest or largest to smallest or arranged in ascending (A-Z) or descending order (Z-A).
To sort the table according to the sum of sales, we click More Sort Options first:
To sort from smallest to largest, we select ‘Ascending,’ and under it, we choose ‘Sum of Sales’:
Here’s how the table looks after sorting:
Step
Filtering the table
Now that you have the table ready, you can even filter out some of the values.
The filter button is the same small arrow on the Pivot table. Use it to look at the filter options.
For our demonstration, we wish to keep the top ten performing stores on the pivot table. Here’s the option we use to do that:
This opens up a dialog box where we confirm the filter settings:
We select the top 5 supermarkets to be displayed on the Pivot table. Here’s the resulting table:
Step
Changing the summary calculations
The final part of a pivot table you need to learn is how to change the summary calculation. Excel will add the items by default. To change this, click on the field in the Value table and click ‘Value Field Settings.’
We want to show values as the percentage of the total sales. In the dialog box, we click ‘Show Values As’ and select the right calculation type:
Here’s what the table looks like after applying the calculation:
Step
Creating a 2D Pivot Table
We have altered our dataset to include the sales that each supermarket did in three months. We then added Supermarkets to rows, Months to columns, and Sales to values. Here’s what the table looks like:
You can even add the months to the filters. This will create a filter at the top of the PIVOT table that you can then use to assess the performance of the supermarkets each month:
If you move both Months and Supermarkets to Rows, here’s the kind of table you will get:
Step
Refreshing a Pivot table
There might be many times when you have to change the source data in your Excel sheet. In this case, making the table repeatedly would be a hassle. A quick and easy way to refresh the Pivot table data is to click “Refresh” from the “PivotTable Analyze” tab on top:
Wrapping up
Pivot tables are great tools for analyzing data in Excel. They allow easy calculations, and changing the table settings, applying filters, and sorting the data is also quite easy with them. Pivot tables are also good for creating dashboards, which can make the data on your Excel sheets come alive.
Learn more about Excel through these helpful guides:
