Control a Chart with a Combo Box

0 Flares 0 Flares ×

The ability to control a chart with a form control such as a Combo Box provides interactivity for the user. Allowing the user to be able to interact with an Excel dashboard takes your dashboard to another level.

In this tutorial, I have a list of the top 5 goalscorers from 4 different European leagues. I want the user to be able to select a league from a list and for the correct chart to be displayed.  The steps required to make this happen are outlined below:

Insert the Combo Box

The Developer tab is required to insert the Combo Box in Excel 2007 and later. If you cannot see the Developer tab;

Excel 2007

  1. Click the Office button and then click the Excel Options button
  2. Select the Show Developer tab in the Ribbon option in the dialogue box
  3. Click Ok

Excel 2010

  1. Click the File tab on the Ribbon and then select Options
  2. Select the Customize Ribbon category
  3. Check the Developer tab box and click Ok

With the Developer tab visible, the Combo Box can be inserted.

  1. Click the Developer tab on the Ribbon
  2. Click the Insert button in the Controls group and select the  Combo Box (Form Control) button

Insert a combo box

  1. Draw the combo box onto the worksheet where you would like it to appear
  2. Right click on the form control and select Format Control from the menu

Format the combo box

  1. Select the Control tab if necessary
  2. Click in the Input range: field and select the range of cells that contain the options you want to appear in the list
  3. Click in the Cell link: field and select a cell to place the result of the combo box selection

Build the list and create a cell link

  1. Click Ok

Create a Nested IF Formula

The combo box is set up for the user to select a league and for the result of that selection to be stored in a cell. It is now time to do something with that selection.

I will create a nested IF formula to check the combo box selection and display data from the selected league.

Enter the formula below where $A$9 is the cell used for the combo box cell link, and the A1, D1, G1 and J1 cells is the data from the selected league. This formula should be entered in all the cells you want to pull data from the league for.


Build the Chart

You can now insert the chart using the data extracted by the nested IF formulas.

  1. Select the range of cells containing the nested IF formulas
  2. Click the Insert tab on the Ribbon
  3. Click the Column chart button and select the type of column chart you want to use
  4. Format the chart as desired

And that’s it. A chart which the user can interact with via a combo box.

Download the chart_with_combo_box.xls spreadsheet used in this tutorial for a better look. Normally all of the data required for the Combo Box to function would be stored on a separate hidden sheet.

The sheet should contain the combo box and the chart only. The data has been left on this sheet to aid the explanation of the technique only.

Learn to make Excel dashboards

6 thoughts on “Control a Chart with a Combo Box

  1. I’ve been using your videos on You tube for some time now for work – I can actually understand them!
    However I’ve used this chart and it works beautifully in Excel 2007 but at work we’ve only got Excel 2003 and I have nested 11 IF statements. I’ve tried to find a work around but other videos are too difficult for a novice to follow. Can you help? Or make a video? I need this by Tuesday (no pressure on me then!)
    My formula is; -=IF($A$16=1,B1,IF($A$16=2,D1,IF($A$16=3,F1,IF($A$16=4,H1,IF($A$16=5,J1,IF($A$16=6,L1,IF($A$16=7,N1,IF($A$16=8,P1,IF($A$16=9,R1,IF($A$16=10,T1,IF($A$16=11,V1)))))))))))

    I thought I had it sorted – I don’t

Leave a Reply

Your email address will not be published. Required fields are marked *