When you create a chart from a Pivot Table, all the columns from the Pivot Table are used in the chart. Even if you highlight specific columns prior to creating the chart, Excel creates the chart by using all the columns in the Pivot Table.
So how do you include only certain columns from the Pivot Table in the chart?
Last year I made a video showing 3 workarounds.
But now, if you’re an Excel 365 subscriber, there’s another way – and that’s to not use Pivot Tables at all. Microsoft have just made the GROUPBY function available to all 365 users. Until last week it was only available to those on the Beta Channel.
In this video I use the GROUPBY function to build something that resembles a multi column Pivot Table and then I build a chart from specific columns. No more hacks needed!
Please note: if you’re on 365 in a corporate environment but don’t have the GROUPBY function, it’s probably because your company hasn’t yet updated to the latest version of 365.
The formula that I used in the solution:
=GROUPBY(Sales[[#All],[Customer Location]],Sales[[#All],[Total Revenue]:[Total Cost]],SUM,3,0,-2)
Download a copy of the file used in this video: https://share.zight.com/X6uj8dN0