Cross-tab charts with SFE

In-cell charts are not meant to be accurate, but to provide a quick overview and by densifying information, to allow the eye to englobe multiple charts. Then, the brain will correlate those small parallel multiples.
Nevertheless, it is key to provide some context or reference, even for small charts.

In SFE, I have always taken care to include reference in the different types of charts :

• Horizontal lines in BarChart and LineChart.
• Normality band in LineChart.
• Horizontal and Vertical lines in (unpublished yet) ScatterChart.

Those references are here to answer the following questions when analysing data  : What is my target ? What is my limit ? Where is the budget level ?... etc

An additionnal reference can provide great help when it comes to read and analyse periodical data : background shading. To identify weekends in a week or every other month in a year for example.

The intensity of the shade must be carefully choosen, in order to be identified by the eye, but remaining neutral, without disturbing the reading of the the lines or columns.

Anyway, SFE now includes the possibility to create periodical vertical grey areas.

For this purpose, I added 3 additional parameters to Barchart and LineChart :

• ShadedStart : At which position the first grey band should start
• ShadedWidth : How many positions on the x axis should be shaded
• TransparentWhidth : How many positions should follow without shading.

Example : show a grey background for saturdays and sundays, starting sunday.

• ShadedStart = -1. With ShadedStart = 0, the chart would have started with a grey zone covering Saturday + Sunday. -1 will have the chart to start with 1 position (sunday) on a grey background.
• ShadedWidth : 2. For Saturday + Sunday
• TransparentWhidth : 5. For the 5 working days of the week

As shown in the example above, the grey zone covers 100% of the cell's height, in order to display a continuous band when LineChart and BarChart are vertically aligned.

Robert Mundigl and Matt Grams provide an excellent example (again!) of Cross-tab chart with standard excel charts here.

PS : You might have noted that LineChart, BarChart and ScaleLine also provide a vertical reference line... that will be described in the next post.

Welcome ScatterChart()

Several users have asked for an additionnal UDF to for XY scatter or lines with uneven X intervals.

This UDF is now ready to be integrated in the next release of Sparklines for Excel.
It offers the following features :

• Display of 2 series of XY data (square + round symbols)
• 1 vertical reference line
• 1 horizontal reference line
• 1 grey "normality" area defined by its X and Y limits
• Possibility to use logarithmic scale on X and/or Y axis
• Possibility to modify the color of each serie (not the symbols however)
• Possibility to zoom in the chart
• Possibility to toggle the display of  lines and symbols

For the ones interested in early testing, here is a template file.
The original code if from Eric Gundersen, with some adjustments.


Feel free to give me your feedback...

SfE : the beginning

Following Jon's comment to my previous post, let's look back at how SfE started.


In December 2006, after a 10 months trip across the Andes, I had a few weeks to kill and decided to learn some VBA programming.


This is how I found 2 posts on the excellent site "Daily Dose of Excel" dealing with in-cell charting.

If you look at the LineChart() code, you will see that the variables names are still the same and the use of the Worksheet_Calculate event introduced by Jon Peltier has not changed either.



At the time I already knew about Edward Tufte's work, and I immediately imagined in-cell bulletcharts and barcharts in grey shades and thin red lines to highlight target or reference values. 
Pareto and Cascade naturally followed as I hade been using them almost daily in my job.
Scales quickly show to be necessary to bring some context around the charts.
Nixnut logically added Boxplot, AreaChart and the brilliant HorizonChart.
Surprisingly, the simple VariChart was not obvious to me, maybe because I was using the Rep() formula hack.


As I knew very little (and still do !) about VBA, I searched and found what I needed to create the add-in, deal with arrays, etc...spending hours browsing through VBA forums.


As I have been travelling extensively during the last year between Paris and Bucharest, I used the flight time and quiet hotel night for programming and endless testing.


SfE started with those 55 lines of code published by Rob van Gelder and improved by Dick Kusleika, Jon Peltier and other contributors, to reach several hundreds lines for 14 different types of charts today (and a couple more in the pipe).


Therefore, from what I know, only Rob van Gelder should be in position to claim the  "invention" of in-cell charting for Excel.


Said this, Bonavista and Bissantz might have started their Sparklines solutions earlier and from different inspiration.

United States Patent Application 20090282325

I have been spending this last year working in Romania, country of the famous Vlad Tepes, better known as Dracula...


Not that I am especially interested in bloodsuckers, but you might have a look at what Microsoft just patented today under a United States Patent Application 20090282325 :


SPARKLINES IN THE GRID (The pdf document is available here).


You will note that only few charts have retained MSFT interest : "The computer-implemented method of claim 6, wherein the visual representation includes one or more of: a line graph; a line graph with value markers; a bar graph; a stacked bar graph; a win/loss graph; and a pie chart. "


Anyway, I really would like to hear what Edward Tufte thinks about the "INVENTION" claimed by  :

• Radakovitz, Samuel Chow (Redmond, WA, US) 
• Buerman, Adam Michael (Bellevue, WA, US) 
• Garg, Anupam (Redmond, WA, US) 
• Androski, Matthew John (Bellevue, WA, US) 
• Becker, Matthew Kevin (Kirkland, WA, US) 
• Ruble, Brian S. (Bellevue, WA, US) 

Sparklines available again on SourceForge

You might have noticed that Sparklines For Excel (SFE) files disappeared from SourceForge and that the blog access was restricted for a week.

Actually, I was wondering if I should leave SFE online or not, as I am the only one supporting this project anyway.

Microsoft are coming up with their own implementation and I bet that sooner or later they will leech on what has been done here and will market it properly (sooo many people amazed by the 3 Mickey Mouse charts they propose) ... so why should I bother ?

Out of 37.000downloads, I can count a handfull of "Thanks, keep up the good job !" not mentionning the few clicks on the coffee cup... so why should I bother ?

Anyway, I decided to put the files back online and re-open the blog, for what it's worth.

The files are available on : https://sourceforge.net/projects/sparklinesforxl/files/

Enjoy !