How to Link Form Controls to Charts in Excel

(Note: Suitable for users of Excel 2013, 2016, 2019, 2021, and Excel for Microsoft 365.)

Objective

Link combo box and option group form controls to an Excel chart.

How to Link Form Controls to Charts Explained

Form Controls are objects that can be inserted anywhere in a workbook to handle the data that is displayed. There are several types of form control available in Excel. In this article, we are going to focus on two of them: the Combo Box and Option Button.

For example, we could insert the Combo Box form control, which allows us to select a value from a drop-down list that we have specified.

Or, we could insert Option Buttons to select one or more options we have specified.

Combo Boxes and Options Buttons output a value when selected. For Combo Boxes, the output value is the position in the list of the Country selected in the drop-down list. So, if we select ‘USA’ from the drop-down, the output would be ‘2’ as it’s in position 2 in the list.

For Option Buttons, if we select ‘Sales’, the output would be ‘1’, and if we select ‘Revenue’, the output would be ‘2’.

We can use these output values to feed into other formulas and create a chart to display the result.

In this example, we are going to recreate the chart below. The combo box and the option buttons control the chart. We can select a country from the drop-down list and then choose to display the values for ‘Sales’ or ‘Revenue’ using the option buttons.

Create a Table
Extract a Unique List of Values
Calculate the Total Sales and Revenue by Country
Insert Form Controls
- Combo Box Form Control
- Option Button Form Control
Extract the Data Based on the Form Control Selections
Create a Column Chart

Dynamic Arrays: How to Use the Excel SEQUENCE Function and Unstacking Records

How to Use the AGGREGATE Function in Excel

Create a Table

We start with some basic sales data. This shows the Sales and Revenue for each country. Notice that the countries are repeated throughout the data. Our first job is to put this data in a table and then find each country’s total Sales and Revenue.

Click on the data.
Press Ctrl+T to create a table.
Check the cell range is correct and press Enter.
From the Table Design tab, in the Properties group, name the table ‘SalesData’.

Extract a Unique List of Values

We first need a unique list of the countries in the table. A unique list shows just the unique values and not the duplicates.

Copy the ‘Country’, ‘Sales’, and ‘Revenue’ headings and paste them somewhere else in the worksheet.

Click in cell F2.
Type =UNIQUE(

The syntax for UNIQUE is:

UNIQUE(array,[by_col],[exactly_once]

The last two arguments are optional. The first argument is the range of cells from which to extract the unique list from.

Select the values in the Country column.

Close the bracket and press Enter.

Calculate the Total Sales and Revenue by Country

Next, we need to calculate each country’s total sales and revenue.

We do this using the SUMIF function in Excel. SUMIF will add together values if a condition is met. For example, we want to add together the sales values if the country is ‘UK’, etc.

Click in cell G2.
Type =SUMIF(

The syntax for SUMIF is as follows:

SUMIF(range,criteria,[sum_range]

The range is the cell range where our criteria are found. Our criteria are what we are looking for in the range.

The sum_range is what we want to add up. So, if we take the ‘UK’ as an example, the range would be the ‘Country’ column, the criteria would be ‘UK’, and the sum_range would be the ‘Sales’ or ‘Revenue’ column (depending on which one we are calculating).

Double-click the fill handle to copy the formula down.
Click in cell H2.

The SUMIF formula is the same, except this time, the sum_range is the Revenue.

Double-click the fill handle to copy the formula down.

How to Calculate Mean in Excel

How to Use Excel FORECAST Function

Insert Form Controls

Next, we are going to insert a Combo Box form control and an Option Button form control onto the worksheet.

Combo Box Form Control

A combo box form control allows us to select an item from a drop-down list. We populate the list with data from the worksheet.

From the Developer tab, in the Controls group, click the drop-down underneath Insert.
In the Form Controls group, select the Combo Box (Form Control) button.
Draw a Combo Box anywhere on the worksheet.

Right-click on the form control and select Format Control.
On the Control tab, in the Input range field, select the unique list of countries.

The Cell link is the cell where the output of the form control will be displayed. This can be any cell on the worksheet. It’s useful to label this cell so we know what it represents.

Drop-down lines are the number of lines we want in the drop-down list. If we don’t want a scroll bar to show, we need to select the same number of drop-down lines as the number of countries.

Click OK.

The drop-down list is now populated with the countries. When we select a country from the list, the output value is shown in cell K3. Remember, the output value is the position in the list of the selected country.

Option Button Form Control

Options buttons allow us to toggle between different options. In our example, we need two option buttons for ‘Sales’ and ‘Revenue’, which we will group together so they function in relation to one another.

From the Developer tab, in the Controls group, click the drop-down underneath Insert.
In the Form Controls group, select the Option Button (Form Control).
Draw two option buttons anywhere on the worksheet.

The option buttons will be given generic names.

Double-click on the first option button.
Rename the button to ‘Sales’.
Double-click on the second option button.
Rename the button to ‘Revenue’.

Excel is smart enough to know that these two buttons are related to each other.

Right-click on either of the option buttons and select Format Control.
In the Cell link field, select cell K2.

Click OK.

The output will be 1 when ‘Sales’ is selected and ‘2’ when ‘Revenue’ is selected.

Extract the Data Based on the Form Control Selections

Now that we have our form control, we have one final step before we create the chart. We need to extract the correct data from the table using the form control selections.

Click in cell F9.
Type =F1
Click in cell G9.

We need this heading to display ‘Sales’ or ‘Revenue’ depending on the option button that has been selected. We can do this using an INDEX formula.

Type =INDEX(

The first argument is array. The array is where we are going to find what we want to return, G1:H1. The next argument is row_num. This relates to the position. We can use the value in the link cell to specify this, K2.

The heading will change depending on which option button has been selected.

Click in cell F10.

We want the country selected in the combo box form control to be displayed in cell F10. We can use the INDEX formula again. This time we are indexing the list of countries and returning the country selected in the drop-down box. We use the cell link cell to specify the row_num.

Finally, we need to extract the correct ‘Sales’ or ‘Revenue’ value from the table based on the selections in the combo box and option buttons. We do this by combining the IFS function and the INDEX function. IFS allows us to perform multiple logical tests and is a quicker way of creating nested IF statements.

IFS requires a logical_test and a value_if_true and value_if_false.

Here we are saying if the value in cell G9 is ‘Sales’, then INDEX the sales range and return the value in position 3. But, if the value in cell G9 is ‘Revenue’, then INDEX the revenue range and return the value in position 3.

Create a Column Chart

Now we’ve completed our setup; it’s time to create a column chart to visualize the data.

Select cells F9:G10.
From the Insert tab, in the Charts group, click the drop-down next to Column Chart.
Select 2-D Column Chart.
Format the chart as required. Move the form controls onto the plot area.

Also read:

3 Different Ways to Move Chart to New Sheet in Excel

How to Use Color Scales in Excel

How to Use Custom Number Formatting in Excel

Please visit our free resources center for more high-quality Excel and Microsoft Suite application guides.

Ready to dive deep into Excel? Click here for advanced Excel courses with in-depth training modules.

Simon Sez IT taught Excel and other business software for over ten years. You can access 150+ IT training courses for a low monthly fee.

Deborah Ashby

Deborah Ashby is a TAP Accredited IT Trainer, specializing in the design, delivery, and facilitation of Microsoft courses both online and in the classroom.She has over 11 years of IT Training Experience and 24 years in the IT Industry. To date, she's trained over 10,000 people in the UK and overseas at companies such as HMRC, the Metropolitan Police, Parliament, SKY, Microsoft, Kew Gardens, Norton Rose Fulbright LLP.She's a qualified MOS Master for 2010, 2013, and 2016 editions of Microsoft Office and is COLF and TAP Accredited and a member of The British Learning Institute.

How to Link Form Controls to Charts in Excel

Objective