Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 48 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 Amount field. If
your PivotTable has the Amount field 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 Data area, you must
specify the name you gave it, or the name you accepted by default
(see Figure 4-5).

Figure 4-5. The Amount field used twice and named Sum of Amount in one case and Number Sold in the other

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.