Category Archives: Charts

Various charts and charting techniques

Slow plot in Excel

It takes a click or two to create a chart in Excel, but it was not always that easy.

Once upon a time, in the days of Commodore 64, Sinclair ZX81 and Spectrum, Amstrad/Schneider 464/6128, and BBC Micro, charts had to be programmed using PLOT(x,y) or a direct POKE to the screen-mapped memory. Sounds tedious, but it was a definite improvement over the IBM 1130 with a drum plotter which had to be fed punch cards; drum and pen movement were controlled separately in incremental mode. Well, I am the same age as Fortran IV.

The machines were slow, so one could always see the chart being plotted dot by dot. That is what I miss these days—for some charts (scatter, polar) it is useful to slow down plotting so one can actually see the line being plotted.

Here is a small VBA program to animate a scatter chart by dynamically changing (increasing) the chart’s data source range—row by row every time increment (tick).

In a VBA module:

Option Explicit
Private Declare Function GetTickCount Lib "kernel32" () As Long

Public Sub AnimateChart(SheetName As String, _
DataRangeName As String, _
Optional ChartIndex As Long = 1, _
Optional Tick As Long = 50)

‘ Comments : Animates chart by dynamically changing the
‘ data source range

‘ Parameters: SheetName – name of the sheet the chart is on.
‘ DataRangeName – named range for the data,
‘ includes header.
‘ ChartIndex – 1 for the first chart on the sheet,
‘ 2 for the second etc..
‘ Tick – update interval;
‘ suggest: 50, 100, 150

‘ Modified : 2008-06-29

On Error GoTo PROC_ERR
Dim Chrt As Chart
Dim rngSrc As Range
Dim rngRef As Range
Dim NumberOfPoints As Long
Dim CurrentPoint As Long
Dim OldPoint As Long
Dim Strt As Long
Dim rw As Long
Set Chrt = Worksheets(SheetName).ChartObjects(ChartIndex).Chart
Set rngRef = ThisWorkbook.Names(DataRangeName).RefersToRange
NumberOfPoints = rngRef.Rows.Count – 1
CurrentPoint = 0 ‘current animation point
‘clear the chart (show first point only)
rw = 2
OldPoint = 1 ‘first point is in the second row of the range
Set rngSrc = rngRef.Resize(rw)
Chrt.SetSourceData Source:=rngSrc, PlotBy:=xlColumns
DoEvents ‘update screen
Strt = GetTickCount ‘remember start tick count
CurrentPoint = CLng((GetTickCount – Strt) / Tick)
If CurrentPoint > OldPoint Then
rw = rw + 1
Set rngSrc = rngRef.Resize(rw)
Chrt.SetSourceData Source:=rngSrc, PlotBy:=xlColumns
OldPoint = CurrentPoint
End If
Loop While (CurrentPoint <= NumberOfPoints)
Set rngSrc = Nothing
Set rngRef = Nothing
Set Chrt = Nothing
Exit Sub
MsgBox Err.Description
End Sub

In a Worksheet module, the button’s click event—cbtAnimate_Click()—prepares data for a specific chart and calls AnimateChart(). Chart data should be defined as a named range which is passed via the DataRangeName parameter.

Private Sub cbtAnimate_Click()
cbtAnimate.Caption = "busy…"
AnimateChart SheetName:=Me.Name, _
DataRangeName:="Lissajous_Data", _
ChartIndex:=1, _
cbtAnimate.Caption = "Animate"
End Sub

You can download the workbook with examples for a Lissajous curve, a logarithmic spiral, and a rose curve.

Misleading charts

Fancy, but quite misleading graphs. Can you actually see that “A” and “G” are the same? Which one do you think is the largest number? Would you say that “D” is greater than “C”? They call it the funnel chart.



Ok, now the same data represented by its first cousin, the pyramid chart.



Can you actually believe this is the same data set? Who came up with this nonsense?

Here are the actual numbers and the plain bar chart.

Label Value
A 600
B 800
C 850
D 875
E 760
F 598
G 600

Something to think about. Moral of the story: beware the fancy graphs, especially when dealing with finances. Apparently the funnel and pyramid charts are frequently used in sales projections and similar. My suggestion, use the bar chart or pie chart instead.

And finally, a good reference on charts; The Visual Display of Quantitative Information by Edward R. Tufte. Among good practices you will also find sections on graphical integrity, distortion in data graphics, lie factor, and the data-ink ratio.

No bubble trouble

If you cannot see the bubble graph from the previous post check the following:

1. Make sure your firewall does not block

2. If the firewall is OK, then you may need to:

The Flash Player problem frequently happens on installations where IE6 was upgraded to IE7.
On some sites it would work fine, on some not.

Quality bubbles

Love this graph. Here is some real data from a line for a period of 20 weeks. The date in the bottom-right corner is always Monday and data is for the week of the date.




  • Sigma = standard deviation.
  • Cpk = How many 3-sigma intervals is the mean away from the nearest limit?
  • Cp = How many 6-sigma intervals fit into the specification window?
  • Quality = Number of good parts divided by total parts measured; for the specific measurement.