Arrow left
Back to guides
Excel Tips

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:


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.


  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’.
How to change the display of a pivot table in the Design tab on Excel

Changing display of pivot table in Excel


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.
Using Pivot table options to show the subtotal at the bottom

 Displaying a subtotal at the bottom in a pivot table 


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
Input pivot table

  1. Click on the field in the ‘Values’ area on the right-hand side of the sheet (highlighted in the above snapshot).
Changing the Value Field Setting in PivotTable Fields in Excel

  1. 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.
A picture showing the Value Field Setting dialog box in Excel

  1. In the output, you would see the count of rows present for each item in the pivot
Displaying the count instead of the Sum of values in the Pivot Table


Learn how to do this step-by-step in the video below 👇

Automate financial reporting with LiveFlow

Want to eliminate manual updates of your Excel & Google Sheets models?

Yes, show me how

Need help?

Our team is here to help you any time between 9am and 10pm EST.
Check Icon
Email us at:

Liked this article? Then you'll love the ones below