get-digital-help.com

I 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 clicks 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.

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

Cell C14 displays the selected country, it changes when you click one of the spin buttons. Cell C14 contains the following formula.

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

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

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. Click the "Insert" button.
3. Click on the "Spin button" (Form Controls), see image below.
   
4. Create a spin button by clicking 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. Right click on spin button.
   
6. Click on "Format Control..." and the following dialog box appears.
   
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 clicking on the button next to cell reference $D$14, see image above.
9. Click OK

Hide contents of cell D14

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. Right-click on cell D14 and click "Format Cells..." and the "Format Cells" dialog box appears.
2. Select "Custom" Category.
3. Type: ;;;
   
4. Click 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?

1. Press Alt + F11 to open the VB Editor.
   
2. Click "Insert" on the menu, see image above.
3. Click "Module" to create a new module.
4. Paste above VBA code to code module.

Assign Spin button macro

1. Right click on spin button.
2. Click "Assign macro..." and dialog box "Assign macro" appears.
   
3. Select macro "ChangeSelection".
4. Click OK button to apply changes.

Conditional formatting

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. Click on the "Conditional formatting" button.
4. Click "New Rule...".
   
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 button.
11. Click OK button again.

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

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