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.

Highlight a bar in a chart
Highlight a bar in a chart VBA

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

Highlight a bar in a chart 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

Highlight a bar chart Excel Table second series

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

Select duplicate x axis.
Press Delete button

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

Get the Excel file

Highlight-a-bar-in-a-chart1.xlsm

Highlight a bar in a chart (VBA)

Highlight a bar 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

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.

Contact Oscar

You can contact me through this contact form

Highlight a bar in a chart

Table of Contents

Highlight a bar in a chart

Create an Excel table

Copy Excel Table

Create a bar chart

Create a drop down list

Enter formulas in empty Excel Table

Add a duplicate series to the secondary axis

Delete axis

Change bar color

Get the Excel file

Highlight a bar in a chart (VBA)

Drop down list

VBA Code

Where to put the code?

Apply Conditional Formatting to Excel Table rows

Get the Excel file

Built-in Charts

Combo Charts

Chart elements

Chart basics

Custom charts

Sparklines

Highlight chart elements

Interactive charts

Animate

Advanced charts

Stock charts

Excel categories

One Response to “How to create a stock chart”

How to comment