Office hacks: Chart type smart switch

When representing data in charts, some data are suitable for bar charts, some for line charts, and some for pie charts. Representing each kind of data in different charts is rather tedious. If you use one chart and select different data with different chart types according to the drop-down list box, it will look compact and organized. This can be achieved by generating a chart data source with a defined name and changing the chart type with the help of simple VBA.

First, the chart data source worksheet in the storage of blank space to enter the required content of the drop-down menu. For example, enter the month and region in cells P1:P2. Select the “Development Tools” tab, click “Insert → ActiveX Control → Combo Box”, in the appropriate location on the worksheet to draw a combo box, select the combo box, click “Properties “, in its properties window, LinkedCell, enter “Q1”, ListFillRange, enter “P1:P2”, click BackColor to set its background color. Click BackColor to set its background color. You can also set its other properties as required. In this way, the content of the drop-down combo box is the content of P1:P2 displayed, click the contents of the combo box, it will be stored in the Q1 cell (Figure 1).


Select the “Formula” tab, click “Name Manager”, create six new names as shown in the figure and set the corresponding reference location (Figure 2). In this way, you can determine the data source of the chart according to the selection of the drop-down list.


Next, select a blank cell and insert a line chart with data markers. Right-click this blank chart, select “Select Data”, click “Add” at the legend item on the left side of the pop-up window, type “Total” at the series name in the pop-up window, type “=Sheet1!Y_tmp” at the series Y_tmp”, click “Edit” at the horizontal axis label, and enter “=Sheet1!X_tmp” in the pop-up window.

Select the “Development Tools” tab, double-click the sheet where the chart is located in the left VBAProject window, and enter the code in the right code area as shown in the figure (Figure 3). This way, the chart type will follow the changes in the drop-down list.



The chart type code can be obtained by macro recording method. First create a bar chart, click “Development Tools→Record Macro”, change the bar chart to a line chart, then click “Stop Recording”, click “Macro”, select the recorded macro name in the pop-up window, click “Edit”, and you can see the changed chart type code. The macro name, click “Edit”, you can see the changed chart type code. Once you know the code, delete the recorded macro.

Finally, place the combo box on the top level and drag and drop it to the right place on the chart (Figure 4).


Leave a Comment