Pivot Table Options in Excel

In this article, you will learn how to use the various options in pivot tables in Excel. To learn how to create a pivot table in Excel please refer to the following article linked here.

‍

What are the different options in pivot tables?

In Excel, pivot tables provide various display options to customize the appearance and structure of the summarized data. Here are some of the common options available in pivot tables:

Display

If you want to view the columns side-by-side then there is an option to change the display to ‘Classic’ or ‘Tabular’ format. This will help in presenting the data more structurally.

Steps: 

1. Click on any cell within the Pivot Table.
2. You will now see a ‘Design’’ tab in the ribbon. 
3. Go to the tab and click on the ‘Report Layout’ option.
4. Click on ‘Show in Tabular Form’.

‍

‍

SUBTOTALS

If you want to see the totals for each grouping in the pivot table then follow the below steps:

1. Go to the ‘Design’ tab by clicking on any cell within the pivot table.
2. Click on the dropdown shown below ‘Subtotals’ option. You will see 3 options either to show subtotals at the Bottom, Top or not show at all.
3. If you want to show the subtotal at the bottom then select the particular option and the out will be seen as below in table 2.

‍

‍

‍

VALUES

The values section allows you to summarize the data by performing calculations such as sum, average, count, etc. You can choose which fields to include in the values area and the type of calculation to be applied. Here, assume we need count the number of deals by saleperson instead of the sales amount by salesperson. 

1. Click on any cell in the PivotTable

‍

2. Click on the field in the ‘Values’ area on the right-hand side of the sheet (highlighted in the above snapshot).

‍

3. Once you click on the ‘Value Field Settings’ a dialog box will open. Select the ‘Count’ option instead of the default ‘Sum’ option as shown below. You can also choose to display ‘Average’, ’Max’, ’Min’, etc.

‍

4. In the output, you would see the count of rows present for each item in the pivot