How to Create Pivot Table in Excel

A pivot table is one of the most powerful and useful data summarization tools in Excel that allows you to quickly summarize, sort, reorganize, analyze, group, count large datasets.

The pivot table allows you to rotate (or pivot) the data stored in the table so as to view it from different perspectives and to have a clear understanding of large data sets.

This tutorial will give you step-by-step instructions on how to create and use pivot tables in Excel.

Organize your Data

To create a pivot table, your data should have a table or database structure. So you need to organize your data into rows and columns. To convert your data range into a table, select all of the data, go to the ‘Insert’ tab and click ‘Table’. In the Create Table dialog box, click ‘OK’ to convert the data set into a table.

Using an Excel Table as the source dataset for creating a pivot table, makes your pivot table dynamic. When you add or remove entries in the Excel table, the data in pivot will update with it.

Let’s assume you have a large data set as shown below, it consists of over 500 records and 7 fields. Date, Region, Retailer Type, Company, Quantity, Revenue, and Profit.

Insert Pivot Table

First, select all cells that contain data, and go to the ‘Insert’ tab and click ‘PivotChart’. Then, select ‘PivotChart & PivotTable’ option from drop-down.

A Create PivotTable dialog box will open up. Excel would automatically identify and fill the correct range in the ‘Table/Range field’, otherwise select the correct table or range of cells. Then specify the target location for your Excel pivot table, it could be ‘New Worksheet’ or ‘Existing Worksheet’ and click ‘OK’.

If you choose ‘New Worksheet’, a new sheet with a blank pivot table and a pivot chart will be created in a separate worksheet.

Build Your Pivot Table

In the new sheet, you’ll see an empty pivot table on the left-hand side of the Excel window and a ‘Pivot Table Fields’ pane on the right-hand edge of the Excel window, where, you’ll find all the options for configuring your pivot table.

The PivotTable Fields pane is divided into two horizontal sections: Fields section (top of the pane) and Layout section (bottom of the pane)

The Field Section lists all the fields (columns) that you added to your table. These field names are all the column names from your source table.
The Layout Section has 4 areas i.e Filters, Columns, Rows, and Values, with which you can arrange and re-arrange the fields.

Add Fields to Pivot Table

To build a pivot table, drag and drop fields from the Field section into areas of the Layout section. You can drag the fields between the areas too.

Add Rows

We’ll start by adding the ‘Company’ field to the Rows section. Usually, non-numeric fields are added to the Row area of the Layout. Just Drag and drop the ‘Comapny’ field into the ‘Row’ area.

All the company names from the column ‘Company’ in the source table will be added as rows in the pivot table and they will be sorted in ascending order, but you can click on the dropdown button within the Row Labels cell to change the order.

Add Values

You added a row, now let’s add a value to that table to make it a one-dimensional table. You can make a one-dimensional pivot table by adding just the row or the column labels and their respective values into areas. The value area is where calculations/values are stored.

In the above example screenshot, we have a row of companies, but we want to find out the total revenue of each company. To get that, just drag and drop the ‘Revenue’ field to the ‘Value’ box.

If you wish to remove any fields from the Areas section, simply uncheck the box next to the field in the Fields section.

Now, we have a one-dimensional table of companies (row labels) along with the sum of revenues.

Add Column

Two-Dimensional Table

The Rows and Columns together will create a two-dimensional table and fill the cells with the third dimension of values. Suppose you want to create a pivot table by listing company names as rows and using columns to show Dates and filling in the cells with the total revenue.

When you add the ‘Date’ field to the ‘Column’ Area, Excel automatically adds ‘Quarterly’ and ‘Years’ to the column fields, to calculate and better summarize the data.

Now, we have two-dimensional table with three dimension of values.

Add Filters

In case you want to filter out the data by ‘Region’, you can drag and drop the ‘Region’ field to the Filter area.

This adds a drop-down menu above your Pivot Table with the selected ‘Filter Field’. With that, you can filter out companies’ revenue for every year by region.

By default, all regions are selected, uncheck them and select only the region you want to filter the data by. If you want to filter the table by multiple entries, check the checkbox next to ‘Select Multiple Items’ at the bottom of the drop-down. And select the multiple regions.

The result:

Sorting

If you want to sort out the table value in ascending or descending order, right-click on any cell inside the Sum of Revenue column, then expand ‘Sort’, and choose the order.

The result:

Grouping

Let’s say you have data listed by months in your pivot table but you don’t want to see it monthly, instead, you want to rearrange the data into financial quarters. You can do that in your pivot table.

First select the columns and right-click on it. Then, select ‘Group’ option from drop-down.

In the Grouping window, select ‘Quarters’ and ‘Years’, because we want them organized into financial quarters of every year. Then, click ‘OK’.

Now, your data organized into financial quarters of every year.

Value Field Settings

By default, the pivot table summarizes the numeric values by the Sum function. But you can change the type of calculation that is used in the Values area.

To change the summary function, right-click on any data in the table, click ‘Summarize Values By’, and choose your option.

Alternatively, you can click on the downward arrow next to ‘Sum of ..’ in the value area of the field section and select ‘Value Field Settings’.

In the ‘Value Field Settings’, choose your function to summarize data. Then, click ‘OK’. For our example, we are choosing ‘Count’ to count the number of profits.

The result:

Excel’s pivot tables also allow you to display values in different ways, for instance, show Grand Totals as percentages or Columns Total as percentages or Row Total as percentages or order values from smallest to largest and vice versa, many more.

To show values as percentages, right-click anywhere on the table, then click ‘Show Values As’ and choose your option.

When we choose ‘% of Column Total’, the result will be like this,

Refresh the Pivot table

Although a pivot table report is dynamic, when making changes in the source table, Excel does not refresh the data in the pivot table automatically. It needs to be manually ‘refreshed’ in order to update the data.

Click anywhere in the pivot table and go to the ‘Analyze’ tab, click the ‘Refresh’ button in the Data group. To refresh the current pivot table in the worksheet, click the ‘Refresh’ option. If you want to refresh all the pivot tables in the workbook, click ‘Refresh All’.

Alternatively, you can right-click the table, and choose ‘Refresh’ option.