Yesterday, we have seen a beautiful example of how showing details (like distribution) on-demand can increase the effectiveness of your reports. Today, we will learn how to do the same in Excel.
Before jumping in to the tutorial,
In this post, I have explained one technique of using charts + VBA to dynamically show details for a selected item. There are 4 other ways to do the same – viz. using cell comments, pivot charts, group / un-group feature and hyperlinks. I have made a 45 minute video training explaining all the 5 techniques in detail. Plus there an Excel workbook with all the techniques demoed. You can get both of these for $17.
Click here to get the video training – Showing on-demand details in Excel
How does the on-demand details chart work – demo:
This is a replica of yesterday’s chart from Amazon. When you click on any cell inside the Items + Rating table, the corresponding items review break-up is shown in the chart aside.
Creating this chart in Excel – Step-by-step Instruction
So you are ready to learn how to do this chart? Great, grab a cup of coffee or tea and get started.
1. Understanding the data
This is how I have setup the source data for the chart. It has 3 columns – Item name, Reviewer ID and Rating. Each item has several ratings from several different reviewers. And our goal is to summarize all these ratings.
All this data is in the range Table1. We will use structured references [what are they?] in formulas to keep them readable.
2. Setting up the Item & Rating Table
The first step is to show a table with all the products we sell and their corresponding average rating. We will then add the circle indicators at the end to visually show the rating.
Calculating the averages using AVERAGEIF() formula:
The formula is quite simple. Assuming the product names are in C5:C13,
We just write =AVERAGEIF(Table1[Item],C5,Table1[Rating]) for first product’s average. Fill the rest by dragging the formula down.
Displaying Circles:
There are no star symbols in the default fonts. But we have circles – a full circle, an empty circle and a donut to indicate half-circle. These symbols are available in Wingdings 2 font. We will use an incell chart to display the circles. Assuming the rating is 2.83, we need to print 2 full circles, one donut and 2 empty circles. [related: inserting symbols in to Excel workbooks]
The formula is quite simple. Since the ratings are in D5:D13, the formula becomes,
=REPT(fullCircleSymbol,INT(D5)) & REPT(donutSymbol,(INT(D5)<>D5)+0) & REPT(emptyCircleSymbol,INT(5-D5))
Naming this grid
Now that we are done with the rating grid, let us name it – rngReviews.
3. Finding out which cell is selected
Now comes the macro part.
Before jumping in to the code, take a sip of that coffee. It is getting cold.
When a user selects any cell inside rngReviews, we need to findout which product it is so that we can load corresponding details.
The macro logic is quite straight forward.
- On Worksheet_SelectionChange, check if the ActiveCell overlaps with rngReviews
- If so,
- findout the relative row number of ActiveCell with respect to topmost row in rngReviews (ie the position of selected cell inside rngReviews)
- Put this value in to a cell on worksheet – say E28
The macro code can be found in the downloaded workbook. Here is an image of macro code.
4. Using the macro output to drive…,
We need to use the value E28 to do 2 things.
- Highlight the corresponding row in the rngReviews using conditional formatting.
- Findout the corresponding product using INDEX formula.
I am leaving both of these to your imagination.
5. Calculating Product – Rating Breakup
In order to show details for the product, we must calculate the corresponding breakup of ratings (ie how many 1 star, 2 star … 5 star reviews the product got).
I am leaving the formulas for this to your imagination. But when you are done, make sure your output looks like this:
(hint: use COUNTIFS formula).
6. Create a Chart to show Rating Break-up
This is the last one before we put everything together. Just follow below 5 steps.
- Select the 3 columns – Rating type, number of reviews, total reviews and create a bar chart (not stacked bar chart). In my workbook, this data is in the range C29:E34 in the sheet “Rating Summary”.
- Reverse the order of categories as Excel shows them upside down. For this select the vertical axis and hit CTRL+1 (or go to axis options from right click menu). Here check the “Show categories in reverse order” option. Also remove the chart legend.
- Set both series of the chart such that they completely overlap each other [image]. Adjust the gap width to 50%. Also, adjust the order of the series from Chart’s source data options [image].
- Remove grid lines, axis line and horizontal axis. Format the chart colors to your pink and translucent green (really!).
- Re-size the chart, add title, add labels, remove border. You need to use dynamic titles.
7. Put everything together
Now is the time to put everything together and test. Move the chart close to the rating table. Test it by clicking on any value inside table.
You can also do some colorful formatting if you prefer.
Finish the coffee and show-off the chart to a colleague or boss. Bask in glory.
Download Example Workbook – On-demand Details in Excel
Click here to download the workbook with this example. Play with it to understand how this chart works.
Note: You must enable macros to use the file.
Note2: If the file does not open on double-click, just open Excel (2007 or above) and drag the file inside to Excel.
Learn this + 4 other techniques using Video Training,
In this post, I have explained one technique of using charts + VBA to dynamically show details for a selected item. There are 4 other ways to do the same – viz. using cell comments, pivot charts, group / un-group feature and hyperlinks. I have made a 45 minute video training explaining all the 5 techniques in detail. Plus there an Excel workbook with all the techniques demoed. You can get both of these for $17.
Click here to get the video training – Showing on-demand details in Excel
How do you like this chart?
Ever since I learned this technique from a good friend, I have been using it in dashboards & complex models to make them more user friendly.
What about you? Did you like this technique? Where are you planning to use it? Please share your views & ideas using comments.
More Resources to One-up your Chart Awesomeness
Want more, here is more:
13 Responses to “Gantt Box Chart Tutorial & Template – Download and Try today”
Hi Chandoo
As one of your students I have followed your detailed example through with great success. However, Excel is acting in an unexpected way and I wonder if you could take a look?
http://cid-95d070c79aef808e.office.live.com/self.aspx/.Public/Gantt%20Box%20Chart.xlsm
On my version, I have to type 40239 (Which equates to 2 Mar 2010) to get the chart to display 31 May 2010 (which should be 40329)!!??
Have I done something wrong or is Excel acting up?
Thx
Oli
PS Your example file in 2007 displays correctly.
Hi,
I like this idea a lot, but I agree the name is a little drab.
As an American I may just be seeing things, but to me the combination of lines and bars on your chart looks like a bunch of cricket bats.
Maybe you could work that into a catchier name. 🙂
Cheers!
Here is some code I use to keep the axis synched.
It may be useful to some of your readers
It is based on a comment I saw on Daily Dose of Excel.
Function SynchGanttAxis(Cname, lower, upper)
'Sets the X min and X max for Category axis
Application.Volatile
On Error Resume Next
'
'Top Horizontal Axis
With ActiveSheet.Shapes(Cname).Chart.Axes(xlCategory, 1)
.MinimumScale = lower
.MaximumScale = upper
End With
'Bottom Horizontal Axis
With ActiveSheet.Shapes(Cname).Chart.Axes(xlValue, 2)
.MinimumScale = lower
.MaximumScale = upper
End With
End Function
Function SynchVerticalAxis(Cname, lower, upper)
Application.Volatile
On Error Resume Next
' Excel 2007 only
'Right hand vertical axis
With ActiveSheet.Shapes(Cname).Chart.Axes(xlValue, 1)
.MinimumScale = 0
.MaximumScale = upper
End With
End Function
@Oli.. Can you check your file again.. I see 40329...
@Dave: Even I saw things.. the bars actually looked like lollipops. How about calling this lollipop chart - now that would be yummy and goes along the tradition of naming charts after eatables (bar, pie, donut...)
@Bob: Superb stuff... thanks for sharing 🙂
Hi Chandoo
This looks really good and I think it can also be applied to show project phases / milestones.
Question: Thinking further could this be amended to display a project lifecycle (Idea through to Implementation say 7 phases) on one bar / row? Just imagine 20 projects within a programme all on one chart one bar each showing their respective lifecycle stages i.e. on one page.
Idea: As the Gantt Box Chart this is quite intensive to set up re formatting etc how about the added extra of once you have completed this to "Save as template" i.e. saves the formatting and layout of the chart as a template so you can apply to future charts. Simple to do and will save the time formatting etc again and again and again.
Therefore tip: Click on your chart demo and then click on Save As template icon (2007) - edit file name and click on save. Ready to use / apply via Templates in Change Chart Type window.
Thanks and be very interested if the lifecycle question can be resolved
Mike
How embarrassing.
I was obviously suffering from numerical dyslexia. I was one of those days.
@Mike H: You can easily make this chart to work like a generic project lifecycle plan chart. All you have to do is,
1. in a separate sheet define the steps of lifecycle and various dates in a table (with 5 columns for each of the projects you have).
2. now use a control cell to input the project name you want to show in the chart
3. based on the input, use OFFSET Formulas to get the correct data
4. Rest is same as the tutorial above
For more info on the dynamic charting visit http://chandoo.org/wp/tag/dynamic-charts/ and http://chandoo.org/wp?s=OFFSET
Your solution is really smart but in the en Excel isn't meant to do stuff like this. I, as a former PM, always thought is was frustrating that you had to do stuff like this for something simple like a Gantt chart. So I built Tom's Planner. And would like to plug it here. I think it really solves the problem you are trying to solve in the most efficient way. Check out http://www.tomsplanner.com for a free account or play around with the demo.
Hi there,
Chandoo - this is really a very nice and helpfull chart - I adopted it, so I can report a forecast or the delay of a certain task (coming from my role as an auditor for projects).
One topic I´m currently struggeling with: I do have a project lasting for lets say 12 month. For a management reporting, I want to have kind of snapshot, lets say one month back and 2 month in the future. I tried with the offset formula, but failed. Any idea?
Thx
Lopi
[...] Ein viel geliebter Klassiker ist die Erstellung von GANTT-Diagrammen mit Excel. Wir hatten das Thema wiederholt schon hier. Chandoo.org hat sich mal wieder mit einer neuen Variante hervorgetan: Das GANTT-Box-Chart. [...]
[...] [...]
Hi Chandoo - fantastic xls. One thing I can't figure out how to do is adjust the alignment of the vertical axis. I would like to left align so that I could indent to represent sub tasks. Can that be done? Or is there a better way?
I've been trying to work out if there's a way to show weekends on the graph. The closest thing I've got is to add them on a secondary axis, but then I haven't been able to keep both axis lined up together! Any ideas?
Following on from this - is it possible to show things like holidays?