Grand Totals to the left of Excel Pivot Table instead of default right

4
16742

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!

grand totals left pivot table 1

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:

=IF(C6=””,””,IF(C6=”Grand Total”,””,LOOKUP(9.99999999999999E+304,6:6)))

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:

grand total left pivot table 2

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:

grand total left pivot table 3

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:

grand total left pivot table 4

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!

grand totals left pivot table 1

Want to learn more about Pivot tables? Check out these tutorials:

  1. Making Cash flow summary in Excel using Pivot tables with data on multiple worksheets
  2. Restructuring (Normalizing) data for Pivot tables using Pivot tables – How To
  3. Create Data Validation lists from Unsorted data with duplicates in Excel [How To]
  4. Excel Pivot Tables – Grouping Dates by Week Within Months, Quarters or Years – Workaround
  5. Running Totals and Running Balance with Excel Pivot Tables – How To

4 COMMENTS

  1. 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.

  2. 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.

  3. 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.

LEAVE A REPLY