How to create an interactive Excel chart

1. How to create an interactive Excel chart

This section describes how to create an interactive chart, the user may press with left mouse button on a button or multiple buttons and the chart shows corresponding data based on the selected buttons.

Slicers were introduced in Excel 2010, see image above. I recommend you use slicers instead if you own Excel 2010 or a later version

No VBA code is required if you choose to use slicers. Slicers let you filter values in the Excel defined Table, the chart is instantly refreshed based on your selection.

Anyway, this article may still be interesting, you will in this article learn to

• Insert buttons to a worksheet.
• Create a macro in a code module.
• Link buttons to macro.
• Filter an Excel defined Table programmatically based on selected buttons.
• Highlight selected button programmatically.
• Remove highlight from button if press with left mouse button oned on a second time.
• Save button text to an array variable.

The animated image below demonstrates the functionality of these buttons.

Create a table

1. Select a cell in your data set.
2. Go to tab "Insert" on the ribbon.
3. Press with left mouse button on "Table" button.
4. Press with left mouse button on OK.

Create chart

1. Select any cell in your Excel defined Table.
2. Go to tab "Insert".
3. Press with left mouse button on "Columns" button.
4. Press with left mouse button on "2D Clustered column".
5. Press with right mouse button on on chart.
6. Select "Select data".
7. Press with left mouse button on "Switch row/column" button.
8. Press with left mouse button on OK.

Insert shapes

1. Go to tab "Insert".
2. Press with left mouse button on "Shapes" button.
3. Press with left mouse button on "Rounded Rectangle".
4. Create three rectangles below the chart.
5. Press with right mouse button on on rectangle and select "Edit text".
6. Type button names and make sure they match the table data in the first column (Revenue, Earnings and Employees).

VBA Code

'Name macro
Sub Chart()

'Dimension variables and declare data types
Dim temp As Variant
Dim Series() As String
Dim i As Single

'Redimension variable Series in order to make the array variable grow dynamically
ReDim Series(0)

'Don't show changes to user
Application.ScreenUpdating = False

'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.
'Application.Caller returns the object that started the macro
With ActiveSheet.Shapes(Application.Caller).Fill.ForeColor

    'Check if brightness property is 0 (zero) meaning check if button is deselected
    If .Brightness = 0 Then

        'Change brightness
        .Brightness = -0.150000006

    'If brightness property is NOT 0 (zero) meaning check if button is already selected
    Else

        'Change brightness to 0 (zero)
        .Brightness = 0
    End If
End With

'Save values to array variable temp, these names correspond to the button names
temp = Array("Rounded Rectangle 1", "Rounded Rectangle 2", "Rounded Rectangle 3")

'Iterate through values in array variable temp
For i = LBound(temp) To UBound(temp)

    '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.Shapes(temp(i))
 
        'Check if brightness is -0.150000006
        If .Fill.ForeColor.Brightness = -0.150000006 Then

            'Save text in button to last container of array variable Series
            Series(UBound(Series)) = .TextFrame2.TextRange.Characters.Text

            'Add another container to array variable Series
            ReDim Preserve Series(UBound(Series) + 1)
        End If
    End With
Next i

'Check if Series variable has more than 1 container, if so remove the last container
If UBound(Series) > 0 Then ReDim Preserve Series(UBound(Series) - 1)

'Enable Autofilter for first column in Excel defined Table Table1 
Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter Field:=1

'Apply filter based on values in variable Series to 
Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter _
    Field:=1, Criteria1:=Series, Operator:=xlFilterValues

'Show changes to user
Application.ScreenUpdating = True
End Sub

Where to put the VBA code?

1. Copy VBA code above.
2. Press Alt + F11 to open the Visual Basic Editor.
3. Select your workbook in the Project explorer.
4. Press with left mouse button on "Insert" on the menu.
5. Press with left mouse button on "Module".
6. Paste to code window.

Assign macro

1. Press with right mouse button on on one of the buttons to open a menu.
2. Press with mouse on "Assign macro".
   
3. Select the "chart" macro.
4. Press with left mouse button on OK button.

Repeat above steps with the remaining rectangles.

Recommended article:

Interactive Sales Chart using MS Excel

2. How to filter chart data

What if you want to show a selection of a data set on a chart and easily change that selection? Later Excel versions have tools that make this easy, I will in this article describe the options you have and how to configure them.

The image above shows a chart and data, the chart shows only a part of the data and I will demonstrate in this article a few techniques you can use to filter chart data based on the Excel version.

If you are looking for a VBA solution then this article is something for you. Change chart data range using a drop down list (vba)

2.1. Manually changing chart data source

This technique works in all Excel versions, however, it is tedious to change the chart source every time you want to display another part of the data.

The image above shows the data in cell range B3:E14 and the column chart below shows months horizontally and the columns show temperatures for column D.

I will now describe how to change the chart data source manually, I don't recommend doing this on a daily basis. There are better options.

To change the item press with right mouse button on on the chart, press with left mouse button on "Select Data...". A dialog box appears.

Press with left mouse button on the "arrow" button, see image above.

Select cell range B2:B14. Press and hold the CTRL key while selecting cell range E2:E14. Press Enter to return to the dialog box.

Press with left mouse button on OK button on the dialog box named "Select Data Source" to apply the changes we made.

Another option is to select the chart to display the chart data source ranges. Press and hold with the left mouse button on a data source range line, then drag with the mouse to select a new range.

Back to top

2.2. Filter chart data using an Excel Table

I recommend that you use an Excel Table if you can. The befits are great, the chart data source expands automatically if you add more records. Also, the chart data source range shrinks if you delete rows in the Excel Table.

Excel Tables came in Excel 2007 and works in all later versions.

I had to transpose the data to be able to filter based on an item. Skip this step if this is not needed in your worksheet.

Copy the original range, press with right mouse button on on a destination cell.

A popup menu appears, press with left mouse button on "Paste Special...". A dialog box shows up named "Paste Special".

Press with left mouse button on the checkbox next to Transpose, see image above. Press with left mouse button on OK button.

This will rearrange data so columns become rows and vice versa. This allows us to filter data based on an item.

Delete the old data and move the transposed data to above the chart. Select the transposed data and Press CTRL + T.

A dialog box named "Create Table" shows up, press with left mouse button on OK button.

Press with left mouse button on the arrow next to column header Month, a popup menu appears.

Deselect all items except "London".

The chart shows only the filtered Excel Table data and is almost instantly refreshed.

Back to top

2.3. Filter chart data with Slicers

Slicers were introduced in Excel version 2010 and I highly recommend this tool if you need to quickly change the chart data source based on what the user selects.

They provide buttons that the user can press with left mouse button on which filter both the Excel Table and the chart simultaneously.

The image above shows a clustered column chart, an Excel Table below, and a slicer to the right. A slicer works only with Excel Tables so you need to convert data to an Excel Table which is quickly done. I describe how to create an Excel Table in the previous section above.

Press with mouse on an item and the Excel Table is filtered based on the selected value, see image above. The chart graphs what the Excel Table shows and the changes are almost instant.

To clear selections press with left mouse button on the button located on the top right corner of the slicer. I have described in another article how to create slicers, check it out: Use slicers to quickly filter chart data

Now, if you have an earlier version than Excel 2010 or slicers are not suitable for your worksheet then read on.

Back to top

2.4. Use a named range to change chart data

The animated image above demonstrates a drop down list that allows you to show specific chart data based on the selected column name.

The named range contains a formula that uses the selected value in cell C25 to create a cell reference that the chart then uses to plot data.

Drop down list -> Named range - > Chart

Basically the same technique used here as in post Make a dynamic chart for the most recent 12 months data, however, the named range formula is different.

2.4.1 Drop down list

Here are the steps to create the drop-down list in cell C25:

1. Select cell C25.
2. Go to tab "Data" on the ribbon.
3. Press with left mouse button on "Data Validation" button.
4. Go to tab "Settings".
   
5. Select List.
6. Source: Select table headers.
7. Press with left mouse button on OK button.

2.4.2 Create a named range

1. Go to tab "Formulas".
2. Press with left mouse button on "Name Manager" button.
3. Press with left mouse button on "New.." button.
   
4. Enter a name.
5. Enter the following formula:
   =INDEX(Table16,0, MATCH('Single - named range'!$C$25,Table16[[#Headers],[London]:[Los Angeles]],0)+1)
6. Press with left mouse button on OK button.

2.4.3 Explaining the named range formula

Step 1 - Find column in Excel Table based on the selected drop-down list value

The MATCH function returns the relative position of an item in a cell range.

MATCH(lookup_value, lookup_array, [match_type])

The first argument lookup_value is the drop down list cell, the second argument is a cell reference to the Excel Table column header names, and the third argument is 0 (zero) which means there must be an exact match.

MATCH('Single - named range'!$C$25,Table16[[#Headers],[London]:[Los Angeles]],0)

becomes

MATCH("London",{"London","Tokyo","Los Angeles"},0)

and returns 1.

Step 2 - Return values from column

The INDEX function returns a single value or multiple values based on a row and optionally a column number.

INDEX(array, [row_num], [column_num])

The first argument is an array or a cell reference, in this case, a cell reference to Excel Table named Table16.

The second argument is the row number, however, if you use 0 (zero) the INDEx function returns all the value from the specified Excel Table column.

INDEX(Table16,0, MATCH('Single - named range'!$C$25,Table16[[#Headers],[London]:[Los Angeles]],0)+1)

becomes

INDEX(Table16,0, 1+1)

There is a column before the column name "London" in the Excel Table, that is why we need to add 1.

INDEX(Table16,0, 1+1)

becomes

INDEX(Table16,0, 1+1)

and returns {5; 5; 8; 9; 13; 16; 18; 18; 16; 12; 8; 6}.

2.4.4 Setting up the chart

Here are the steps to create the chart:

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 "Edit" button (Legend Entries).
   
4. Series name: Cell C25.
5. Series values: The named range "data". Don't forget the worksheet name!
6. Press with left mouse button on OK button.
7. Press with left mouse button on OK button again.

Back to top

2.5. "Helper" table

The following example has two tables and a drop-down list. The table to the right has "dynamic" values and they change depending on the chosen value in cell C25. But if you add values to the first table, the second table does not automatically include the new values. You have to change the table size yourself.

It is not as pretty as a named range but easier to work with if you want multiple data columns and chart series. The attached file below contains an example with multiple chart series.

2.5.1 Drop down list

1. Select cell C25.
2. Go to tab "Data" on the ribbon.
3. Press with left mouse button on "Data Validation" button.
4. Go to tab "Settings".
   
5. Select List.
6. Source: Select table headers.
7. Press with left mouse button on OK button.

2.5.2 Create the second table

1. Type month in cell F1 and city in cell G1.
2. Select cell range F1:G13.
3. Go to tab "Insert" on the ribbon.
4. Press with left mouse button on "Table" button.
5. Press with left mouse button on "My tables has headers".
6. Press with left mouse button on OK.

2.5.3 Enter Excel Table formula

1. Select cell F2.
2. Type:
   =Table1[@Month]

   and press Enter.

3. Select cell G2.
4. Type:
   =INDEX(Table1[@[London]:[Los Angeles]],MATCH($C$25,Table1[[#Headers],[London]:[Los Angeles]],0))
5. Press Enter.

Back to top

Back to top

2.7. Change chart series by pressing on data - VBA

The image above shows a chart populated with data from an Excel defined Table. The worksheet contains event code that changes the shown cart series based on which cell is selected. See the animated image below.

You can also select multiple cells in the table by press and hold with the left mouse button and then drag with the mouse to select multiple cells.

If you want to select cells that are not adjacent you can press and  CTRL key and then press with left mouse button on the cells you want to select. The corresponding chart series shows up in the chart automatically, this is made possible with event code.

VBA code

'Event code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Dimension variables and declare data types
Dim ACell As Range
Dim ActiveCellInTable As Boolean
Dim c As Single
Dim str As String

'Iterate every cell in selection
For Each ACell In Target

    'Enable error handling
    On Error Resume Next

    'If selected cell is in a table and the table name is Table1, save TRUE in ActiveCellInTable (boolean)
    ActiveCellInTable = (ACell.ListObject.Name = "Table1")

    'Resume normal error handling (stop if an error occurs)
    On Error GoTo 0
    
    If ActiveCellInTable = True Then

        'Save cell reference (First column in table) if str is empty
        If str = "" Then str = "Table1[[#ALL]," & ACell.ListObject.Range.Cells(1, 1).Value & "]"

        'Calculate selected cell's column number in table
        c = ACell.Column - ACell.ListObject.Range.Cells(1, 1).Column + 1

        'Check if column number is above 1
        If c > 1 Then

            'Add cell reference
            str = str & "," & "Table1[[#ALL]," & ACell.ListObject.Range.Cells(1, c).Value & "]"

            'Change "Chart 1" data source
            ChartObjects("Chart 1").Chart.SetSourceData Source:=Range(str)
        End If
    End If
Next ACell

End Sub

Where to put the code?

1. Press Alt + F11 to open the Visual Basic Editor.
2. Press with right mouse button on on sheet name Sheet1.
3. Press with left mouse button on on "View Code".
4. Copy/Paste VBA code to sheet module.
5. Exit Visual Basic Editor and return to Excel.

2.8. 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 image shows the drop-down list and the input range is cell range E2:E4.

Cell range E2:E4 contains the table names that points to Excel defined Tables located on sheet 2011, 2010 and 2009, see picture below. When you select a table name in the drop-down list, the chart is instantly populated and refreshed, see picture above.

Excel defined Tables are great to work with, they expand automatically when new values are added, this makes charts with data sources pointing to Excel defined Tables easy to work with. You don't need to adjust the cell references when records are added or deleted.

Here are the steps to set up this scenario:

Create a chart

1. Go to "Insert" tab
2. Press with left mouse button on "Column chart" button
3. Press with left mouse button on "Clustered Column" chart button

Create Combo Box

1. Go to "Developer" tab
2. Press with left mouse button on "Insert controls" button
3. Press with left mouse button on Combo Box (Form Control)
   
4. Press with left mouse button on and drag on the worksheet to create the combobox.

Add VBA code to a module

1.  Press Alt + F11 to open the VB Editor.
2. Press with right mouse button on on your workbook in the Project Explorer window.
3. Press with left mouse button on "Insert"
4. Press with left mouse button on "Module" to insert a module to your workbook.
5. Copy VBA code below (Ctrl + c)
6. Paste VBA code to the code module, see image above.
7. Return to Excel

VBA code

'Name macro
Sub SelectTable()

'Apply actions to selected drop-down list
With ActiveSheet.Shapes(Application.Caller).ControlFormat

    'Make sure the selected drop-down list name is "Drop Down 1"
    If ActiveSheet.Shapes(Application.Caller).Name = "Drop Down 1" Then

        'Change Chart 1 data source
        Worksheets("Chart").ChartObjects("Chart 1").Chart.SetSourceData Source:= _
        Range(.List(.Value) & "[#All]")
        Worksheets("Chart").ChartObjects("Chart 1").Chart.PlotBy = xlRows
    End If
End With

End Sub

Assign macro

1. Press with right mouse button on combo box.
2. Press with left mouse button on "Assign macro...".
3. Press with left mouse button on "SelectTable".
   
4. Press with left mouse button on OK button.

Populate Combo Box

1. Press with right mouse button on combo box.
2. Press with left mouse button on "Format control..." and a dialog box appears, see image below.
   
3. Go to tab "Control".
4. Press with left mouse button on "Input range" button.
5. Select cell range E2:E4.
6. Press with left mouse button on OK button.

3. How to build an interactive map in Excel

This article describes how to create a map in Excel, the map is an x y scatter chart with an inserted background picture.

The image above shows the map to the right and a table with cities and their chart coordinates. A drop-down list in cell B14 lets you pick a city and a formula extracts the appropriate coordinates.

A block dot displays the location of the selected city on the map and it changes accordingly when a new city is selected. There is a workbook to get below if you want to try it out.

How I built this map

A scatter chart allows you to place dots based on x and y values which is great in this case. I will show you how to

• insert a scatter chart
• create a drop-down list
• create formulas
• create a dynamic chart
• insert a background picture

Insert a scatter chart

The following steps describe how to place a scatter chart on a worksheet.

1. Go to "Insert" tab on the ribbon.
2. Press with left mouse button on "Scatter" button.
3. Press with left mouse button on "Scatter with only markers" button.

A blank chart shows up on the screen.

You can press and hold with the left mouse button on the chart and then drag to the desired location.

Press and hold on the handles then drag to resize the chart, see image above. Hold the shift key as you resize the chart to lock the relationship between height and width.

Hold the Alt key while resizing to snap the chart to the cell grid.

Create a drop down list

The drop-down list makes it easier for the user to select items, it is populated with values from cell range B3:B10. Change the values in cell B3:B10 and the drop-down list automatically changes the list accordingly.

If you know you will be adding more values later I recommend that you convert cell range B2:D10 to an Excel Table. That will save you time adjusting the drop-down source cell reference when you add new values.

Remember to use the INDIRECT function if you reference an Excel Table in a drop down list.

1. Select cell B14.
2. Go to tab "Data" on the ribbon.
3. Press with left mouse button on "Data Validation" button.
4. Go to "Settings" tab.
5. Select "List".
6. Select source range: B3:B10.
7. Press with left mouse button on OK.

Formulas

The worksheet has two cells containing formulas, they extract x and y values respectively from the list based on the selected item in cell B14.

Formula in cell C14:

The MATCH function looks for the selected value in cell B14 in cell range B3:B10 and returns a number representing the position of that value.

MATCH($B$14, $B$3:$B$10, 0)

becomes

MATCH("Berlin", {"Vienna"; "Copenhagen"; "Warsaw"; "London"; "Madrid"; "Paris"; "Rome"; "Berlin"}, 0)

and returns 8. Berlin is the eight and last value in cell range B3:B10.

The INDEX function returns a value from cell range C3:C10 based on a row number which is provided by the MATCH function.

INDEX(C3:C10, MATCH($B$14, $B$3:$B$10, 0))

becomes

INDEX(C3:C10, 8)

becomes

INDEX({34; 54; 46; 44; 15; 37; 17; 46}, 8)

and returns 46 in cell C14.

Formula in cell D14:

This formula is exactly the same as the first one except that the INDEX function returns a value from D3:D10.

Adjust chart data source

The dot location on the chart is based on the values in cell C14 and D14, the following steps demonstrate how to change the data source to these two coordinates.

1. Press with right mouse button on on the chart.
2. Press with left mouse button on "Select Data" from the menu.
3. Press with left mouse button on "Add" button.
   
4. Select a name, an x value, and a y value.
   
5. Press with left mouse button on Ok.

Insert a background picture

The map will be the background picture in our chart, make sure the chart has the same ratio between height and width as the image. You will get a chart that looks stretched out horizontally or vertically if the don't match.

1. Press with right mouse button on on the empty chart.
2. Press with left mouse button on "Format Plot Area...".
3. Press with left mouse button on "Fill".
4. Select "Picture or texture fill".
5. Press with left mouse button on "File..." button.
6. Select a picture.
   
7. Press with left mouse button on "Insert".
8. If you like, change "Transparency" value.
9. Press with left mouse button on "Close" button.

To check the image aspect ratio open windows file explorer and locate the image. Hover over the image file name with the mouse cursor and a box appears named tool tip. It contains the image width and height and also the image file size.

Divide the height with the width to get the aspect ratio we need. Now go back to Excel and double press with left mouse button on the chart.

A format pane shows up on the right side of the screen, press with left mouse button on the Size and properties button and then press with left mouse button on the black arrow next to "Size" to expand settings.

Make sure the height and width has the same aspect ratio as the background image.

Divide the chart height with the width to calculate the chart aspect ratio.

Change the chart height or width to make the aspect ratios match.

Chart settings

The chart has a horizontal, and a vertical axis, gridlines, a chart titel and a legend that is not needed, we are now going to remove those chart elements.

The marker type can be customized as well, I will describe how below.

1. Press with mouse on the Legend and press Delete button to remove it from the chart. You can always get it back later if you change your mind. CTRL + z will undo your last action or go to "Chart Design" tab on the ribbon. Press with mouse on "Add Chart Element" button and press with left mouse button on the element you want back.
   Now delete the chart title as well.
   
2. Delete chart gridlines. You do that by press with left mouse button oning on the to select them and then press Delete.
   
3. Change x and y axis minimum and maximum value to 0 and 100.
   
4. Make sure x and y coordinates in the table are ok. If not, make appropriate adjustments.
   
5. If you like, delete x and y axis.
   
6. Select data series on the chart.
   
7. Press with right mouse button on on data series.
8. Press with left mouse button on "Format Data Series...".
   
9. Press with left mouse button on "Marker Options".
10. Select "Built-in".
11. Select a type.
12. Press with left mouse button on "Marker Fill".
13. Select "Solid fill".
14. Pick a color.
15. Press with left mouse button on Close.

Final notes

First I thought of using longitude and latitude coordinates but I gave that up really quickly. The map is geted from Wikimedia Commons.

Tip! You can add a data label and use the series name to show the city name on the map.

1. Press with right mouse button on on the marker.
2. Press with mouse on "Add Data Labels".
3. Double press with left mouse button on the data label next to the marker to open the Format pane.
4. Select "Series Name" and deselect the remaining check boxes.

Recommended links

• Create a Map chart in Excel
• How to make a killer map using Excel in under 5 minutes with PowerMap plugin
• How to create an interactive Excel dashboard with slicers?

4. Hover with mouse cursor to change stock in a candlestick chart

This article demonstrates how to change chart series while hovering with mouse cursor over a series name. The image above shows a stock chart and two company names in the top right corner.

Hover with the mouse cursor over a company name to trigger a macro. The macro checks which company and changes the chart data source accordingly.

The chart is instantly updated with the values corresponding to the chosen company (series name). The technique shown in this article can be applied to any kind of chart.

What you will learn in this article

• Create a hyperlink using the HYPERLINK function.
• Utilize a User defined Function (UDF) in the hyperlink formula.
• Explain how to change chart series programmatically.
• How to change axis min and max values programmatically.
• Where to put UDF code in a module of your workbook.
• Save the workbook with the correct file extension.

Hover over a company name and the chart instantly changes data source.

Create a hyperlink

To insert a hyperlink you have two options, manually add a hyperlink or use the HYPERLINK function.  In this example, I will use the HYPERLINK function to create a hyperlink.

Formula in cell J2:

Formula in cell J3:

I will explain these formulas below.

Explaining formula in cell J2

I recommend that you use the "Evaluate Formula" tool to understand formula calculations in greater detail. Select a cell containing the formula you want to examine.

Go to tab "Formulas" on the ribbon. Press with left mouse button on "Evaluate Formula" button and a dialog box appear, see image above. Press with left mouse button on the "Evaluate" button to iterate through each step in the formula calculation, press with left mouse button on "Close" button to dismiss the dialog box.

Step 1 - HYPERLINK function

The HYPERLINK function has two arguments: HYPERLINK(link_location, [friendly_name])

Based on the docs the link_location argument can be the path to a file, file on a server, file on the internet, a workbook, webpage or a bookmark in a word document. However, it is also possible to use a User Defined Function that is rund when you hover over the link. This is not mentioned in the documentation.

The [friendly_name] argument is optional, we are going to use nothing "". If we type something here it will be formatted as a hyperlink in the worksheet which is not what we want. Step 3 below will explain how to show a text string without being formatted as a hyperlink.

HYPERLINK(MouseHover("Google"),"")

Step 2 - Prevent errors

The IFERROR function returns a value you specify if the first argument returns an error. This will prevent an error appearing if something goes wrong with the HYPERLINK function or the UDF.

IFERROR(HYPERLINK(MouseHover("Google"),""),"")

Step 3 - Append text

The ampersand character allows you to concatenate two or more strings. This will make sure that a value is shown in the cell. The hover will work anyway with an empty cell, however, it will certainly confuse the user when the UDF is rund perhaps by accident.

"Google"&IFERROR(HYPERLINK(MouseHover("Google"),""),"")

User defined function

'Name user defined function, dimension argument variable and declare it's data type
Function MouseHover(str As String)

'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("Chart 2")

    'Check if variable str meets condition and value in cell A1 in sheet1 is not equal to variable str
    If str = "Google" And Sheet1.Range("A1") <> str Then

        'Save text string "Google" to cell A1
        Sheet1.Range("A1") = "Google"

        'Change chart source to "A2:A54,C2:F54" in worksheet name specified in cell A1
        .Chart.SetSourceData Source:=Sheets("" & Range("A1") & "").Range("A2:A54,C2:F54")

        'Change axis max value based on number in cell K2
        .Chart.Axes(xlValue).MaximumScale = Range("K2")

        'Change axis min value based on number in cell L2
        .Chart.Axes(xlValue).MinimumScale = Range("L2")
 
    'Check if variable str equals "Apple" and cell A1 is not equal to variable str
    ElseIf str = "Apple" And Sheet1.Range("A1") <> str Then

        'Change cell contents in cell A1
        Sheet1.Range("A1") = "Apple"

         'Change chart source to "A2:A54,C2:F54" in worksheet name specified in cell A1
        .Chart.SetSourceData Source:=Sheets("" & Range("A1") & "").Range("A2:A54,C2:F54")

        'Change axis max value based on number in cell K3
        .Chart.Axes(xlValue).MaximumScale = Range("K3")

        'Change axis min value based on number in cell L3
        .Chart.Axes(xlValue).MinimumScale = Range("L3")
    End If
End With
End Function

Where to put the code?

1. Press Alt + F11 to open the Visual Basic Editor.
2. Select your workbook in the Project Explorer.
3. Press with left mouse button on "Insert" on the menu.
4. Press with left mouse button on "Module" to create a module in your workbook.
5. Copy above VBA code.
6. Paste to code module, see image above.

Calculate max and min values for chart axis

The formulas below returns the min and max values for each chart source using the min and max values in cell range D2:D54.

Formula in cell K2:

Formula in cell K3:

Formula in cell L2:

Formula in cell L3:

5. Compare data in an Excel chart using drop down lists

I will in this article demonstrate how to set up two drop down lists linked to an Excel chart, the drop down lists allow the Excel user to easily compare data series based on two Excel Tables.

The image above shows one drop down list in cell B21 and another in cell D21, the chart above those drop down lists displays the selected data series.

How to build

You will in this article learn how to:

• create drop down lists.
• link drop down list to Excel Table.
• create a named range containing a formula.
• insert a chart
• link chart data source to named range

5.1 Drop down lists

A drop down list allows you to control which values a user can enter in a cell. If the Excel user selects a cell containing a drop down list it changes and shows an arrow next to the cell.

Press with left mouse button on the arrow next to the cell with left mouse button and the cell displays all valid values you can choose from. Press with left mouse button on a value to select it and the cell will be populated with the selected value.

Note that you can't spot a drop down list on a worksheet, the arrow is only displayed if you select a cell containing a drop down list.

Be aware that it is really easy to enter an invalid value in a cell that contains a drop down list, copy a cell containing the value you want to use and paste it to the cell containing the drop down list. It will be overwritten with the new value and the drop down list is now gone.

Here are the steps to create a drop down list:

1. Select cell B21.
2. Go to tab "Data" on the ribbon.
3. Press with left mouse button on the "Data Validation" button.
   
4. Allow: Select List, see image above.
5. Source: =INDIRECT("Table1[#Headers]")

Repeat above steps with cell D21 and use this Data Validation formula:

The INDIRECT function makes it possible to use structured references in drop down lists. A structured reference is basically a reference to an Excel Table, in this case it is a cell reference to the column headers in Table2 which is an Excel Table.

The following post explains in greater detail how to use table names in drop down lists and conditional formatting formulas: How to use a table name in data validation lists and conditional formatting formulas

5.2 Named ranges

A named range allows you to specify a name for a single cell or a cell range, constant, formula or a data set. This makes it easier to work with formulas, charts, etc. For example, a cell reference in a formula usually don't say much about what it contains whereas a named range makes it clear what it is, given that you use a descriptive name.

Compare cell reference Sheet4!B3:G6 with the named range Budget2019 and you understand the benefits of using named ranges. It is easy to manage named ranges, Excel has a tool for that named "Name manager". Go to tab "Formulas" on the ribbon, press with left mouse button on "Name manager" button.

The reason I am using a named range in this example is that it allows you to define a formula as a named range, I will be using that formula in a chart to display values based on the selected values in the drop down lists.

There are two tables in worksheet Calculation. Let's create named ranges and later we are going to use them as series values in a chart.

1. Go to worksheet Calculation.
2. Go to tab "Formulas" on the ribbon.
3. Press with left mouse button on "Name Manager" button and a dialog box appears.
4. Press with left mouse button on "New..." button.
   
5. Name: Chrt_tbl1
6. Refers to: =INDEX(Table1,0,MATCH(Chart!$B$21,Table1[#Headers],0))
7. Press with left mouse button on OK

Create another named range, named Chrt_tbl2 and use this formula: =INDEX(Table2, 0, MATCH(Chart!$D$21, Table2[#Headers], 0))

5.3 Explaining formula in named range Chrt_tbl1

Step 1 - Find position of selected value

The MATCH function returns a number representing the relative position of the value in cell Chart!$B$21 in Table1[#Headers].

MATCH(Chart!$B$21,Table1[#Headers],0)

becomes

MATCH("North America 2011",Table1[#Headers],0)

becomes

MATCH("North America 2011",{"Region", "Africa 2011", "Europe 2011", "North America 2011", "South America 2011", "Australia 2011", "Asia 2011"},0)

and returns 4.

"North America 2011" is the fourth header name in the Excel Table.

Step 2 - Return header name based on position

The INDEX function returns, in this case, an array of values based on the selected drop down value.

INDEX(array, [row_num], [column_num])

An array is returned if you use 0 (zero) in the [row_num] or [column_num] argument or in both. In this example the [row_num] is 0 (zero) and the INDEX function will return all values from a given column.

INDEX(Table1,0,MATCH(Chart!$B$21,Table1[#Headers],0))

becomes

INDEX(Table1, 0, 4)

and returns {56612.3491014125; 57438.8810088729; 59697.2292143781; 56747.2057561886; 54464.3935799677; 53768.3710509187; 53320.1658788137; 54348.2912056957; 54497.7912101573; 53331.9166437539; 51050.540119587; 51948.5058470412}.

5.4 Insert chart

1. Go back to sheet "Chart".
2. Go to tab "Insert" on the ribbon.
3. Press with left mouse button on "Column" chart button | "Clustered column" chart button.
4. Press with right mouse button on on the empty chart.
5. Press with left mouse button on "Select Data...", see image above.
6. Press with left mouse button on "Add" button.
   
7. Series name: Chart!$B$21
8. Series values: =Calculation!Chrt_tbl1 (Don't forget to use the sheet reference)
9. Press with left mouse button on OK button.
10. Press with left mouse button on "Add" button again.
    
11. Series name: Chart!$D$21.
12. Series values: =Calculation!Chrt_tbl2  (Don't forget to use the sheet reference)
13. Press with left mouse button on OK button.
14. Press with left mouse button on OK button.

The following post shows you how to select a series using both an index column and an index row: Dynamic chart – Display values from a table row or column

5.5 Animated image

6. 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 Explicit VBA blog blogged about this more than a year ago. He made an amazing Periodic Table of Elements.

How to use the user defined function

I used his technique in this basic chart, see above animated image. I simply move the mouse pointer over a cell and the chart changes instantly, there is really nothing more to it. No need to press with left mouse button on cell to select it or anything like that.

Excel defined Table

My data is converted to an Excel defined Table located on a different sheet, shown in the picture below.

The great thing about Excel defined Tables is that they grow automatically if more data is added, you don't have to change cell references in formulas.

1. Select any cell in the data set.
2. Go to tab "Insert".
3. Press with mouse on "Table" button.
   
4. Press with left mouse button on OK button.

Hide value in cell I11

The user defined function saves a value in cell I11. I made it invisible by applying custom formatting to it: ;;; The value is still there, however, you can't see it on the worksheet unless you select the cell and check out the formula bar.

1. Select cell I11.
2. Press CTRL + 1 to open the Format Cell dialog box.
   
3. Make sure you are on tab "Number".
4. Press with left mouse button on Category: Custom to select it.
5. Type ;;; (see image above).
6. Press with left mouse button on OK button.

Formulas

Formula in cell I8:

Explaining formula in cell I8

Step 1 - User defined function

The user defined function MouseHover is triggered when the mouse pointer hovers over the cell. The first argument tells the UDF which value to use in order to sort the Excel defined Table. The second argument tells the UDF to either sort or change chart data source.

MouseHover("Apple", FALSE)

Step 2 - Create Hyperlink

The HYPERLINK function triggers the UDF when the mouse pointer is over a cell.

HYPERLINK(link_location, [friendly_name])

HYPERLINK(MouseHover("Apple", FALSE),"")

Step 3 - Remove error

The IFERROR function allows you to hide errors.

IFERROR(HYPERLINK(MouseHover("Apple", FALSE),""),"")

Step 4 - Remove hyperlink underline

I wanted to remove the hyperlink effect so I added "Apple"& to the formula.

"Apple"&IFERROR(HYPERLINK(MouseHover("Apple", FALSE),""),"")

Formula in cell I3:

The user defined function MouseHover filters column 1 if mouse pointer is over any cell in cell range I8:I10. The chart data range is changed if mouse pointer hovers over any cell in cell range I3:I7 based on the value in the given cell.

The bln argument determines what to do, TRUE changes the data source range based on variable str and FALSE filters the Excel defined Table based on variable str.

Conditional Formatting

A conditional formatting formula highlights the value currently being hovered over, it simply compares the "invisble" value in cell I11 with the value in eac cell in cell range I8:I10.

VBA code

The boolean value bln determines if

'Name user defined function and dimension arguments and declare data types
Function MouseHover(str As String, bln As Boolean)

'Check if boolean variable bln is equal to False
If bln = False Then

    'Check if variable str is equal to "Deselect all" and cell value in cell I11, sheet2 is not equal to variable str
    If str = "Deselect all" And Sheet2.Range("I11") <> str Then

        'Save value "Deselect all" to cell I11 on sheet2
        Sheet2.Range("I11") = "Deselect all"

        'Apply Autofilter to Excel defined table named Table 1 
        Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter _
        Field:=1, Criteria1:="", Operator:=xlFilterValues

        'Stop user defined function
        Exit Function
    End If

    'Check if value in cell I11 (sheet2) is not equal to variable str
    If Sheet2.Range("I11") <> str Then

        'Save value in variable str to cell I11 (sheet2)
        Sheet2.Range("I11") = str

        'Change data source to Excel defined table named Table1
        ActiveSheet.ChartObjects("Chart 4").Chart.SetSourceData Source:=Sheets("Sheet1").Range("Table1[#All]")

        'Plot data by rows on chart 4
        ActiveSheet.ChartObjects("Chart 4").Chart.PlotBy = xlRows

        'Use value in variable str as a condition in Excel defined Table Table1 and applied to first column
        Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter _
        Field:=1, Criteria1:=str, Operator:=xlFilterValues
    End If

'Proceed here if boolean variable bln is not equal to False
Else

    'Check if value in cell I11 (sheet2) is not equal to variable str
    If Sheet2.Range("I11") <> str Then

        'Convert value in variable str to string and save it to cell I11 (sheet2)
        Sheet2.Range("I11") = CStr(str)

        'The asterisk character makes the Excel defined Table show all values
        Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter _
        Field:=1, Criteria1:="*", Operator:=xlFilterValues

        'Change data source based on first argument
        ActiveSheet.ChartObjects("Chart 4").Chart.SetSourceData Source:=Sheets("Sheet1").Range("Table1[[#All],[Column1]], Table1[[#All],[" & str & "]]")

        'Change chart settings so it sets the way columns or rows are used as data series on the chart.
        ActiveSheet.ChartObjects("Chart 4").Chart.PlotBy = xlColumns
    End If
End If
End Function

Where to put the code?

1. Create a backup of your workbook.
2. Copy above VBA code.
3. Press Alt + F11 to open the Visual Basic Editor.

Final notes

I found out that you can't clear a table filter from a user defined function:

'This won't work!
Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter Field:=1

I had to use "*" to show all values in table column 1.

'This works!
Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter _
Field:=1, Criteria1:="*", Operator:=xlFilterValues

7. Use drop down lists and named ranges to filter chart values

This section demonstrates how to use drop down lists combined with an Excel defined Table and a chart. This allows you to select which values to show on the chart. If you own Excel 2010 or a later version I highly recommend using slicers instead.

The first drop down list lets you choose which column to show on the chart based on the selected column header, the second drop down list allows you to choose a row to show on the chart based on values from an Excel defined Table.

What you will learn in this section

• Use drop down lists to filter values shown on a chart.
• Extract specific columns or rows from an Excel defined Table using a formula.
• Create a named range containing a formula that returns specific columns or rows.
• Extract columns and rows from an Excel defined Table based on drop down lists.
• Show specific values on a chart using an Excel defined Table as a data source based on selected drop down values.

How to use this worksheet

The following animated image shows you a sheet where you can select column (Region) or a row (Month) and the chart updates correspondingly. I am only using named ranges and a table to create the functionality.

The great thing with this dynamic chart is that you can easily add more rows or columns to the Excel defined table, you don't need to update the formulas every time you add or remove records.

Example,

1. Select cell F14.
2. Press Tab key on your keyboard.
3. Selected cell is now B15 which is the first cell of the new record. This creates a new row in the Excel defined Table.
4. Type values in the empty cells.

You can also simply select cell B15 and type a value, the Excel defined Table gros as soon as you press Enter.

The table automatically expands and the drop down lists and chart are instantly refreshed with the new row or column values.

How I made this worksheet

This worksheet contains a few named ranges containing formulas, an Excel define Table that contains the source data, a chart and two drop down lists that let the user filter values on the chart.

How to convert data to an Excel defined Table

1. Select any cell in your data set.
2. Press CTRL + T to open the Table dialog box.
   
3. Press with left mouse button on OK button.

Named ranges

I created five named ranges that allows me to use Excel defined Tables in drop down lists, you can also use the INDIRECT function to accomplish the same thing.

Here are the steps to add a named range:

1. Go to tab "Formulas" on the ribbon.
2. Press with left mouse button on "Name Manager" button to open the "Name Manager" dialog box.
3. Press with mouse on "New..." button to create a new named range.
4. Type a name based on the names displayed below.
5. Copy/Paste the corresponding formulas to the "Refers to:" field.
6. Press with left mouse button on OK button.
7. Press with left mouse button on "Close" button.

Month - Formula:

Named range "Month" is used in drop down list in cell C17, instructions below on how to create drop down lists and edit chart settings.

Region - Formula:

Named range "Region" is used in drop down list in cell C16.

Chart - Formula:

Named range "Chart" is used as Series values in the chart.

ChartCat - Formula:

Named range "ChartCat" is used as category values in the chart.

Series - Formula:

Named range "Series" is used as series name in the chart.

Explaining "ChartCat " Formula

The ChartCat formula extracts the category values based on which drop down list is being used.

Step 1 - Return headers or column values?

The IF function checks if cell C16 is not empty, if TRUE then return column Month/Region values, if FALSE then return headers except the first one.

IF(Sheet1!$C$16<>"", INDEX(Table1[Month/Region], 0, 0), OFFSET(Table1[#Headers], 0, 1, , COUNTA(Table1[#Headers])-1))

becomes

IF("East"<>"", INDEX(Table1[Month/Region], 0, 0), OFFSET(Table1[#Headers], 0, 1, , COUNTA(Table1[#Headers])-1))

and returns

IF(TRUE, INDEX(Table1[Month/Region], 0, 0), OFFSET(Table1[#Headers], 0, 1, , COUNTA(Table1[#Headers])-1))

Step 2 - Return an array of values

The INDEX function allows you to get a value from a cell range, however, if you use a 0 (zero) as a row or column argument then you will get the entire row or column as an array. If you use 0's (zeros) in both row and column arguments you will get the entire cell range.

IF(TRUE, INDEX(Table1[Month/Region], 0, 0), OFFSET(Table1[#Headers], 0, 1, , COUNTA(Table1[#Headers])-1))

becomes

IF(TRUE, {"January"; "February"; ... ; "December"}, OFFSET(Table1[#Headers], 0, 1, , COUNTA(Table1[#Headers])-1))

and returns

{"January"; "February"; ... ; "December"}.

Step 3 - Return headers

If the logical expression returns FALSE the following will happen.

IF(Sheet1!$C$16<>"", INDEX(Table1[Month/Region], 0, 0), OFFSET(Table1[#Headers], 0, 1, , COUNTA(Table1[#Headers])-1))

The COUNTA function counts the number of headers in the Excel defined Table, we need a value that is 1 less than the number of headers.

The OFFSET function extracts the headers except the first one.

returns {"North","East","South","West"}.

Step  - Check if both cell C16 and C17 are empty

If both drop down lists are empty then return nothing.

IF((Sheet1!$C$16="")*(Sheet1!$C$17=""), 0, IF(Sheet1!$C$16<>"", INDEX(Table1[Month/Region], 0, 0), OFFSET(Table1[#Headers], 0, 1, , COUNTA(Table1[#Headers])-1)))

returns 0 (zero).

Create two drop down lists

1. Select cell C16
2. Go to tab "Data"
3. Press with left mouse button on "Data Validation" button
4. Select List
   
5. Type =Region in Source:
6. Press with left mouse button on OK
7. Select cell C17
8. Go to tab "Data"
9. Press with left mouse button on "Data Validation" button
10. Select List
11. Type =Month in source
12. Press with left mouse button on OK

Setting up the chart

1. Create a bar chart.
2. Press with right mouse button on on chart.
3. Press with left mouse button on "Select Data...".
4. Press with left mouse button on "Add" button.
   
5. Type =Sheet1!Series in Series name:
6. Type =Sheet1!Chart in Series values:
   
7. Press with left mouse button on Ok
8. Press with left mouse button on "Edit" button
   
9. Type =Sheet1!ChartCat in Axis label range:
   
10. Press with left mouse button on ok
11. Press with left mouse button on ok

8. Heat map using pictures

I made a heat map calendar a few months ago and it inspired me to write this article. The heat map calendar changes the background color of each cell unlike the technique used here where I change the brightness of each picture based on numbers from a data set.

The drop-down list below allows you to change statistics, it contains three items: Population, Area and Population/area.

The map changes automatically when you change the value in the drop-down list, the workbook contains Event code that is run when the value in the drop-down list changes.

Instructions

Step 1 - Find and copy a map

1. I found a map at the Wikimedia commons website.
2. Press with right mouse button on on the image you want to use and press with left mouse button on "Copy".
3. Paste it to your favorite image editing software.

Step 2 - Copy each state/country/territory

This step, step 3 and 4 are somewhat tedious if you have many images to copy.

1. Select the "Magic Wand" tool. I made this in paint.net.
2. Tolerance : 0%.
3. Press with mouse on a state.
4. Copy the selected area (Ctrl + c).

Step 3 - Paste to Excel

1.  Start Excel.
2. Paste the picture to a worksheet.
3. Select the picture and name it using the name box located almost at the top left corner, see image below.

The name is important, we will use it in a macro to change the brightness.

Step 4 - Select a transparent color

1. Double press with left mouse button on picture. This takes you to the "Format" tab on the ribbon.
   
   
2. Press with left mouse button on "Color" button.
3. Press with left mouse button on "Set Transparent Color".
4. Press with left mouse button on a "white" area on the picture.

Repeat step 2 - 4 and copy all states to Excel.

Step 5 - Organize pictures

Press and hold on a state and then drag to the location you want. You will se something like the image above when all states have been copied to the worksheet.

Step 6 - Create a group (optional)

Make a group of all pictures and you will be able to resize the entire map in one single step.

1. Select all pictures. Tip! Use the "Selection pane" on the "Format" tab.
   
   
2. Press with right mouse button on on a picture.
3. Press with left mouse button on Group | Group.
4. Size handles appear when you select the group.
   
5. Press and hold on one of the handles then drag with the mouse to resize the group.

Step 7 - Create an Excel defined Table

I geted data from wikipedia and created the Excel Table above. An Excel Table has a few great features, one of them is that they use structured references which makes them really easy to reference and work with.

Here is how to convert a data set to an Excel Table:

1. Press with mouse on any cell in your data set.
2. Press shortcut keys CTRL + T and a dialog box appears.
3. Press with left mouse button on checkbox if you data set contains header names.
4. Press with left mouse button on OK.

The data set changes it appearance and is now formatted differently, this tells you that you now have an Excel Table.

Table column Column1 contains a single value. The brightness property is ranging from 0 to 1. 0 - darkest and 1 brightest. It is hard to see pictures with a brightness ranging from 0.5 to 1 so I am using values from 0 to 0.5.

We need to create values between 0 (zero) and 0.5  based on the data in one state compared to the maximum and minimum value in the same column, the formulas are shown below.

Formula in table column %:

Excel Tables uses structured references which are different than regular cell references. [@Population] is a reference to a value in column Population on the same row as the formula. [Population] is a reference to all values in column Population.

The formula above calculates a value between 0 and 0.5 based on the ratio of a given state's population compared to the largest population of the values in column "Population". The formulas below calculate a ratio using the values in column "Area sq km" and "Pop / km".

Formula in table column %2:

Formula in table column %3:

What we now have done is indexing values, I have made a post about indexing data before:
Compare your stock portfolio with S&P500 in excel

Step 8 - Create a drop down list

A drop-down list allows you to control which values the user can choose from, they are easy to create:

1. Select cell D26.
2. Go to tab "Data" on the ribbon.
3. Press with left mouse button on "Data Validation" button.
4. Select "List".
5. Source: United states of America,Population, Area, Population / area.
6. Press with left mouse button on OK.

There is also a hidden formula in cell F26. The following formula calculates a value that the event code below uses.

These steps demonstrate how to hide the value in cell F26.

1. Select cell F26.
2. Press CTRL + 1 to open the "Format Cells" dialog box.
3. Select Custom, see image below.
4. Type ;;;
5. Press OK button.

The value in cell F26 is still there but you can't see it. The worksheet looks cleaner with this setup.

Step 9 - Change picture brightness for each picture

1. Copy code below. (CTRL + c)
2. Press with right mouse button on on sheet name, see image above.
3. Press with left mouse button on "View Code".
4. Paste code below to code window. (CTRL + v)
   
5. Exit VB Editor and return to Excel.

'Event code is rund when a cell is changed in worksheet "Map".
Private Sub Worksheet_Change(ByVal Target As Range)

'Dimension variables and declare data types
Dim r As Single, c As Integer, Arr() As Variant

'Check if cell's address is D26
If Target.Address = "$D$26" Then

    'Populate variable Arr with values from Excel Table "Table1" in worksheet "Table"
    Arr = Worksheets("Table").Range("Table1").Value

    'Iterate through values in a given column based on the hidden value in cell F26
    For r = LBound(Arr, 1) To UBound(Arr, 1)

        '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.Shapes.Range(Arr(r, 1)).PictureFormat

            'Set brightness from value in array variable based on row and column number
            .Brightness = Arr(r, Range("F26"))
        End With
    Next r
End If
End Sub

If you like maps, check out this post: Use a map in an Excel chart

Recommended links

• Create a Map chart in Excel
• How to make a killer map using Excel in under 5 minutes with PowerMap plugin 

9. Change chart axis range programmatically

This article demonstrates a macro that changes y-axis range programmatically, this can be useful if you are working with stock charts and you are not happy with the axis range Excel has chosen for you.

The macro automates this for you, the formulas in cell E2 and E3 extracts the largest and smallest stock price respectively. The macro simply uses those values to change the y-axis range for you, you don't need to change these values manually each time you change the data source (stock ticker).

What you will learn in this article

• Extract the largest and smallest number from a cell range.
• Round a number up and down to the nearest integer.
• Create named range that expands based on size of the data source.
• Change chart axis range programmatically.
• How to use the minimumScale and maximumScale property
• Assign a macro to a chart allowing the user to press with left mouse button on the chart in order to run the macro.

Formula in cell E2:

High is a named range that expands based on the size of the data source, it references the column that contains the highest stock price for a given period.

The MAX function returns the maximum number and the ROUNDUP function rounds the number up to the nearest integer.

Formula in cell E3:

Low is a named range that expands based on the size of the data source, it references the column that contains the lowest stock price for a given period.

The MIN function returns the minimum number and the ROUNDDOWN function rounds that number down to the nearest integer.

How to use named ranges?

1. Press with mouse on tab "Formulas" on the ribbon.
2. Press with mouse on "Name Manager".
   
3. Press with mouse on "New" button.
   
4. Type the name and paste the formula shown below to "Refers to:".
5. Repeat steps with remaining named ranges.

Named range formulas

Close:

Date:

High:

Low:

You can find an explation to these formulas here: Create a dynamic named range

How to use macro?

The macro is rund when you press with left mouse button on the chart, see animated image above. The macro uses the values in cell E2 and E3, the chart axis max and min values are instantly changed.

I also created a dynamic named ranges. You don´t need to adjust chart cell ranges manually anymore when stock data changes.

VBA macro

'Name macro
Sub RefreshChart()

'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.
'The Axis object has one argument, choose between xlCategory, xlSeriesAxis, or xlValue
With Worksheets("Sheet3").ChartObjects("Chart 1").Chart.Axes(xlValue)

    'Change maximum scale for chart 1 on sheet 3 to value in cell E2 on worksheet Sheet3
    .MaximumScale = Worksheets("Sheet3").Range("E2")

    'Change minimum scale for chart 1 on sheet 3 to value in cell E3 on worksheet Sheet3
    .MinimumScale = Worksheets("Sheet3").Range("E3")
End With
End Sub

How to assign macro to the chart

1. Press with right mouse button on on a chart.
   
2. Press with left mouse button on on "Assign Macro...".
3. Press with mouse on the macro you want to link to the chart.
4. Press with left mouse button on OK button.

User defined function

Edit: Yahoo Finance has changed, the following UDF is not working anymore.

You can copy historical stock data from the Yahoo Finance website.

I posted a userdefined function a year ago that automatically imported stock prices from yahoo finance. The workbook demonstrated in this blog posts imports stock prices and refreshes a stock chart. The problem is how excel calculates maximum and minimum axis values.

I made some small modifications to the user defined function.

• Stock data are now sorted from smallest to largest by date
• Prices are numbers

Function YahooStockQuotes(Fyear As String, Fmonth As String, Fday As String _
, Tyear As String, Tmonth As String, Tday As String, interval As String _
, ticker As String)

Dim url As String, http As Object
Dim csv As String, temp() As Variant, txt As String
Dim r As Integer, a As Single, b As String, c As Single, irows As Single
Dim temp1() As Variant
ReDim temp(6, 0)
ReDim temp1(6, 0)

url = "https://ichart.finance.yahoo.com/table.csv?s=" & ticker & _
"&d=" & Tmonth - 1 & "&e=" & Tday & "&f=" & Tyear _
& "&d=d&a=" & Fmonth - 1 & "&b=" & Fday & "&c=" & Fyear & "&g=" & interval & "&ignore=.csv"
On Error Resume Next
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", url, False
http.Send
csv = http.responseText
r = 0
txt = ""
For a = 1 To Len(csv)
  b = Mid(csv, a, 1)
  If b = "," Then
    If UBound(temp, 2) = 0 Then
        temp(r, UBound(temp, 2)) = txt
    ElseIf r = 0 Then
        temp(r, UBound(temp, 2)) = txt
    Else
        temp(r, UBound(temp, 2)) = Val(txt)
    End If
    r = r + 1
    txt = ""
  ElseIf b = Chr(10) Then
    If UBound(temp, 2) = 0 Then
        temp(r, UBound(temp, 2)) = txt
    ElseIf r = 0 Then
        temp(r, UBound(temp, 2)) = txt
    Else
        temp(r, UBound(temp, 2)) = Val(txt)
    End If
    ReDim Preserve temp(UBound(temp, 1), UBound(temp, 2) + 1)
    txt = ""
    r = 0
  Else
    txt = txt & b
  End If
Next a

For c = LBound(temp, 1) To UBound(temp, 1)
    temp1(c, UBound(temp1, 2)) = temp(c, 0)
Next c
ReDim Preserve temp(UBound(temp, 1), UBound(temp, 2) - 1)
ReDim Preserve temp1(UBound(temp1, 1), UBound(temp1, 2) + 1)
For a = UBound(temp, 2) To LBound(temp, 2) Step -1
    For c = LBound(temp, 1) To UBound(temp, 1)
        temp1(c, UBound(temp1, 2)) = temp(c, a)
    Next c
    ReDim Preserve temp1(UBound(temp1, 1), UBound(temp1, 2) + 1)
Next a

irows = Range(Application.Caller.Address).Rows.Count
For a = UBound(temp1, 2) - 1 To irows
    For c = 0 To 6
        temp1(c, a) = ""
    Next c
    ReDim Preserve temp1(UBound(temp1, 1), UBound(temp1, 2) + 1)
Next a

YahooStockQuotes = Application.Transpose(temp1)

Set http = Nothing

End Function

Sheet 2 - Import stock prices

Array formula in cell range C1:I150

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 (117)