Columnist
Tags:

The Nuance of Grand Totals in Excel-based Pivot Tables

Feb 1st 2017
Columnist
xfgiro/istock

If you have experience with pivot tables in Microsoft Excel, your emotions have probably swung from one extreme to another.

First there’s the thrill of the pivot table feature helping you to quickly summarize lists of data with almost no effort. But that surge of victory can crash to the ground when you can’t quite accomplish what feels like a simple task, such as adding grand totals to rows within a pivot table.

To create a pivot table, we must begin with a list of data, such as the example shown in Figure 1. Data you wish to analyze with a pivot table must have a unique title at the top of each column, no blank rows, and no blank columns. As shown in Figure 1, you:

1. Click on any cell within your data.
3. Select the PivotTable command.
4. Click OK when the Create PivotTable dialog box appears.

Figure 1: The steps involved in creating a pivot table.

At this point a new worksheet will appear within your workbook. Figure 2 illustrates these steps:

1. Choose all three fields within the PivotTable Fields list.
2. A pivot table will appear with a row for each color, and a column for shoes and for shirts. A common requirement is to add a fourth column to the pivot table that will sum the two.
3. Users often then activate the Design menu.
4. A Grand Totals command seems to be the answer.

Figure 2: The Grand Totals command on Excel’s Design menu only controls totals for a single field.

The nuance here is that Grand Totals are calculated on a field basis. In this situation, Shoes is one field, and Shirts is a second field. Thus, Grand Totals for the columns appear on row 9 of the worksheet.

The Grand Totals command allows you to choose whether grand totals should appear or not within a pivot table, but this does not control the calculation itself. Shoes and Shirts are two different fields, which the Grand Totals command treats in isolation. When you drag fields into the Columns section of the PivotTable Field list, Excel will then add a total column for data presented across, but again only for a single field.

As you have probably surmised, there is a solution here in the form of Excel’s Calculated Fields command, as shown in Figure 3:

1. Activate the Analyze menu in Excel 2013 or later or the Options menu in Excel 2007 and 2010. Click anywhere within the pivot table if you do not see this menu.
2. In Excel 2010 and later, choose Fields, Items & Sets. Excel 2007 users click the dedicated Calculated Field button here instead.
3. In Excel 2010 and later choose Calculated Field from the menu.
4. In all versions of Excel, type a field name of your choice in the Name field. Be sure that the name is not an exact spelling of any field name that already appears within the first row of your source data.
5. Double-click on Shoes to add it to the formula.
6. Type a plus sign (+) and then double-click on Shirts to add it to the formula.
7. Click OK to close the Insert Calculated Field dialog box.
8. The new field will appear automatically on your pivot table.

Figure 3: You can create custom grand totals for two or more fields with a Calculated Field.

You can turn this new calculated field on or off by way of the PivotTable Field list. If you wish to edit the grand total, such as to add or remove a column, repeat steps 1, 2, and 3 from Figure 3. Click the arrow at the right of the Name field and choose the existing calculation. From here you can edit or delete the calculated field entirely.

Tags:

You might also be interested in

Replies (2)

By JamesA
Dec 14th 2017 06:20

David - is it possible to apply the ROUNDDOWN function to the Grand Total in the pivot table ?

I have the function applied to a total in the spreadsheet but the Grand Total in the pivot performs its own calculation on the data, and delivers a different answer. I would like the 2 totals to match.

Thanks (0)
By vanessa2210
Feb 27th 2019 14:36

I'm hoping someone can help with a calculated field of a Pivot table: I want to take say, column B in the Pivot Table and divide it by the TOTAL of column A. Can anyone help?﻿

Thanks (0)