Highlight a column in a stacked column chart
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.
- Go to tab "Developer" on the ribbon. Developer tab missing?
- Click the "Insert" button.
- Click on the "Spin button" (Form Controls), see image below.
- 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. - Right click on spin button.
- Click on "Format Control..." and the following dialog box appears.
- 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. - Link to cell D14 by clicking on the button next to cell reference $D$14, see image above.
- 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.
- Right-click on cell D14 and click "Format Cells..." and the "Format Cells" dialog box appears.
- Select "Custom" Category.
- Type: ;;;
- 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?
- Press Alt + F11 to open the VB Editor.
- Click "Insert" on the menu, see image above.
- Click "Module" to create a new module.
- Paste above VBA code to code module.
Assign Spin button macro
- Right click on spin button.
- Click "Assign macro..." and dialog box "Assign macro" appears.
- Select macro "ChangeSelection".
- 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.
- Select the entire Excel table Table1.
- Go to tab "Home" on the ribbon.
- Click on the "Conditional formatting" button.
- Click "New Rule...".
- Click "Use a formula to determine which cells to format".
- Format values where this formula is true:
=$C$14=INDIRECT("Table1[@Country]")
- Click "Format..."
- Go to tab "Fill".
- Pick a color.
- Click OK button.
- Click OK button again.
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
How to use mouse hover on a worksheet [VBA]
I recently discovered an interesting technique about using a user defined function in a HYPERLINK function. Jordan at the Option […]
Change chart data range using a Drop Down List [VBA]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
Color chart columns based on cell color
This article demonstrates macros that automatically changes the chart bar colors based on the corresponding cell, the first example is […]
How to create an interactive Excel chart [VBA]
This article describes how to create an interactive chart, the user may click on a button or multiple buttons and […]
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 clicking on a spin button. The chart and table shows […]
Highlight a data series in a line chart
This article demonstrates how to highlight a line in a chart based on the selected item in a drop-down list. […]
2 Responses to “Highlight a column in a stacked column chart”
Leave a Reply
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.
Oscar -
I use Excel 2007 and I fiddled with this code forever to try and make it work. The problem is in conditional formatting with your rule =$C$14=INDIRECT("Table1[@Country]")
The code that finally worked for me was
=$C14=INDIRECT("Table1[[#This Row],[Country]]")
Without the [#This Row] addition the conditional format you use always returns the first row of the table in Excel 2007, so it only highlights the table row when you pick the first country. I'm not sure what the @ symbol does in your formula - is it the Excel 2010 equivalent of [#This Row]?
Not that you need to stress-test your solutions against every Excel version, but hopefully this will help people like me who are stuck in older versions until their employer upgrades!
GMF,
is it the Excel 2010 equivalent of [#This Row]?
Yes, thank you for pointing that out.