Highlight a bar in a chart
This article demonstrates how to highlight a bar in a chart, it allows you to quickly bring attention to a specific chart bar in a chart. Highlighted bars in two or more charts make it easier to read and make comparisons.
This example has a Drop Down List that lets the user choose a value from a list. The list is expanded when you press with left mouse button on the arrow next to the cell, make sure you have the cell selected if you don't see the arrow.
Table of Contents
I recommend that you show the user that a certain cell contains a drop down list, for example, create a border around the cell or change the cell color.
So what happens when the user selects a value in the drop down list?
Both the charts above the drop down list highlight instantly the corresponding bar and the Excel Table to the left of the drop down list highlights the record.
Highlight a bar in a chart
The first part of this article explains how to highlight a chart bar with only formulas and a drop down list.
We will start with how to create an Excel Table then copy the Table and paste to a new location. The copied Table will be different from the first Table, it will contain formulas that change based on the selected value in the drop down list.
The bar chart will be linked to both these Excel defined Tables, one chart series to the first Excel Table and the second chart series to the second Excel Table that contains formulas.
Create an Excel table
The Excel defined Table is great in many ways, the biggest advantage, in my opinion, is combined with a chart you don't need to change the data source reference when new values are added or deleted.
The chart refreshes instantly if new values are added which makes it a great combo, here are the steps to create an Excel Table.
- Select the cell range that will be the chart data source.
- Press CTRL + T to open the "Create Excel Table" dialog box.
- Press with mouse on OK button to create the Excel Table.
Copy Excel Table
Select the entire first Excel Table then copy it, I usually press CTRL + C to copy things in Excel which is the keyboard shortcut for copying.
Select a new cell where you want to paste it, remember to not select an adjacent cell to the copied Excel Table, however, it must be next to the original Table meaning it must be on the same rows.
Press CTRL + V to paste the Excel Table to the new location, this will create a new Excel Table identical to the first one, except for the Excel Table name. Two Excel Tables can't share the same name.
Now delete all values except headers in the new Excel Table, see image above.
Create a bar chart
- Select Excel Table columns Country and % GDP in the first Excel Table.
- Go to tab "Insert" on the ribbon.
- Press with mouse on "Clustered Bar" button.
The chart shows up on the worksheet, see image below.
Create a drop down list
- Select a cell (H18)
- Go to tab "Data" on the ribbon.
- Press with left mouse button on "Data validation" button.
- Press with left mouse button on "Data Validation..." and a dialog box appears, see image below.
- Change to Allow: List
- Source: =INDIRECT("Table13[Country]")
- Press with left mouse button on OK button to apply changes.
The Drop Down List contains all countries entered in the Excel Table, it will automatically add new countries. No need to adjust cell ranges, the Excel Table uses structured references which are dynamic.
The INDIRECT function lets you use structured references in Drop Down Lists.
Enter formulas in empty Excel Table
The formulas described below will show the selected record based on the value in the Drop Down List, see image above.
- Select the first cell in table column Country
- Type
=IF(Table13[@Country]=$H$18,Table13[@Country],"")
$H$18 is the cell reference to the Drop Down List.
- Press Enter which will automatically fill the cells below with the same formula.
- Select the first cell in table column % GDP Type
=IF(Table13[@Country]=$H$18,Table13[@[% GDP]],"")
- Press Enter.
- Select the first cell in table column "GDP per capita $"
- Type
=IF(Table13[@Country]=$H$18,Table13[@[GDP per capita $]],"")
- Press Enter.
These IF functions will check the selected Drop Down List value with the Country on the same row as the formula, this is why it is important to place the Excel Table with formulas on the same rows as the original Excel Table.
The record will be visible if the Drop Down List value matches the country.
Add a duplicate series to the secondary axis
- Press with right mouse button on on chart.
- Press with left mouse button on "Select Data...".
- Press with left mouse button on "Add" button.
- Series name: Cell reference to table column header "% GDP".
- Series values: Cell reference to table column "% GDP".
- Press with left mouse button on OK button.
- Press with left mouse button on OK button.
- Select the new series on the chart.
- Press with right mouse button on on series.
- Press with left mouse button on "Format Data Series...".
- Select the secondary axis.
- Press with left mouse button on Close button.
Delete axis
Change bar color
- Select a bar on the secondary axis
- Press with right mouse button on on bar
- Press with left mouse button on "Format Data Series..."
- Press with left mouse button on "Fill"
- Select "Solid Fill"
- Select a color
- Press with left mouse button on Close
Repeat above steps with the second chart
Highlight a bar in a chart (VBA)
I created two bar charts based on the data in Excel Table Table1, this VBA example has only one Excel Table. VBA code changes chart series color based on the selected value in cell H18.
Drop down list
- Select cell H18
- Go to tab "Data"
- Press with left mouse button on "Data Validation" button
- Select List
- Select table column Country
- Press with left mouse button on OK
VBA Code
The following VBA code is event code, it is triggered when a cell value in a worksheet is changed. This code won't work if you have more than two charts inserted to the worksheet.
'Event code Private Sub Worksheet_Change(ByVal Target As Range) 'Dimension variables and declare data types Dim r As Integer, chrtobj As Integer 'Check if cell is H18 If Target.Address = "$H$18" Then 'Iterate through 1 to 2 For chrtobj = 1 To 2 'Iterate through 1 to the number of countries in Table column Country For r = 1 To Range("Table1[Country]").Rows.Count 'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property. With ActiveSheet.ChartObjects(chrtobj).Chart.SeriesCollection(1).Points(r).Interior 'Change chart series color to RGB(0, 0, 255) if row is equal to selected country If Application.WorksheetFunction.Match(Range("H18"), Range("Table1[Country]"), 0) = r Then .Color = RGB(0, 0, 255) Else .Color = RGB(165, 165, 255) End If End With Next r Next chrtobj End If End Sub
Where to put the code?
- Press with right mouse button on on the sheet name and a context menu appears.
- Press with left mouse button on "View Code", see image below.
- Paste above VBA code to the sheet module. The image below shows that sheet1 is selected in the Project Explorer and the code window contains the VBA code.
Apply Conditional Formatting to Excel Table rows
- Select table1.
- Go to tab "Home" on the ribbon.
- Press with left mouse button on "Conditional formatting" button.
- Press with left mouse button on "New rule...".
- Press with left mouse button on "Use a formula to determine which cells to format".
- Format values where this formula is true:
=INDIRECT("Table1[@Country]")=$H$18
- Press with left mouse button on "Format..." button
- Go to tab "Fill"
- Pick a color
- Press with left mouse button on OK
- Press with left mouse button on Ok
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 […]
This article demonstrates macros that automatically changes the chart bar colors based on the corresponding cell, the first example is […]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional […]
Fatou asks: Going back to my question, I had created a table and used the data to create a chart. […]
You can easily change data labels in a chart. Select a single data label and enter a reference to a […]
The image above shows a chart populated with data from an Excel defined Table. The worksheet contains event code that […]
Today I am going to show you how to create a dynamic Gantt chart in excel 2007. A Gantt chart helps […]
The picture above shows a chart that has custom data labels, they are linked to specific cell values. This means […]
I will in this article demonstrate how to set up two drop down lists linked to an Excel chart, the […]
This article describes how to create a map in Excel, the map is an x y scatter chart with an […]
This article demonstrates how to insert pictures to a chart axis, the picture above shows a column chart with country […]
I made a heat map calendar a few months ago and it inspired me to write this article. The heat […]
This article demonstrates a macro that changes y-axis range programmatically, this can be useful if you are working with stock […]
This article describes a macro that hides specific columns automatically based on values in two given cells. I am also […]
This article demonstrates how to use drop down lists combined with an Excel defined Table and a chart. This allows […]
This article demonstrates how to highlight a line in a chart based on the selected item in a drop-down list. […]
Highlight category
This article demonstrates how to highlight a line in a chart based on the selected item in a drop-down list. […]
This article demonstrates how to highlight a group of bars in a chart bar, the techniques shown here works fine […]
This interactive chart allows you to select a country by press with left mouse button oning on a spin button. […]
I discovered this chart from Google Public policy blog and it got me thinking if I could do the same […]
Functions in this article
More than 1300 Excel formulas
Excel categories
5 Responses to “Highlight a bar in a chart”
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.
Nice work!
David Hager,
Thank you!
Oscar -
Can you do the non-VBA equivalent for a stacked bar chart? You had a post on highlighting a row in a table and having the related stacked column chart (GDP sample data) highlight the color using VBA. I'd like to see this technique used for that (overlaying the existing stack with the selected one). I can get the overlay series to show correctly on the chart, but I can't figure out how to move it to a different X-Axis category from the first.
Is this possible with pivot tables as well as normal tables?
Is it possible to do the same on more than 2 charts?
Thanks.