This post demonstrates how to quickly bring attention to a single bar in a chart. Highlighted bars in two or more charts makes it easier to read and make comparisons.

highlight a bar in a chart1

Here is how to do it with and without using vba:

Highlight a bar in a chart

Copy the table and delete the contents

highlight a bar in a chart - copy table

Create a bar chart

  1. Select table columns Country and % GDP
  2. Go to tab "Insert"
  3. Click Bar chart button

highlight a bar in a chart - chart

Create a drop down list

  1. Select a cell (H18)
  2. Go to tab "Data"
  3. Click "Data validation" button
  4. Click "Data Validation..."
    highlight a bar in a chart - data validation
  5. Change to Allow: List
  6. Source: Select table column "Country"
  7. Click OK

Setup the empty table

  1. Select the first cell in table column Country
  2. Type =IF(Table13[@Country]=$H$18,Table13[@Country],"")
    $H$18 is the cell reference to the drop down list
  3. Press Enter
  4. Select the first cell in table column % GDP
    Type =IF(Table13[@Country]=$H$18,Table13[@[% GDP]],"")
  5. Press Enter
  6. Select the first cell in table column "GDP per capita $"
  7. Type =IF(Table13[@Country]=$H$18,Table13[@[GDP per capita $]],"")
  8. Press enter

Add a duplicate series to the secondary axis

  1. Right click on chart
  2. Click "Select Data..."
    highlight a bar in a chart - add series
  3. Click "Add" button
    highlight a bar in a chart - add series2
  4. Series name: Cell reference to table column header "% GDP"
  5. Series values: Cell reference to table column "% GDP"
  6. Click OK
  7. Click OK
    highlight a bar in a chart - secondary axis
  8. Select the new series on the chart
  9. Right click on series
  10. Click "Format Data Series..."
    highlight a bar in a chart - secondary axis2
  11. Select secondary axis
  12. Click Close

Delete axis

  1. Select duplicate x axis.
    highlight a bar in a chart - delete x axis
  2. Press Delete button

highlight a bar in a chart - delete x axis2

Change bar color

  1. Select a bar on the secondary axis
    highlight a bar in a chart - delete x axis3
  2. Right click on bar
  3. Click "Format Data Series..."
    highlight a bar in a chart - delete x axis4
  4. Click "Fill"
  5. Select "Solid Fill"
  6. Select a  color
  7. Click Close

Repeat above steps with the second chart

Download excel *.xlsm file

Highlight a bar in a chart.xlsm

Highlight a bar in a chart (vba)

I created two bar charts from the table Table1.

Drop down list

    1. Select cell H18
    2. Go to tab "Data"
    3. Click "Data Validation" button
    4. Select List
    5. Select table column Country

highlight a bar in a chart3

  1. Click OK

VBA Code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Integer, chrtobj As Integer
If Target.Address = "$H$18" Then
    For chrtobj = 1 To 2
        For r = 1 To Range("Table1[Country]").Rows.Count
            With ActiveSheet.ChartObjects(chrtobj).Chart.SeriesCollection(1).Points(r).Interior
                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
  1. Right click on sheet name
  2. Click "View Code"
    highlight a bar in a chart1
  3. Paste above vba code to sheet module
    highlight a bar in a chart2

Conditional format table rows

  1. Select table1
  2. Go to tab "Home"
  3. Click "Conditional formatting" button on the ribbon.
  4. Click "New rule..."
  5. Click "Use a formula to determine which cells to format"
  6. Format values where this formula is true:
    =INDIRECT("Table1[@Country]")=$H$18

    highlight a bar in a chart

  7. Click "Format..." button
  8. Go to tab "Fill"
  9. Pick a color
  10. Click OK
  11. Click Ok

Download excel *.xlsm file

Highlight a bar in a chart.xlsm