Build a Dynamic Excel Chart That Updates Based on a Combo Box Selection

You may have encountered an Excel worksheet with the below feature - a chart that changes values based on a selection from a drop-down button or combo box.

This functionality is powered by a combination of Form Controls (Combo Box in particular) and an Excel chart which takes its source from a dynamic range that looks up the selected value and returns the individual items using the INDEX function.

Here's how to set it up

Step 1: Set up your source data table. For this example, we have an original table that shows the volumes sold per product for six months (January to June):

Step 2: Insert a combo box form control into the worksheet (File>Developer>Controls>Insert>Form Controls). You can find the Form Controls under the Developer Tab on the Microsoft Excel ribbon

Note: If you cannot find the Developer Tab on your Excel ribbon, then you need to enable it from the Options menu. Click File>Options>Customize Ribbon, and check the box next to the “Developer” option.

Step 3: Program the combo box. Right-click the combo box and select 'Format Control...'.

For the input range, use a list of the products in the table (I had already prepared on in A24:A29). For the cell link, select the cell above the next empty column in the table. You can really choose any cell (preferably a cell on a backend sheet) but I'm going with this for the purpose of this demonstration.

Once this is set up, you will notice the drop down on the combo box is now active and it shows the list of the products. You will also notice that for each product selected, the number corresponding to the relative position of that product in the input range shows up in the cell link cell (for instance, when Lettuce is selected the cell link returns 4, when Carrots is selected the return is 1, etc.)

Step 4: In the column next to your source table, set up your dynamic range. Use the =INDEX function to lookup the source table, row by row, and return the cell content based on the value in the cell link. This is what the INDEX formula will look like:

=INDEX(B3:G3,$H$2)

Drag the formula down to the rest of the dynamic range (highlighted in yellow). Because the array reference in the INDEX formula is set up as a relative reference, the formula will change on each row to reference the appropriate row range. For instance, the formula in cell H6 will be =INDEX(B6:G6,$H$2) , the one in cell H9 will be =INDEX(B9:G9,$H$2) , and so on.

The dynamic range has now been fully set up. From the demo gif below, the range changes any time the selection on the combo box changes to a different product and returns the corresponding monthly sales for each month based on the selected product.

Step 5: Insert an Excel chart using the dynamic range as source data. Before you do this though, create a new column in between the source data and the dynamic range that will contain the category labels (the Months in Column A). You can skip this step if you know how to set up an Excel chart with non-contiguous source data (data in columns separated from one another). This is what it looks like with the new column:

Now, insert the chart using the dynamic range as source data:

Your dynamic chart is now set up.