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.

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.

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.

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

Sort

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)

The output of the function is shown in the figure below

Filter

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.

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…

3. Choose the type of calculation of your choice, however, in this tutorial, you will find 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.

Below you can find the two-dimensional pivot table.

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