Copy grand total cell of a pivot table to another cell in google spreadsheet

Question

I need to build a small 'Balance' sheet in google spreadsheet merging two pivot tables side by side - Incomes and Expenses - and then compute in one single cell the subtraction of expenses from incomes.

The problem is that the grand total cells in pivot tables are not in a fixed cell, as they shift down when rows are added. This mess up my incomes-expenses formula.

I'm searching a way to reference the grand totals cells as they move, or to copy them in fixed cells to allow me to reference with a standard formula.

any idea on how to accomplish it?

Sowmya Shankar Sowmya Shankar · Accepted Answer · 2016-09-28T14:05:11

This might be a late response. However, I was faced with the same problem and from the views of this question it seems like a lot of others have tried to find the answer too.

There is an easy way to reference grand total from the pivot tables into a single cell. I did this using =getpivotdata("source data field for grand total", pivot-table-reference).

For e.g., if the count of Items Sold is the grand total value I am looking to copy into a new cell, I'd just type in the cell =getpivotdata("Items Sold", $A$3) assuming the $A$3 is the cell of the first column and row of the pivot table.

If you are still stuck on solving this, just type = in the cell and click on the grand total cell from the pivot table. You will be able to see the formula and reference the grand total.

Copy grand total cell of a pivot table to another cell in google spreadsheet

2 Answers