MS-Excel / General Formatting

---

Move PivotTable Grand Totals

One of the most annoying things about PivotTables is that the Grand Total that summarizes your data always ends up at the bottom of the table, meaning you have to scroll down just to see the figures. Move your Grand Total up to the top where it's easier to find.

Although PivotTables are a great way to summarize data and extract meaningful information, there is no built-in option to have the Grand Total float to the top for a quick bird's-eye view.

Before we describe a very generic method to move the Grand Total to the top, we'll explain how you can accomplish this with the GETPIVOTDATA function, which is designed specifically to extract data from a PivotTable. You can use the function like this:

=GETPIVOTDATA("Sum of Amount",$B$5)

or like this:

=GETPIVOTDATA("Amount",$B$5)

Either function will extract the data and will track the Grand Total as it moves up, down, left, or right. We used the cell address $B$5, but as long as you use any cell within the PivotTable, you always will pick up the total.

The first function uses the Sum of Amount field, while the second one uses the Number Sold field. If your PivotTable has the Amount field in the Values area (pre-2007, in the Data area), you need to name the field Amount. If, however, the Amount field is being used two or more times in the Values area, you must specify the name you gave it, or the name you accepted by default.

You can double-click these fields to change them. This issue can become confusing if you are not up to speed with PivotTables. Luckily in Excel 2002 and later, the process is much easier, as you can have a cell fill in the arguments and give the correct syntax by using the mouse pointer. In any cell, type = (an equals sign) and then use your mouse pointer to click in the cell currently housing the Grand Total. Excel will automatically fill in the arguments for you.

Unfortunately, if you use the Function Wizard, or first type =GETPIVOTDATA( ) and then click in the cell currently housing the Grand Total, Excel makes a mess by trying to nest another GETPIVOTDATA function within that cell.

Probably the easiest, if least sophisticated, way to extract the Grand Total is to use the following function:

=MAX(PivGTCol)

where the column currently housing the Grand Total is named PivGTCol.

You also can use the LARGE and SMALL functions to extract from a PivotTable a host of figures according to their size. The following formula, for instance, extracts the second largest figure from a PivotTable:

=LARGE(PivGTCol,2)

You can add some extra rows immediately above the start of the PivotTable and place these formulas there so that you can see this type of information instantly, without having to scroll to the bottom of your PivotTable.