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 with Outlined Stars,
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:
33 Responses to “How to make a 5 Star Chart (Similar to Amazon)”
Great technique. You could also use a star symbol and the REPT function to do an in cell chart to show stars.
I took your idea and reversed it. You are using the chart as the background, and displaying the value using a mask.
What I did was to hide the stars altogether, and show only that much as the ratings want.
I first put in stars from the Insert -> Shapes box. Then aligned and grouped them together.
Then, I made two cells, one as 'MaxValue' and other as 'Difference'. MaxValue is the maximum possible score, and Difference the difference between the maximum and given ratings.
Hence you have ratings in B2, MaxValue in B3, and Difference in B4. Values of B3 and B4 are frozen at "5" and "=B3-B2" respectively.
The chart was made as you described, except for these differences:
1. The value it refers to is Difference, instead of ratings.
2. The chart lies over the stars, instead of below the mask.
3. The Plot Area background was also removed - 'No Fill'.
4. The Bar colour was made the same as the document background.
5. In Format Axis (Horizontal), the check box for 'Values in Reverse Order' was ticked.
Rest of the chart formatting is similar to what you have said, that all stars are covered by the graph, the gridlines and the axes are removed, no borders, etc.
The end result is that the graph, at 0 ratings, is covering all the stars. This is because it is reflecting a difference of 5 from the MaxValue of 5. At 3.5, it covers upto value 1.5, but since it is reversed, the outer 3.5 stars are uncovered and seen, reflecting the rating value. At rating 5, no chart is visible, hence all the stars can be seen.
Not exactly what you wanted - making holes in the object, but similar to it - making the object above hide the objects below.
@Andrew
The use of Rept is restricted to display integer values
The technique I have described here shows any value including fractional values
If you watch the Animated Gif on the Chandoo.org page you will see the stars show all the values between 3.0 and 1.0 in steps of -0.1. Same with the thermometer chart.
Alternatively, could you fill the bar with a picture of a star? I may have missed something but it seems to produce the desired effect and is much easier.
Example: https://docs.google.com/uc?id=0B4NW2k5VCmymODU1YWE1Y2MtZmQ5MS00YWY1LWJmNDUtZmNmMDRlZWNlNjZl&export=download&authkey=CPm5g5EO&hl=en
I think what I missed was the fact that your method allows for much greater flexibility. While the star chart can be accommodated easily with a simple fill picture, other shapes and effects might not be possible. This is a great technique, thanks!
Josh I downloaded your file (from the URL provided) it looks easy and good..but if you put the Average value as 4.5 then it shows something which it should not,anyways it was a good attempt.
Hui Its really a good technique of creating a graph using a mask, i have tried to create a PNG file via MS-Paint, where i saw the .png extention in SAVE AS section, but when i tried to import the png file thru Insert->Object. It was not accepting.
Then I tried using Clip Art->Organize Clips-> then I imported my png clip there, it was imported successfully but the output was not a transparent Object it was Opaque 🙁
I think we can create good png files thru CorelDRAW and Photoshop Only.
If we try this via REPT function then it’s too easy but then we have very limited Objects. By limited Option I just mean to say I we want the graph in Horizontal direction then its fine you have many options but if we want it on Vertical direction then we have very few Objects, because we cannot select the Object, we can only change the color and size of the object.
Hope Windows will soon give this feature of creating a png object clip in excel only 🙂
One more problem with REPT, it can not show you the decimal values's representation. i mean the output of this "=REPT("!",10)" and "=REPT("!",10.5)" will be the same 🙁
Hui you're awesome! Using REPT will be of very little or of no help in this case....
Josh, This works great THANKS as for the problem thar rajranja mentioned when the average is 4.5, I was able to correct that problem by formatting the X-axis and setting the minimum to 0. The worksheet originally had the max set to 5 and minimum set to automatic which caused the flaky display. Setting both min and max to 0 and 5 respectively seems to take care of the problem. THANKS AGAIN JOSH!
Excellent way of showing stars.
But I am more impressed with Harshal's method given in comments section. (2nd Comment)
By using his technique you can insert any objets instead of star, add text on it, have different colurs, can have different images as well & you dont have to find mask for all these.
Great tutorial.
I will have to create myself a mask and get cracking on this. I love the footsteps idea. I was thinking of the tongue of a chameleon or the sea coming in to tide.
I maybe taking this too far 🙂
Great tutorial. this gives me some idea on how to go about showing the chart.
My problem has to do with company PC. when i insert the png file the center of the stars turned into black and I can't make it transparent. May be i'll have to create my own in "Paint".
I wish Excel's "Shapes" can let us not fill the star "but" fill the box outside the star. That will make life easier for us to create our own template. Group them up and apply the same technique Hui showed us above.
My PC doesn't have very good painting software...sigh.
A tangent question, Chandoo, but I was wondering what program you use to creat all your cool animated Gif? I'd like to do use that as well for our trainings. Thanks so much!
Great site and great posts Chandoo!!!!! You are awesome and making me awesome in Excel.
I have created a Selectable Non-VBA/Non-Macro Bar Chart (Histogram), X-Y Chart (Stars) and spreadsheet (Selectable Values) that replicates Amazon pretty closely after seeing Chandoo's and Hui's work inspired me.
Process involves an "Option" button selection for the Book/Item, that is then highlighted as the chosen one using "Conditional Formatting".
Next to the selection area of each book/item is a XY Scatter chart where i have replaced the data series data points with an Excel Shape of either a Full Star, Half Star or Empty Star.
Stars were created with Excel Shapes: Full Star = Format Shape of Solid Fill and Border Color equal to Fill; Empty Star = Format Shape of No Fill and Border Color equal to full star border color; Half Star = Format Shape of Gradient Fill Color 1 = Full Star and Color 2 = White with border Color equal to full star.
All of these techniques can be found here and on Jon Peltier's site.
Thanks Chandoo for making me more awesome. I would post a link to a picture or xls file, but not sure how/where to do that. Thanks and happy excelling!
@Twee
I use:
Camtasia for doing Video Capture and production of Animated GIF's
CorelDRAW for Masks and other picture editting and drawing
Snipping Tool (Win 7) for Screen Captures of stills
Although the free Paint.net can fairly much replicate most CorelDRAW functions
@Alan, @All
Have a look at:
Waves and Cameleon
Thank you so much, Hui, for helping me get better at Excel as well as how to impress my coworkers. You guys are awesome. Thanks!!
... thank you for this good idea ... as there are always many ways to Rome, also Joshs idea is very helpfull .. greetings, Mike
Hi there,
This is fantastic and so simple. I am wondering if it is possible to get the thermometer mask png picture ?
@Kim
It is included above in the Files section, directly under the Green Thermometers
or you can use this link
http://chandoo.org/wp/wp-content/uploads/2011/04/5-Stars1.xlsx
also have a look at
http://chandoo.org/wp/wp-content/uploads/2011/04/Waves-Cameleon.xlsx
Dear Sir,
How can we import 5 star mask in excel 2007, i cant do this after some attempts.
How can we make other mask.
@Ali
To insert a mask just use the Insert, Picture menu
The masks are simple PNG Pictures but can be any file type that supports transparency
.
I used CorelDRAW to make the masks but I could have used Paint.NET
In either package make the shapes you want leaving an area of no color in the shape you want to animate with your chart
The surrounding area needs to have the color of the background of your chart.
in all the attached versions this was set to white, but can be any color, noting that you can't change it in excel.
Export as a PNG or JPG which both support transparency
and then import and use as required.
.
You can group several shape objects together in excel to make an outline shape,
make sure that you set the Borders to No Color before you group them
Hui, every excel doc that I open from your links on this post just has a bunch of letters, numbers and symbols. Like this --> Î#§ó5Ú<¤½£@M<ABˆ¶ó:àûK)ò¯/¿æý‡~§y7K.
Is it because I have excel 2003?
@JimH
The File are 2007/10
but the techniques will work in 2003
.
I have updated the post to include the 2003 version of the files
as well as links to the Waves and Chameleon examples (2003 and 2007 versions)
[...] How to make a 5 star chart like Amazon.com [...]
hello everyone
I mean chandoo ,Hui and others.
I read few things on this site.I dont know who will recieve this post.I am a senior executive and working in delhi with healthcare sector . My department is training (all behavioral training ) i did my graduation and that is BCA .So i have interest to teach my employees MS Excel,MS powerpoint,MS word etc. I love to read about computers software. I am working on making one module for my employees those who belong to very lower level like technicians and all .I would like to be associated with you all . Help me out please in making a nice module for my employees and teach me also . I will be very greatful. Give or send some material on Excel 2007.
Sunita Sharma
Hello
great template!
i am however struggling as i need to make the axis "auto adjustment", so it pretty much throws off the axis.
i.e.
if value is $15000
Goal is $ 8000
% = 187%
is it possible to have that adjustable? Can you guys help, thxs
@Jay
You can make the Axis Auto Scale as opposed to a fixed value
@All
Also note that there are a few online Graphic Capture and editting programs around
eg: http://snag.gy/
I love the thermometer chart...but how can I make it red?
Jeff
Hi, not sure if anyone has replied to "If anybody knows how to join objects together in Excel to make holes through them as required here, Please let us know". The answer is "yes" and the solution is as follow:
1. Insert a rectangle shape in your sheet, set fill to white and no line
2. Insert the required star shapes into the rectangle, set star fill to black and no line.
3. Group the rectangle and the stars.
4. Rightmouse on the rectangle and select copy.
5. Then Paste Special and select "Picture (PNG)"
6. Select the picture you have just pasted. Then PictureTools-->Format-->Adjust Group-->Color (down arrow)-->Set Transparent Color, then click on one of the black stars
7. All Done
1) Put the numbers you want shown in stars in one column
2) Copy the formulas in B-F into the first row of the 5 columns set aside for the stars (Change $A2 (etc. ) if needed to match what cell you have the numbers from step 1 in)
3) Highlight the first row of formula's and expand the selection by dragging the lower right corner to bring the formula's down as far as needed (this way it automatically changes the # next to the A for you)
4) Set the column width to 1.71 (this number works for me in excel 2013 when using Calibri 11, this may change for you if you are altering that)
5) Highlight the columns you are using for the stars and go to the home tab and click on Conditional Formatting
6) Go to Icon Sets and choose the stars
7) Go back into Conditional Formatting, click Manage Rules, if none show up, change the dropdown to show "This Worksheet"
8) Click Edit Rule with the stars rule highlighted, and edit the settings so it matches the image in the excel file.
9) Click Ok, then Apply/ok, and there you have your stars
10) (optional) You can now hide the column with the initial numbers in it, but do not delete it
File with example (on dropbox): http://db.tt/1N0v4rg5
way too hard