How do I make a chart using two different pivot tables' grand totals

New Contributor


I'm making an income v expenses spread sheet, as a part of if I've created two pivot tables (Income and expenses) I'm using the grand totals in a line chart to visualize them, but when my pivot tables change and the grand total row shifts down I lose my visuals. 


How can I lock the grand totals as my selection for each line in my graph?



2 Replies
best response confirmed by jorgegarcia1 (New Contributor)

Hi @jorgegarcia1 


use the GETPIVOTDATA function to create a little helper table and use that helper table for your chart.

Just take an empty cell somewhere outside your pivt table, start typing the = sign and click on your grand total amount in the first pivot table. This will automatically create the GETPIVOTDATA function for you:





The advantage of this function is, that at will always refer to the grand total (if you selected that on with your first step), no matter if that cell moves later up or down in your pivot table.

Thank you! it worked great