Best way to use a pivot table as data for a report

If you know how to use Pivot tables, you may be tempted to use one as the source data for a report. You should almost never do that. Pivot tables are not meant to be static . If the data that drives the pivot table changes unexpectedly, your report changes and you may not realize it. Not only that, but when you reference a cell in a Pivot table, you get the weird¬†GETPIVOTDATA function which doesn’t copy relatively very well.

TIP: If you want to reference a cell in a Pivot table, just type the cell reference manually. You can then copy that formula to reference other cells in the Pivot table.

The solution is to copy the Pivot table and then paste the values to a new tab using the Paste Special function (Paste > Paste Values from the Clipboard group on the Home tab). If your Pivot data changes deliberately, you can always re-paste the values.

Leave a Reply

Your email address will not be published. Required fields are marked *