Move the Grand Total to the left or to the first column in Excel PivotTable

While we are working with PivotTable in Microsoft Excel the Grand Total columns always appear on the right side and at the end of the records. One of my friends ask me if it could be possible to move the Grand Total column to the beginning of records as it would help to see the totals first and then the rest of the value.

Well, it’s possible and let’s do it !!!

In the above dataset, we have Products, Location, Quantities Sold, and Transaction Count.

In order to add the Grand Total, we need to slightly alter the source table

I recommend adding 3 rows on top of your source data. In this example as we have 3 products, in case you have more then you need to add the number of rows accordingly

In place of the Location in the blank records we add will “Grand Total” and add another column name as Qty Sold.2 [Aggreate Column] (You can give any name to this column. It is preferred to give the same name add “.2” to identify easily what aggregation will be added PivotTable)

This aggregate column (Qty Sold.2) will be used as a helper column to aggregate the values as a sum from the original quantity column. We will use a simple IF function and SUMIFS to get the information from the original Qty Sold column.

=IF	( [@Location]="Grand Total", 
    		SUMIFS( [Qty Sold] , [Product], [@Product]),
  			[@[Qty Sold]]
  	)

It’s almost done you just need

1. Go To PivotTable Analyze
2. Refresh the PivotTable
3. Remove the Qty Sold from Values
4. Add Qty Sold.2 to Values

Our PivotTable Grand Total is in the first column, which was pretty smooth…

⚠️ NOTE: Grand Total on the right side will always add the location Grand Total Rows values. Take care of it, and remove the original PivotTable Grand Total before you share the file with someone to avoid confusion.

Check this post where we will talk about how to handle this solution using the Power Query