World education ranking

Here is my contribution to Chandoo's last little dataviz challenge : How would you Visualize World Education Ranking Data

I have used Stripechart() to provide a global overview of the dispersion of the countries scores (note that the scale starts at Zero)
ScaleXY() provides a common scale and some additional info about ranking and score.
Varichart() in front of a list of countries sorted by score provides both ranking and score comparison, with the little red line showing the OECD average.

PS :
I have not used the parameter "Show Average" in Stripechart() as the supplied OECD average value does not coincide with the calculated average of all countries.

Files :

Microsoft potential fallacy regarding Sparklines ©

After reading this article about one of Microsoft's last advertising (which by the way must be one of the worst ever), I was wondering what was the intention behind the © after "Sparklines".

Does it mean "term copyrighted by E. Tufte" and therefore Microsoft HAS TO mention the © symbol or "newly re-copyrighted by Microsoft (in case nobody pays attention)" and in this case, Microsoft would be playing a very insidious disinformation game here.

What do you think ?

64 bits version available for download

As first feedbacks are rather positives, the 64bits version for Excel 2010 is available for download HERE.

I had doubts about how the Color Chooser tool is behaving (Menu "Sparklines Tools" --> "Color Chooser"), but it seems to be OK.

Keep me posted if any issue.

64 bits version testers wanted

I have done some work on the 64 bits version, tested under Windows 7 / Office 2010 environment.

Drop me a line if you are interested in testing the add-in, as I do not have the proper environment to do so.

The version to be tested is the last 3.9, with BumpChart() included.

Thanks in advance

Update for Excel 2007

Version 3.9.22062010 for Excel 2007 / 2010, both add-in and template file, is available for download under the coffe cup --------------->

Here is what changed since V 3.8

'11/03/2010 - Reactivated Turnmytrappingon in ThisWorkbook module
'11/03/2010 - LineChart : add Tag =8 and 9 for open or close tag
'29/04/2010 - Boxplot includes new parameter "HighlightValue" + "ShowMedian" by Art Steinmetz
'29/04/2010 - StripeChart includes new parameter "HighlightValue" + "ShowMedian" by Art Steinmetz
'06/05/2010 - Pareto if Label = 1 --> show cumulated % if label=2 --> show individual %
'06/05/2010 - Heatmap, if color code left empty, use background color of "color" cells from colorscale range
'12/05/2010 - Add ColorRange to Barchart ---> each bar can have a specific color - Based on Ramesh code
'12/05/2010 - Add possibility to use background color of cell for Barchart. Cell must be empty or non numeric
'14/05/2010 - Reviewed legend display for CascadeChart
'17/05/2010 - Added BumpChart UDF
'18/05/2010 - Corrected PieChart grouping for 0%
'03/06/2010 - Corrected HorizonChart() when values = all negative
'03/06/2010 - Corrected AreaChart() when consecutive Zeros
'17/06/2010 - Added Transparency and DiameterRatio to Piechart
'18/06/2010 - Added ColorRange to Bullet and RevBullet... Rasta colors everywhere !

I am currently working on the manual, to include all those changes... and on the Excel 2003 version.

PS : the "Template" file looks like an empty file... It just contains all the VBA code for SfE to work but does not contain examples of how to use SfE.

I you want to easily share files using SfE formulas in a corporate environment where it is forbidden to install programs on local PCs, the Template file is your solution. Use is as a New blank file, rename it keeping the .xlsm extension and share it as any other XL file.

Still moving...

Here goes a quick copy of SfE log of modifications I have been working on lately.

Just to let you know tht despite of the latest "3 month Home Revolution" (sell the house in Paris, get baby twins, move down to southern France (incl. 2 temporary location) and now desperatly looking for a house... not mentionning tons of diapers), I still find some time, in airplanes to work on SfE.

'11/03/2010 - Reactivated Turnmytrappingon in ThisWorkbook module
'11/03/2010 - LineChart : add Tag =8 and 9 for open or close tag
'29/04/2010 - Boxplot includes new parameter "HighlightValue" + "ShowMedian" by Art Steinmetz
'29/04/2010 - StripeChart includes new parameter "HighlightValue" + "ShowMedian" by Art Steinmetz
'06/05/2010 - Pareto if Label = 1 --> show cumulated % if label=2 --> show individual %
'06/05/2010 - Heatmap, if color code left empty, use background color of "color" cells from colorscale range
'12/05/2010 - Add ColorRange to Barchart ---> each bar can have a specific color - based on code by Ramesh
'12/05/2010 - Add possibility to use background color of cell for Barchart. Cell must be empty or non numeric
'14/05/2010 - Reviewed legend display for CascadeChart
'17/05/2010 - Added BumpChart UDF

... 

BumpChart() is coming

Although I had very little time lately for playing w/ SfE, I managed to create a new little UDF, not to display in-cell charts, but to link cells this time.

Bump Charts have been extensively discussed (here, here and here) and ProcessTrend.com shows very good examples of how they could be used to describe ranking evolutions over time.

This new function has the following parameters :
- InitialPositionsRange - Range of initial positions (Mandatory)
- FinalPositionsRange - Range of final positions (Mandatory)
- NameHighlight- Name you want to highlight (Optional)
- PositionHighlight - Initial position you want to highlight (Optional)
- ColorUp - Color for values which final position is higher than initial (Optional)
- ColorDown - Color for values which final position is lower than initial (Optional)
- ColorHighlight - Color for the highlighted line (Optional)

Here is the final result with default colors and only the 3 first parameters (one UDF for each couple of Start/End data) :








Columns with intermediary position can be hidden for a smoother and more compact view :

Quick update

Quick post to let you know that I updated the following files :
*.xlam Excel 2007/2010 add-in
*.xla Excel 2003/2002 add-in
*.xlsm Template for Excel 2007

Only Treemap() has been changed, to solve issues related to "zero" values and very small values.

Files are available for download in the right-hand column of the blog.

Template for Excel 2007

I just updated the template file for Excel 2007.

Template files as easier to share as they do not require users to install the addin and try to dodge the restrictive corporate policy for software install...

So click on the icon to download the xlsm template file.

PS : I have not done a lot of testing on this one, so drop me a line if any issue.
PPS : Excel 2003 file is on the way but I have to replicate all the debugging done previously on the 2007 version ...

Display series in reverse order

I have been following Ben Garvey's suggestion and added 1 more optional boolean parameter to AreaChart(), BarChart(), LineChart() and HorizonChart().

This parameter is called "MirrorDisplay".

If MirrorDisplay = True or = 1, then the chart will be horizontally flipped, displaying a data serie in reverse order.
So far, only the XL2007/2010 xlam file has been updated and available under the coffee cup.

PS : Thanks Ben for the extensive feedback and debugging !

Sparklines For Excel vs. Excel 2010 Sparklines

Jon Peltier (no need to introduce Jon, I suppose) and Alex Kerin from Data Driven Consulting posted a quick comparison between SfE and Microsoft Excel 2010 "native" Sparklines.

Although my point of view as author os SfE would be completely biased, I posted a quick reply. 
If I find some time, I will try to make a quick survey about SfE's pros & cons, in the meantime, feel free to share your experiences and whishes by commenting here, I will take them into account for the next release of SfE.



Alex benchmark :
Sparklines for Excel
Excel 2010
Types of cell charts14 and growing: Line, Pie, Pareto, Bullet, XY, Scatter, Bar, Column, Stripe…
Three: Line, Column, Win/Loss
Overall look (sparklines)
Some blocking, not noticeable at normal zoom levels

Smoother
Features on individual chart typesExcellent, whole range of markers like hi/low lines, max/min points
Limited to some basic additional data indicators, but they work well
Ease of deploymentHave to distribute macros with file, some VBA crashes, mostly during creation. Win today, as no built-in optionNo extra package, but Excel 2010 needed. Win in 2-3 years when 2010 is ‘standard’.
Ease of useMore complex, for  example looking up meaning of formula variable, color chartEasier, native to the product, so formatting is quick
Code Customization and StabilityVBA macro based (non-locked) so customization possible (and legal)Not VBA based – more stable, but no customization
GroupingCan set max and min so that multiple charts 
scale the same
Adjacent charts are grouped automatically making for easy changes of formatting/scales
Ease of editingCharts are shapes created on the basis of formula in the cellEffectively separate charts, edited with own section on ribbon
Future additionsConstant addition of new chart types/upgrades to existing
Maybe some fixes, but will likely have to wait until Excel 20XX for new chart types
CostFree, but you need Excel of course
Free, at least after you’ve paid any upgrade costs


While there are equal numbers of ticks in both columns, there are clear winners depending on your required functionality and how you intend to share the information. For creating dashboards that benefit from different types of in-cell charts and line markers, SFE is still a clear winner – the range of chart types, the continued development, along with the fact that the sparklines are formula based makes for a powerful tool. Distribution is complicated by the need to load the add-in file, but for dashboards that shouldn’t be a barrier.
On the other hand, for a quick summary of data that is emailed around, the native version in Excel 2010 is clearly simpler. In the near term (until 2012 I would guess), SFE is still really the only option anyway as sparklines created in Excel 2010 simply won’t show on earlier versions.
It’s great that Microsoft has offered sparklines – hopefully end-users will appreciate their value more now they are a native part of the program, and become used to sparklines’ inclusion in their data lives.

My answer :


"Thanks so much for this constructive comparison. Little frenchy VBA amateur vs. almighty MSFT coder team … I am thrilled.
Lets review quickly your comparison table.
Number of charts : XL2010 also offers VariChart() in the conditional formatting menu… in the other hand, the win/loss is nothing but a column chart with 1 ; 0 or -1. This limited number of charts does not reflect MSFT’s Business Intelligence ambitions… at least when it comes to Excel. Let’s assume it’s a start.
Overall look : irrelevant from my point of view. Even w/ Zoom of 400% blocking is not noticeable, and specially not on printed reports.
Features : Even with tiny charts, we need some reference: a line to materialize a threshold or a target. XL2010 sparklines do not provide any reference and more important, do not offer the possibility to add a visual scale, a context … it looks good but you do not know what you are looking at. Clearly not enough to design usable dashboards.
Ease of use : SfE formulas are used like any other Excel formula… I agree however that the “formula wizard” offers limited features when UDFs are a bit complex. I could spend some time on SfE user interface to make the parameters more understandable…
Customization : The code is yours, it’s open-source… be fair, share your ideas and improvements on SfE’s blog, quid pro quo.
Stability : This is from my perspective SfE’s weak point, especially with XL2003. VBA events and many shapes to refresh are a cause of crash. SfE can also be painfully slow, inefficient coding and numerous shapes being the reason. I have been thinking of using .Net instead of VBA… but is it worth the effort with XL2010 at the corner ?
Grouping : True that XL2010 will make it “easier” (just quicker actually), but as a user, I prefer to see the formula and identify clearly the scale I am using. Using named ranges makes this so easy with SfE… your choice.
Ease of editing : Well, this is where I do not fully agree. SfE makes the formula easy to edit, plus you can use any nested formula inside SfE formula. I reckon however that only “advanced” excel users will fully appreciate this. Basic users (the ones that use Sum(a1:a2) and nothing else == > a vast majority) will prefer to tick a few boxes et voila.
Future additions : Except maybe a formula for creating quick Gantt chart, I would say that the toolbox is pretty complete. Improved “User Experience” would be the next priority (userforms, tick boxes, color picker, embedded help…). (well… less crash and more speed also !)
Price/cost : SfE is a great hobby.. priceless to me. Now, if Steve Balmer wants to include SfE features in XL2013… just let me know, we’ll talk $
As a bottom line, I would say that MSFT XL2010 is offering “Sparklines for the masses”, a simple solution that works for most users, but is definitely not enough for users who want to build more elaborated and comprehensive dashboards. But that’s maybe MSFT “SQL Server Reporting Services” job ?
SFE in the other hand, is a tool made by an “end user” with limited programming skills + great help from more advanced programmers, but with a much better understanding of what is required in a “decision making” process and dashboard construction (show a reference or target ; limited use of colors ; simple formulas if only mandatory parameters are used).
I am just frustrated that MSFT did not even consider looking at what SfE (and Bonavista and Bissantz) was offering before implementing their simplistic solution… serioulsy, the guys at MSFT France said “We don’t care about SfE, we have THE SAME in Office 2010″ … “THE SAME”… LOL"


Alex is founder of Data Driven Consulting, an independent consulting company that focuses on obtaining high quality data to facilitate effective business decisions. Data Driven Consulting specializes in a number of areas, including market research, dashboard and data presentation, and authoring of white papers and e-content.

SfE and BeGraphic join forces.

Aldecis is a young and dynamic team based in Paris I have been exchanging ideas with for some time now, not only about Sparklines but information visualization in general.

They have just released their creative Excel add-in : BeGraphic, which allows users to control precisely all parameters of a shape : Color, border, rotation, position etc... with a single cell.



Behind this simple idea, lays endless possibilities to create dynamic diagrams and enhance dashboards, reporting documents, presentations and scorecards with innovative visual data graphics... as easy as with your old Excel charts.


BeGraphic Lite is more an drawing engine than a ready-to-use solution. 
BeGraphic gives the opportunity to handle parameters of any specific picture or shape. But first users must build their custom data visualizations. Then BeGraphic is able to transform any infographics into dynamic charts / animated graphics.

In order to show some quick usages in the 'lite version', two pre-built templates are offered: gauges and choropleth maps (+ 7.000 geographical maps given only  to users who ask  for it and provide their professional email). But all other automatic applications will be provided with the final release of BeGraphic, probably available in summer 2010.


Summarizing, I wouldn say that BeGraphic:
- works inside Excel and PowerPoint
- doesn't need macro (only have to know the right-click or ribbon)
- needs your imagination (to build the initial graphics)



Did I mention that BeGraphic also includes an embeded "Sparklines for Excel" menu...


The free version Begraphic Lite can be downloaded here... enjoy.

Ben's "5K Results Reimagined"

Ben has helped to track some bugs in SfE.

Here is one his Sparklines production : a correlation of race time, gender and age of runners.

Feel free to send me some of your dashboards (screenshots or files), I will post them here as an inspiration for SfE users.

Enola & Victor

We just implemented 2 new types of Sparklines :
Enola() and Victor() were born on 01-02-2010 @ 9pm
I expect a lot of debugging for some time with those 2 UDFs ... ;-)
Sorry guys, but I couldn't resist to post this one.

Cheers

SFE Manual update

I am slowly updating the manual and will release updates function by function before publishing the full set.

  • Full Set (except functions under construction) <--- updated 01-28-2010

SFE @ Chandoo Visualization Challenge #2

If you have not heard about Chandoo's blog "Pointy Haired Dilbert", check it out NOW and dig this "Excel knowledge" mother lode.
In November 2009, Chandoo launched his second visualization challenge. 32 participants submitted their solutions of "Sales Team Dashboard", most of them using Excel.
150 readers voted for the best dashboard, and to my surprise (and delight !), the winner solutions were using Sparklines for Excel...


1st place : Alex Kerin – who writes at Data Driven Consulting.
Alex used BulletChart(), Varichart(), StackedChart(), LineChart() and even Piechart() to create a simple yet comprehensive dashboard. Alex’s entry received 23 votes.



2nd placeCuboo – who writes at Open BI, made this clear and well structured dashboard using MS Excel & Palo. Cuboo already won the previous visualization challenge. Cuboo’s entry received 22 votes.



I hope to have some spare time to participate to the next Challenge and show what can be achieved with SFE ...

Permanent beta links

I just added 2 links to my development files in the sidebar.
for Excel 2007 and  for Excel 2003

Those files are permanent beta versions, so make the necessary backups before using them !
I was also thinking about opening a forum for SFE with FAQ and bug reports... what do you think ?