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 specific chart bar in a chart. Highlighted bars in two or more charts make it easier to read and make comparisons.
This example has a Drop Down List that lets the user choose a value from a list. The list is expanded when you press with left mouse button on the arrow next to the cell, make sure you have the cell selected if you don't see the arrow.
Table of Contents
I recommend that you show the user that a certain cell contains a drop down list, for example, create a border around the cell or change the cell color.
So what happens when the user selects a value in the drop down list?
Both the charts above the drop down list highlight instantly the corresponding bar and the Excel Table to the left of the drop down list highlights the record.
Highlight a bar in a chart
The first part of this article explains how to highlight a chart bar with only formulas and a drop down list.
We will start with how to create an Excel Table then copy the Table and paste to a new location. The copied Table will be different from the first Table, it will contain formulas that change based on the selected value in the drop down list.
The bar chart will be linked to both these Excel defined Tables, one chart series to the first Excel Table and the second chart series to the second Excel Table that contains formulas.
Create an Excel table
The Excel defined Table is great in many ways, the biggest advantage, in my opinion, is combined with a chart you don't need to change the data source reference when new values are added or deleted.
The chart refreshes instantly if new values are added which makes it a great combo, here are the steps to create an Excel Table.
- Select the cell range that will be the chart data source.
- Press CTRL + T to open the "Create Excel Table" dialog box.
- Press with mouse on OK button to create the Excel Table.
Copy Excel Table
Select the entire first Excel Table then copy it, I usually press CTRL + C to copy things in Excel which is the keyboard shortcut for copying.
Select a new cell where you want to paste it, remember to not select an adjacent cell to the copied Excel Table, however, it must be next to the original Table meaning it must be on the same rows.
Press CTRL + V to paste the Excel Table to the new location, this will create a new Excel Table identical to the first one, except for the Excel Table name. Two Excel Tables can't share the same name.
Now delete all values except headers in the new Excel Table, see image above.
Create a bar chart
- Select Excel Table columns Country and % GDP in the first Excel Table.
- Go to tab "Insert" on the ribbon.
- Press with mouse on "Clustered Bar" button.
The chart shows up on the worksheet, see image below.
Create a drop down list
- Select a cell (H18)
- Go to tab "Data" on the ribbon.
- Press with left mouse button on "Data validation" button.
- Press with left mouse button on "Data Validation..." and a dialog box appears, see image below.
- Change to Allow: List
- Source: =INDIRECT("Table13[Country]")
- Press with left mouse button on OK button to apply changes.
The Drop Down List contains all countries entered in the Excel Table, it will automatically add new countries. No need to adjust cell ranges, the Excel Table uses structured references which are dynamic.
The INDIRECT function lets you use structured references in Drop Down Lists.
Enter formulas in empty Excel Table
The formulas described below will show the selected record based on the value in the Drop Down List, see image above.
- Select the first cell in table column Country
- Type
=IF(Table13[@Country]=$H$18,Table13[@Country],"")
$H$18 is the cell reference to the Drop Down List.
- Press Enter which will automatically fill the cells below with the same formula.
- Select the first cell in table column % GDP Type
=IF(Table13[@Country]=$H$18,Table13[@[% GDP]],"")
- Press Enter.
- Select the first cell in table column "GDP per capita $"
- Type
=IF(Table13[@Country]=$H$18,Table13[@[GDP per capita $]],"")
- Press Enter.
These IF functions will check the selected Drop Down List value with the Country on the same row as the formula, this is why it is important to place the Excel Table with formulas on the same rows as the original Excel Table.
The record will be visible if the Drop Down List value matches the country.
Add a duplicate series to the secondary axis
- Press with right mouse button on on chart.
- Press with left mouse button on "Select Data...".
- Press with left mouse button on "Add" button.
- Series name: Cell reference to table column header "% GDP".
- Series values: Cell reference to table column "% GDP".
- Press with left mouse button on OK button.
- Press with left mouse button on OK button.
- Select the new series on the chart.
- Press with right mouse button on on series.
- Press with left mouse button on "Format Data Series...".
- Select the secondary axis.
- Press with left mouse button on Close button.
Delete axis
Change bar color
- Select a bar on the secondary axis
- Press with right mouse button on on bar
- Press with left mouse button on "Format Data Series..."
- Press with left mouse button on "Fill"
- Select "Solid Fill"
- Select a color
- Press with left mouse button on Close
Repeat above steps with the second chart
Highlight a bar in a chart (VBA)
I created two bar charts based on the data in Excel Table Table1, this VBA example has only one Excel Table. VBA code changes chart series color based on the selected value in cell H18.
Drop down list
- Select cell H18
- Go to tab "Data"
- Press with left mouse button on "Data Validation" button
- Select List
- Select table column Country
- Press with left mouse button on OK
VBA Code
The following VBA code is event code, it is triggered when a cell value in a worksheet is changed. This code won't work if you have more than two charts inserted to the worksheet.
'Event code Private Sub Worksheet_Change(ByVal Target As Range) 'Dimension variables and declare data types Dim r As Integer, chrtobj As Integer 'Check if cell is H18 If Target.Address = "$H$18" Then 'Iterate through 1 to 2 For chrtobj = 1 To 2 'Iterate through 1 to the number of countries in Table column Country 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(chrtobj).Chart.SeriesCollection(1).Points(r).Interior 'Change chart series color to RGB(0, 0, 255) if row is equal to selected country 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
Where to put the code?
- Press with right mouse button on on the sheet name and a context menu appears.
- Press with left mouse button on "View Code", see image below.
- Paste above VBA code to the sheet module. The image below shows that sheet1 is selected in the Project Explorer and the code window contains the VBA code.
Apply Conditional Formatting to Excel Table rows
- Select table1.
- Go to tab "Home" on the ribbon.
- Press with left mouse button on "Conditional formatting" button.
- Press with left mouse button on "New rule...".
- Press with left mouse button on "Use a formula to determine which cells to format".
- Format values where this formula is true:
=INDIRECT("Table1[@Country]")=$H$18
- Press with left mouse button on "Format..." button
- Go to tab "Fill"
- Pick a color
- Press with left mouse button on OK
- Press with left mouse button on Ok
Built-in Charts
Combo Charts
Combined stacked area and a clustered column chartCombined chart – Column and Line on secondary axis
Combined Column and Line chart
Chart elements
Chart basics
How to create a dynamic chartRearrange data source in order to create a dynamic chart
Use slicers to quickly filter chart data
Four ways to resize a chart
How to align chart with cell grid
Group chart categories
How to add lines between stacked columns/bars [Excel charts]
Custom charts
How to build an arrow chartHow to graph a Normal Distribution
How to graph an equation
Build a comparison table/chart
Heat map yearly calendar
Advanced Gantt Chart Template
Sparklines
Win/Loss Column LineHighlight chart elements
Highlight a column in a stacked column chartHighlight a group of chart bars
Highlight a data series in a line chart
Highlight a column in a stacked column chart
Highlight a bar in a chart
Interactive charts
How to filter chart dataHover with mouse cursor to change stock in a candlestick chart
How to build an interactive map in Excel
Highlight group of values in an x y scatter chart programmatically
Use drop down lists and named ranges to filter chart values
How to use mouse hover on a worksheet [VBA]
How to create an interactive Excel chart [VBA]
Change chart series by clicking on data [VBA]
Change chart data range using a Drop Down List [VBA]
How to create a dynamic chart
Animate
Line chart Excel Bar Chart Excel chartAdvanced charts
Custom data labels in a chartImprove your X Y Scatter Chart with custom data labels
Label line chart series
How to position month and year between chart tick marks
How to add horizontal line to chart
Add pictures to a chart axis
How to color chart bars based on their values
Excel chart problem: Hard to read series values
Build a stock chart with two series
Change chart axis range programmatically
Change column/bar color in charts
Hide specific columns programmatically
Dynamic stock chart
How to replace columns with pictures in a column chart
Color chart columns based on cell color
Heat map using pictures
Dynamic Gantt charts
Stock charts
Build a stock chart with two seriesDynamic stock chart
Change chart axis range programmatically
How to create a stock chart
Excel categories
One Response to “How to create a stock chart”
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.
[…] Learn how to create a stock chart in excel […]