New Cloud folder for downloads

A new cloud folder is available for downloads. Here.

you will find the following sub-folders :

• Add-in files for Excel 2007 to 2013 - 32 bits
• Add-in files for Excel 2013 - 64 bits
• Add-in files for Excel 2011 - Apple Mac
• Manual and color codes
• Sample files

If your company security policy prevents you from connecting to Dropbox, please contact me : sparklines.excelgmailcom

Remember that you can connect to SfE's forum and share tips / questions with other users.
Register here.

Enjoy

SfE for Office 2011 Mac and Office 2013 PC

Quick post here, as most of the activity is now on SfE Forum (see previous post for link). I just tested SfE on Office 2011 for Mac.

Works reasonably well but needs some modifications (shadow is added by default and other display weird things). No Ribbon however and I also have to find a way to make a proper menu with all formulas.

 I got my hands on Office 2013 x64(sleek interface ;-) I like it so far) and got SfE working there too... good news ! There is a good chance that you can create an SfE *.xls in Office 2003 (almost previous century software guys !)and still use it in Office 2013.

 See SfE running running on both environments :

SfE Forum

SfE users now have the possibility to share files and tips, give constructive feedback or suggest improvements on a forum I just set  up. Follow the link.

I am quite curious about SfE's community, Who are you ? How do you use SfE ? Let's share ...

BeanPlot() and StepChart() in 64 bits

Just updated the 64bits version of SfE (both add-in and xltm template file)

Download links here for the xlam add-in and here for the xltm.

BeanPlots available

A new version of SfE is online for download, on time for Xmas (Excel 2207 and 2010 only so far).
Few changes except that BeanPlot() is available in the Distribution menu.

This function requires only one mandatory parameter : Serie1, which is the range of data you want to visualize.
The 2nd parameter, Bins, represents the number of "slices" you want to divide the data into.

For large datasets, Bins=10 works nice. As you will play with it, you'll find out that the calculation time increases dramatically with a higher number of bins.

The list of parameters is as follow :

    Mandatory Serie1 As Variant
    Optional Bins As Integer
    Optional ScaleStart As Variant
    Optional ScaleEnd As Variant
    Optional Vertical As Boolean
    Optional HighlightValue As Variant
    Optional ShowMedian As Boolean
    Optional ColorScheme1 As Long
    Optional Serie2 As Variant
    Optional ColorScheme2 As Long 

So far the 2 last parameters related to a second serie of data are not used.

Downloads :

• Excel 2007/2010 Add-in (.xlam) 
• Excel 2007/2010 Template (.xlmt)
• Example file (.xlsx)

Happy Xmas !

Additional chart in SfE : BeanPlot()

Currently working on a new type of chart : Beanplot()

It´s an enhanced version of Boxplot.
Boxplots gives a quick view about the structure of a univariate dataset, but remains a black box when it comes to provide clear information about the detailed distribution of this data.

The BeanPlot shows a mirrored estimated density of the distribution, specially usefull to identify multimodal distribution as shown in the picture above (1st serie of data).

This paper provides a good description of Beanplots principles : http://goo.gl/svK8F

And here is the first screenshot of SfE's Beanplot() UDF in progress.... stay tuned.

64 bits version updated

The Office 2010-64 bits version is now at the same stage as the 32 bits version, including GanttChart.

Here is the change-log :


'24/06/2010 - Corrected FunctionWizard behavior for some functions (wrong counting of letters in formula) in SpraklinesSheetActions
'30/06/2010 - Modified Boxplot so Points can be entered not only as a Range but also as an Array {1;2;3}
'24/07/2010 - Added control for negative values in BulletChart & RevBulletchart
'24/07/2010 - Corrected call for DrawTarget in BulletChart & RevBulletchart
'13/08/2010 - Added in StackedChart : If Points.Cells(i).Value = 0 Then TextLabel = ""
'13/08/2010 - Added Targets to Barchart, possibility to add target value for each bar value
'17/09/2010 - Added VerticalFlip parameter to LineChart
'14/10/2010 - Changed FontSize algorithm in Treemap, to maintain visual hierachy (does not work OK)
'21/10/2010 - Piechart : If Val(Application.Version) >= 12, in order to handle properly piecharts in XL2010 (Version = 14)
'04/01/2011 - Assign Ctrl+Alt+F5 & Ctrl+Alt+F6 to display / hide Sparklines menu
'04/01/2011 - as default in Xl2007 template file, Sparklines menu will be hidden
'05/01/2011 - Modified text size algorithm in Treemap()
'07/01/2011 - Changed scaleXY to scalexy (low caps) as it erased existing formula
'08/05/2011 - Changed Barchart Target behavior (Thicker and smaller tick)
'08/05/2011 - Changed refresh routine --> call procedure DrawCharts
'21/09/2011 - Modified Heatmat so it works w/ merged cells
'26/09/2011 - Added label, tooltip and hyperlink to Heatmap
'27/10/2011 - Created GanttChart

The file is available here : http://www.box.net/shared/3rm3nbkiqn

GanttChart() available

I have uploaded the last version of the add-in that includes the GanttChart() function.
A new icon is included in the "Composition" group

At this time GanttChart() is only available for Excel 2007+2010 in the usual sparklines.xlam file.

I also uploaded a sample file so you can play with and report some bugs ... ;-)

Link to the add-in : http://www.box.net/shared/yqf4u9evib
Link to the sample file : http://www.box.net/shared/sxl6zog5k7zha0dhnhxa

Looking forward for some feedback...

Gantt Chart on its way

Just finished to put together a new UDF : GanttChart.

Pretty straight forward, this function displays tooltips when hovering over a task.

I am finishing some testing before uploading the new version SfE.

Here are the parameters for this function, only the first 4 are mandatory

ProjectStart As Date

ProjectEnd As Date

TaskStart As Date

TaskEnd As Date

TaskColor As String
TaskHyperlink As String
TaskTooltip As String
TaskCompletion As Double
CompletionLabel As Boolean
Today As Date
TodayColor As String
Deadline1 As Date
Deadline1Color As String
Deadline2 As Date
Deadline2Color As String
Deadline3 As Date
Deadline3Color As String
HitDate As Date
HitSymbol As Integer
HitColor As String

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 :

pdf printout : http://www.box.net/shared/d0uogsi9rb
xlsx file : http://www.box.net/shared/aqtcsqrcpq

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 charts	14 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 types	Excellent, whole range of markers like hi/low lines, max/min points			Limited to some basic additional data indicators, but they work well
Ease of deployment	Have to distribute macros with file, some VBA crashes, mostly during creation. Win today, as no built-in option			No extra package, but Excel 2010 needed. Win in 2-3 years when 2010 is ‘standard’.
Ease of use	More complex, for  example looking up meaning of formula variable, color chart			Easier, native to the product, so formatting is quick
Code Customization and Stability	VBA macro based (non-locked) so customization possible (and legal)			Not VBA based – more stable, but no customization
Grouping	Can 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 editing	Charts are shapes created on the basis of formula in the cell			Effectively separate charts, edited with own section on ribbon
Future additions	Constant addition of new chart types/upgrades to existing			Maybe some fixes, but will likely have to wait until Excel 20XX for new chart types
Cost	Free, 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.