Author: Oscar Cronquist Article last updated on December 13, 2019

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

The Google chart is static, only the last column is divided into vertical rectangles, however, I will in this article demonstrate that I can build this and more.

I will show you how to build a dynamic stacked column chart that allows you to select and highlight any column in the chart using spin buttons.

When highlighted the column becomes a stacked column providing more information and is also more visually appealing because of the colors.

The Excel user simply press with left mouse button ons on one of the spin buttons and the chart highlights the previous/next column automatically.

The following animated image shows how the spin buttons control the chart and also highlights the corresponding record in the Excel Table.

Highlight a column in a stacked column chart1

The spin buttons are linked to cell D14 and the value is hidden behind the actual spin buttons, they are also linked to a VBA macro that changes the colors for the selected column.

The original chart is also a stacked column chart, however, all parts of the column have the same color applied to make them look like a regular column.

Here is how I did it.

Select country

Highlight a column in a stacked column chart formula

Cell C14 displays the selected country, it changes when you press with left mouse button one of the spin buttons. Cell C14 contains the following formula.

=INDEX(Table1[Country],D14)

The INDEX function returns a value from column Country in the Excel Table Table1 based on the hidden value in cell D14. Press with left mouse button on the spin button with an arrow pointing down and the value in cell D14 will increment by 1 with each press with left mouse button on.

The spin button with an arrow pointing up will decrease the number in cell D14 by 1. The value in cell D14 can't be smaller than 1 and larger than the number of countries in your Excel Table.

I will show you later on in the next section of this article how to change the min and max value of the spin buttons.

How to create spin buttons

Highlight a column in a stacked column chart spin buttons

The spin buttons allow you to increase or decrease a value, this makes the worksheet interactive and more user-friendly.

  1. Go to tab "Developer" on the ribbon. Developer tab missing?
  2. Press with left mouse button on the "Insert" button.
  3. Press with mouse on the "Spin button" (Form Controls), see image below.
    Highlight a column in a stacked column chart spin buttons1
  4. Create a spin button by press with left mouse button oning and hold with left mouse button on the desired location in your worksheet.
    Now drag with mouse to change the spin button size, you change this later if you are not happy with the result.
  5. Press with right mouse button on on spin button.
    spin button format control
  6. Press with mouse on "Format Control..." and the following dialog box appears.
    format control spin button
  7. Enter values in each field. Make sure to use a maximum value that is equal to the number of items (Countries) in the column.
    The minimum value is always 1, so is the incremental change value as well.
  8. Link to cell D14 by press with left mouse button oning on the button next to cell reference $D$14, see image above.
  9. Press with left mouse button on OK

Hide contents of cell D14

Hide cell value using cell formatting

You can use cell formatting to hide the value in the cell if you don't want to place the spin button above cell D14 so it hides the value.

The value is still there but not visible, you can verify that by examining the formula bar while the empty looking cell is selected.

  1. Press with right mouse button on on cell D14 and press with left mouse button on "Format Cells..." and the "Format Cells" dialog box appears.
  2. Select "Custom" Category.
  3. Type: ;;;
    Hide cell value using cell formatting Format cells
  4. Press with left mouse button on OK button to apply changes.

VBA

Copy the VBA code below and paste it to a code module in your workbook, detailed instructions below. The macro described below will change the color of the stacked columns for the selected item.

'Name macro
Sub ChangeSelection()

'Dimension variable and declare data type
Dim r As Integer

    'The FOR ... NEXT statement iterates from 1 to the number of rows in column Country in Table1 using variable r
    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(1).Chart

            'Check if r is equal to the relative position of the selected value in column Country in Table1
            If Application.WorksheetFunction.Match(Range("$C$14"), Range("Table1[Country]"), 0) = r Then

                'Change column colors for series collections 1,2 and 3
                .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
                'Apply the same color to series collections 1,2 and 3
                .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

Where to put the code?

vba procedure

  1. Press Alt + F11 to open the VB Editor.
  2. Press with left mouse button on "Insert" on the menu, see image above.
  3. Press with left mouse button on "Module" to create a new module.
  4. Paste above VBA code to code module.
Note, save the workbook with file extension *.xlsm (macro-enabled). This will save the VBA code as well as your workbook.

Assign Spin button macro

spin button assign macro

  1. Press with right mouse button on on spin button.
  2. Press with left mouse button on "Assign macro..." and dialog box "Assign macro" appears.
    spin button assign macro1
  3. Select macro "ChangeSelection".
  4. Press with left mouse button on OK button to apply changes.

Conditional formatting

Highlight a column in a stacked column chart CF formula

I will now describe how to highlight the entire record in the Excel Table based on the select country.

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

Get the Excel file


stacked-column-chart.xlsm

Weekly Blog EMAIL

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

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 […]

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 […]

Highlight a column in a stacked column chart

This interactive chart allows you to select a country by press with left mouse button oning on a spin button. […]