How to Create a Pivot Table in Excel

Let’s assume you are working in a company that deals in exporting fruits and vegetables to different countries. You have a data set of orders that have been processed to various countries date wise, and you want to explore your data set to see which product categories are best-selling in various countries. A pivot table allows you to split, filter, and group your data as per product categories, countries, and dates. This dataset contains 6 data fields as shown below.

Create a Pivot Table

After formatting your data set in an appropriate way as shown above, now you are ready to create a Pivot table in Excel. You need to perform the following steps to create the Pivot Table;

• Click any cell in your dataset
• Go to Insert tab, click on PivotTable in Tables group

• The following dialog box appears. Excel automatically selects your data set and by default creates a New Worksheet where you want the Pivot table report to be placed. Alternately, you can select Existing Worksheet and location where you want the Pivot table report to be placed. Press OK.

Placing Data Fields

After inserting a Pivot Table, Excel generates the Report View pane on the left side and lists all the data fields in Pivot Table Field List panel on right side. You need to pick and drag data fields from Field List panel and place them in Row Labels, Column Labels, Filter and Values areas to generate their report view on the left-hand side.

You can create single dimensional or two-dimensional Pivot tables by placing data field in their respective Labels as per your requirement. Number fields are placed in Values area to make a calculation, and other data fields are placed in Row, Column and Filter areas of Pivot Table Fields list panel as per requirement.

Single Dimensional Pivot Table

A single dimensional Pivot table summarizes dataset either in Row layout or Column layout for the selected data fields.

For example, if you want to see the summarized report of all products only, then it is called a single dimensional Pivot table. In order to do that, you need to drag and place Product field either in Row or Column Label area and place Amount field in Values area to sum their respective order amounts.

This summarizes the sales amounts of all the products in the data set.

Two Dimensional Pivot Table

Two-dimensional Pivot tables are created by dragging and placing data fields in Rows and Columns areas. It further splits the data and gives more insight into data by generating a different view of data.

For example, if you want to see the summary of sales of products country-wise, then you can create a two-dimensional pivot table by placing Product field in Row area, Country field in Column area and Amount field in Values area. It will give you break down products sale country-wise

Filtering Pivot Table

You can filter Pivot table results by placing the data field in Report Filter area, on the basis of which you want to narrow down your results by applying filters. For example, you want to filter results based on product category, so you can place Category field in Report Filter area and narrow down the results by selecting one category from the list. Suppose you want to summarize products related to Fruit category only.

Summarize Value field by

In Excel, Pivot table by default summarizes numeric field results by applying SUM. But, you can change that by changing the Field Value Settings of Value area field.

For example, instead of SUM, you can change the result by COUNT, Percentage, or Average, etc. Suppose you want to count how many orders have been placed in each country for each product. In order to do that, just click on the drop-down list of Amount field in Value area, and click on “Value Field Settings…”.

A Value Field Settings dialog box appears. In Summarize Values By tab, click on COUNT and click OK

The Report View pane will show a count of values in each country for each product, instead of the sum of values.

Show Values As

In Excel, Pivot table by default shows values as “No Calculation.” But, you can change that by changing the Field Value Settings on “Show Value As” tab of Value area field.

For example, you can show the values as % of Row, % of Column or % of Grand Total, etc. In order to do that, just click on the drop-down list of Amount field in Value area, and click on “Value Field Settings…”.

A Value Field Settings dialog box appears. On Show Values As tab, click on the drop-down arrow and select % of Grand Total and click OK

You can see Pivot table has changed the output as % of Grand Total values of each product and for each country. In this way, you can check the contribution of each product in total revenue country wise.

Sort Pivot table Field by

In Excel, Pivot table by fault sorts the fields’ results placed in Row and Column areas in ascending order and places their summarized values accordingly. But you can change this order and sort any of the fields as per your own requirement.

For example, you can sort the product from ascending to descending order. Right-click any cell inside the Pivot table and click on Sort and select the desired order.

In the same way, you can sort the results on the basis of Grand Total values from Largest to Smallest.

Grouping Pivot table data by

The pivot table can group the fields’ data in report view as per data type and user requirements. For example, you can group the date and time fields in hours, minutes, days, months, quarters, years. It is recommended to place such fields in the Row area, right-click in any cell of that field inside the pivot table, click on Group and click on the desired grouping item.

For example, you can group the Date field in Months and summarize the Pivot table data as per months for product sold. Here, you need to place Date field in the Row area, and then place Product field under Date field in Row area. Go to Pivot table; right-click in any cell of the Date field in Pivot table, click on Group, select Months and click on OK.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.