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

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

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:

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:

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:

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:

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:

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: