How to make a 5 Star Chart (Similar to Amazon)

Share

Facebook
Twitter
LinkedIn

Earlier in the week Chandoo presented Give more details by showing average and distribution

At the top of the post was a small screen capture from Amazon.com showing a 5 Star chart  showing that Twilight had a 3.5 Star Rating (way over-rated if you ask me).

I received an email shortly afterwards from Rajiv, “How can I make one of those charts ? ” with the Stars Circled

It’s actually very simple and this post will show you how.

The Technique

The technique involves putting a mask in front of a single bar from a Bar Chart

The mask has a plain background and has cut-outs where the Stars are, which are transparent and so only the bar chart shows through in those areas which are cut out.

Lets Do It

On a worksheet we need a cell where we have a Rating Value, lets use B2

Make the value in Cell B2, 5

Select the cell B2 and Insert Chart

Insert a Bar Chart (Clustered Bar)

Delete the following chart objects

  • Title
  • Legend
  • Major Grid Lines


Select the Horizontal Axis

Format Axis

Change the Horizontal Axis Scale to

  • Minimum 0
  • Maximum 5


Delete the Horizontal and Vertical Axis

Move the chart and resize the Bar to your requirements

Change the Bar’s Fill to suit

Set Border color to No Color

Insert Picture

Import the 5 Star mask attached here

Position the mask in front of the charts Bar

With the mask selected shift the Right hand side and then left hand side so that you can just see the edges of the bar.

Check the placement by trying the numbers from 0, 1, 2, 3, 4, 5 and 0.1 in B2

You should see all the stars perfectly when the placement is correct

Select the Chart and 5 Star Mask together

Use Shift while selecting each one

Group the Chart and Mask together, so that they can’t be moved

Your are free to shift and resize this combined object on your worksheet as required

Vertical Charts

A Similar technique can be used for Vertical Charts using a Column Chart instead of a Bar Chart


Masks

The masks used here were made in CorelDRAW, but can be made in any Drawing/Paint program like Paint.NET, that allows you to save PNG’s with Transparency effects

The masks consists of:

  • 5 Stars which have no outline color and are transparent
  • 1 Rectangle which is White with no Outline color

The 6 objects are then Joined enabling the holes of the Stars to show through the White Rectangle

Using this technique any shape can be used as a mask

I have included the following masks for you to practice with or use:

5 Stars Mask,

5 Stars Mask with Outlined Stars,

5 Circles Mask,

Swirling Line Mask,

Footsteps Mask.

If anybody knows how to join objects together in Excel to make holes through them as required here, Please let us know in the comments below:

Thermometer Charts

The above technique is great for application to Thermometer Charts, where the Thermometer can take on all values from 0 to 100% or 0 to $200,000

or whatever you require.

Files

All the above examples are shown in one file which you can download here or here for the 2003 Version

Download the Waves and Chameleon 2007 or Waves and Chameleon 2003 examples

Extensions of the Technique

This technique can be extended in a number of areas

The Thermometer chart above shows one such area

The other is applying multiple Masks to multiple Bars/Columns in one chart, But I’ll leave you to practice that.

Limitations of the Technique

Two main limitations of this technique are:

Scaling

As Excel charts are scaled, Excel internally decides what space should be between the Plot Area, Titles and the edge of the Chart Area. This is not maintained constantly and hence the Plot Area may scale at a different ratio to the Chart area and overlying mask.

If this happens Ungroup the Chart and mask and reset ecverything at the new size.

Mask Color

The mask has a Fixed color, in the above examples it is white.

The mask cannot be colored in Excel to Match the background color of the Worksheet if it isn’t white.

So a new Mask will need to be made.

What Do you Think of this Technique

What Do you Think of this Technique?

How else can you see this technique being extended?

Let us know in the comments below:

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

15 Responses to “A Gantt Chart Alternative – Gantt Box Chart”

  1. Kenjin says:

    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?

  2. ross says:

    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

  3. Cyril Z. says:

    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 ?

  4. Vijesh says:

    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....

  5. PK says:

    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.

  6. Andy says:

    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?

  7. Eric says:

    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.

  8. Bob says:

    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

  9. Shyam says:

    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

  10. Peter says:

    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

  11. Matthew Galman says:

    Chandoo,

    I like it. How would you display an entry once it has been completed (actual)?

    Thank you,

    Matt

  12. Phil says:

    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?

  13. [...] Firday, we proposed a new chart for showing project plans. I chose an ugly name for it and called it Gantt Box [...]

  14. TommyZ says:

    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!

  15. […] Chandoo.org’s  Gantt Box Chart. […]

Leave a Reply