Author: Oscar Cronquist Article last updated on October 01, 2018

googleI discovered this chart from Google Public policy blog and it got me thinking if I could do the same column chart in excel.

The google chart is static, only the last column is divided into vertical rectangles.

You can highlight any column in the chart I created, see below:

Highlight a column in a stacked column chart1

Here is how I did it:

Select country

Formula in cell C14

  1. Select cell C14:
  2. Type in formula bar:
    =INDEX(Table1[Country],D14)
  3. Press Enter

Spin buttons

  1. Go to tab "Developer"
  2. Click "Insert" button
  3. Click "Spin button (form controls)"
  4. Create a spin button
  5. Right click on spin button
  6. Select "Format Control"
    format control spin button
  7. Enter values in each field. (Remember to adjust Maximum value)
  8. Link to cell D14
  9. Click OK
  10. Select cell D14
  11. Right click and click "Format Cells..."
  12. Select "Custom" Category
  13. Type;;;
  14. Click OK

VBA

Sub ChangeSelection()
Dim r As Integer
    For r = 1 To Range("Table1[Country]").Rows.Count
        With ActiveSheet.ChartObjects(1).Chart
            If Application.WorksheetFunction.Match(Range("$C$14"), Range("Table1[Country]"), 0) = r Then
                .SeriesCollection(1).Points(r).Interior.Color = RGB(82, 130, 189)
                .SeriesCollection(2).Points(r).Interior.Color = RGB(198, 81, 82)
                .SeriesCollection(3).Points(r).Interior.Color = RGB(165, 190, 99)
            Else
                .SeriesCollection(1).Points(r).Interior.Color = RGB(198, 198, 198)
                .SeriesCollection(2).Points(r).Interior.Color = RGB(198, 198, 198)
                .SeriesCollection(3).Points(r).Interior.Color = RGB(198, 198, 198)
            End If
        End With
    Next r
End Sub
  1. Go to VB Editor (Alt+F11)
    vba procedure
  2. Click "Insert"
  3. Click "Module
  4. Paste above code to code module

Assign Spin button macro

  1. Right click on spin button
  2. Click "Assign macro..."
  3. Select "ChangeSelection"
  4. Click OK

Conditional formatting

  1. Select table Table1
  2. Go to tab "Home"
  3. Click "Conditional formatting" button
  4. Click "New Rule..."
    conditonal formatting column chart
  5. Click "Use a formula to determine which cells to format"
  6. Format values where this formula is true:
    =$C$14=INDIRECT("Table1[@Country]")
  7. Click "Format..."
  8. Go to tab "Fill"
  9. Pick a color
  10. Click OK
  11. Click OK

Download excel *.xlsm file

stacked column chart.xlsm

Related articles

Highlight a bar in a chart

This post demonstrates how to quickly bring attention to a single bar in a chart. Highlighted bars in two or [โ€ฆ]

Highlight a group of chart bars

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 [โ€ฆ]

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 [โ€ฆ]