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.
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.
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.
Suitable for visualizing project plans, sales reports etc.
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.
All the chart types are available for micro charting, so you can create spark lines, pie charts, stacked bars etc.
Needs a lot of formatting to remove all the chart labels etc.
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.
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.
Makes a good addition to your sales report, project plans etc.
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.
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.
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.
"You can only make variants of bar charts.
Difficult to format, highlight specific points with out tweaking."
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.
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.
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.
I will do my best to answer back to users who have trouble, and I might learn some stuffs in the process.
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.
Damn, I was writing the same thing about the Camera tool, perhaps the easiest way to create sparklines.
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.
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.
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?
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.
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.
@Jorge: Thanks for letting me know about the camera tool, I have never used it before. Will try it.
@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.
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 !
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.
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.
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.
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.
We're sorry, but we weren't able to process this request.
I just stumbled upon your site...this is good stuff.
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!
I want to know other commercial alternatives microcharts like bonavista. Could you please list me.