The pie of pie chart has two plots and is mostly used to improve visibility by transferring some pf the data points from the main plot to a second plot. The transferred data points are summed up as "Others" in the first plot. The pie of pie chart provides several options for shifting the data points between plots: by position number from the bottom of the data table, by value (all points less than a certain value), by percentage value, and custom.
The custom option means manually selecting which plot the selected point belongs to. I would like to use this feature to automate the pie of pie chart to show a drill down of any segment of the first plot, if it is expanded in a pivot table to show its sales by quarter of the year. The solution is based on combining the summing functionality of a pivot table and automation provided by Microsoft Visual Basic for Applications.
Chapters
00:00 Drilldown of a pie of pie chart
01:01 Preparing the pivot table and pivot chart
04:21 Available options for splitting the data series between plots in Excel
07:15 VBA-macro for automating custom data point assignment
08:05 Pivot table update event
08:25 Declaring the variables
09:54 Defining the variables
11:57 Transferring data points to the second plot
14:19 Resizing the second plot (optional)
16:18 Indenting the code for better visibility
18:54 Testing the VBA-macro
17:40 Adapting the macro to your own workbook
For more contents like this, please subscribe to my channel.
#MsExcel #ExcelTips #VBA
Screenshots used with permission from Microsoft.
Негізгі бет Automate the pie of pie chart and control it with a pivot table to drill down any slice in MS Excel
No video
Пікірлер