Author: Oscar Cronquist Article last updated on August 31, 2018

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 chart highlights countries depending on the selected region. 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

How to create chart

Step 1 - Insert bar chart

excel table

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

bar chart

Step 2 - Build option buttons

  1. Go to "Developer" tab on the ribbon
  2. Click "Insert" button
  3. Click "Option button" (Form Control)
  4. Drag on sheet to create an option button control
  5. Create two more option button controls
  6. Edit "option button control" text and change it to "Asia", "Europe" and "North America"
  7. Right click on option button control and click "Format Control..."
  8. Link cell $A$3 to option button control
  9. Repeat step 7 and 8 with the remaining two option button controls
    highlight a group of values in a chart - format option button control
  10. Select cell A4
  11. Type: =CHOOSE(A3,"Asia","Europe","North America")
  12. Select cell A3 and A4
  13. Press Ctrl + 1
  14. Go to Category "Custom"
  15. Type: ;;;
  16. Click OK (This hides values in cell A3 and A4)

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

Step 3 - Extract values

  1. Copy excel table, cell B18:D26
  2. Paste it to the right but leave a column between
  3. Delete all values except the headers
  4. Select cell F19
  5. Type: =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

Step 4 - Add a second series to bar chart

  1. Right click on bar chart
  2. Click "Select Data..."
  3. Click "Add" button
    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
  7. Click OK
  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 axis above chart
    highlight a group of values in a chart - delete axis
  13. Delete

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.

group values in a excel chart1

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

Download excel *.xlsx

Highlight groups in a chart.xlsx

Functions in this post

IF(logical_test, [value_if_true], [value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

Related articles

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 [โ€ฆ]