This article demonstrates how to highlight a group of bars in a chart bar, the techniques shown here works fine with column charts as well.

Excel has some incredible tools for highlighting cells, rows, dates, comparing data and even series in line charts. A technique using the secondary axis allows you to highlight a bar in a bar chart or column chart.

The following animated image shows a chart that highlights countries based on the selected region using radio (option) buttons. You select a region by pressing the left mouse button on an option button, to the left of the chart.

Create an Excel Table

I recommend converting the data range to an Excel Table, it allows you to easily organize, sort, and format data any way you like.

1. Click on any cell in the data set.
2. Press shortcut keys CTRL + T to open the "Create Table" dialog box.
3. Click on "OK" button.

That is how easy it is to create Excel Tables.

Back to top

How to create a bar chart

It is very easy as well to create an Excel chart, you have plenty to choose from. The image above shows a bar chart and that is what I am going to insert now.

1. Select columns "Country" and "Value" to only use these values in the chart.
   
2. Go to tab "Insert" on the ribbon.
3. Click "Bar chart" button.

Back to top

Build radio (option) buttons

The image above shows three radio buttons located to the left of the bar chart. The following steps describe how to create and link them to a worksheet.

Back to top

How to insert radio buttons

1. Go to the "Developer" tab on the ribbon. Read this article: If "Developer" button is missing.
2. Click the "Insert" button.
3. Click the "Option" button (Form Control).
4. Click and hold with left mouse button on the worksheet, drag with mouse to create an option button control.
5. Release the left mouse button when you are happy with the option button size. You can easily change the size and location after you created the option button.
6. Create two more option button controls, repeat steps 2 to 5 above.

Back to top

How to edit radio button text

1. Right-click on a radio button. A pop-up menu appears.
2. Click on "Edit text".
3. A prompt appears allowing you to edit the radio button text.
4. Change text to "Asia", "Europe" and "North America" for all three radio buttons respectively.
5. Click with left mouse button somewhere outside the selected radio button to deselect it.

Back to top

How to link radio buttons to a cell

1. Right-click on option button control and click "Format Control..."
2. Link cell $A$3 to option button control
3. Repeat step 7 and 8 with the remaining two option button controls
   

Back to top

Create formula based on selected radio button

Formula in cell A4:

The CHOOSE function returns a value from an array based on a number. CHOOSE(index_num, value1, [value2], ...)

The number is in cell A3 and changes when the user selects a radio button. The above image shows that the third radio button is selected, cell A3 contains 3.

CHOOSE(A3,"Asia","Europe","North America")

becomes

CHOOSE(3,"Asia","Europe","North America")

and returns "North America" in cell A4.

Back to top

Format cell values

These steps demonstrate how to hide the cell values using cell formatting, however, the formula bar still shows the value if you select cell A3 or A4.

1. Select cell range A3:A4.
2. Press Ctrl + 1 to open the "Format Cells" dialog box.
3. Go to Category "Custom".
4. Type: ;;;
5. Click OK (This hides values in cell A3 and A4).

The cell values in cell A3 and A4 will now change based on the selected radio button but the user can't see the values on the worksheet.

Back to top

Extract values

I will now copy the Excel Table and use formulas to extract values from the first Excel Table based on which radio is selected.

1. Copy Excel Table, select cell range B18:D26. Press shortcut keys CTRL + c to copy the selected cell range.
2. Paste it to the right but leave a column between, see image above. Column E is the column between, Excel Tables can't be adjacent.
3. Delete all values except the table header names.
4. Select cell F19.
5. Type the following formula: =IF($A$4=Table1[@Region],Table1[@Region],"")
6. Press Enter.
7. Select cell G19.
8. Type: =IF($A$4=Table1[@Region],Table1[@Country],"")
9. Press Enter.
10. Select cell H19.
11. Type: =IF($A$4=Table1[@Region],Table1[@Value],"")
12. Press Enter.

Back to top

Explaining formula in cell F19

The IF function returns one value if the logical test evaluates to true and another value if false.

IF(logical_test, [value_if_true], [value_if_false])

The logical_test argument is $A$4=Table1[@Region] It compares the value in cell A4 with a value on the same row in column Region in Table1.

The dollar signs make the cell reference an absolute cell reference meaning it won't change if you copy the cell and paste to cells below.

IF($A$4=Table1[@Region],Table1[@Region],"")

becomes

IF("North America"="North America",Table1[@Region],"")

becomes

IF(TRUE,Table1[@Region],"")

becomes

IF(TRUE,"North America","")

and returns "North America" in cell F19.

Back to top

Add a second series to the bar chart

1. Right-click on the bar chart. A pop-up menu appears.
2. Click "Select Data..." on the pop-up menu. A dialog box shows up.
3. Click the "Add" button on the dialog box, see the image below.
   
4. Series name: Click "Value".
   
5. Series values: Select cell range "Value" column on the second table.
6. Click OK button.
7. Click OK button.
8. Right-click on the second series.
9. Click "Format Data Series...".
10. Plot series on: Secondary axis.
    
11. Click Close.
12. Select the axis above chart, see the image below.
    
13. Click Delete key on your keyboard to remove.

Back to top

Group values in a bar chart

This chart has categorized the countries into regions with multi-level category labels.

You can create great-looking categories by deleting some of the values in the region column. Make sure the region column is sorted and then delete all duplicates. See this picture.

The region names are now centered on the chart. You can find this chart on sheet 2 on the attached file below.

Back to top

Download Excel file

Highlight-groups-in-a-chart.xlsx

Back to top

---

Weekly Blog EMAIL

Email

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

---

Highlight a bar in a chart

This article demonstrates how to highlight a bar in a chart, it allows you to quickly bring attention to a […]

Highlight a column in a stacked column chart

I discovered this chart from Google Public policy blog and it got me thinking if I could do the same […]

Highlight a column in a stacked column chart

This interactive chart allows you to select a country by clicking on a spin button. The chart and table shows […]