How to Get a Single Value from an Excel Pivot Table

By Stephen L. Nelson, E. C. Nelson

To get a single value from an Excel pivot table using a formula, create a cell reference. For example, suppose that you want to retrieve the value shown in cell C8 in the worksheet. Further suppose that you want to place this value into cell C15.

To do this, click cell C15, type the = sign, click cell C8, and then press Enter. This is how your worksheet looks before you press Enter.

When you retrieve information from an Excel pivot table, the cell reference isn’t a simple cell reference as you might expect. Excel uses a special function to retrieve data from a pivot table because Excel knows that you might change the pivot table. Therefore, upon changing the pivot table, Excel needs more information about the cell value or data value that you want than simply its previous cell address.

image0.jpg

Look a little more closely at the pivot table formula. The actual formula is

=GETPIVOTDATA("Sales$",$A$3,"Product","KonaKoast","State",
"Oregon")

The easiest way to understand the function arguments is by using the Insert Function command. To show you how this works, assume that you enter a function formula into cell C15. If you then click cell C15 and choose the Formulas tab’s Function Wizard command, Excel displays the Function Arguments dialog box.

The Function Arguments dialog box, as you might already know if you’re familiar with Excel functions, enables you to add or change arguments for a function. In essence, the Function Arguments dialog box names and describes each of the arguments used in a function.

image1.jpg