Chart Events in Microsoft Excel

Chart Events

When you use a computer, you continuously interact with objects and programs, through events. An event is anything that happens, such as moving the mouse, clicking on objects, changing data, or activating windows. Programs can raise events of their own as well. A program can respond to events using event procedures. For example, if you click a scroll button, Excel responds by scrolling the window down.

As a programmer, you can write event procedures that will respond to (or trap) events by users. These event procedures can trap events in the worksheet, in the workbook, at the application level, or in a chart. In this article, I introduce you to chart event procedures in Microsoft Excel, which you can use for charts on a chart sheet and embedded charts.

Why Use Chart Events?

Chart events can make it much easier for people to use your programs, and add interactivity to your charts. Perhaps you need users to select a point in a series. If you create an event procedure that detects a click on the chart, users won’t have to type in a series name and a point number.

After the click the program can perform one or more useful tasks:

  • Display a customized chart tip;
  • Extract information from the chart and place it in a worksheet;
  • Activate another chart or worksheet;
  • Identify points to be included in a subsequent analysis.

Although Excel can accept data in a variety of formats and arrangements, you can do certain things with the data to ensure your charts are easy to build and maintain. “Good data” has these characteristics:

  1. Data for each series is arranged in Columns (preferable to Rows)
  2. First column is used for Categories (X axis labels)
  3. First row is used for Series Names
  4. Top left cell is blank
  5. Data below series names is numeric (right-adjusted with no alignment specified)
  6. There are no blank rows or blank columns within the data range
  7. There is a buffer row or column between the table and any other non-blank cells.

Well-Formed Chart Data

In this case, I use sample data similar to the above above to demonstrate chart event procedures.

Chart Sheet Events

The first set of examples is for charts that reside on their own sheets, not embedded in worksheets. These examples are contained in the downloadable workbook ChartSheetEvents.xlsm.

Create A Chart Sheet

Select the entire range you want included in your chart, or select a single cell within this range. If you followed guidelines 6 and 7 about Good Data, Excel selects the range of continuous data that includes the selected cell.

Select the data range, or a single cell within the data range. Press the F11 function key, to quickly create a chart sheet from the data range range, using the default chart type.

Chart Sheet

The Chart Code Module

Right-click the chart sheet tab, and select View Code from the pop-up menu. This opens the Visual Basic Editor (VBE).

Chart Sheet Tab Context Menu

Within the VBE, a code module appears with the caption ChartSheetEvents.xlsm – Chart1 (code):

Chart Code Module

This is a special kind of code module, which is associated with the chart. Code contained in this module runs when events occur on the chart.

The code module should contain only the line

Option Explicit

This line means any variable used in the module must be explicitly declared prior to use. This approach is slightly more work, but it assures that no errant variable will be accidentally defined by a typographical error.

If the module does not include the Option Explicit line, type it in, then go to the VBE’s Tools menu, select Options…, and on the Editor tab, check the Require Variable Declarations box. Every module you create after setting this option will automatically contain the Option Explicit line.

While you’re in the dialog, you may as well uncheck Auto Syntax Check, which pops up a dialog whenever you enter code with a syntax error. Since the code with an error is also colored red, there’s no need for an annoying dialog which you have to cancel to continue.

Chart Code Module with Event Procedure Stub

Chart Event Procedure

At the top of the code module, you see two dropdowns. One contains the items (General) and Chart, the other contains (Declarations). While you can type your entire event procedure from scratch, it’s easier to use these dropdowns when creating procedures.

Select Chart from the left hand dropdown. The following stub of an event procedure appears in the code module:

Chart Code Module with Event Procedure Stub

Private Sub Chart_Activate()
 
End Sub

This procedure runs whenever the chart is activated. Between these lines, type whatever code you want to be executed when the chart is activated. For example:

Private Sub Chart_Activate()
    MsgBox "Welcome to my Chart!"
End Sub

Now when you activate the chart, the following message box appears:

Welcome Message Box

This example is demonstrated using the chart named Welcome in the companion workbook. This is just an example. Remove the message box code before it makes you insane.

More Chart Events

Chart_Activate is good for illustration of chart events, but other events can be much more useful. The chart events available to you are listed below. These can be accessed from the right hand dropdown in the chart code module when Chart is selected in the left hand dropdown.

  • Activate
  • BeforeDoubleClick
  • BeforeRightClick
  • Calculate
  • Deactivate
  • DragOver
  • DragPlot
  • MouseDown
  • MouseMove
  • MouseUp
  • Resize
  • Select
  • SeriesChange

Next you can use an event to tell which chart element has been selected. Choose Chart from the left hand dropdown of the code module, then choose Select from the right hand dropdown, to produce the following procedure stub:

Chart Code Module with Event Procedure Stub

Private Sub Chart_Select(ByVal ElementID As Long, _
        ByVal Arg1 As Long, ByVal Arg2 As Long)
 
End Sub

Note that I’ve inserted the line continuation character (an underscore) in the first line to wrap the line at a convenient place.

The Chart_Select event fires whenever a chart element has been selected. Three arguments are passed to the procedure: ElementID, which identifies the selected chart element, and Arg1 and Arg2, which provide additional information. For example, if ElementID identifies that a series has been selected, Arg1 identifies which series has been selected, and Arg2 identifies the point within the series which has been selected, or Arg1 = -1 to indicate that the entire series is selected. If Arg1 or Arg2 are not relevant to the selected ElementID, they are assigned the value zero.

When you select Chart from the left hand dropdown, the Chart_Activate procedure shell is created in the code module (Activate is the default event for a chart). You can ignore it, delete it, or use it for another purpose.

This table lists the ElementID values, and the meanings of their associated Arg1 and Arg2 parameters.

Chart Element List

No need to memorize the details, since the VBE Object Browser (VBE View menu > Object Browser) knows everything.

Object Browser showing Chart Element List

Now you can add a line to the procedure to identify which chart element has been selected, and show the values of its associated arguments:

Private Sub Chart_Select(ByVal ElementID As Long, _
        ByVal Arg1 As Long, ByVal Arg2 As Long)
 
    MsgBox "Element: " & ElementID & vbCrLf & "  Arg 1: " & Arg1 _
        & vbCrLf & "  Arg 2: " & Arg2
 
End Sub

To test your event procedure, select the chart’s title. Click OK in the message box, and then test your event procedure with other elements, such as a series, and a point in a series.

The following three message boxes appear when selecting the Chart Title (ElementID = 4), Series 1 (ElementID = 3, Arg1 = 1, and Arg2 = -1), and Series 1 Point 4 (ElementID = 3, Arg1 = 1, Arg2 = 4). To select an individual point, you must single click twice: once to select the entire series, then again to select the point.

Object Browser showing Chart Element List

This example is demonstrated using the chart named Select in the companion workbook.

These message boxes aren’t very informative – text descriptions would be more useful. Replace the Chart_Select event code with the following more extensive procedure. It converts the values to text, and creates a more informative message:

Private Sub Chart_Select(ByVal ElementID As Long, _
        ByVal Arg1 As Long, ByVal Arg2 As Long)
 
    Dim sElement As String
    Dim sArg As String
 
    Select Case ElementID
        Case xlChartArea
            sElement = "Chart Area"
        Case xlChartTitle
            sElement = "Chart Title"
        Case xlPlotArea
            sElement = "Plot Area"
        Case xlLegend
            sElement = "Legend"
        Case xlFloor
            sElement = "Floor"
        Case xlWalls
            sElement = "Walls"
        Case xlCorners
            sElement = "Corners"
        Case xlDataTable
            sElement = "Data Table"
        Case xlSeries
            sElement = "Series " & Arg1
            If Arg2 > 0 Then sArg = "Point " & Arg2
        Case xlDataLabel
            sElement = "Data Label"
            sArg = "Series " & Arg1
            If Arg2 > 0 Then sArg = sArg & ", Point " & Arg2
        Case xlTrendline
            sElement = "Trendline"
            sArg = "Series " & Arg1 & ", Trendline " & Arg2
        Case xlErrorBars
            sElement = "Error Bars"
            sArg = "Series " & Arg1
        Case xlXErrorBars
            sElement = "X Error Bars"
            sArg = "Series " & Arg1
        Case xlYErrorBars
            sElement = "Y Error Bars"
            sArg = "Series " & Arg1
        Case xlLegendEntry
            sElement = "Legend Entry"
            sArg = "Series " & Arg1
        Case xlLegendKey
            sElement = "Legend Key"
            sArg = "Series " & Arg1
        Case xlAxis
            sElement = IIf(Arg1 = 1, "Primary ", "Secondary ")
            sElement = sElement & IIf(Arg2 = 1, "Category ", "Value ")
            sElement = sElement & "Axis"
        Case xlMajorGridlines
            sElement = IIf(Arg1 = 1, "Primary ", "Secondary ")
            sElement = sElement & IIf(Arg2 = 1, "Category ", "Value ")
            sElement = sElement & "Major Gridlines"
        Case xlMinorGridlines
            sElement = IIf(Arg1 = 1, "Primary ", "Secondary ")
            sElement = sElement & IIf(Arg2 = 1, "Category ", "Value ")
            sElement = sElement & "Minor Gridlines"
        Case xlAxisTitle
            sElement = IIf(Arg1 = 1, "Primary ", "Secondary ")
            sElement = sElement & IIf(Arg2 = 1, "Category ", "Value ")
            sElement = sElement & "Axis Title"
        Case xlDisplayUnitLabel
            sElement = IIf(Arg1 = 1, "Primary ", "Secondary ")
            sElement = sElement & IIf(Arg2 = 1, "Category ", "Value ")
            sElement = sElement & "Axis Display Unit Label"
        Case xlUpBars
            sElement = "Up Bars"
            sArg = "Group Index " & Arg1
        Case xlDownBars
            sElement = "Down Bars"
            sArg = "Group Index " & Arg1
        Case xlSeriesLines
            sElement = "Series Lines"
            sArg = "Group Index " & Arg1
        Case xlHiLoLines
            sElement = "High-Low Lines"
            sArg = "Group Index " & Arg1
        Case xlDropLines
            sElement = "Drop Lines"
            sArg = "Group Index " & Arg1
        Case xlRadarAxisLabels
            sElement = "Radar Axis Labels"
            sArg = "Group Index " & Arg1
        Case xlShape
            sElement = "Shape"
            sArg = "Shape Number " & Arg1
        Case xlNothing
            sElement = "Nothing"
    End Select
 
    MsgBox sElement & IIf(Len(sArg) > 0, vbCrLf & sArg, "")
End Sub

Test your new code, by selecting elements in the chart. Here are the revised message boxes, made more informative by analyzing the ElementID and other arguments:

Object Browser showing Chart Element List

This example is demonstrated using the chart named Nicely Select in the companion workbook.

This is a more useful procedure, because it tells us in human words what has been selected. On the other hand, it is less useful than it could be, because it pops up a message box every time a new chart element is selected, forcing the user to clear the message box before proceeding.

Identify the Point Clicked By the User

Another useful event procedure tells you which point was clicked on, and display its values. Chart_Select is not the best event to trap to detect clicking on a point, because two Chart_Select events must occur when a point is selected: the series is selected by the first click, and the point by the second. The MouseUp event is a better event to trap.

To create a new event procedure, choose Chart in the left dropdown and MouseUp in the right, to create this shell:

Private Sub Chart_MouseUp(ByVal Button As Long, ByVal Shift As Long, _
        ByVal x As Long, ByVal y As Long)
 
End Sub

Four arguments are passed to this procedure.

Button tells which mouse button was released: xlNoButton, xlPrimaryButton (usually the left button), xlSecondaryButton (right button), or xlMiddleButton.

Shift tells which keys are depressed when the mouse button was released. The value of Shift can be one or a sum of several of the following values:

Shift Key Codes

For example, if Shift = 2, the CTRL key was depressed when the mouse button was clicked; if Shift = 5, both the SHIFT and ALT keys were depressed.

The coordinates of the cursor, x and y, are in chart object client coordinate units. It’s not necessary to be concerned about these, since you just pass them along to other functions to get more useful information.

Test the following event procedure, which uses Chart_MouseUp to determine where the mouse click occurred. The procedure:

  • traps the release of the button,
  • determines where on the chart the click occurred (x, y),
  • passes these coordinates to the GetChartElement function to determine what chart element is located at these coordinates,
  • determines whether a data point (or data label) is located at the mouse click,
  • extracts the X and Y values for the data point, if the click occurred at a data point or data label,
  • displays this information in a message box.
Private Sub Chart_MouseUp(ByVal Button As Long, ByVal Shift As Long, _
        ByVal x As Long, ByVal y As Long)
 
    Dim ElementID As Long, Arg1 As Long, Arg2 As Long
    Dim myX As Variant, myY As Double
 
    With ActiveChart
        ' Pass x & y, return ElementID and Args
        .GetChartElement x, y, ElementID, Arg1, Arg2
 
        ' Did we click over a point or data label?
        If ElementID = xlSeries Or ElementID = xlDataLabel Then
            If Arg2 > 0 Then
                ' Extract x value from array of x values
                myX = WorksheetFunction.Index _
                    (.SeriesCollection(Arg1).XValues, Arg2)
                ' Extract y value from array of y values
                myY = WorksheetFunction.Index _
                    (.SeriesCollection(Arg1).Values, Arg2)
 
                ' Display message box with point information
                MsgBox "Series " & Arg1 & vbCrLf _
                    & """" & .SeriesCollection(Arg1).Name & """" & vbCrLf _
                    & "Point " & Arg2 & vbCrLf _
                    & "X = " & myX & vbCrLf _
                    & "Y = " & myY
            End If
        End If
    End With
End Sub

Here is an informative dialog that results from clicking on the 4th point of the first series. Compare to the third dialog from the last example above.

Mouse Up Dialog

This example is demonstrated using the chart named Identify Point in the companion workbook.

You could do many things with the data from this procedure.

  • A message box requires a user response to continue; you could replace the dialog by drawing a textbox with the same information, as a smarter chart tip. Use the MouseMove event instead, to mimic Excel’s normal chart tip behavior.
  • You could place this data into a worksheet range. After clicking on several points, you would have a small table of values from the selected points. These could be used for subsequent analysis.
  • You could identify the clicked point, then jump to another appropriate chart. This approach has the effect of drilling down deeper into the data. See the short example below.

Drill Down to a Related Chart

Suppose the values for the categories A through E in the chart reflected sums of other values. You could have several other chart sheets, Chart A through Chart E, that show detail for the selected categories. An event procedure such as the following produces this kind of drill down effect (to test the procedure, create chart sheets named Chart A, Chart B, etc.):

Private Sub Chart_MouseUp(ByVal Button As Long, ByVal Shift As Long, _
        ByVal x As Long, ByVal y As Long)
 
    Dim ElementID As Long, Arg1 As Long, Arg2 As Long
    Dim myX As Variant
 
    With ActiveChart
        ' Pass x & y, return ElementID and Args
        .GetChartElement x, y, ElementID, Arg1, Arg2
 
        ' Did we click over a point or data label?
        If ElementID = xlSeries Or ElementID = xlDataLabel Then
            If Arg2 > 0 Then
                ' Extract x value from array of x values
                myX = WorksheetFunction.Index _
                    (.SeriesCollection(Arg1).XValues, Arg2)
 
                ' Don't crash if chart doesn't exist
                On Error Resume Next
                ' Activate the appropriate chart
                ThisWorkbook.Charts("Chart " & myX).Select
                On Error GoTo 0
            End If
        End If
    End With
End Sub

To return to the starting chart on another mouse click, you can add the following Chart_MouseUp event procedure to the code modules of the detail charts. For example, add the following procedure to the code module for the Chart A sheet. When you select an element in Chart A, you’ll return to the main chart:

Private Sub Chart_MouseUp(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)

    ' Don't crash if chart doesn't exist
    On Error Resume Next
    ' Return to calling chart
    ThisWorkbook.Charts("Chart Drilldown").Select
    On Error GoTo 0

End Sub

This example is demonstrated using the charts named DrillDown and Chart A through Chart E in the companion workbook.

The VBE Project Explorer

You may have noticed the Project Explorer window of the Visual Basic Editor. By default it is docked in the upper left of the VB window. If the Project Explorer window is not open, from within the VBE, choose VBE View menu > Project Explorer or press Ctrl+R. Here is the Project Explorer window in its floating (undocked) state.

VBE Project Explorer Pane

Every VBA Project that you have open in Excel is listed in the Project Explorer, including open workbooks and installed add-ins. If the project is unprotected, its constituent objects are listed in a tree view structure underneath it. If the folder icon in the toolbar is selected, the objects are grouped. If the folder icon is not selected, the objects are listed in alphabetical order without grouping.

You can see three of the four groups of objects in the screenshot above. All chart sheets and worksheets are listed under the Microsoft Excel Objects group, with the ThisWorkbook object that represents the workbook itself. Regular code modules are listed under the Modules group, where we see the module named MChartEvents in the workbook ChartClassEvents.xlsm. The Class Modules group contains class modules, such as CAppEvent and CChartEvent, which will be constructed later in this article. If a project has any user forms, these will be listed under the Forms group.

Under Microsoft Excel Objects, the sheets and workbook are listed in alphabetical order by their “Code Name”. The Code Name is the name of the object in the VB Editor; we see code names Chart1 through Chart5, Chart5a through Chart5e, Sheet1, and ThisWorkbook are shown above. The Excel name of an object is shown in parentheses after its Code Name. You can change the Code Name by changing the (Name) property in the VBE’s Properties window; if you change the Name property, the name displayed on the sheet tab in Excel will be changed.

To access a sheet’s code module, double click it in the Project Explorer window, or right-click a sheet tab in Excel and choose View Code. To access the workbook’s code module, double-click on the ThisWorkbook object in the Project Explorer window.

New Forms, Modules, and Class Modules are added to a project using the VBE’s Insert menu. A new chart sheet or worksheet object is added whenever a sheet is created within Excel. A new project is added to the Project Explorer’s list when a workbook is created or opened, or an add-in is installed, within Excel.

Embedded Chart Events

The approach I have described so far is effective, but only for a single chart sheet. To apply to multiple chart sheets, the event code has to be copied to each chart sheet’s code module. It does not work at all for embedded charts.

A more flexible and powerful technique is to use a class module to trap chart events. A class module can be used for any chart, whether it’s an embedded chart or chart sheet. And it eliminates the need to place the code in a chart sheet’s code module. Any events in the code module of a chart are available in a chart event class module. A chart event class module is demonstrated in the downloadable workbook ChartClassEvents.xlsm.

To create a new class module, click Alt+F11 to switch to the VB Editor. Make sure the ChartClassEvents.xlsm workbook is selected in the Project Explorer window.

Within the VBE, choose Insert menu > Class Module. A new code module opens, with the caption ChartClassEvents.xlsm – Class1 (Code). If the Properties window is not open, choose View menu > Properties, or press the F4 function key. In the Properties window, change the class name to CChartEvent, following the convention that the name of a class module begins with capital C.

The class module should begin with this line:

Option Explicit

If not, follow the instructions in The Chart Code Module subsection in the Chart Sheet Events section of this article, above.

Next, add this line in the Declarations section of the class module, to declare a Chart type object with event trapping enabled.

Public WithEvents EventChart As Chart

After adding this line, the class module contains the EventChart item in the left hand dropdown, in addition to (General) and Class. When the EventChart item is selected, all of the chart events described in the first half of this article appear in the right hand dropdown.

To create the equivalent chart event procedure as you created in the “More Chart Events” subsection above, select EventChart from the left dropdown and Select from the right dropdown, to produce this procedure shell:

Private Sub EventChart_Select(ByVal ElementID As Long, _
        ByVal Arg1 As Long, ByVal Arg2 As Long)
 
End Sub

The shell of the EventChart_Activate procedure is produced when the EventChart item is selected in the left dropdown. This can be deleted or ignored. Add the Msgbox command within this procedure to identify the element and arguments for the selected chart element. The entire class module code should look like this:

Option Explicit
 
' Declare object of type "Chart" with events
Public WithEvents EventChart As Chart
 
Private Sub EventChart_Select(ByVal ElementID As Long, _
        ByVal Arg1 As Long, ByVal Arg2 As Long)
 
    MsgBox "Element: " & ElementID & vbCrLf & "  Arg 1: " & Arg1 _
        & vbCrLf & "  Arg 2: " & Arg2
 
End Sub

The code is almost ready to trap chart events, but first you need to enable events for the chart.

Enabling Events for the Chart

Make sure the ChartClassEvents.xls workbook is selected in the Project Explorer window. From the VBE Insert menu, select Module. A new code module opens, with the caption ChartClassEvents.xls – Module1 (Code). In the Properties window, change the module name to MChartEvents, following the convention that the name of a regular module begins with capital M.

The module should begin with this line:

Option Explicit

If not, follow the instructions in “The Chart Code Module” section. Now you must declare an instance of the chart events class, by adding this line in the Declarations section of the class module, immediately below the Option Explicit line.

Dim clsChartEvent As New CChartEvent

Next, you create two procedures, Set_This_Chart and Reset_Chart, To activate a chart for events, you assign the active chart to the EventChart property of the instance of the class:

Set clsChartEvent.EventChart = ActiveChart

To deactivate the chart for events, you assign the EventChart property of the instance of the class to Nothing:

Set clsChartEvent.EventChart = Nothing

These two lines are wrapped in the Set_This_Chart and Reset_Chart procedures, with a check within the Set_This_Chart procedure to prevent errors if no chart is selected. The entire module looks like this:

Option Explicit
 
Dim clsChartEvent As New CChartEvent
 
Sub Set_This_Chart()
    ' Skip if no chart is selected to prevent an error
    If Not ActiveChart Is Nothing Then
        ' Enable events for the active chart
        ' Works for chart sheets and embedded charts
        Set clsChartEvent.EventChart = ActiveChart
    End If
End Sub
 
Sub Reset_Chart()
    ' Disable events for chart previously enabled as active chart
    Set clsChartEvent.EventChart = Nothing
End Sub

To test the procedures, select any chart, embedded or chart sheet, and run Set_This_Chart. Now select any chart element, and a message box pops up to tell you the ElementID, Arg1, and Arg2.

Enabling Events for Chart(s)

This approach is fine for a single embedded chart, but it can be broadened to include all charts embedded in the active sheet. Instead of a variable clsChartEvent, you declare an array clsChartEvents() to contain all of the enabled charts, and wrap them within loops so all embedded charts on the active sheet are included.

Adjust the MChartEvents module to look like this:

Option Explicit
 
Dim clsChartEvents() As New CChartEvent
 
Sub Set_All_Charts()
    ' Enable events for all charts embedded on a sheet
    ' Works for embedded charts on a worksheet or chart sheet
    If ActiveSheet.ChartObjects.Count > 0 Then
        ReDim clsChartEvents(1 To ActiveSheet.ChartObjects.Count)
        Dim chtObj As ChartObject
        Dim chtnum As Integer
 
        chtnum = 1
        For Each chtObj In ActiveSheet.ChartObjects
            ' Debug.Print chtObj.Name, chtObj.Parent.Name
            Set clsChartEvents(chtnum).EventChart = chtObj.Chart
            chtnum = chtnum + 1
        Next ' chtObj
    End If
End Sub
 
Sub Reset_All_Charts()
    ' Disable events for all charts previously enabled together
    Dim chtnum As Integer
    On Error Resume Next
    Set clsChartEvent.EventChart = Nothing
    For chtnum = 1 To UBound(clsChartEvents)
        Set clsChartEvents(chtnum).EventChart = Nothing
    Next ' chtnum
End Sub

Activate a sheet with embedded charts and run Set_All_Charts. All embedded charts will now respond to chart events as the single embedded chart did in the previous section. This approach works for any embedded charts on a worksheet or a chart sheet (yes, you can embed chart objects on a chart sheet).

The previous code won’t activate the parent chart sheet, however. You reinstate the clsChartEvent declaration, to be used with the chart sheet, and adjust the Set_All_Charts procedure to activate a chart sheet and then activate the embedded charts.

Modify the module to look like this:

Option Explicit
 
Dim clsChartEvent As New CChartEvent
Dim clsChartEvents() As New CChartEvent
 
Sub Set_All_Charts()
    ' Enable events for active sheet if sheet is a chart sheet
    If TypeName(ActiveSheet) = "Chart" Then
        Set clsChartEvent.EventChart = ActiveSheet
    End If
 
    ' Enable events for all charts embedded on a sheet
    ' Works for embedded charts on a worksheet or chart sheet
    If ActiveSheet.ChartObjects.Count > 0 Then
        ReDim clsChartEvents(1 To ActiveSheet.ChartObjects.Count)
        Dim chtObj As ChartObject
        Dim chtnum As Integer
 
        chtnum = 1
        For Each chtObj In ActiveSheet.ChartObjects
            ' Debug.Print chtObj.Name, chtObj.Parent.Name
            Set clsChartEvents(chtnum).EventChart = chtObj.Chart
            chtnum = chtnum + 1
        Next ' chtObj
    End If
End Sub
 
Sub Reset_All_Charts()
    ' Disable events for all charts previously enabled together
    Dim chtnum As Integer
    On Error Resume Next
    Set clsChartEvent.EventChart = Nothing
    For chtnum = 1 To UBound(clsChartEvents)
        Set clsChartEvents(chtnum).EventChart = Nothing
    Next ' chtnum
End Sub

Using Event Procedures to Enable Chart Events

Event procedures in Microsoft Excel have different levels of influence. In the earlier example, the event procedures in the code module behind a chart sheet only detect events within the chart sheet. Similarly, event procedures on a worksheet’s code module only detect events in that worksheet. Event procedures on a workbook’s code module trap events that occur in all sheets within the workbook. Finally, application-level event procedures watch over all sheets and workbooks in Excel. There is no application code module, however, so you need to create a special class module to handle application events.

Using these events is a more convenient way to activate chart events than running the Set_All_Charts and Reset_All_Charts procedures used above. The following code examples demonstrate how you can use events to set different levels of activation for chart events:

  1. all charts in one worksheet
  2. all charts in one chart sheet
  3. all charts on all sheets in one workbook
  4. all charts on all sheets in all workbooks

You can decide which level of activation you need, based on the purpose of your chart event code. If you are only concerned with a single chart in one worksheet, you can stop with the first example. If you have built an extensive add-in that processes events for any charts, you need a full-blown application events class module. The companion workbook ChartClassEvents.xlsm follows level 4.

1. Worksheet Events to Enable All Charts In One Worksheet

You must trap the Activate and Deactivate events for the desired worksheet, and use these events to run the Set_All_Charts and Reset_All_Charts procedures. Right-click the sheet tab, and choose View Code from the pop up menu to open the VBE. You see a new code module with the caption ChartClassEvents.xls – Sheet1 (Code), assuming this is Sheet1. Choose Worksheet on the left hand dropdown and Activate from the right hand dropdown. Now type the line Set_All_Charts inside the Worksheet_Activate procedure stub. Then choose Deactivate from the right hand dropdown, and type the line Reset_All_Charts inside the Worksheet_Deactivate procedure stub. You see the following in the code module:

Option Explicit
 
Private Sub Worksheet_Activate()
    Set_All_Charts
End Sub
 
Private Sub Worksheet_Deactivate()
    Reset_All_Charts
End Sub

This code turns on chart events for all charts embedded in the worksheet when it is activated, then turns off chart events when the worksheet is deactivated. If your program executes additional code in the Worksheet_Activate and _Deactivate events, include the lines above with the other code, in the order that works best for your application.

2. Chart Sheet Events to Enable All Charts In One Chart Sheet

You need to trap the Activate and Deactivate events for the desired chart sheet, and use these events to run the Set_All_Charts and Reset_All_Charts procedures. To open the VBE, right-click the sheet tab, and choose View Code from the pop up menu. It opens the code module with the caption ChartClassEvents.xls – Chart1 (Code), assuming this is Chart1. Choose Chart on the left dropdown and Activate from the right dropdown, and type the line Set_All_Charts inside the Chart_Activate procedure shell. Then choose Deactivate from the right dropdown, and type the line Reset_All_Charts inside the Chart_Deactivate procedure shell. You see the following in the code module:

Option Explicit
 
Private Sub Chart_Activate()
    Set_All_Charts
End Sub
 
Private Sub Chart_Deactivate()
    Reset_All_Charts
End Sub

This code turns on chart events for the chart sheet itself and for all charts embedded in the chart sheet when the sheet is activated, then turns off chart events when the sheet is deactivated. If your program executes additional code in the Chart_Activate and _Deactivate events, include the lines above with the other code, in the order that works best for your application.

3. Workbook Events to Enable All Charts On All Sheets In One Workbook

Remove the Worksheet_Activate, Worksheet_Deactivate, Chart_Activate, and Chart_Deactivate code that enables chart events for specific worksheets or chart sheets. These procedures are rendered redundant by the following Workbook_SheetActivate and _SheetDeactivate event procedures.

You must trap the Activate and Deactivate events for all sheets (worksheets and chart sheets) in the desired workbook, and use these events to run the Set_All_Charts and Reset_All_Charts procedures. Right-click the small Excel icon to the left of the File menu, and choose View Code from the pop up menu to open the VBE. It opens the code module with the caption ChartEvents.xls – ThisWorkbook (Code). Choose Workbook on the left dropdown, SheetActivate from the right dropdown, and type the line Set_All_Charts inside the Workbook_SheetActivate procedure stub. Then choose SheetDeactivate from the right dropdown, and type the line Reset_All_Charts inside the Workbook_SheetDeactivate procedure stub. You now see the following in the code module:

Option Explicit
 
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Set_All_Charts
End Sub
 
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Reset_All_Charts
End Sub

This code turns on chart events for all charts embedded in the newly activated sheet, and for the sheet itself if it’s a chart sheet, then turns off chart events when the sheet is deactivated. If your program executes additional code in the Workbook_SheetActivate and _SheetDeactivate events, include the lines above with the other code, in the order that works best for your application.

4. Application Events to Enable All Charts on All Sheets In All Workbooks

You must trap the Activate and Deactivate events for all sheets (worksheets and chart sheets) in any workbook, and the Activate and Deactivate events for all workbooks, and use these events to run the Set_All_Charts and Reset_All_Charts procedures. This process is a little more involved than using sheet or workbook events because you must create a class module to detect application events.

To create the Application Event Class Module, switch to the VB Editor to create a new class module. Make sure the ChartClassEvents.xls workbook is selected in the Project Explorer window. In the VBE, choose Insert menu > Class Module. A new code module opens with the caption ChartClassEvents.xls – Class1 (Code). If the Properties window is not open, choose View menu > Properties or press the F4 function key. In the Properties window, change the class name to CAppEvent (following the convention that the name of a class module begins with a capital C). Now declare an Application type object with event trapping enabled, by adding this line in the Declarations section of the class module.

Public WithEvents EventApp As Excel.Application

After adding this line, the class module contains the EventApp item in the left dropdown, in addition to (General) and Class. Select EventApp from the left dropdown, and select in turn SheetActivate, SheetDeactivate, WorkbookActivate, and WorkbookDeactivate from the right dropdown. Insert the line Set_All_Charts in the Activate event procedures, and Reset_All_Charts in the Deactivate event procedures. The CAppEvent class module should now contain this code:

Option Explicit
 
Public WithEvents EventApp As Excel.Application
 
Private Sub EventApp_SheetActivate(ByVal Sh As Object)
    Set_All_Charts
End Sub
 
Private Sub EventApp_SheetDeactivate(ByVal Sh As Object)
    Reset_All_Charts
End Sub
 
Private Sub EventApp_WorkbookActivate(ByVal Wb As Workbook)
    Set_All_Charts
End Sub
 
Private Sub EventApp_WorkbookDeactivate(ByVal Wb As Workbook)
    Reset_All_Charts
End Sub

You must create an instance of the application class before it will respond to events. To activate the application event class, in the MChartEvents module, add a declaration line:

Dim clsAppEvent As New CAppEvent

Insert the following procedures into the MChartEvents module. The first tells the class that it is responding to events in the current application, and enables events in the active sheet’s charts. The second tells the class that it is responding to events in Nothing (no events!), and disables events in the active sheet’s charts.

Sub InitializeAppEvents()
  Set clsAppEvent.EventApp = Application
  Set_All_Charts
End Sub
 
Sub TerminateAppEvents()
  Set clsAppEvent.EventApp = Nothing
  Reset_All_Charts
End Sub

The last step in activating application-level events is to run the InitializeAppEvents procedure. You could use a command bar button or menu command for this, but those approaches both require the user to remember to go click something. Since you now are an expert on events, you can use workbook events to activate the application events.

The application events and chart events class modules likely reside within an add-in or workbook you’ve built to trap events from the entire application. Open the ThisWorkbook code module for your workbook. If your program is a regular workbook, use the dropdowns at the top of the code module to insert Workbook_Open and Workbook_BeforeClose procedures, and enter InitializeAppEvents and TerminateAppEvents where appropriate, as shown below:

Private Sub Workbook_Open()
    InitializeAppEvents
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    TerminateAppEvents
End Sub

If your program is an add-in, use the dropdowns at the top of the code module to insert Workbook_AddinInstall and Workbook_AddinUninstall procedures, and enter InitializeAppEvents and TerminateAppEvents where appropriate, as shown below:

Private Sub Workbook_AddinInstall()
    InitializeAppEvents
End Sub
 
Private Sub Workbook_AddinUninstall()
    TerminateAppEvents
End Sub

When the workbook is opened or the add-in installed, the application event class is instantiated, so it can begin watching for chart events. When the workbook is closed or the add-in uninstalled, the application event class in terminated, and chart events are no longer trapped.

In a perfect world, this would be sufficient to keep the application event procedures working. In our programming environment, however, many things can break the application event hooks. If your program encounters a run time error, for example, you may find yourself without application events, and therefore, without chart events. You should insert the InitializeAppEvents line into other procedures where appropriate. For example, you could place it at the top or bottom of every procedure run from your program’s command bar and menu buttons.

The Final Event

In this article, you found out how useful chart events can be. They can extend the user interface of the programs you write, making it easy for users to identify points for your program to work on. With a little ingenuity, you can create powerful applications. For example, recently, I made a utility that draws a trend line for only part of a plotted series using the first and last points indicated by two mouse clicks. I made another utility that lets the user zoom in and out on the basis of mouse actions. Once you’ve experimented with chart events, you’ll think of many ways to enhance your projects.

Chart Event Articles on the Peltier Tech Blog

There are several examples that make use of chart events on this blog, including:

Chart Event to Highlight a Series

Chart Event Class Module to Highlight a Series

Highlight a Series with a Click or a Mouse Over

Accordion Chart for Jorge

Peltier Tech Chart Utility

Error Bar Ideosyncrasy

After using Excel 2007 for a while, getting used to the idea that everything had changed and we weren’t going back to 2003 ever again, I started fooling around with charts and chart elements. I began to find lots of little changes, most of them actually good ones, even if they were implemented in a funny way (at least until service packs and Excel 2010 cleaned up things).

Anyone who used my first charting utility knows things ain’t always done right the first time, and mine were tiny bits of software. I can’t imagine Microsoft’s task trying to coordinate thousands of developers and a bazillian lines of code.

One of the neat changes I discovered was that error bars were now treated as AutoShape lines, just like any lines in any shapes in Office. This means you had a lot of formatting options available to you, including nice embellishments like arrowheads on the ends of the error bar line segments. Then I forgot about it until a colleague brought it up recently.

To show this nice formatting, let’s look at a simple line chart with simple error bars. For clarity, and to avoid spoiling the story before I’m ready to tell it, I’ll start with positive error bars only.

Simple Line Chart with Simple Error Bars

Select the error bars and click Ctrl+1 (numeral one) to open the Format Error Bars task pane. In the main tab (below the bar chart icon, shown below left) change End Style from Cap to No Cap. Then on the formatting tab (below the paint can icon) check out the Begin and End Arrow Types.

This is the Format Error Bars task pane in Excel 2013; the Format Error Bars dialog in Excel 2007 and 2010 is substantially the same.

Format Error Bar Dialog

The error bar begins at the point, right? And ends at the, uh, end of the error bar, right? At least that’s what seems to make sense. So let’s pick a nice big round ball for the Begin Arrow Type.

Balls at Beginning of Error Bars

And let’s pick a nice big old arrow for the End Arrow Type. I’ve chosen the largest size for both ends of the error bar.

Arrowheads at End of Error Bars

And here’s our line chart with error bars, where the markers of the line chart could be replaced by the balls at the beginning of the error bars, and the arrows point away from the line.

Simple Line Chart with Fancy Ball and Arrowhead Error Bars

This is a very nice embellishment. I’ve used it in several projects already, and shown it to many people.

Anyway, back to the beginning of the story: the message from my colleague was that Excel 2010 and 2013 seem to define the beginnings and ends of the error bars differently.

Well, for positive error bars, Excel 2007, 2010, and 2013 all look the same.

Simple Line Chart with Simple Error Bars

But if we look at negative error bars, only Excel 2013 shows them beginning at the points and ending at the ends of the error bars, pointing downward. Excel 2007 and 2010 show the balls at the ends of the error bars and the arrowheads at the points, pointing upward. That’s bizarre.

Simple Line Chart with Simple Error Bars

Even more bizarre, if the error bars go both ways, only Excel 2013 has symmetric arrows. Excel 2010 shows both sets of arrows pointing upward, the negative ones toward the points, the positive ones away from the points. And Excel 2007 only has one set of arrows, beginning at the end of the negative error bars, ending at the ends of the positive error bars. Though if you had to, you could just pretend the end was the beginning and vice versa. Whatevs.

Simple Line Chart with Simple Error Bars

Excel 2007’s charting infrastructure was finished quickly at the end of the development cycle, so a few glitches weren’t ironed out of the final product. Excel 2010 fixed a lot of inconsistencies in Excel 2007’s charts, but these error bars show that not everything was totally fixed. In Excel 2007 or 2010 you could fake it with a second, hidden plotted series, and format two sets of error bars so they looked right. Excel 2013 has gotten the error bars working nicely, though.

Peltier Tech Chart Utility

Pivot Tables in Microsoft Excel: Links

This introduction has been contributed by Debra Dalgleish, Excel MVP. 
Contextures, Copyright © 2014. All rights reserved.
Debra Dalgleish, Excel MVP since 2001, is the author of three books on pivot tables, published by Apress. She shares Excel tips, tutorials, and videos on her websites, www.contextures.com and www.pivot-table.com.

Web Sites

Peltier Tech Blog – Pivot Table Resources – a listing of posts on this blog, covering many aspects of pivot tables in Microsoft Excel, including:

Debra Dalgleish’s Excel Tutorials and Videos - which includes these pages of Pivot Table-related topics:

Microsoft – Overview of PivotTable and PivotChart reports
Good introduction to Pivot Tables.

Microsoft – 25 Easy PivotTable Reports
Sample file to download, and on-line instructions

Microsoft – Create a Pivot Table to Analyze Worksheet Data
Basic steps, and links to other articles

Chandoo – Excel Pivot Table Tutorial
Chandoo gives a good overview of pivot tables, with helpful tips and links.

Microsoft Support Articles

Calculated field returns incorrect grand total in Excel (211470)
In a PivotTable, Microsoft Excel may calculate an incorrect grand total for a calculated field.

Pivot Table Uses COUNT Instead of SUM with Blank Cells (110599)
Microsoft Excel automatically uses the SUM function for numeric data and the COUNT function for non-numeric data

How to create a dynamic defined range in an Excel worksheet (830287)
Create a named range that extends to include new information

GETPIVOTDATA formula is automatically created (287736)
When you try to create a simple cell link formula that refers to a cell in the data area of a PivotTable in Microsoft Excel, a GETPIVOTDATA formula is automatically created instead.

You cannot create a PivotTable in Excel 2013 when field names in a source range contain similar characters (2756731)
The data model cannot differentiate between the similar characters.

Pivot Table Books

Here are a few books about pivot tables, available from Amazon and elsewhere. I’ve selected these books because I know the authors and I’ve used the books, so I can vouch for their quality. Disclosure: if you purchase one of these books using my link, I’ll get a teeny commission.

Beginning PivotTables in Excel 2007: From Novice to Professional

Microsoft Excel Most Valuable Professional Debra Dalgleish explains what PivotTables are, how you can benefit from using them, how to create them and modify them, and how to use their enhanced features. Using a Pivot Table in Microsoft Excel 2007 is a quick and exciting way to slice and dice a large amount of data. Debra carefully explains the benefits of using Pivot Tables for fast data analysis, provides a step-by-step approach to those new to Pivot Tables, and offers tips and tricks that cannot be found elsewhere.

Excel Pivot Tables Recipe Book: A Problem-Solution Approach

Debra Dalgleish, Microsoft Excel Most Valuable Professional since 2001, and an expert and trainer in Microsoft Excel, brings together a one-stop resource for anyone curious about representing, analyzing, and using their data with PivotTables and PivotCharts. Debra presents tips and techniques in this collection of recipes that can’t be found in Excel’s Help section, while carefully explaining the most confusing features of PivotTables to help you realize their powerful potential.

Excel 2013 Pivot Table Data Crunching (MrExcel Library)

This book will help you leverage all the amazing flexibility and analytical power of Pivot Tables. You will learn how to generate complex pivot reports complete with drill-down capabilities and accompanying charts. Then you’ll discover how to build a comprehensive, dynamic pivot table reporting system for any business task or function. Microsoft Excel Most Valuable Professionals Bill Jelen and Mike Alexander include step-by-step instructions, real-world case studies, plus complete and easy recipes for solving your most common business analysis problems.

Peltier Tech Chart Utility

Using Pivot Tables in Microsoft Excel

This introduction has been contributed by Debra Dalgleish, Excel MVP.
Contextures, Copyright © 2014. All rights reserved.
Debra Dalgleish, Excel MVP since 2001, is the author of three books on pivot tables, published by Apress. She shares Excel tips, tutorials, and videos on her websites, www.contextures.com and www.pivot-table.com.

What is a Pivot Table?

A pivot table creates an interactive summary from many records.

For example, you may have hundreds of invoice entries in a list on your worksheet.

A pivot table can quickly total the invoices by customer, product or date.

Pivot Table Data

Before you create a Pivot Table or Pivot Chart, organize your data in a table that Excel can understand.

  1. Use Headings: As in this example, the first row must have headings. Use a different heading for each column.
  2. Keep It Together: Keep all the data together — don’t include any completely blank rows or columns in the table.
  3. Isolate It: Leave at least one blank row and one blank column between the data table and any other information on the worksheet.
  4. Create a Named Table: To make it easy to maintain your pivot table, and ensure that new data is included when you update, format the source data as a Named Table.

Create a Pivot Table

Many web sites give instructions for creating a pivot table. (See Pivot Table and Pivot Chart Links).

Create a blank pivot table by clicking a command on the Ribbon.

Click another command and Excel will even recommend a few layouts, based on your data.

Excel shows a preview of a few recommended pivot tables.

After the pivot table is built, Excel displays a task pane summarizing the data in the pivot table.

You can easily rearrange the information in the pivot table by checking the boxes and dragging the buttons to a new position.

Pivot Table Links

The following links on the Contextures web site help with some of the common issues and questions about pivot tables:

These are the most popular pivot table articles on the Contextures web site:

More Pivot Table Resources

Peltier Tech Chart Utility

Swimmer Plots in Excel

A reader of the Peltier Tech Blog asked me about Swimmer Plots. The first chart below is taken from “Swimmer Plot: Tell a Graphical Story of Your Time to Response Data Using PROC SGPLOT (pdf)“, by Stacey Phillips, via Swimmer Plot by Sanjay Matange on the Graphically Speaking SAS blog.

The swimmer chart below is an attempt to show the responses of several patients to drug treatments. Each horizontal stripe represent a patient’s history, color coded by the stage of the patient’s disease at the onset of treatment at month zero. Different events are plotted within each patient’s stripe. The term “Swimmer Plot” comes from the resemblance to lanes in a swimming pool, where swimmers (events) must stay in their own lanes.

Swimmer Plot

The paper cited above gives a detailed protocol and related code for constructing this chart in a statistical graphics package that I’ve never used. The Peltier Tech blog reader wondered if there was a way to build this chart in Excel without having to draw shapes on the chart and locating them inexactly with the mouse.

I’m never one to pass up a reasonable challenge in Excel charting, so I decided to give it a try. This is the kind of chart that can probably be adapted to a variety of uses, and it’s a great way to help people learn how to think outside their usual approaches, to push Excel beyond its supposed limits.

The Approach

One’s first thought might be to build a horizontal bar chart, then use XY scatter data series for the various symbols on the bars. The arrows would have to be shapes drawn on the chart. This mixture of bar and XY chart types causes problems when trying to synchronize axes and aligning markers with bars.

On second thought, though, this chart can be built using XY chart series exclusively. The thick horizontal bands can be made from thick-lined error bars. The arrows at the end of certain bands can also be made using error bars. The only shape needed is for the arrow in the legend.

This entire protocol is one of my longest ever, but it will be worth the ride.

Swimmer Plot Data

All of the data in the chart conforms to two axes. The horizontal (X) axis shows months, either the total duration of the patient’s history or the treatment events. The vertical (Y) axis is simply the index of the subjects (patients): subject 1 is the bottom band in the chart and its associated symbols, subject 2 is the second band and symbols, on up to subject 10 at the top of the chart. In this example, the X axis data has been color coded blue and the Y axis data orange.

This first set of data shows the endpoints of the patient histories. Patients 10 and 3 have month data (18.2 and 9.5 months) in the Stage 1 column, patients 9, 5, and 1 in the Stage 2 column, etc. I optically extracted this data from the chart above before I realized it was included in the pdf article. I’ll take responsibility for any transcription errors.

Swimmer Plot Main Data

Each set of markers in the chart need X (month) and Y (subject) data. The ranges below show data for the four sets of markers and the set of arrows that will be added to the chart.

Swimmer Plot Supplemental Data

Building the Chart: The Swimmer Lanes

The first block of data is used to create the bands in the swimmer chart. Excel’s usual arrangement is to have X values in the first column of the data range and one or more columns of Y values to the right. Our data has Y values in the last column, and several columns of X values to the left. So putting this data into the chart will take a few steps.

Select the Disease Stage 1 column of data, and hold down the Ctrl key to select the unlabeled column of subject indices, then insert an XY Scatter chart. Most of the Disease Stage 1 column is blank, so only two points appear.

Swimmer Plot Construction

I’ve already stretched the chart above to its final size, and I’ve scaled the axes using their ultimate scale parameters. The X axis extends to -1 to allow room for the Durable Responder indicators.

The chart below has had the tick marks and labels removed from the vertical (Y) axis. Note that I’m leaving out chart and axis titles and other annotations to avoid distractions.

Swimmer Plot Construction

Now the next three Disease Stage series must be added. You can do this in at least two ways. My favorite is to select and copy the data, using the Ctrl key if needed to select discontiguous regions, then select the chart and use Paste Special from the Home tab. Use the settings on the screen shot below: add data as new series, values in columns, series names in first row, categories in first column.

Paste Special Dialog

Alternatively, right click on the chart and choose Edit Data from the popup menu or click on Edit Data on the Chart Tools > Design tab to open the Select Data Source dialog. Click the Add button, then populate the Edit Series dialog with the ranges containing the data for the new series.

Select Data Dialog

Repeat until all of the Disease Stages are potted on the chart.

Swimmer Plot Construction

I’ve formatted the markers with the desired formatting for the swimmer plot lanes. Basically I picked four colors that were a bit darker than in the original chart from the cited paper, and lightened them by applying 30% transparency.

Shameless Plug

Previously I noted that the Disease Stage data for this chart was listed with several columns of X data and one column of (shared) Y data. This differs from Excel’s assumption that the data consists of one column of (shared) X data and several columns of Y data. Because of this data arrangement, the chart has to be created tediously, one series at a time.

Such tedium isn’t necessary, however. Using my Peltier Tech Chart Utility, I was able to use the Quick XY Charts feature to create the chart with all of these series in one shot.

Peltier Tech Chart Utility - Quick XY Charts

I selected the data, clicked on the Quick Charts button on the Peltier Tech ribbon, selected Series in Columns and the X-X-Y data layout (highlighted above). The result was this XY Scatter chart, which requires much less manipulation to generate our Swimmer Plot.

Quick XY Swimmer Chart

The series need to be formatted: square markers with the colors and transparency described above and no lines. Also the legend should be moved to the right of the chart.

Back to the Lanes

We’ll use thick error bars as the swimmer lanes. Select one series and add error bars using the “+” icon in Excel 2013…

Add Error Bars in Excel 2013

… or using the Chart Tools > Layout tab in Excel 2007 and 2010.

Add Error Bars in Excel 2007-2010

Excel adds horizontal and vertical error bars to an XY series.

Swimmer Plot Construction

Select the vertical error bars, and click the Delete key. Then select the horizontal error bars and press Ctrl+1 (numeral one) to open the format dialog or task pane. This is the Excel 2013 task pane, but the Excel 2007-2010 format dialog is essentially the same.

Format Error Bars

Select the options shown above: Minus direction only, No End Caps, and Percentage using 100%. The chart below shows Minus 100% error bars with End Caps not removed.

Swimmer Plot Construction

Format the error bar lines with the colors used for the markers, and select a thickness for the bars which will be suitable for the chart.

Format Error Bar Lines

Hey look, bars without a bar chart.

Swimmer Plot Construction

Add and format error bars for the rest of the Disease Stage series.

Swimmer Plot Construction

We need to remove the markers from the chart without removing them from the legend. We have to trick Excel into thinking each series in the legend is formatted with markers while each point is formatted without.Click once to select a series, then again to select a marker, then click Ctrl+1 to open the formatting dialog or task pane for the single point, and choose the No Marker option. The marker from the top Stage 1 point has been removed in this chart:

Swimmer Plot Construction

Repeat; all Stage 1 markers are hidden below:

Swimmer Plot Construction

And repeat for all of the markers:

Swimmer Plot Construction

Add the Treatment Events

Several sets of XY markers are now added. You can copy the data,select the chart, and use Paste Special as described above, or you can use the Select Data Source dialog.

Here is the chart with Complete Response Start markers added to the lanes, with the markers formatted as red triangles like those in the example chart.

Swimmer Plot Construction

Now Partial Response Start points have been added.

Swimmer Plot Construction

Finally, Response Episode End has been added.

Swimmer Plot Construction

Add the Continuation Arrows

The continued Response data is displayed not as markers but as arrows, indicating that the activity extends further to the right than the lanes. This is easy to do with an XY series that shows no markers but instead has error bars with arrowheads on the ends of the bars.

First, add the data as a new series with no markers and a heavy black line. The heavy dark line will somewhat resemble the arrows in the legend.

Swimmer Plot Construction

Add error bars (use the same method as for the error bar lanes above).

Swimmer Plot Construction

Select and delete the vertical error bars as before, then format the horizontal error bars as follows: Plus direction only, No End Caps, and a Fixed Value of 1 (e.g., one month along the X axis).

Format Error Bars

Then format the error bar lines as medium thickness black lines with the appropriate arrowhead type and size. The “begin” arrow points toward the markers, the “end” arrow points away.

Format Error Bar Arrows

The result is a set of arrows extending beyond some of the swimming lanes.

Swimmer Plot Construction

Now we need to remove the black lines from the chart without removing them from the legend. In the same way we hid the unwanted markers from the chart but kept them in the legend to denote the lanes, we can select the entire series with one click, then select one individual line segment, then press Ctrl+1 and format this segment as No Line.

Here the first segment has been hidden.

Swimmer Plot Construction

Now the second segment is hidden.

Swimmer Plot Construction

A few clicks later, all traces of the line are gone.

Swimmer Plot Construction

Add Durable Responder Indicators

Finally, the Durable Responder data is added to the chart. The -0.25 X values place the markers just left of the vertical axis and the start of the lanes.

Swimmer Plot Construction

Final Adjustments

A couple small adjustments improve the look of the chart. First, the vertical axis scale of 0 to 11 leaves rather wide margins above and below the data. If the axis scale min and max are changed to 0.25 and 10.75, this margin is slightly reduced.

The -1 horizontal axis minimum is strange, but changing the horizontal axis number format to 0;;0 hides the negative value.

Swimmer Plot Construction

Finally, if the thick black line is not acceptable as a legend marker for the Continued Response arrows, you can use an arrow for the legend entry.

First, use a white line instead of the black line for the series lines, then hide each segment of this line as you hid the black line segments; this places a white line in the legend. If you had formatted the series as No Line, the line in the legend would not be there, and the markers would be squeezed right up against the legend labels, which does not leave room for a centered arrow.

Then insert an arrow from the Insert Shapes gallery on the ribbon. Format it as a heavy line with the matching arrow appearance. Drag it into the desired position in front of the Continued Response label.

Swimmer Plot Construction

Wrap Up

Charts like this swimmer plot, the related Gantt chart, and other charts were difficult to make in Excel 2003 and earlier versions of Excel. You needed to combine stacked bar chart and XY scatter chart data in the same chart. This required special effort to ;ine up the XY markers with the bars, and keep them aligned.

Excel 2007 introduced much more flexibility in formatting of lines, so you can make thick bars with any line segments, such as series connecting lines and error bars. Swimmer plots and Gantt charts are much easier to make and maintain, because markers and bars are easy to keep in place using a single set of axes.

Peltier Tech Chart Utility

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.