Pivot Table in Excel

Pivot Table

Pivot table is a business intelligence tool used for data summarization in spreadsheets or other BI software. Pivot table can automatically sort, sum and count or give the average of the data from one table or spreadsheet and displaying its results in a second table in form of summarized data.

MS Excel supports Pivot tables and is one of its most powerful features.

Our data set consists of 200 rows and 6 fields. Order_ID, Product_Name, Category, Amount, Ord_Date and Origin.

Order ID

Insert a Pivot Table

To insert a pivot table, follow the steps below.

1. Click any single cell inside the data set.

2. On the Insert tab, click PivotTable.


Create PivotChart dialog box will appear having an automatically selected range of data. By default, Pivot Table will produce results in a new Worksheet. In this tutorial, you will find that the PivotChart shows a selected range from A1 to F200.

3. Click OK.

Click OK

Drag fields

On the new worksheet, the PivotTable field list will appear. To calculate the total cost of imports against each product, drag the following fields in the respective PivotTable areas.

1. Product Field to the Legend Area.

2. Amount Field to the Values area.

3. Country Field to the Filters area.

Country Field to the Filters area

In the figure below, you can observe that the Lady Finger is the highly imported product.

Lady Finger is the highly imported product


To arrange the PivotTable data in a top-down order, let’s sort the pivot table. Unlike previous versions of MS Excel, where Sort section was made a part of PivotTable, In MS Excel 2013, you can use the standard Sort method to arrange data either in ascending or descending order.

To sort the PivotTable, follow the steps below.

1. Click any cell inside the Sum of Amount column.

2. On Data tab, under the Sort & Filter section, click on Sort in descending order (ZA)

descending order

The output of the function is shown in the figure below

output of the function


Filtering data is another advantage of using PivotTable. In this tutorial, you will observe that Origin field is added to the Report Filter area, which can be used to filter this pivot table by Origin wise. For example, which products are mostly imported from Pakistan?

1. Click the filter drop-down and select Pakistan.

The output reflected that Lady Finger are the mostly imported product from Pakistan.

output reflected

Change Summary Calculation

By default, MS Excel summarizes the data by either summing up or counting the number of items. To change the type of calculation that you want to use, follow the steps below.

1. Click any cell inside the Sum of Amount column.

2. Right click and click on Value Field Settings…

Value Field Settings

3. Choose the type of calculation of your choice, however, in this tutorial, you will find Max value selected.

Max value selected

4. Click OK.

The output shows that the maximum amount paid was 300$ for Tomato. Similarly, max amounts paid against each product are also displayed against respective products.


Two-dimensional Pivot Table

Two dimensional pivot table can also be generated by adding/dragging a field to the Rows area and Columns area. To get the Sum of amount paid for import goods to each country, of each product, drag the following fields to the different areas.

1. Origin Field to the Rows area.

2. Product Field to the Columns area.

3. Amount Field to the Values area.

4. Category Field to the Report Filter area.

Report Filter area

Below you can find the two-dimensional pivot table.

two-dimensional pivot table

For effective and easy comparing of these numbers, create a pivot chart and apply a filter.

apply a filter