Create Rating Scale Using Excel
I was helping a colleague out with some survey data. We were kind of in a rush to get it done, and looking for a software or an online app to generate the charts for the report (and figuring out on how to use them?) would take too much of my time, then I came across a rather interesting thing that we can do on Excel. I mean maybe everyone already knows how to do it but I just figured out that we can create a rating scale using one of the charts on Excel. I referred to some websites on how to do it but it didn’t quite give the result I wanted because the pointer on the scale just won’t sit on the precise value. So I made some little adjustments.
For this article, let’s say I have this cocoa bean rating data and I want to display the average rating of the cocoa beans on scale-like chart based on its country of origin. The dataset can be found on Kaggle .
I selected a sample of the rating data based on four countries (A, B, C, D) and calculated the average rating score for each country. Then prepared some values to create the scale chart. Here is the result:
The first row is the average rating of the cocoa bean by the four countries. The three bottom rows will be the bars in the scale chart. The second row is the average value of the cocoa bean rating minus by 0.1. The third row will be the pointer or marker in the scale chart, I fixed the value to 0.2 as the size of the marker. The fourth row is the maximum rating of 5 minus by the value in the second row. This is to make sure the bars in the scale chart cover the maximum rating score.
For the second row, I subtracted 0.1 from the average rating value to give space for the marker size. So basically what I’m saying is that the value to subtract depends on the size of the marker. This is to make sure the marker points to the precise value of the average rating. Let’s put it like this:
Now with these values in the data table, let’s create the scale chart.
Select the whole table and select the horizontal stacked bar chart. The chart should be looking like this:
Now we can see that the blue bar represents the first row from the table, orange bar the second row, grey bar the third row, and yellow bar the fourth row. Next, select the blue bar and change its plot series to Secondary Axis. Then, adjust the Gap Width to a greater value (such as 300%). The blue bar will appear narrower in the chart as shown:
The axis values don’t seem right, do they? Since the cocoa bean rating scale is based on the value of 1 to 5, modify the top and the bottom axis values (Minimum and Maximum) to the same range of 1 to 5.
After that, select the blue bar and change the colour to No Fill to make the bar transparent. Change the colour of the orange and yellow bar to same colour. For example I chose “Chocolate, Accent2, Light Colour 80%”. Basically the light brown colour. Also in my case, I removed the bottom horizontal axis since I only needed one axis to measure the rating value.
Create the marker for the scale chart to indicate the value of the average rating. For example, I used the diamond symbol and used the background colour similar to the colour selected for the two bars on the sides, then merged the two shapes together by grouping the image (using the right-click > Group option).
Copy the symbol. Select the grey bar and paste the symbol. The marker to indicate the average values is displayed on the chart.
From the chart above, we can barely guess what is the value for each country’s cocoa bean rating, it would be nice to put a label somewhere. In this example, I selected the transparent bar, which located on the left side of the marker. The transparent bar would be a narrower bar because of the Gap Width value set earlier. Then I turned on the Data Label, and selected the Inside End position. The values of the average rating are displayed.
If necessary, you can adjust the position of the data label by selecting the data label and modifying the Right Margin value.
Then change the chart title and the scale chart to get something like this:
From the chart we can notice how the markers are positioned correctly along the scale value.
The chart visualizes the values so that it’s easier to see where its at on the scale. Assuming the values of 1 to 5 represents these:
1— Low Intensity
2 — Medium-Low Intensity
3 — Medium Intensity
4 — Medium-High Intensity
5 — High Intensity
We can see that all cocoa bean from the four countries are on average, have medium to medium-high intensity.
Maybe there is a way to include the score description in the chart but I hope this helps some people like it helped me!
