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.
15 Responses to “A Gantt Chart Alternative – Gantt Box Chart”
That's a great idea.
Maybe the planned End Date should be highlight more.
I don't know how it would look like (nor how to do it yet), but what if instead of finishing the bold line to the best case End Date, it finishes to the realistic End Date?
The idea is ok, I think other project management tools have this, already? Maybe not.
Gantt charts in my view are about the signal most unless thing in the world, theres no way you can look at one thats more that a little complex and understand what it's telling you. I'm going to write a diatribe on project management at some point, its one of my pet areas I think!! 😉
The issue I have with this chart Chandoo, is that Tasks need to be linked to each other, so they should inherit the uncertainty, which would mean the as you moved down chart the lines would be miles apart for later tasks, and you might have to add lots of lines for subsequent tasks to cover the various outcome of it's parents.
Having said that, for the high level board summary, it's a nice way to go, it it appeals to the management 😉
thanks Chandoo, great post.
Ross
Whoooa !!! That's a very clever idea Chandoo. I really love it.
I think i'll update my gantt project sheet with that idea soon (remember my template ?)
@ross : you can link start date to the end date of the previous task in your data. The only problem I still se is to which end date (real ? planned ? best ?) in order to have average amount of information.
If best end date, you'll tend to increase uncertainty at the end of chain, although if you link to real end date, uncertainty will be decreased too much, leading in both cases to wrong management direction.
Maybe planned till the task is finished then real will do the job ?
Hey chandoo, this looks good and this would definite add value in production planning / scheduling. Uncertainity in finishing a task is very high in production scheduling and this could give an insight or a bird eye view of possible shipments we can have....
I've always been frustrated by the limitations of gantt charts. Will definitely use this, I've always struggled with how to succinctly communicate the uncertainty of certain tasks without confusing stakeholders.
I like this, I think it's a very effective way of showing how a timeline can change and which parts of a project need close attention.
@Cyril / @Ross: I would intially link the the start date to the planned end date of the previous task, with the chart updating when a task has been completed to reflect the true end date.
Or what about giving a drop-down selection box to allow the user to see the chart based on planned/best-case/worst-case end dates?
Like the idea. Have found that Excel is more flexible than MS Project for graphical solutions. The "Best Case"\"Worst Case" metrics are theoretically appealing but once the project and\or phase commences their reliability diminishes. A chart like the above that showed Planned Start, Planned End, Replan End Start, Replan End Date, Number of Replans the Start and End Dates, and Actual would provide an active, actionable view of each task\phase. It would also highlight the areas which are riskiest.
It is always amazing how flexible excel can be.
My question is how would the chart show a scenario where the date moved up? If a task is dropped or the duration of the task is significantly reduced by applying more people or machinery to the task, the dates will move up.
The gantt chart has been around for a long time, but it is still quite useful to show progress.
Cheers,
B
I like the idea but seems bit complicated in case of long projects involving numerous activity.
Also, reading and explaining is required hence not feasible where plans are just send to audience for approval.
Cheers
SY
Great idea Chandoo,
When I was reading this idea regarding delivery dates, another thought popped into my mind, how can you show the uncertainty with MONEY!!
In this case, applies to cost management or even a normal budget, you think?
Would Box Chart and Gannt Chart help to understand the best case, middle case and worst case when money is spend or planned with these three risks are involved?
I imagine that this chart could help people who write their budgets get a better understanding of risks affecting their spending.
Peter
Chandoo,
I like it. How would you display an entry once it has been completed (actual)?
Thank you,
Matt
From what you have shown so far I think that this box Gantt chart is awesome! I think that this could be an extremely useful tool.
I can't wait to learn how to make my own charts in Excel.
Will the methods that you are going to teach us work in 2003 as well?
[...] Firday, we proposed a new chart for showing project plans. I chose an ugly name for it and called it Gantt Box [...]
You need to read Eli Goldratt's Critical Chain. The uncertainty you are looking for should be accounted for in a project buffer. Not at each task level.
Further you should spend time understanding Agile Development. This would have you plan only in 1-3week iterations. This allows you to embrace changes to work not yet started, and for your customer to re-direct your course at regular intervals (after each iteration) throughout your project. keyword search: Agile Scrum
These items will show you that you are solving a tracking problem for something that you can entirely avoid!
[…] Chandoo.org’s Gantt Box Chart. […]