Highlight a data series in a chart

This article demonstrates how to highlight a bar, group of bars, a line, and a column in their charts respectively.

1. Highlight a column in a stacked column chart (VBA)

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

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.

1.1 Select country

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.

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.

1.2 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. Press with left mouse button on the "Insert" button.
3. Press with mouse on the "Spin button" (Form Controls), see image below.
   
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.
   
6. Press with mouse 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 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

1.3 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. 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: ;;;
   
4. Press with left mouse button on OK button to apply changes.

1.4 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

1.5 Where to put the code?

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.

1.6 Assign Spin button 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.
   
3. Select macro "ChangeSelection".
4. Press with left mouse button on OK button to apply changes.

1.7 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. Press with mouse on the "Conditional formatting" button.
4. Press with left mouse button on "New Rule...".
   
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.

2. Highlight a column in a stacked column chart (no VBA)

This interactive chart allows you to select a country by press with left mouse button oning on a spin button. The chart and table show the selected country.

I have made something very similar in the year 2013, Highlight a column in a stacked column chart but using Visual Basic for Applications (VBA). This article explains how to build and make it work without VBA.

The animated image above demonstrates a stacked column chart with a highlighted column based on the selected item using a spin button.

The corresponding row in the Excel Table is also highlighted and the selected country is displayed in cell C14.

2.1 How to insert a spin button (Form Control)

1. Go to tab "Developer" on the ribbon.
2. Press with left mouse button on the "Insert" controls button, see the image below.
3. Press with left mouse button on "Spin Button" in group "Form Controls".
   
4. Press and hold with left mouse button on the worksheet.
5. Drag with mouse on the worksheet to size the spin button.
6. Release the left mouse button.

Back to top

2.2 How to configure spin button

1. Press with right mouse button on on spin button
2. Press with left mouse button on "Format Control..."
3. Go to tab "Control"
   
4. Change the maximum value, if you have 10 countries use value 10. 20 countries use value 20.
5. Select a cell to link the spin button to, see the image above.
6. Press with left mouse button on OK button to apply changes.

Back to top

2.3 How to link a spin button to a cell value?

Cell C14 shows the selected country, the cell is linked to the spin button using a formula and refreshes every time you press with left mouse button on the spin button.

1. Double-press with left mouse button on with left mouse button on cell C14.
2. Type this formula:
   =INDEX(Table13[Country],D14)
3. Press Enter.

The value changes in cell C14 when you press with left mouse button on the spin buttons. The spin button is linked to cell D14.

The formula in cell C14 then uses the value in cell D14 to get the country name in column Country in table Table13. The value is shown in cell C14.

Back to top

2.4 How to apply Conditional Formatting to an Excel table

1. Select all values in the Excel Table.
2. Go to tab "Home" on the ribbon.
3. Press with left mouse button on the "Conditional formatting" button.
4. Press with left mouse button on "New Rule...". A dialog box appears on the screen, see the image below.
   
5. Press with mouse on "Use a formula to determine which cells to format" to select it.
6. Type in "Format values where this formula is true:"
   =$C$14=INDIRECT("Table1[@Country]")
7. Press with left mouse button on "Format..." button. Another dialog box appears.
8. Pick a color.
9. Press with left mouse button on OK button to return to the first dialog box.
10. Press with left mouse button on OK button to dismiss the first dialog box.

This formula

compares the value in cell C14 with each of the values in column Country, Table13. If a value is a match the entire row is highlighted.

Back to top

Explaining Conditional Formatting formula

Step 1 - Create a structured reference

A cell reference pointing to a value in an Excel Table is different than regular cell references.

Table13[@Country]

The first value is the Table name, in this example: Table13

The item in brackets is the column name in the Excel Table, the @ character points to a cell that is on the same row as the CF formula itself.

Table13[@Country]

Step 2 - Workaround for Excel Tables in CF formulas

The INDIRECT function is needed in order to use a structured reference in a Conditional Formatting formula.

INDIRECT("Table13[@Country]")

Step 3 - Compare values in column "Country" to value in cell C14

The equal sign lets you compare two or more values to a condition.

$C$14=INDIRECT("Table13[@Country]")

An expression using logical operators (equal sign is one of them) is called a logical test or logical expression. The return TRUE or FALSE or their equivalent numerical digit TRUE - 1 or FALSE - 0 (zero).

The Conditional Formatting formula highlights the row if the logical expression returns TRUE and nothing happens if it evaluates to FALSE.

Back to top

2.5 Copy Excel Table and new formulas

1. Copy table13
2. Paste it next to table 13 but keep at least one blank column between tables.
   
   (Press with left mouse button on to enlarge)
3. Delete all values except headers in the new table
4. Select the first cell (I3) in the new table and type: =IF(Table13[@Country]=$C$14,Table13[@Country],""). You don´t need to copy the formula to cells below, the table takes care of that.
5. Cell J3: =IF([@Country]<>"",Table13[@[GDP (nominal)]],"")
6. Cell K3: =IF([@[GDP (nominal)]]<>"",Table13[@[GDP per capita (nominal)]],"")
7. Cell L3: =IF([@[GDP (nominal)]]<>"",Table13[@Agricultural],"")
8. Cell M3: =IF([@[GDP per capita (nominal)]]<>"",Table13[@Industrial],"")
9. Cell N3: =IF([@[GDP per capita (nominal)]]<>"",Table13[@Service],"")

(Press with left mouse button on for larger picture)

We are using the second table to populate the second series in a chart. If this doesn't make sense to you, don't worry.

Back to top

2.6 Insert a column chart

1. Select table13 data.
2. Go to tab "Insert".
3. Press with left mouse button on "Column Chart" button.
4. Press with left mouse button on "Stacked Column" button.
   
5. Press with right mouse button on on chart and press with left mouse button on "Select Data...".
   
6. Remove GDP (nominal).
7. Remove GDP per capita (nominal).
8. Press with left mouse button on OK.

This is what the chart looks like now.

Back to top
2.7 Color all stacked columns

1. Press with right mouse button on on a column
   
2. Press with left mouse button on "Format Data Series...".
3. Press with left mouse button on "Fill".
4. Press with left mouse button on "Solid Fill".
5. Pick a color.
6. Press with mouse on the next column series on the chart.
7. Repeat step 3 to 5 with all remaining column series.

Back to top

2.8 Add new data to chart

1. Press with right mouse button on on the chart.
2. Press with left mouse button on "Select Data...".
3. Press with left mouse button on the "Add" button.
4. Add series "Agriculture", "Industrial" and "Services" from the second table (not table13).

Back to top

2.9 Plot series on the secondary axis

1. Press with right mouse button on on one of the new column series.
   
2. Press with left mouse button on "Format Data Series...".
3. Press with left mouse button on "Plot Series On Secondary Axis".
   
4. Go to tab "Layout" on the ribbon.
5. Select one of the remaining new series (Agriculture, Services and Industrial).
   
6. Repeat step 3.
7. Make sure you have moved Agriculture, Services, and Industrial series to the secondary axis.

Back to top

2.10 Delete secondary y-axis

1. Select secondary y-axis.
2. Press Delete.

Back to top

Back to top

3. 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.

The method used here is to have the thinner lines on the primary axis and the highlighted lines on the secondary axis.

A formula then hides all series values except the chart series that is selected in the drop-down list. This is possible without using VBA och UDFs.

Instructions

What you will learn in this article:

• How to create a drop-down list and populate it using a cell range as a source.
• Build two Excel Tables and populating one of them with a formula that hides values except for the record that corresponds to the value in the drop-down list.
• Create a line chart
  • Populate the chart with values from both Excel Tables.
  • Use the first Excel Table on the primary axis.
  • Use the second Excel Table on the secondary axis.
  • Customize chart lines colors.
  • Remove the horizontal and vertical axis from the secondary axis.

Step 1 - Convert data to an Excel Table

One of the great things about Excel Tables is that they are easy to reference. If the table grows or shrinks the reference stays the same which makes them really easy to work with.

No need to adjust references like regular cell references in formulas. The correct term is structured references and they work somewhat differently than regular cell references. I will tell you more about it when I explain the formulas below.

1. Select any cell in the data set.
2. Press CTRL + T to open the "Create Table" dialog box.
3. Press with left mouse button on checkbox "My Table has headers" if your data set has header names.
4. Press with left mouse button on OK button.

The data set is now converted to an Excel Table, Excel has automatically changed the cell formatting and given it a name.

Press with mouse on one of the cells in the Excel Table, go to tab "Table Design" on the ribbon. You can find the Table name in the very top left corner of your Excel screen.

Step 2 - Create a drop down list

A drop-down list is a list that extends when you press with left mouse button on the arrow next to the cell. You must select the cell to see the arrow, see image above.

It is a good idea to format the cell or add a value to the adjacent cell so the user understands that you have to choose a value.

1. Select cell J2.
2. Go to tab "Data" on the ribbon.
3. Press with left mouse button on the "Data Validation" button.
4. Choose List in the drop down list near "Allow:", see image below.
5. Select cell range B19F19 as source.
6. Press with left mouse button on OK button.

Step 3 - Copy table

The first table values will populate the primary axis and the second table will populate the secondary axis. The primary axis contains the greyed out lines and the secondary axis contains the highlighted line (chart series).

1. Select the entire first Excel table named table1 cell range A19:F31.
2. Copy cells (Ctrl + c).
3. Paste to cell range H19:M31 (CTRL + v).
4. Clear the numbers in the second table, it will contain a formula that populates the table based on the selected value . I will build that formula in the next step.

Step 3 - Formula in the second Excel Table

This formula will return values if the selected drop-down list value is equal to the column Header and blanks if not.

1. Select the first empty value (cell I20) in the second table (table2).
2. Press with left mouse button on in the formula bar.
3. Use the following formula:
   =IF($J$2=Table2[[#Headers],[A]],Table1[@A],"")
4. Press Enter.
5. Select cell I20.
6. Press with left mouse button on and drag the black dot to the right, all the way to table column E.
   
7. Select cell range J20:M20.
8. Double press with left mouse button on black dot. The formulas are copied to the remaining cells below in the table.

Explaining formula

IF($J$2=Table2[[#Headers],[A]],Table1[@A],"")

Cell J2 contains the selected drop-down list value. We use an absolute cell reference meaning it will not change when we copy the formula across the table, the cell reference becomes $J$2.

Table2[[#Headers],[A]] is a structured reference pointing to column header name A in Excel Table named Table2.

$J$2=Table2[[#Headers],[A]]

becomes

"E"="A"

and returns FALSE

The IF function returns the corresponding value from Excel Table Table1, note that the reference changes when you copy the cell to the remaining cells in Table2.

IF($J$2=Table2[[#Headers],[A]],Table1[@A],"")

becomes

IF(FALSE ,[A]],Table1[@A],"")

becomes

IF(FALSE, 1, "")

and returns "" in cell I20.

Step 4 - Insert a line chart

The Line Chart is great for graphing data series, they are easy to create and modify. Use the scatter chart if you want to plot data that contains numbers in both the x and y-axis.

1. Select Excel Table table1.
2. Go to tab "Insert" on the ribbon.
3. Press with left mouse button on "Line".
4. Press with left mouse button on "Line chart".

A line chart appears populated with lines based on the data in Table1, see image above. Press and hold with left mouse button on the chart then drag to change the location.

Press and hold SHIFT key simultaneously as you drag to move it horizontally or vertically. Press and hold the Alt key while you drag to align the chart border to the cell grid beneath.

To select the chart simply press with left mouse button on it with the left mouse button, size handles appear. Press and hold with the left mouse button and then drag to resize the chart.

Step 5 - Color lines

This step will make the lines more subtle to make the selected line stand out a little more.

1. Select a data series.
2. Press with right mouse button on on the selected data serie.
3. Press with left mouse button on "Format Data Series...".
4. Select "Line Color".
5. Select "Solid line".
6. Pick a light gray color.
7. Press with left mouse button on Close button.

Repeat line 1 to 7 with remaining data series.

Step 6 - Add the second Excel Table to the second axis

This step will plot the highlighted line on the secondary axis.

1. Press with right mouse button on on chart.
2. Press with left mouse button on "Select Data...".
   
3. Press with left mouse button on "Add" button.
4. Series name: I19
5. Series values: I20:I31
6. Press with left mouse button on OK button.
   
7. Press with left mouse button on the "Edit" button.
8. Select cell range H20:H31.
9. Press with left mouse button on OK button.
10. Press with left mouse button on OK button.
11. Select item A in the drop down list.
12. Press with right mouse button on on data series A on the chart.
13. Press with left mouse button on "Format Data series...".
14. Select secondary axis.
15. Press with left mouse button on OK.

Repeat above steps 1 - 15 with table column B, C, D and E.

Remove secondary y axis, see picture below.

Step 7 - Remove series in first axis from legend

Remove A to E colored gray from the legend. (Press with mouse on each character and press Delete)

Delete major gridlines

Animated image

4. Highlight a bar in a chart

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.

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.

1. Select the cell range that will be the chart data source.
2. Press CTRL + T to open the "Create Excel Table" dialog box.
   
3. 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

1. Select Excel Table columns Country and % GDP in the first Excel Table.
2. Go to tab "Insert" on the ribbon.
3. Press with mouse on "Clustered Bar" button.

The chart shows up on the worksheet, see image below.

Create a drop down list

1. Select a cell (H18)
   
2. Go to tab "Data" on the ribbon.
3. Press with left mouse button on "Data validation" button.
4. Press with left mouse button on "Data Validation..." and a dialog box appears, see image below.
   
5. Change to Allow: List
6. Source: =INDIRECT("Table13[Country]")
7. 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.

1. Select the first cell in table column Country
2. Type
   =IF(Table13[@Country]=$H$18,Table13[@Country],"")

   $H$18 is the cell reference to the Drop Down List.

3. Press Enter which will automatically fill the cells below with the same formula.
4. Select the first cell in table column % GDP Type
   =IF(Table13[@Country]=$H$18,Table13[@[% GDP]],"")
5. Press Enter.
6. Select the first cell in table column "GDP per capita $"
7. Type
   =IF(Table13[@Country]=$H$18,Table13[@[GDP per capita $]],"")
8. 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

1. Press with right mouse button on on chart.
2. Press with left mouse button on "Select Data...".
   
3. Press with left mouse button on "Add" button.
   
4. Series name: Cell reference to table column header "% GDP".
5. Series values: Cell reference to table column "% GDP".
6. Press with left mouse button on OK button.
7. Press with left mouse button on OK button.
   
8. Select the new series on the chart.
9. Press with right mouse button on on series.
10. Press with left mouse button on "Format Data Series...".
    
11. Select the secondary axis.
12. Press with left mouse button on Close button.

Delete axis

1. Select duplicate x axis.
   
2. Press Delete button

Change bar color

1. Select a bar on the secondary axis
   
2. Press with right mouse button on on bar
3. Press with left mouse button on "Format Data Series..."
   
4. Press with left mouse button on "Fill"
5. Select "Solid Fill"
6. Select a color
7. Press with left mouse button on Close

Repeat above steps with the second chart

5. 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

1. Select cell H18
2. Go to tab "Data"
3. Press with left mouse button on "Data Validation" button
4. Select List
5. Select table column Country

1. 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?

1. Press with right mouse button on on the sheet name and a context menu appears.
2. Press with left mouse button on "View Code", see image below.
   
3. 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

1. Select table1.
2. Go to tab "Home" on the ribbon.
3. Press with left mouse button on "Conditional formatting" button.
4. Press with left mouse button on "New rule...".
5. Press with left mouse button on "Use a formula to determine which cells to format".
6. Format values where this formula is true:
   =INDIRECT("Table1[@Country]")=$H$18

   

7. Press with left mouse button on "Format..." button
8. Go to tab "Fill"
9. Pick a color
10. Press with left mouse button on OK
11. Press with left mouse button on Ok

6. 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 with column charts as well.

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 to highlight a bar in a bar chart or column chart.

The following animated image shows a chart that highlights countries based on the selected region using radio (option) buttons. You select a region by pressing the left mouse button on an option button, to the left of the chart.

6.1 Create an Excel Table

I recommend converting the data range to an Excel Table, it allows you to easily organize, sort, and format data any way you like.

1. Press with mouse on any cell in the data set.
2. Press shortcut keys CTRL + T to open the "Create Table" dialog box.
3. Press with mouse on "OK" button.

That is how easy it is to create Excel Tables.

Back to top

6.2 How to create a bar chart

It is very easy as well to create an Excel chart, you have plenty to choose from. The image above shows a bar chart and that is what I am going to insert now.

1. Select columns "Country" and "Value" to only use these values in the chart.
   
2. Go to tab "Insert" on the ribbon.
3. Press with left mouse button on "Bar chart" button.

Back to top

6.3 Build radio (option) buttons

The image above shows three radio buttons located to the left of the bar chart. The following steps describe how to create and link them to a worksheet.

Back to top

6.4 How to insert radio buttons

1. Go to the "Developer" tab on the ribbon. Read this article: If "Developer" button is missing.
2. Press with left mouse button on the "Insert" button.
3. Press with left mouse button on the "Option" button (Form Control).
4. Press and hold with left mouse button on the worksheet, drag with mouse to create an option button control.
5. Release the left mouse button when you are happy with the option button size. You can easily change the size and location after you created the option button.
6. Create two more option button controls, repeat steps 2 to 5 above.

Back to top

6.5 How to edit radio button text

1. Press with right mouse button on on a radio button. A pop-up menu appears.
2. Press with mouse on "Edit text".
3. A prompt appears allowing you to edit the radio button text.
4. Change text to "Asia", "Europe" and "North America" for all three radio buttons respectively.
5. Press with left mouse button on with left mouse button somewhere outside the selected radio button to deselect it.

Back to top

6.6 How to link radio buttons to a cell

1. Press with right mouse button on on option button control and press with left mouse button on "Format Control..."
2. Link cell $A$3 to option button control
3. Repeat step 7 and 8 with the remaining two option button controls
   

Back to top

6.7 Create formula based on selected radio button

Formula in cell A4:

The CHOOSE function returns a value from an array based on a number. CHOOSE(index_num, value1, [value2], ...)

The number is in cell A3 and changes when the user selects a radio button. The above image shows that the third radio button is selected, cell A3 contains 3.

CHOOSE(A3,"Asia","Europe","North America")

becomes

CHOOSE(3,"Asia","Europe","North America")

and returns "North America" in cell A4.

Back to top

6.8 Format cell values

These steps demonstrate how to hide the cell values using cell formatting, however, the formula bar still shows the value if you select cell A3 or A4.

1. Select cell range A3:A4.
2. Press Ctrl + 1 to open the "Format Cells" dialog box.
3. Go to Category "Custom".
4. Type: ;;;
5. Press with left mouse button on OK (This hides values in cell A3 and A4).

The cell values in cell A3 and A4 will now change based on the selected radio button but the user can't see the values on the worksheet.

Back to top

6.9 Extract values

I will now copy the Excel Table and use formulas to extract values from the first Excel Table based on which radio is selected.

1. Copy Excel Table, select cell range B18:D26. Press shortcut keys CTRL + c to copy the selected cell range.
2. Paste it to the right but leave a column between, see image above. Column E is the column between, Excel Tables can't be adjacent.
3. Delete all values except the table header names.
4. Select cell F19.
5. Type the following formula: =IF($A$4=Table1[@Region],Table1[@Region],"")
6. Press Enter.
7. Select cell G19.
8. Type: =IF($A$4=Table1[@Region],Table1[@Country],"")
9. Press Enter.
10. Select cell H19.
11. Type: =IF($A$4=Table1[@Region],Table1[@Value],"")
12. Press Enter.

Back to top

6.10 Explaining formula in cell F19

The IF function returns one value if the logical test evaluates to true and another value if false.

IF(logical_test, [value_if_true], [value_if_false])

The logical_test argument is $A$4=Table1[@Region] It compares the value in cell A4 with a value on the same row in column Region in Table1.

The dollar signs make the cell reference an absolute cell reference meaning it won't change if you copy the cell and paste to cells below.

IF($A$4=Table1[@Region],Table1[@Region],"")

becomes

IF("North America"="North America",Table1[@Region],"")

becomes

IF(TRUE,Table1[@Region],"")

becomes

IF(TRUE,"North America","")

and returns "North America" in cell F19.

Back to top

6.11 Add a second series to the bar chart

1. Press with right mouse button on on the bar chart. A pop-up menu appears.
2. Press with left mouse button on "Select Data..." on the pop-up menu. A dialog box shows up.
3. Press with left mouse button on the "Add" button on the dialog box, see the image below.
   
4. Series name: Press with left mouse button on "Value".
   
5. Series values: Select cell range "Value" column on the second table.
6. Press with left mouse button on OK button.
7. Press with left mouse button on OK button.
8. Press with right mouse button on on the second series.
9. Press with left mouse button on "Format Data Series...".
10. Plot series on: Secondary axis.
    
11. Press with left mouse button on Close.
12. Select the axis above chart, see the image below.
    
13. Press with left mouse button on Delete key on your keyboard to remove.

Back to top

6.12 Group values in a bar chart

This chart has categorized the countries into regions with multi-level category labels.

You can create great-looking categories by deleting some of the values in the region column. Make sure the region column is sorted and then delete all duplicates. See this picture.

The region names are now centered on the chart. You can find this chart on sheet 2 on the attached file below.

Back to top

Back to top

7. Highlight group of values in an x y scatter chart programmatically

I will in this article demonstrate how to highlight a group of values plotted in an x y scatter chart using a Drop Down List and a small VBA macro.

The example used here lets you select a region and the chart instantly highlights the corresponding countries, it also shows the names of those countries next to the values.

The animated image below shows how the chart changes when a new region is selected.

Add a Drop Down List

A regular Drop Down list in Excel is easy to create, it allows you to control the value in a given cell using Excel's Data Validation. Excel returns an error message if the value is not in the Drop Down List.

The most significant disadvantage is they are easy to remove, copy a cell and paste it to the cell containing the Drop Down List and it is gone, you need VBA code to protect it from being overwritten if this is an issue for you.

A smaller disadvantage is that you can't see if a cell contains a Drop Down List or not until you select the actual cell.  I recommend that you change the cell color or create a cell border, so the user can quickly identify the Drop Down List. Here are the steps to create a Drop Down List:

1. Select cell I18.
2. Go to tab "Data" on the ribbon.
3. Press with mouse on the "Data Validation" button.
4. Select List.
5. Type Africa, Asia, Europe, North America, South America in Source:
   
6. Press with left mouse button on OK to create a Drop Down List

Insert a scatter chart

A scatter chart shows the relationships between two sets of values. My graph displays the relationship between GDP per Capita and life expectancy for a few countries.

The Drop Down List allows the user to select a group of Countries to highlight data in the chosen region, making the chart more comfortable to read.

It makes the worksheet more fun to use as it is now interactive allowing the user to examine the data in a way they prefer.

1. Select the numbers in table table1
   
2. Go to tab "Insert".
3. Press with left mouse button on the "Scatter" chart button.
4. Press with left mouse button on "Scatter with only markers".

Change y and x-axis formatting.

VBA code

Microsoft Excel lets you create programs called macros that you can use in your workbook, macros, and User-defined Functions consists of VBA code. VBA stands for Visual Basic for Applications, and a macro is easy to add to a workbook, there are detailed instructions below.

This particular macro is an event macro that runs when a cell value changes in a worksheet. In this case, it looks for a change in cell I18. Event macros are a bit different than regular macros. They are located in a worksheet module and not in a regular module.

I will explain how it works and how to implement it.

'Event macro
Private Sub Worksheet_Change(ByVal Target As Range)

'Dimension variable and declare data type
Dim r As Integer

'Check if cell is I18
If Target.Address = "$I$18" Then

    '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

        'The FOR ... NEXT statement iterates from 1 to the number of rows in column Country in Table1 using variable r as a container
        For r = 1 To Range("Table1[Country]").Rows.Count

            'Check if value in column Region in Excel table Table1 based on variable r is equal to the value in cell I18
            If Range("Table1[Region]").Cells(r).Value = Range("$I$18") Then

                'Change color of chart marker based on variable r
                .SeriesCollection(1).Points(r).Format.Fill.ForeColor.RGB = RGB(0, 0, 255)

                'Show Data Labels
                .SeriesCollection(1).Points(r).ApplyDataLabels

                'Use country name as Data Label text
                .SeriesCollection(1).Points(r).DataLabel.Text = Range("Table1[Country]").Cells(r).Value
            
            Else

                'Change marker color
                .SeriesCollection(1).Points(r).Format.Fill.ForeColor.RGB = RGB(198, 198, 198)

                'Enable errror handling
                On Error Resume Next

                'Delete Data Label
                .SeriesCollection(1).Points(r).DataLabel.Delete

                'Disable error handling
                On Error GoTo 0
            End If
        Next r
    End With
End If
End Sub

Where to put the VBA code?

Follow these instructions to access the sheet module for a specific worksheet.

1. Press with right mouse button on on the sheet name.
2. Press with mouse on "View Code".
   
3. Paste code (see below) to sheet module.
   

   

4. Exit VB Editor

Built-in Charts

Combo Charts

Combined stacked area and a clustered column chart
Combined chart – Column and Line on secondary axis
Combined Column and Line chart

Chart elements

Chart basics

How to create a dynamic chart
Rearrange 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
Excel charts tips and tricks

Custom charts

How to build an arrow chart
Advanced Excel Chart Techniques
How to graph an equation
Build a comparison table/chart
Heat map yearly calendar
Advanced Gantt Chart Template

Sparklines

Win/Loss Column Line

Highlight chart elements

Highlight a column in a stacked column chart no vba
Highlight a group of chart bars
Highlight a data series in a line chart
Highlight a data series in a chart
Highlight a bar in a chart

Interactive charts

How to filter chart data
Hover 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
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 chart

Advanced charts

Custom data labels in a chart
How to improve your Excel Chart
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 series
Dynamic stock chart
Change chart axis range programmatically
How to create a stock chart

Excel categories

Comments (17)