On opening a file, the add-in now checks is there is any existing link with a Sparklines.xla or Sparklines.xlam in a cell on the sheet.
If any Sparklines link is found, it is automatically updated and the local add-in is used instead.
The add-in is available here. Let me know if it works !
Using the stock quote symbol for sectors labels would make it more readable, but it shows that a lot of things can be achieved with this UDF and a few Vlookup().
I did some research on the net and mainly used Jonathan Hodgson's post on codeproject.com, dating from june 2004, that describes the Squarified treemap technique.
The Squarified treemap algorithm is explained in an academic paper related to Human Computer Interaction (HCI) by Ben Shneiderman of the University of Maryland.
You can find here his excellent explanation that I followed to write this User Defined Function for Excel.
A great Treemap summary post on Juice Analytics' blog also helped me to provide the right features.
First of all, remember that your data must be sorted with highest values at the top of the column (I know, this could easily be integrated in the UDF... sometime maybe).
The syntax of the UDF is :
TREEMAP(DataRange, DestinationRange, HeightPixel, WidthPixel, ColorRange, ColorScale, LabelRange, TooltipRange, HighlightPosition).
Only DataRange and DestinationRange are compulsory parameters. Others are optionals.
Lets describe each parameter :
- DataRange : Range of data containing the values used for the Size of the sectors. Must be 1 column by X rows.
As clearly explained on Juice Analytics blog : "Size of the boxes should be a quantity measure.
The measures should sum up along the hierarchical structure of the data.
The sum of all the elements in one branch need to sum to the value of the branch as a whole.
Therefore, you can’t use ratios or dates or any other measure you wouldn’t use in a pie chart."
- DestinationRange : Range of cells that will contain the Treemap or 1 single cell...
If DestinationRange is a single cell, we have 2 cases with the following parameters :
- HeightPixel or WidthPixel are not specified or both = 0 :
- HeightPixel AND WidthPixel are specified (integer number, lets say between 100 and 500):
This is especially important when comparing 2 Treemaps and you want to have exact proportionnality of their respective total surface (lets say representing my stock portfolio's value before and after the crisis... just a painful example).
- ColorRange : contains the data (number or text !) used to define Color of the individual sectors. If this parameter is left blank, all sectors will be grey... clear and simple (Hi Robert !)
Again Juice Analytics : "Color of the boxes is best suited to a measure of performance or change such as growth over time, average conversion rate, or customer satisfaction."
Color can also be used to identify categories.
ColorRange must have the same size as DataRange : One color per sector, so logically organize your data in identically sized columns.
- ColorScale : No flexibility here. This has to be a range of 2 columns by Y rows, like for the Heatmap() UDF.
The first columns contains intervals or categories, in line with the values of the ColorRange area.
In front of each interval value, the 2nd column contains the RBG color code that will fill the sectors.
Color codes are available here and from Cynthia Brewer's ColorBrewer.
The color scale must be organized so that the lowest value is at the top, like in the screenshot from the previous post.
- LabelRange : Staightforward, range of cells (same size as DataRange again), containing the text for labels to be applyed to each sector.
I tried to maximize the size of the text inside the sector, like for tag-clouds, it emphasises the importance of big sectors in the treemap.
Labels will be displayed in black or in white, always maximizing the contrast against the background sector color.
- ToolTipRange : same as LabeRange, but this text will be displayed in a floating tooltip when hovering the mouse over the treemap.
Defenitely usefull to provide more detailed information than the label could.
- HighlightPosition : Integer number. Basically, it's the rank in the data range of the sector that you want to highlight. A red frame will be displayed around the corresponding sector... long to explain, easy to understand if you have come so far (Hopefuly!)
Regarding the VBA code.
1) it works reasonnably well. I tested it with 1 500 sectors with no trouble and great resolution in the smallest sectors
2) it is damn slow due to numerous iterations and loops, that could certainly be avoided by using collection or dictionnary objects instead of arrays.
3) Unlike C#, Silverlight or Java implementations, this code is not recursive... might be a reason why it's soooo slow.
Actually, this UDF is not a Treemap, rather a Branchmap... A treemap would have a fractal structure, with each sector including at least one embeded treemap, like real tree-trunk with branches and leaves.
I you are interested to go further with Treemap, specific Treemaping softwares are listed here and more information available from the University of Maryland.
Last and not least.
The permantly-beta version of the add-in is available here (for XL2007 only)
An example file is available here.
Now you can create an interactive version of this nice Treemap from the NY Times... with Excel !
Felices fiestas !
Here goes the screenshot, explanations will follow !
Works more or less like the Heatmap() UDF and has contextual tooltip when hovering on a sector.
Ok, not a Sparkline anymore, but a usefull feature for dashboards.
I will come back after some debugging.
The parameters used in this UDf are :
- Points : range of values to be displayed - Mandatory
- LegendRange : range of cells containing the text to be displayed next to each segment - Optional
- Minimum : Value - Specifies the minimum value of the chart - Optional
- Maximum : Value - Specifies the maximum value of the chart - Optional
- ColorPositive : RGB color code for positive segments - Optional
- ColorNegative : RGB color code for negative segments - Optional
- Chart 1 from Net sales to Operating Profit
- Chart 2 from Operating Profit to Profit before tax
- Chart 3 from Profit before tax to Net profit
Successive charts share a commun value and identical scale (min and max values)
The chart is automatically drawn horizontally or vertically, and aligned with source values, according to the data layout.
I am however still not satisfied with the way the legend is handled.
The beta version of the add-in for XL2007 can be found here.
(again, if the file extension happens to be *.xlam.zip, just remove the ".zip")
It is "built" around the central ScatterChart(), and flanked by StripeChart() to show the X and Y distribution of both series of Red + Black points, including their respective averages (black line).
X and Y scales are ScaleXY(), including a line that highlights the median of the red + black series.
The median is shown also with the grey area in the lower left corner :
It took me 3 or 4 minutes to create this combination of 9 SFE charts... completely meaningless, but good looking at least !
- Business management thinker Joseph M. Juran suggested the principle and named it after Italian economist Vilfredo Pareto, who observed in 1906 that 80% of the land in Italy was owned by 20% of the population.
- Pareto principle serves as a baseline for ABC-analysis, widely used in logistics and procurement for the purpose of optimizing stock of goods, as well as costs of keeping.
- Pareto charts commonly display an ordered column chart and a line chart showing the cumulated percentage on top.
I decided to combine both charts in one :
- Each segment represent the individual weight of each item within the total.
- Segments are stacked one after the other, simulating the cumulated percentage line.
In ABC Analysis for example :
- "A class" inventory will typically contain items that account for 80% of total value, or 20% of total items.
- "B class" inventory will have around 15% of total value, or 30% of total items.
- "C class" inventory will account for the remaining 5%, or 50% of total items.
Unlike the previous version of Pareto(), this release requires only one function in the upper cell (left cell if data is organized in one row x several columns) to display the entire chart.
Pareto() UDF uses the following parameters :
- Points : Range of values - Mandatory
- ColorRange : Range of colors values. Allows one color per segment - Optional
- Target : Percentage - Red line - Optional
- Target2 : Percentage - Green line - Optional
- HighlightPosition : Position - integer value - Optional
- Legend : True / False - Display cumulated % - Optional
- ColorRange is a range containing RGB color codes (provided in the manual), so each individual segment can have a specific color. ColorRange and Points must have identical number of values.
This can be usefull to group items by family or, as show in the picture, to better visualize the A, B and C product groups.
Sparklines for Excel 2007 v. 3.6 beta available here. (Box.net might rename the file into "sparkline.xlam.zip"... so please remove the ".zip")
Demo file for Pareto available here.
PS : To provide a scale to Pareto(), use ScaleXY() ...
- ScaleLine() : used next to LineChart() and Barchart(), with the ticks aligned under each "Position".
- ScaleXY() : Used under or above ScatterChart(), with the tick aligned with given "Values".
- Scaleline() starts with 1 , that is with the first item, date, or in general Position. Ticks are aligned with the dots of LineChart() and the center of the columns of BarChart().
- ScaleXY() starts with 0, as any xy chart, where X and Y axis cross at 0. Ticks are aligned with the points of ScatterChart().
A beta version of the add-in for XL2007 is available HERE if you want to give it a try and give me some feedback.
A sample file illustrating this post is available here.
Note that Pareto() and Cascade() now have different behavior (Still have to update the manual...).
Be sure to backup the previous version of SFE before testing.
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.
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.
- 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.
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...
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.
Not that I am especially interested in bloodsuckers, but you might have a look at what Microsoft just patented today under a
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)
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/
There are however two problematic cases with LineChart.
"Graphics works better than a table because, when give a visual shape to data, I'm creating a tool useful to accelerate (and sometimes replace) the processes the brain follow to reach its target"
"My personal method when I create infographic projects is very easy. It has three stages: research, planning and final art"
"The complexer the information gets, more basic should be the style to represent it"
- Icon for Horizon Chart
- Option to extrapolate gaps in linechart
- Syntax of Spreadchart has been modified (all parameters are numbers. Not text)
- For XL 2007, ribbon aspect has changed for more direct access to the "Refresh" icon
- Better handling of volatile functions (no more infinite loop while redrawing... but still one too much!)
- Activate / deactivate sparklines formulas
- Update the path of sparklines.xla (or xlam) for those who decided to install the add-in in special directory (by default : c:\program files\Sparklines)
- Delete Sparklines formulas ( but leave the charts in place)
- Delete ALL shapes on the worksheet (including controls and Excel charts)
- Delete ONLY Sparklines (without deleting controls and Excel charts)
- Refresh Sparklines
- to many to be listed here, but according to my tests, the add-in is much more stable now, and faster at refreshing the shapes.... you'll let me know
- Several files packaged in one :
- The stand-alone version (*.xls)
- The color codes table in pdf
- The sparklines manual (permanent draft...)
- Possibility to choose the install directory
- An optional shortcut can be created in your Desktop menu (or on the desktop)
- Creation of an Uninstall file
- Easy upgrade of new versions
- Users will have to declare manually the add-in in Excel (not a big deal though...)
- Sparkline chooser interface
- Standalone files creator (as requested by Robert here)
- Scatter plot chart
- Updated manual (embeded in the Sparklines Chooser)
- Recoding of Pareto and Cascade charts
The default install directory is : c:\program Files\sparklines
The file is available HERE and HERE on SourceForge.
As Gustavo informed me, in order to use the add-in, you first have to add this install directory in Excel's "Trusted Zone":
- Click the Microsoft Office Button , and then click Excel Options.
- Click Trust Center, click Trust Center Settings, and then click Trusted Locations.
- If you want to create a trusted location that is not local to your computer, select the Allow trusted locations on my network (not recommended) check box.
- Click Add new location.
Important We recommended that you don't make your entire Documents or My Documents folder a trusted location. Doing so creates a larger target for a hacker to potentially exploit and increases your security risk. Create a subfolder within Documents or My Documents, and make only that folder a trusted location.
- In the Path box, type the name of the folder that you want to use as a trusted location, or click Browse to locate the folder.
- If you want to include subfolders as trusted locations, select the Subfolders of this location are also trusted check box.
- In the Description box, type what you want to describe the purpose of the trusted location.
- Click OK.
Lets take a warehouse where goods are stored on shelves or pallets.
- The short circuit, on which all fast movers products can be found. Ideally those products should cover 80% of your orders.
- The long circuits, on which all products, including slow movers can be picked
Fast movers (red) on the short circuit. (blue) Slow movers (green) on the long circuit (orange).
In this Excel file, I have :
a 2 columns table, with the picking place ID and the picking frequency.
The simple layout of the warehouse in 2 versions :
- One with cells containing a VLookup formula in order to retrieve the picking frequency of each location
- One with the picking locations ID
Finally, 2 cells containing the Heatmaps formulas.
The transparent heatmap is layed over the layout showing the picking IDs.
With the data set I used, you can clearly see that the product on location A20 shouldn't be there.
I implemented this is a fruit and veg. warehouse some time ago.
Productivity increase was spectacular and workers happy to reduce their mileage ...
Now... why bother with heatmaps when this can be done with simple conditionnal formatting ?
Well, you might want to regroup products according to another criteria, that requires more than 3 colors.
File available here and on Sourceforge. (Sparklines add-in required ... again)
It consits in 12 line charts, one per month, displayed in a narrow cell.
All cells obviously share the same vertical scale.
The red and blue points represent the minimum and maximum over 12 years.
The file is available for download here, and requieres the add-in to be installed.
My favorite posts so far :
- Take care of customers : provides a superb, minimalist template of dynamic customer satisfaction dashboard.
- Lithuania at a glance : another brilliant example of clean and complete layout.
The best of all ? Almost NO VBA involved...
A site to bookmark ... NOW !
Keep the posts coming Robert.
It works pretty well, although I would prefer to have separate reports for sales and margin, and not split in 2 consecutive lines like here.
The file is available here.
Note : this file does not contain any code. The Sparklines add-in must be installed before using it.
I modified the code on my machine in order to identify bars that have in this case more than 100% variance (white triangle in the screenshot).
His last post gives an example of how Palo, Excel and Sparklines for Excel can be used to create dashboards.
Not only Palo is a powerfull open-source database and Olap server, it also provides a great add-in, that allows users to easily embed pivot-table in Excel... very powerfull indeed.
The last step is to integrate sparklines for quick and efficient visualization.
Here is his article : “Pimp my spreadsheet” with Palo, and a screenshot of a dashboard using Sparklines for Excel.
Juice Analytics gave up trying with Excel, but Robert said "Yes we can" et voilà !
The NY Times chart.
Robert's replica with Sparklines for XL heatMap function.
The file is available for download here.
Note : Robert added one line in the code to make the circles transparent, so the text behind could be seen.
Robert is also contribuiting to Chandoo's excellent blog Pointy Haired Dilbert.