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.
- Press with mouse on any cell in the data set.
- Press shortcut keys CTRL + T to open the "Create Table" dialog box.
- Press with mouse 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.
- Press with left mouse button on "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.
- Press with left mouse button on the "Insert" button.
- Press with left mouse button on the "Option" button (Form Control).
- Press 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
- Press with right mouse button on on a radio button. A pop-up menu appears.
- Press with mouse 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.
- Press with left mouse button on with left mouse button somewhere outside the selected radio button to deselect it.
How to link radio buttons to a cell
- Press with right mouse button on on option button control and press with left mouse button on "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: ;;;
- 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.
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
- Press with right mouse button on on the bar chart. A pop-up menu appears.
- Press with left mouse button on "Select Data..." on the pop-up menu. A dialog box shows up.
- Press with left mouse button on the "Add" button on the dialog box, see the image below.
- Series name: Press with left mouse button on "Value".
- Series values: Select cell range "Value" column on the second table.
- Press with left mouse button on OK button.
- Press with left mouse button on OK button.
- Press with right mouse button on on the second series.
- Press with left mouse button on "Format Data Series...".
- Plot series on: Secondary axis.
- Press with left mouse button on Close.
- Select the axis above chart, see the image below.
- Press with left mouse button on 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.
Charts category
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
I recently discovered an interesting technique about using a user defined function in a HYPERLINK function. Jordan at the Option […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
Highlight category
This article demonstrates how to highlight a bar in a chart, it allows you to quickly bring attention to a […]
This article demonstrates how to highlight a line in a chart based on the selected item in a drop-down list. […]
This interactive chart allows you to select a country by press with left mouse button oning on a spin button. […]
Functions in this article
More than 1300 Excel formulas
Excel categories
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 […]