I found a basic interactive chart on the chitika website and my first thought was if I could do this in excel. So now I would like to share this simple chart with three buttons.

You can select multiple buttons and the chart is updated instantaneously. You can also deselect buttons.

How to create the chart

Create a table

  1. Select data cell range
  2. Go to tab "Insert"
  3. Click "Table" button
  4. Click OK

Create chart

  1. Select your table
  2. Go to tab "Insert"
  3. Click "Columns" button
  4. Click "2D Clustered column"
  5. Right click on chart
  6. Select "Select data"
  7. Click "Switch row/column" button
  8. Click OK

Insert shapes

  1. Go to tab "Insert"
  2. Click "Shapes" button
  3. Click "Rounded Rectangle"
  4. Create three rectangles beneath the chart
  5. Right click 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

Sub Chart()
Dim temp As Variant
Dim Series() As String
Dim i As Single
ReDim Series(0)
Application.ScreenUpdating = False
With ActiveSheet.Shapes(Application.Caller).Fill.ForeColor
    If .Brightness = 0 Then
        .Brightness = -0.150000006
    Else
        .Brightness = 0
    End If
End With
temp = Array("Rounded Rectangle 1", "Rounded Rectangle 2", "Rounded Rectangle 3")
For i = LBound(temp) To UBound(temp)
    With ActiveSheet.Shapes(temp(i))
        If .Fill.ForeColor.Brightness = -0.150000006 Then
            Series(UBound(Series)) = .TextFrame2.TextRange.Characters.Text
            ReDim Preserve Series(UBound(Series) + 1)
        End If
    End With
Next i
If UBound(Series) > 0 Then ReDim Preserve Series(UBound(Series) - 1)
Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter Field:=1
Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter _
    Field:=1, Criteria1:=Series, Operator:=xlFilterValues
Application.ScreenUpdating = True
End Sub

 

  1. Copy vba code
  2. Open vb editor (Alt + F11)
  3. Select your workbook in the project explorer
  4. Click "Insert" on the menu
  5. Click "Module"
  6. Paste to code window

Assign macro

  1. Right click on the first rectangle
  2. Select "Assign macro"
  3. Select "chart" macro
  4. Click OK

Repeat above steps with the remaining rectangles.

Recommended posts:

Interactive Sales Chart using MS Excel [video]

Download excel *.xlsm file

Chart.xlsm

Related posts:

Learn how to create a stock chart in excel

Adjust stock chart axis automatically

Change chart data range using a drop down list (vba)

Use a mouse hovering technique to create an interactive chart

Use a map in an excel chart