get-digital-help.com

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.

How to create chart

Step 1 - Insert bar chart

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

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

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

Step 4 - Add a second series to bar chart

1. Right click on bar chart
2. Click "Select Data..."
3. Click "Add" button
   
4. Series name: Click "Value"
   
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
    
11. Click Close
12. Select axis above chart
    
13. Delete

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.

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 […]