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 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.
What's on this page
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.
- Click on any cell in the data set.
- Press shortcut keys CTRL + T to open the "Create Table" dialog box.
- Click on "OK" button.
That is how easy it is to create Excel Tables.
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.
- Select columns "Country" and "Value" to only use these values in the chart.
- Go to tab "Insert" on the ribbon.
- Click "Bar chart" button.
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.
How to insert radio buttons
- Go to the "Developer" tab on the ribbon. Read this article: If "Developer" button is missing.
- Click the "Insert" button.
- Click the "Option" button (Form Control).
- Click and hold with left mouse button on the worksheet, drag with mouse to create an option button control.
- 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.
- Create two more option button controls, repeat steps 2 to 5 above.
How to edit radio button text
- Right-click on a radio button. A pop-up menu appears.
- Click on "Edit text".
- A prompt appears allowing you to edit the radio button text.
- Change text to "Asia", "Europe" and "North America" for all three radio buttons respectively.
- Click with left mouse button somewhere outside the selected radio button to deselect it.
How to link radio buttons to a cell
- Right-click on option button control and click "Format Control..."
- Link cell $A$3 to option button control
- Repeat step 7 and 8 with the remaining two option button controls
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.
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.
- Select cell range A3:A4.
- Press Ctrl + 1 to open the "Format Cells" dialog box.
- Go to Category "Custom".
- Type: ;;;
- 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.
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.
- Copy Excel Table, select cell range B18:D26. Press shortcut keys CTRL + c to copy the selected cell range.
- 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.
- Delete all values except the table header names.
- Select cell F19.
- Type the following formula: =IF($A$4=Table1[@Region],Table1[@Region],"")
- Press Enter.
- Select cell G19.
- Type: =IF($A$4=Table1[@Region],Table1[@Country],"")
- Press Enter.
- Select cell H19.
- Type: =IF($A$4=Table1[@Region],Table1[@Value],"")
- Press Enter.
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.
Add a second series to the bar chart
- Right-click on the bar chart. A pop-up menu appears.
- Click "Select Data..." on the pop-up menu. A dialog box shows up.
- Click the "Add" button on the dialog box, see the image below.
- Series name: Click "Value".
- Series values: Select cell range "Value" column on the second table.
- Click OK button.
- Click OK button.
- Right-click on the second series.
- Click "Format Data Series...".
- Plot series on: Secondary axis.
- Click Close.
- Select the axis above chart, see the image below.
- Click Delete key on your keyboard to remove.
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.
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
How to use mouse hover on a worksheet [VBA]
I recently discovered an interesting technique about using a user defined function in a HYPERLINK function. Jordan at the Option […]
Change chart data range using a Drop Down List [VBA]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
Color chart columns based on cell color
This article demonstrates macros that automatically changes the chart bar colors based on the corresponding cell, the first example is […]
How to create an interactive Excel chart [VBA]
This article describes how to create an interactive chart, the user may click on a button or multiple buttons and […]
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
This interactive chart allows you to select a country by clicking on a spin button. The chart and table shows […]
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 data series in a line chart
This article demonstrates how to highlight a line in a chart based on the selected item in a drop-down list. […]
3 Responses to “Highlight a group of chart bars”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Very cool Mister (Oscar)
Thank you very much, you're a genius
[…] Using option buttons, Oscar Cronquist shows how to highlight groups of data in a bar chart. […]
[…] Highlight a group of chart bars […]