In Petal Charts – an Alternative to Radar Charts I have suggested using a radar chart tweak to replace the radar charts. Both PTSBlog and Information Ocean have posted their critical reviews of these petal charts.
So as a penance for proposing petals, I am going to provide a tutorial on creating a comparison table in Excel for replacing the radar charts. We are going to create a comparison table chart like the one featured on right that is proposed at Information Ocean.
Creating Spot Matrix Charts in Excel in Just 5 Steps
The trick for creating comparison table like the above lies in using a dingbat (symbol) font named “Wingdings 2” that is pre-installed in most of the Windows machines.
- First arrange your data in tabular structure. For eg. let us assume this is the data we are trying compare:
- Now create another identical table. We are going to fill this one with one of the 5 circular symbols :
. 
Next go to an empty cell, lets say C3, and press ALT+I followed by S (Menu > Insert > Symbol). Set the font to “Wingdings 2” and insert these 5 circular symbols to the cell C3.- Now, in the blank table you have created in step 2, let us write formulas to fetch one of the circle symbols based on our data. A sample formula can be like this:
=MID($C$3,FLOOR((data-1)/5,1)+1,1)
Copy paste the formula in the entire table. - Finally, change the font of the new table to “wingdings 2”. Adjust colors / size if needed. When you are done, the comparison chart table should look like:
Download the Spot Matrix Charts Template and Replace your Radars
That is all. How do you like this compared to petals?
28 Responses
Very nice approach.
Chandoo – the table using windings reminded me of a post I did late last year. The post was about Harvey Balls, which are somewhat similar to the one you created. I seem to like you version more because it doesn’t use a derivative of pie charts like the Harvey balls do. Both have the same 5 step scale.
Thanks for giving the font (windings2) tip in this post.
http://supportanalytics.com/blog/2007/12/qualitative-comparison/
My technique has always been about using bubble charts, in a similar way to this heatmap tutorial in XLCubed. You’ve found a different way.
I really like the Wingdings idea; thanks for pointing me to those symbols, I may well use them in tables in the future.
The comparison table is “OK” as far as it goes and I’m sure it has its uses. However, I liked the “tweaked” radar chart more for a particular application. The idea is to provide a quick picture of the status of a sales cycle. The cycle can have various phases in it, from prospecting, to qualifying, to proposal to actual sales (there are actually more phases but I don’t want to get too specific here to protect the company’s identity). The need is to portray to top management, who just want a very quick, non detailed picture, of how many dollars are “tied-up” in each phase. I find that the radar chart fills the bill quite well. The phases are arrayed in a circular pattern around the “radar”. The relative length of each “ray” gives a general picture of the dollars involved and the vertical scale provides an indication of the actual amount.
I know this could be done with a bar chart, but somehow it doesn’t quite “click” in the same way that the tweaked radar does. So for this particular application I vote for the “tweaked” radar.
This detracts nothing from the comparison table, which is very good and has its own applications… and I’ll probably use it somewhere down the line. But let’s not discard the tweaked radar just yet.
Yes, the old saying “A picture paints a thousand words” is as true today as it ever was. Neat alternative – just goes to show that you can tweak Excel to do just about anything you need it to do.
One of the common finance tasks I have to do is “bridging” – reporting how we arrived at one number from the base/start of another – for example, explain how the actual results varied from plan. I often use a “waterfall” chart for this and it’s fairly effective at showing +ve/-ve variances, but have you anything to “spice them up”?
Great idea! And unlike in-cell pie charts, Excel users will already have Wingdings, so this can be easily distributed without having to supply additional fonts.
Chandoo — thanks so much for this post.
I commented on Jon’s blog about how my rule of thumb is that the main insight of the chart needs to be able to be grasped within 30 seconds. And the radar/petal charts just needed too much explaining. These spot matrices are a great alternative. And just in time. I’m gonna wow some folks at work today!
@all : thanks for comments and love 🙂
@Angelo : You have a very good point. In your case a petal (or radar) would have made perfect sense to provide that at a glance look at the sales cycle. If you dont mind can you post a picture / xls of your data & charts for us to take a look at how you are using this data in the petal. Just post with some dummy data if you can.
@DMurphy: hmm, that is interesting data / need to explore. Let me think about other ways to show how various variables can contribute to a final value. Let me know if you come across any fun / simple alternatives. As usual we will sponsor a donut 😉
thanks for post
Hi, love this.
I struggled with getting the symbols in the cell, so came up with a work around using the CHAR function..
Instead of the symbols you can enter the following as a string the symbol reference cell:
=”152 153 154 155″
and change the formula for the table to be:
=CHAR(ABS(MID( [cell with string] ,IF(FLOOR(([cell with value]-1)/5,1)+1=1,1,(FLOOR(([cell with value]-1)/5,1)-1)*4),4)))
Then simply change the font to Windings2
@Michel: you are welcome
@Justin: Awesome stuff. I use char() and ascii code functions all the time because they are simple and reduce the clutter. Thanks for sharing that formual with us.
Going one step further, since we know all the symbols are from 152 to 155, you can just use char(152+normalized value) like:
=char(152+floor(([cell with value]-1)/5,1))Just glad to be shown such clever tricks.
I guess one reason for keeping the in-string lookup would be to be able use non sequential character codes.
I’m using a very buttoned down Office2k but others with more fonts may have some fun with this. .
I think I’d just use a lookup table. That way, if I wanted for whatever reason to change the symbols I was using, I’d only have to change entries in some cells, rather than dig through a space delimited string (and risk messing up the delimited array).
@Justin: agree…
another thing that comes to my mind is… instead of creating the string like “152 153 154 155” you can actually type out these characters in between the quotes with default font (it used to work with alt+numeric code in olden days, I dont think it works anymore, nevertheless you can insert them using menu > insert > symbol and use the default font)
@Jon: Of course, lookup would be the smart way to design. hard coding the codes can always be a problem, especially you never know which character the boss likes 😀
Or just use the Harvey Balls font and then you can just use normal numbers (no formula required).
http://www.ambor.com/public/hb/harveyballs.html
hello evry one………..can anyone help me out sorting my problm.I hav a task before me to design a dashboard design tool for Electronic corporation of india limited(ECIL).Ian equipped with raw data like turn ovr,profit before n after tx,networth,capital employed,value added. i have year wise annual reports frm 2003-2009.database shd be usd is oracle………….plllzzzz if anyone could help me……….will b very very thnk ful.
This was great, provided your measurement was on a 5-point scale. Does not work well with 7-point scales, at least when using Wingdings.
@Jan
Wingdings 1 & 2 contain a few other character sets that are useful for similar results
Like the 7 +’s and the 7 X’s for numbers up to 7
or
Like the numbers 0 to 10 in White and Black circles for any numbers up to 10
Chandoo I don’t think you need to penance for doing a petal graph after all a spanner can’t do a screwdrivers job. Each graph, formula and program is built to do a specific job it’s up to the developer/programmer to deside the best way to convey information at a glance, not a critic from another website.
That’s not to take away from their experience and knowledge and no one could ever clam that they know everything about excel as there are multiple ways of completing a task. It is good to provide different ways of thinking to prompt people to look at a design differently and improve it, so in a way Jon has helped you to think in a different way. But again with regards to your tutorials you shouldnt be getting criticised for providing a different way of thinking and helping everyone do better creating solutions to excel issues.
While other sites have been great at problem solving specific issues they don’t have tutorials that change your thinking and approach to issues. Not to mention the grace you’ve shown so you will continue to be a great source of knowledge of mine and others.
Chandoo I don’t think you need to penance for doing a petal graph after all a spanner can’t do a screwdrivers job. Each graph, formula and program is built to do a specific job it’s up to the developer/programmer to deside the best way to convey information at a glance, not a critic from another website.
That’s not to take away from their experience and knowledge and no one could ever clam that they know everything about excel as there are multiple ways of completing a task. It is good to provide different ways of thinking to prompt people to look at a design differently and improve it, so in a way Jon has helped you to think in a different way. But again with regards to your tutorials you shouldnt be getting criticised for providing a different way of thinking and helping everyone do better creating solutions to excel issues.
While other sites have been great at problem solving specific issues they don’t have tutorials that change your thinking and approach to issues. Not to mention the grace you’ve shown so you will continue to be a great source of knowledge of mine and others.
Just as a heads up when submitting comments via iPhone it doesn’t let you publish if there are returns/spaces but then when they are taken out it posts the original and the amended text.