With each passing day the amount of information contained in a single spreadsheet, slide, document is growing. Thanks to demanding bosses, clients and colleagues, we are now supposed to provide all the relevant information in as much less space as possible.
This is where micro charting or light weight data exploration has become a rage. The idea of shrinking a chart to fit in side a cell has been catching up with corporates and individuals alike.
In this post, we are going to review 7 of the MS Excel Micro-charting Alternatives so that you have a good idea of finding the right micro charting tool for your purpose.
1. Incell excel charts using REPT() spreadsheet function
Incell charting using REPT() spreadsheet is one of the easiest ways to include some data visualization capabilities to your excel tables without sweat. Click here to learn this technique of drawing incell charts.
Advantages:
- Very easy to implement
- No need to install any VBA or Add-ins
- Suitable for simple data visualizations in tables
Disadvantages:
- You can only make variants of bar charts
- Difficult to format, highlight specific points with out tweaking
- Not suitable for corporate environment where you need lots of visualizations on the tables
2. Incell charts using REPT(), cell formatting and conditional formatting
This technique involves using in-cell charts to prepare the micro chart and then using excel features like cell alignment and conditional formatting to provide additional information, thus making the charts rich. Learn more.
Advantages:
- Moderately easy to implement
- No need for VBA or add-ins
- Suitable for visualizing project plans, sales reports etc.
Disadvantages:
- Conditional formatting has limitation of only 3 conditions / formats
- Not suitable for complex visualizations
3. Resizing regular excel charts to fit inside a cell
By resizing the normal excel charts and removing all the chart labels, axis, background etc. we can get a micro chart effect with all the goodness of regular excel charts.
Advantages:
- Since most of us familiar with regular excel charts, this is an easy to implement technique
- All the chart types are available for micro charting, so you can create spark lines, pie charts, stacked bars etc.
- Easy to format, highlight charts
Disadvantages:
- Not all charts scale elegantly
- Needs a lot of formatting to remove all the chart labels etc.
- Not suitable if you have lots of charts to prepare as maintaining that many charts is painful
4. Using custom fonts / ding bats to create micro charts
Since we can insert any character in to a cell using formula, by installing a custom bar chart / pie font in our computer we can create incell graphs in excel with ease. Click here to see example pie chart, line chart.
Advantages:
- Easy to implement
- Reduces lots of chart maintenance / creation work because of the fonts
- Suitable for simple visualizations
Disadvantages:
- Not shareable since other person need to have the font installed before seeing the spreadsheet
- Not for everyone, since installing fonts is often not possible on office computers
- Not suitable for complex visualizations / dashboards
5. Using Spark lines UDF from Daily dose of excel
If you are planning to get simple spark lines on your spreadsheet cells then Daily dose of Excel’s sparklines UDF can be handy for you. This technique takes a set of numeric values as input and draws a line in the output cell based on the input.
Advantages:
- Moderately easy to implement
- Suitable for instant spark lines
- Makes a good addition to your sales report, project plans etc.
Disadvantages:
- You need to install the User Defined VBA Function in order to get this work
- When sharing the work book with others, they need to enable UDFs / VBA to make this work
- Suitable only when you want spark lines
6. Using a free excel micro charting tool like Spark lines for Excel by Fabrice
Spark lines for Excel is an excellent alternative to make your reports / dashboards look truly professional without spending a penny. This is set of VBA UDFs defined to draw micro bar charts, line charts, bullet charts, reverse bullet charts, Pareto charts, Scale-lines, variance charts and cascade charts. The latest version is available for download on sourceforge.
Advantages:
- Totally free with truly world class micro charting in excel options
- Easy to implement if you know how to install UDFs / excel add-ins
- Suitable for enterprise class dashboards, sales reports
Disadvantages:
- Since this is a free / open source version, any implementation issues will have to be solved by you
- Requires installing UDFs on others computer or enabling VBA before you can share this with them
7. Commercial alternatives like Bonavista micro charts
Of course if you are a heavy user of micro charts and you (your company) needs a totally professional solution for your dashboards then you may want to consider one of the commercial alternatives like Bonavista micro charts.
Since they advertise on my site through Google ads, I am not planning to talk about this any further. But if you have any questions, drop a comment. Andreas, who represents both Xlcubed and Bonavista systems is a frequent commenter here and he would be happy to answer your questions.
So, which one should I use?
If you want a simple incell chart, use one of the REPT() based techniques.
and If you want a full fledged micro charts for you reports / dashboards then start with free excel spark lines and then if needed migrate to one of the commercial alternatives.
30 Responses to “Microcharting in Excel – 7 Alternatives Reviewed”
"You can only make variants of bar charts.
Difficult to format, highlight specific points with out tweaking."
Au contraire. In my tutorial
In Cell Charting with Worksheet Formulas, I show how to make dot plots and use different markers and colors for different ranges of values.
P.S. The comment form looks fine now.
Don't forget about resizing regular charts using the camera tool...
Chandoo,
thanks the nice comments about sparklinefor XL...
World class... you are getting me under serious pressure !! 😉
Some comments :
"Totally free" :
I learned a lot from free info like your blog, Jon Peltier's blog or Roberts tips, the sparklines is a way to share back.
"Easy to implement if you know how to install UDFs / excel add-ins" :
You do not need to install anything to use the UDFs. The file requires Excel's Analysis Tool Pak for the =randbetween() formula, not for the UDFs. You will need however to allow the execution of macros.
Disadvantages:
"Since this is a free / open source version, any implementation issues will have to be solved by you" :
I will do my best to answer back to users who have trouble, and I might learn some stuffs in the process.
"Requires installing UDFs on others computer or enabling VBA before you can share this with them." :
NOP ! The file is not an add-in, but just an XL file w/ VBA code. That is all the benefit of UDFs, no special fonts, no add-in to install on distant PC if you want to share a file using them. Just accept the macros when opening the file.
Thanks again for you great blog !! Keep the posts coming.
"Share your knowledge, it's the path to eternity" The Dalaï Lama.
@ Jorge
Damn, I was writing the same thing about the Camera tool, perhaps the easiest way to create sparklines.
check out Excel MVP Fernando Cinquegrani's example here : http://www.prodomosua.eu/zips/sparklines.xls
I thought the camera tool implementation of sparklines was clever when I saw it on Fernando's site. (Fernando does some wonderful stuff.)
But the camera tool is no panacea. 1. You need to copy the range under the chart being shrunken, which may have unintended consequences. 2. More important, the camera tool is a resource pig, and if you use more than about eight of them on a sheet, you're likely to suffer from very slow redraws.
If you keep the charts simple, you don't really need to shrink them. They don't line up so well with the cells, of course. I have a rough plan in mind that I may get to sometime, in which the code draws charts, without all the axes and labels and stuff, just the data. Then it oversizes them, because at least up to 2003, the plot area cannot get quite as large as the chart's container object.
With VBA you can make the charts line up nicely. And using real charts is less complicated than keeping track of all those little drawing objects that make up the kinds of sparklines we were messing with on the Daily Dose site. I assume these are the ones Fabrice used for his inspiration. And Fabrice, don't sell yourself short. We were all hacking away, but you put some ART into it, man.
And don't forget about the new conditional formatting in Excel 2007...
"Conditional formatting has limitation of only 3 conditions / formats" - not anymore
I've used VBA to create a thematic map in Excel using auto shapes. It is not micro-charting, but the same principles can be used. A friend of mine uses auto shapes to create small bar charts in a dynamic report.
Jorge -
I've used autoshapes for a number of charts which had to line up with the cells:
http://peltiertech.com/Excel/Commentary/InCellBarChart.html
http://peltiertech.com/Excel/Commentary/InCellBarChart2.html
and once I built a program to build this large scale heat map from an outline:
http://peltiertech.com/Excel/Commentary/HeatMap.html
Jon, Fabrice, Jorge,
there is another shortfall of the camera tool when using Excel 2007:
It doesn’t work anymore!
If you want to use the camera to display cell ranges or charts located somewhere to the right or below the visible cell range or (as I am doing most of the time) on another worksheet, the camera doesn’t work with Excel 2007. The camera works fine only if the cell range or chart (you are taking the snapshot of) is visible on the screen, i.e. within the displayed cell range. And that is pretty useless, insn't it?
As far as I know this bug has been reported to Microsoft, but I guess fixing the camera tool bug will not have highest priority for Microsoft…
I found the work-around for Excel 2007:
The camera tool is working fine in Excel 2007, if you define a name for the cell range and link the camera object to this name.
But without using a name, it is still a bug. Sorry, Microsoft...
@ all
As Excel's "Analysis Tool Pak" add-in seemed to generate some issues, I posted on my blog a version that does not use it.
Cheers
@Jon, agree... making other charts using REPT() is possible 🙂
@Jorge: Thanks for letting me know about the camera tool, I have never used it before. Will try it.
Btw, I agree that you can use shapes to create micro chart effect. I didnt cover it because of the simpler alternatives available. thanks for sharing it with our readers 🙂
@Fabrice: I had to say "installing UDFs" part because... your workbook with sparklines works fine as long as I create the sparklines in it. But what if I need to create sparklines in my own workbook, in that case I need to save your workbook as an .xla file.. isn't it?
Thanks for removing the need for "analysis tool pack", Your spark lines are easier to use now.
Hi Chandoo,
A new release of Sparklines for Excel is available for download on Sourceforge (http://sourceforge.net/project/showfiles.php?group_id=229215&package_id=277672&release_id=628820)
This new version includes the following changes :
All charts - added possibility to make intermediary calculation inside the UDFs (pass parameters without referring to a range containing the parameter)
All sparklines are now renamed : Sprk & sparklinetype & active sheet name & active cell range.
Pie-chart - New type of chart... Jon Peltier and Stephen Few will certainly love it ... one piece of pie displayed only !
Heatmap - New type of chart, suitable for 2D and 3D heatmaps (Size + Color) ... not a sparkline anymore, but it can be efficient if properly used
Boxplot - New type of chart... a standard for statical analysis of populations, with the help of Nixnut from Holland
BarChart - Added parameters for colors of bars... for those who really do not appreciate the simple and clean grey scale
LineChart - Added parameters for colors of lines
BulletChart - Added possibilty to choose a color scheme
ScaleLine - Added 3 text zone (Left ; center ; right) and choice of font size... I think this was really missing, but text display dramatically slows down the code.
VariChart - Added possibilty to choose a color scheme... just to be coherent w/ other charts, an allow users to have a certain uniformity across their dasboards
Color Scales - 2 separate sheets with well balanced color scales. Some personal inspiration and great color scales from ColorBrewer
Additional macro (via Alt+F8) to delete all sparklines of the active sheet (but only the sparklines...)
The next topics I will work on are :
A macro + menu to automatically copy all required VBA modules and code to other workbooks
A 4D heatmap ... just for fun : Color, size and piechart in an array (ok, a small one)
An Add-in version of sparklines for XL
Solve the printing issue on Piecharts (perfect circles on the screen but ovals when printed ...)
All ideas, suggestions and feed back are welcome...
Best regards
PS : Seattle... I'd love to live there for a while.
@Fabrice: That is an awesome update to spark-lines. Cant wait to test it once I get to my PC.
PS: yeah, Seattle is a lovely place...
[...] Microcharting in Excel - 7 Alternatives Reviewed [...]
[...] Micro-charting in Excel - Alternatives Reviewed [...]
There is not bad utility which works with excel files-how to fix corrupted Excel file,as far as i know it is free,software works on a very important report, with statistical data, graphics, tables and a lot of critical information and it is lost at once,will analyze your damaged document and show you its content in a preview window,allow you to fix files Excel and see, which elements will be recovered and which are damaged too badly,can choose, whether you'd like to recover all of your damaged documents manually and spend many days for this purpose.
Andreas
I'm just learning how to use use MicroCharts by Bonavista, and looking at their dashboards, it looks AWESOME. However, mine don't look so awesome. I'd love to know how to layer a line graph over a bar chart. I haven't been able to find a good user group online.
Sparklines is awesome. Great work Fabrice.
Fabrice,
Your link dated September 25, 2008 reports:
SourceForge.net > Error
Error 500
We're sorry, but we weren't able to process this request.
[...] Microcharting in Excel – 7 Alternatives [...]
[...] [Related: 7 ways to create micro-charts in Excel] [...]
[...] These are small charts that can be shown inside a cell and are linked to data in other cells.You can insert a line chart, win-loss chart or column chart type of spark line in excel 2010. They add rich information analysis capability to mundane tables or dashboards. We learn more about using them in tomorrows article. [meanwhile: Learn how you can make sparklines in earlier versions of Excel] [...]
I just stumbled upon your site...this is good stuff.
I created another template based on Tony's in cell bar charts. I made it to accept up to 50 rows. Check it out http://scorecardanalysis.blogspot.com/2010/08/microcharts-in-excel.html
I just got my blog started but will be posting similar topics about charting, scorecards, etc.
I'll try Sparklines for Microsoft Excel and i'll see how it is!
Hi,
I want to know other commercial alternatives microcharts like bonavista. Could you please list me.
Thanks,
Dear Sir
My self is Navnit Pratik sir i do't know about micro so kindly provide tell me how to use micro in excel 2007
[...] ???? ?????? ??????? ??? In Cell Charts, ?????? ??? ???? ???? ???? ??? ?? ???? ????? ????? [...]
Hi.... I have a small thought in mind to prepare a spreedsheet... If any one is ready mail me I will share the information and we can work on it...
I am truly grateful to the holder of this site who has shared this wonderful article at here.