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. Press with mouse on any cell in the data set.
2. Press shortcut keys CTRL + T to open the "Create Table" dialog box.
3. Press with mouse 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. Press with left mouse button on "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. Press with left mouse button on the "Insert" button.
3. Press with left mouse button on the "Option" button (Form Control).
4. Press 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. Press with right mouse button on on a radio button. A pop-up menu appears.
2. Press with mouse 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. Press with left mouse button on 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. Press with right mouse button on on option button control and press with left mouse button on "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. Press with left mouse button on 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. Press with right mouse button on on the bar chart. A pop-up menu appears.
2. Press with left mouse button on "Select Data..." on the pop-up menu. A dialog box shows up.
3. Press with left mouse button on the "Add" button on the dialog box, see the image below.
   
4. Series name: Press with left mouse button on "Value".
   
5. Series values: Select cell range "Value" column on the second table.
6. Press with left mouse button on OK button.
7. Press with left mouse button on OK button.
8. Press with right mouse button on on the second series.
9. Press with left mouse button on "Format Data Series...".
10. Plot series on: Secondary axis.
    
11. Press with left mouse button on Close.
12. Select the axis above chart, see the image below.
    
13. Press with left mouse button on 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

Get the Excel file

Highlight-groups-in-a-chart.xlsx

Back to top