In pivot tables by default grand totals appear at the right end of the report. But sometimes the pivot table is so wide that user just can’t see the grand totals and we have to scroll every time we need to refer to that cell.
Situation is even more frustrating if the width of pivot table is changing. For example in if you have slicers in place and every time report is filtered the width changes.
In short, the right end is not all the time visible and is also shifting places. And it is happening just because it is at the RIGHT of pivot table. Problem will be solved if somehow we can have grand totals to the LEFT of the pivot table!
If we have the grand totals at the left, it will not only be visible all the time but also won’t be shifting places every time we filter the report. It would be awesome if Excel provided us with such option right inside pivot table layout options but its not! So we have to improvise and yes there is a workaround and once applied following is the result!
To get the grand totals column at the left, insert a new column at left if you don’t already have the space for it. Then put the following formula in the cell adjacent to the first cell of pivot table containing records:
Drag the fill handle down as far as appropriate so that calculations are visible even if pivot table attains maximum length down the columns.
Important
One important factor to consider is the last argument of LOOKUP function. You can see in my formula I put 6:6 as my first cell was in row 6. If it was row 7 then it would have been 7:7 and if it was 10 then it would have been 10:10. So you have to change it accordingly. The best way is to simply click the row heading while typing in the formula as shown in the animation below:
One last thing to mention is the reason of using two IF() functions. I wanted grand totals of individual records only and not the total of grand totals. And even if the grand total option is enabled, it won’t show up as it can be seen in the following illustration:
But if you like to have it then modify the formula slightly in which case the formula will become:
=IF(C6=””,””,LOOKUP(9.9999999999999E+304,6:6))
And now you can see that even grand total figure is visible:
Now with few formatting tweaks like coloring few cells, giving heading and disable grid view under page layout tab, its show time with slicers enabled!
Liked it? Pin it!
Want to learn more about Pivot tables? Check out these tutorials:
An easier way without a convoluted formula is just put a simple range of SUM (or can go SUMIF if you want to add some smarts) starting at D6 to D9999 and copy that down. As the pivot table expands, the new cells will already be included in the formula.
That’s all I do
Cant sort anything that way.
Its even easier now:
1. Insert an extra column to the left
2. In the required cell type =
3. Click on the cell in the pivot table that you want to use GETPIVOTDATA to automatically reference the cell. Excel automatically fills the formula. Even if the pivot table move or expands the reference will still work.
But that formula can’t be dragged down to fill a large number of of rows. Much easier to use SUMIF (as Dean has pointed out)
good tips, can achieve a simialr thing using Hlookup – just extend the range way to the right – or be clever with an inbuilt offset fucntion perhaps) – anyway thanks for this as it sparked me to use HLookup and get the result i was after.
“Boeing 710”
“Air Bus”
…
(facepalm)
on making break even graph i want to fill color differently for loss area before break even point and profit area with different fill color and also outer area with also different color.
How does C6 fit into this equation?
An easier way without a convoluted formula is just put a simple range of SUM (or can go SUMIF if you want to add some smarts) starting at D6 to D9999 and copy that down. As the pivot table expands, the new cells will already be included in the formula.
That’s all I do
Cant sort anything that way.
Its even easier now:
1. Insert an extra column to the left
2. In the required cell type =
3. Click on the cell in the pivot table that you want to use GETPIVOTDATA to automatically reference the cell. Excel automatically fills the formula. Even if the pivot table move or expands the reference will still work.
But that formula can’t be dragged down to fill a large number of of rows. Much easier to use SUMIF (as Dean has pointed out)
good tips, can achieve a simialr thing using Hlookup – just extend the range way to the right – or be clever with an inbuilt offset fucntion perhaps) – anyway thanks for this as it sparked me to use HLookup and get the result i was after.
“Boeing 710”
“Air Bus”
…
(facepalm)
on making break even graph i want to fill color differently for loss area before break even point and profit area with different fill color and also outer area with also different color.