Author: Oscar Cronquist Article last updated on September 10, 2020

Highlight a group of chart bars

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 cellsrowsdatescomparing 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.

Highlight groups in a chart6

Create an Excel Table

Highlight a group of chart bars 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.

Highlight a group of chart bars create Excel Table

  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

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.
    excel table
  2. Go to tab "Insert" on the ribbon.
  3. Click "Bar chart" button.

bar chart

Back to top

Build radio (option) buttons

highlight a group of values in a chart - option button controls

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

Highlight a group of chart bars create option 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

Highlight a group of chart bars 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

Highlight a group of chart bars format control

  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
    highlight a group of values in a chart - format option button control

Back to top

Create formula based on selected radio button

Highlight a group of chart bars build a formula

Formula in cell A4:

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

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

Highlight a group of chart bars format cell values1

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).

highlight a group of values in a chart - option button controls

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

highlight a group of values in a chart - extract values3

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.

highlight a group of values in a chart - extract values3

Back to top

Explaining formula in cell F19

Highlight a group of chart bars explaining formula

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.

Note, the Excel Table copies the formula in the first cell and pastes to cells below automatically.

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.
    highlight a group of values in a chart - add second series
  4. Series name: Click "Value".
    highlight a group of values in a chart - add second series2
  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.
    highlight a group of values in a chart - secondary axis
  11. Click Close.
  12. Select the axis above chart, see the image below.
    highlight a group of values in a chart - delete axis
  13. Click Delete key on your keyboard to remove.

Highlight a group of chart bars secondary axis1

Back to top

Group values in a bar chart

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

group values in a excel chart

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.

Highlight a group of chart bars excel table2

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

Back to top