What is a Pivot table in Excel and how do you make one?

Summarize your data using Pivot tables in Excel!

Logo of pivot table excel with a green "x" on a stack of shaded squares, set against a gradient aqua background.

You can trust PC GuideOur team of experts use a combination of independent consumer research, in-depth testing where appropriate – which will be flagged as such, and market analysis when recommending products, software and services. Find out how we test here.

Last Updated on

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:

  1. Creating a Pivot table
  2. Creating the fields
  3. Sorting the table
  4. Filtering the table
  5. Changing the summary calculations
  6. Creating a 2D Pivot table
  7. Refreshing a Pivot table
  8. Creating a Pivot chart

Step

1

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:

Screenshot of an Excel spreadsheet with a table listing store names and values, instructing on creating a pivot table, highlighting cell C4.
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.’

Screenshot of a Microsoft Excel window with an open "pivot table field name" dialog box prompting the user to enter a field name, with a sample dataset visible in the background.
Selecting data range

Step

2

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.

Screenshot of a Microsoft Excel pivot table displaying a column of supermarket names and corresponding sales figures, with a total sum calculated at the bottom.
Field settings

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:

Create pivot table in Excel
Remove field

Step

3

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:

An open Microsoft Excel window displaying a pivot table with several field names listed, including 'qtysold', 'freightcost', and 'discounts'. A cursor highlights 'qtysold'.
More sort options

To sort from smallest to largest, we select ‘Ascending,’ and under it, we choose ‘Sum of Sales’:

Screenshot of an excel spreadsheet showcasing the usage of the "sum" function in a pivot table, with a dialogue box indicating to sum corresponding data in cell b2 to b10.
Sum of sales

Here’s how the table looks after sorting:

Excel spreadsheet featuring a pivot table of sales data by supermarket branches with columns for branch names and sum of sales, highlighted in red.
Table sorted

Step

4

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:

Create Pivot Table in Excel
Top 10 option

This opens up a dialog box where we confirm the filter settings:

Screenshot of a pivot table Excel spreadsheet with a "top 10" filter dialog box open, showing a selection for the "top 5" items.
Filter settings

We select the top 5 supermarkets to be displayed on the Pivot table. Here’s the resulting table:

Screenshot of an Excel pivot table displaying a list of store names in column A and their corresponding sales figures in column B, with a sum total at the bottom.
Table result

Step

5

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.’

Screenshot of an excel spreadsheet with data on supermarket sales, displaying a pivot table fields pane adjusting data filters for a sales report.
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:

Screenshot of a pivot table in Excel with a "show values as" dropdown menu open, highlighting the option "% of grand total.
Percentage of grand total

Here’s what the table looks like after applying the calculation:

A screenshot of an Excel spreadsheet displaying sales data with columns labeled 'id', 'name', 'type of sales', and 'amount'. Cells from A1 to D6 are filled, including a pivot
Calculation applied

Step

6

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:

Screenshot of an excel spreadsheet featuring a pivot table with data on monthly food sales by product, including totals and various toolbar options.
Table with data sorted

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:

Screenshot of an Excel workbook with a pivot table, displaying active fields and a filter menu for "month" with options for January, February, and March.
Adding months

If you move both Months and Supermarkets to Rows, here’s the kind of table you will get:

Screenshot of an open Excel spreadsheet displaying a pivot table with monthly sales data and corresponding sums in the left column, and a pivot table field list on the right.
Months and supermarkets added to rows

Step

7

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:

Screenshot of an Excel program showing a pivot table summarizing sales data by month, highlighting the "PivotTable Analyze" tab in the toolbar.
Refresh Pivot Table

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:

Kevin is the Editor of PC Guide. He has a broad interest and enthusiasm for consumer electronics, PCs and all things consumer tech - and more than 15 years experience in tech journalism.