You can also easily substitute the named range if you wish, by simply typing in the cell reference e.g. Please note, you must save the file with the extension *.xlsm if you want the macros to work. Set rngRow = Range(ThisWorkbook.Names(“PieChartValues”).RefersTo)įor Each rngRow In Range(“PieChartValues”).RowsĬhtMarker.SeriesCollection(1).Values = rngRowĬ xlScreen, xlPictureĬhtMain.SeriesCollection(1).Points(lngPointIndex).Paste Set chtMain = ActiveSheet.ChartObjects(“chtMain”).Chart Set chtMarker = ActiveSheet.ChartObjects(“chtMarker”).Chart Open up the selection pane (Home > Find and select > Selection pane) and rename the graph “chtMain” Step 5: Add the vba code to add the pie charts to the bubble graph. Adjust the axes and the formatting of the graph as you wish. Highlight the columns B, C & D of your data table, and insert a bubble graph (Click on Insert > Other graphs > Bubble).Įxcel will automatically format the bubbles for you. The X and Y columns will be used by Excel to plot your bubbles on the graph, the Size column tells Excel the size of the bubble Step 4: Create the bubble graph. Format the segments as you wish: Step 3: Include the bubble graph data in the data table.Īdd the data to the 3 columns of your data table as shown below (X, Y and Size):
Open up the selection pane (Home > Find and select > Selection pane) and rename the graph “chtMarker”. Remove the fill of the chart and the outline, leaving only the six segments of the pie chart. Using the Data 1 row, create a pie chart.
Create a named range by highlighting the numbers in your data table and then typing the new name “PieChartValues” in the name box. You should be able to see this table on the basic tab of the spreadsheet above. Each row of data represents a pie chart you wish to create with 6 segments per pie chart. (Please note the three blank columns, we’ll fill those in in Step 3). Imagine you have a data table like the one shown below.
0 kommentar(er)
