Excel total: Subtotals and grand totals in a pivot table

Excel total: Subtotals and grand totals in a pivot table

Excel total – Subtotals and grand totals:YOUR OBJECTIVE

Your company’s sales are recorded in an Excel spreadsheet. After creating a pivot table based on that spreadsheet, you organized the customers in your pivot table into groups, identifying them with your company’s order-processing specialists. Now you want to create a subtotal for each group so that you can see, in addition to the grand excel total’s, the value of the orders handled by each order-processing specialist.

This learning module ” Excel total – Subtotals and grand totals”will show you how easy it is to add and manage subtotals and grand totals in a pivot table.

Excel total – Subtotals and grand totals:HOW DOES IT WORK?

In a pivot table, you can show or hide excel total ‘s,  subtotals and grand totals for any column or row. You can also, if you like, include a subtotal for each group you create. In the example below, you want to present a more complete sales analysis by including in your pivot-table report a subtotal for each order-processing specialist.

Excel total – Subtotals and grand totals:Inserting and enabling subtotals

In the pivot table, first select the row or column group label to which you want to add a subtotal. Then – under PivotTable Tools, on the Options tab, in the Active Field group – click the Field Settings button to access the Field Settings dialog box.

Now, on the Subtotals & Filters tab, select one of the following options…

Automatic – to insert a subtotal using the default function: Sum.

Custom – to insert a different function or more than one function. To select multiple functions, press and hold the [Ctrl] key when selecting functions from the displayed list. This allows you, for example, to insert subtotals for both Count and Sum.

Conclude by clicking OK .

Subtotals may appear above (in the row with the group name) or below the grouped items. You can change this by clicking the Subtotals button in the Layout group (PivotTable Tools, Design tab). In the selection list that appears, you can choose from the following options:

  1. Select to display pivot-table subtotals at the bottom of their groupings.
  2. Select  to display pivot-table subtotals at the top of their groupings (in the row with the group name).
  3. Select  to hide all subtotals in a pivot table.

Excel total – Subtotals and grand totals:Hifing and showing grand totals

You can also hide or show the grand totals in your pivot table. To do so, click the Grand Totals  button in the Layout group (PivotTable Tools, Design tab). There are four options to choose from:

  1. Select Off for Rows and Columns to hide all grand totals in your pivot table.
  2. Select On for Rows and Columns to display all grand totals in your pivot table.
  3. Select On for Rows Only to display grand totals for rows, but hide them for columns.
  4. Select On for Columns only to display grand totals for columns, but hide them for rows.