This blog post demonstrates how to quickly change chart data range. I have created a drop down list (form control) above the chart and selected input range:$E$2:$E$4.

Cell range $E$2:$E$4 contains the table names from sheet 2011, 2010 and 2009. See picture below. When you select a table name in the drop down list, the chart is instantly refreshed. See picture above.

Excel tables expand automatically when new values are added. This makes charts combined with tables easy to work with.

Instructions:

Create a chart

  1. Go to "Insert" tab
  2. Click "Column chart" button
  3. Click "Clustered column" chart button

Create drop down list

  1. Go to "Developer" tab
  2. Click "Insert controls" button
  3. Click combobox (form control)
  4. Create a drop down control on sheet "Chart"

Add vba code to a module

  1.  Press Alt + F11
  2. Insert a module
  3. Copy vba code below (Ctrl + c)
  4. Paste vba code into code module
  5. Return to excel

VBA code

Sub SelectTable()
With ActiveSheet.Shapes(Application.Caller).ControlFormat
    If ActiveSheet.Shapes(Application.Caller).Name = "Drop Down 1" Then
        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. Right click combo box
  2. Click "Assign macro..."
  3. Click "SelectTable"
  4. Click OK!

Populate combo box

  1. Right click combo box
  2. Click "Format control..."
  3. Go to tab "Control"
  4. Click "Input range" button
  5. Select a cell range
  6. Click OK

Download excel 2007 *.xlsm file
change chart data range.xlsm