Automate the bar of pie chart in Microsoft Excel: answering your questions
I received several questions regarding my video on automating a bar of pie chart and controlling it with a slicer in Microsoft Excel, so in this video I would like to address them. I showed a solution using Microsoft Visual Basic for Applications, pivot tables, and a slicer to automate the custom assignment of the data points to the second plot in the bar of pie chart. The example data consisted of retail sales of different ice cream flavors and brands. Every time that a different flavor was selected using a slicer, this flavor got drilled down by brand.
The covered topics in this video are how to explode the slice, which is drilled down, rename the slice "Other" using flavor names, separate flavors from brands in the second plot, changing to a pie of pie chart instead of the bar of pie chart, adding new data, like an additional flavor, and troubleshooting, namely what to do if the pivot chart does not keep its formatting and switches back to "Other" after saving and reopening it.
00:00 Automated bar of pie chart with a slicer
00:33 Questions covered in this video
01:04 Point explosion in a bar of pie chart
02:07 Rename point "Other" using slicer selection
04:35 Remove slicer selection from labels in the second plot
06:36 Changing to pie of pie chart
08:37 Adding new data using Refresh
09:15 Troubleshooting if the pivot chart changes back to "Other"
You can download the file with the chart and macro using this link (please activate macros):
https://drive.google.com/file/d/19_KO...
Note: If you choose the troubleshooting solution at the end of the video (regular chart instead of a pivot chart), then the additional code under 06:36 becomes necessary for getting correct data labels at each change of the slicer selection. It is optional for pivot charts (data labels can instead be applied and formatted once manually after changing the chart type for the macro to work properly). For regular charts it is necessary.
The link to the original video on automating the bar of pie chart:
• Automate the bar of pie chart and control ...
For more contents like this, please subscribe to my channel.
#MsExcel #ExcelTips #VBA
Screenshots used with permission from Microsoft.