In the Mr. Excel forums there was a question raised on how can you create a Likert Chart or Graph using Excel. The person asking the question started to create a chart using a XY Scatter chart, but then got stumped. This is the chart that the user wanted to create or replicate:
Two solutions came to mind:
1) I have created a similar tutorial to this on a previous post using the camera tool with a Excel line chart to create the same effect. Check out the posting here:
Using the Camera Tool to Create a Vertical Line Chart in Excel
2) I suggested just using a regular Excel line chart with markers instead of an XY Chart. Here is what a sample Excel line Likert Chart would look like:
3) Then I gave it some more thought and it occurred to me that I created a similar chart to this when I created Vertical Axis categories. You can check out that posting with this link:
How-to Make Categories for Vertical and Horizontal Axis in an Excel Chart
I was able to create the chart as you can see below using these techniques:Below is the How-to Excel Chart Tutorial for building your own Likert Excel Chart. There is also a Free Sample file and Video tutorial at the bottom of the post.
The Breakdown
1) Create an XY Chart with the Likert Data.
2) Add the Categories series to the Chart and convert it to a Bar Chart.
3) Modify the line formats, axis settings and gridlines to match the chart.
Step-by-Step
1) Setup your Chart data
Create your Data for the chart using this format:
2) Create a XY Scatter Straight Line Chart with Markers
Highlight C3:E13 and then create a XY Scatter Straight Line Chart with Markers.
Your chart will now look like this:
Go ahead and delete the legend as we won’t be needing it.
3) Change the XY Scatter Chart series references.
I don’t like how Excel sets up the XY Scatter Chart series, so we need to fix the X’s and Y’s.
Select the chart and then go to the Data group and choose the Select Data button
From the Select Data Series dialog box, choose the Series1 Legend Entry and click on the Edit button:
Your edit series dialog box should look like this (I have switched the Xs and Ys so that the line is vertical):
Repeat the same step for the second series so that it looks like this:
Your chart will now look like this:
4) Insert the Categories Series
Now we need to add another series that will show us the Categories.
Select A3:B13 and Copy the range (Ctrl+C).
Then select the chart and from the Home Ribbon, you need to select PASTE SPECIAL:
then press the OK button from the Paste Special dialog box:
Your chart will now look like this:
5) Change the New Series to a Clustered Bar Chart
This is how you will get the categories on the Vertical Axis.
Select the Series 3 (new green line). Then from the Design Ribbon, choose the Change Chart Type button from the Type group:
Then choose a Clustered Bar Chart from the Change Chart Type dialog box:
Your chart will now look like this:
6) Change the Primary Vertical Axis and Horizontal Axis Formats and Limits
a) First Select the Primary Vertical Axis (on the left) and press Ctrl+1 to bring up the Format Axis dialog box. Then change the settings as follows:
Minimum = 1
Maximum = 11
Major = 1
Your chart will now look like this (the data points on the top and bottom will stretch out to the top and bottom of the chart):
Go ahead and delete the Primary Vertical Axis as we don’t need it anymore by selecting it and pressing the delete key.
b) Now lets fix the Primary Horizontal Axis. Select the bottom Horizontal Axis and press CRTL+1 to bring up the Format Axis dialog box and change the following settings:
Minimum = 1
Major unit = 1
Major tick mark type = None
Your chart should now look like this:
7) Change the Secondary Vertical Axis and Horizontal Axis Formats and Limits
a) First Select the Secondary Horizontal Axis (on the top) and press Ctrl+1 to bring up the Format Axis dialog box. Then change the settings as follows:
Minimum = 0
Maximum = 5
Major tick mark = None
Axis labels = None
Then choose the Line Color Options for this axis and set the Line Color to “No Line”
Your chart will now appear as follows:
This may seem strange, but the next step will make everything work out.
b) Select the chart and Add the Secondary Vertical Axis by going to the Layout Ribbon and then Choose the Axis button from the Axis group and then select “Show Default Axis” as follows:
Your chart is now really close and will look like this:
Then select the Secondary Vertical Chart Axis and Press Ctrl+1 to bring up the Format Axis dialog box and select the following options:
Categories in reverse order = Yes
Major tick mark type = None
Position Axis = On tick marks
Your chart should now look like this:
Wow, it almost looks exactly like the original chart. Only one last thing to do.
8) Add Vertical Gridlines
We just need to add vertical major gridlines and the chart will be completed.
Select the chart and then select Major Gridlines from the Axis group in the Layout Ribbon:
Your final Likert chart will now look like this:
9) Fix Makers and Line Color
Oops, I forgot that the original chart has different colors and makers, so lets fix that now and then we will be done. Select one of the lines and press Ctrl+1 to bring up the Format Data Series dialog box. Then change:
a) Line Color to dark blue
b) Marker Options Type to Built In circle
c) Marker Line color to dark blue
d) Marker Fill color to dark blue
Repeat these same steps for the second series, but this time to change the blue colors to one or two shades lighter than the previous series. You are now done and your Vertical Likert Chart should now look like this:
Lets compare the two charts side by side:
Looks great and it is all done with the standard Excel charting engine. Below is the sample file and video tutorial.
Video Tutorial
Free Sample Excel Likert Chart Template File
Excel-Vertical-Line-Likert-Chart-with-Categories.xlsx
Let me know what you think by leaving me a comment and if you have any chart that you need help with creating. Thanks!
Steve=True
It’s very useful for me.
Thanks you very much.
Manit. Thank you for the nice comment. Glad I was a help. Steve=True
Steve this is really terrific — much more complicated than I would have expected and would never have figured it out!
I would love to also know how to add labels in place of the numbers (1-5) on the primary horizontal axis.
Thanks!
Hi JonathanT, thanks for the comment. It is somewhat complicated as Excel only deals well with one category and one value, not 2 categories. You can do it from the “Select Data” button on the “Design Ribbon”. Then edit your Horizontal (Category) Axis Labels. Then you can select the range for your horizontal labels. Thanks. Steve=True
That tutorial really helped me.
I’ve got one problem though: At 7b the labels appear on the right sight of my graph. How can I move them to the left side?
Hi Micahel,
Glad it helped and thanks for the nice comment.
Do you mean labels or vertical axis categories?
Please advise.
Steve=True
I am in the same spot as Michael. What you have labeled “Bar categories” is showing up on my secondary vertical axis (right side).
Hi Laura,
You may have missed a step. Check out the video at minute 7:20. That should solve your problems.
Hope this helps
Steve=True
Hi Michael,
You may have missed a step. Check out the video at minute 7:20. That should solve your problems.
Hope this helps.
Steve=True
Do you have an update for Excel 2013? I found that I could not create the secondary vertical axis after several tries using Excel 2013. I finally had to move the spreadsheet to a computer with Excel 2010 to complete the process.
Hi Craig,
Hopefully you were able to figure it out. Is there a chance that you were using Excel Online?
Check out this post if so: https://www.exceldashboardtemplates.com/things-you-cannot-do-in-excel-online/
If not, Excel may have moved this option on you. In Excel 2013, Click on your chart and then click on the Design Ribbon and then choose the Change Chart Type button and finally, click on the Combo Chart. You will then see that you can move individual series to the Secondary Axis.
Hope this helps.
Steve=True
This is exactly what I am looking for but I am stuck on step #4. It only plots out one triangle. My data is in $ amounts ranging up to 1.5M. Am I supposed to change the bar value to cover that?
Hi Cindy, It is hard to trouble shoot your exact situation from your comment. Did you get it worked out? If not, can you send me your data so we can trouble shoot it?
Thanks
Steve=True
Steve – fantastic. Been battling this one for ages, and at last I can design such a chart. Very useful, thank you.
Thanks for the nice comment Ant. Much appreciated and so glad to help. Steve=True
my “secondary axis” ended up on the right side. how do I fix this?
thank you very much for this tutorial–I’m almost there.
Hi Alex, hard to tell. I assume that you moved your data from the Bar chart to the secondary axis. Only suggestion is to follow the steps exactly and start over 🙁 sorry, but without your spreadsheet, that would be my only advice.
Hi Steve, that was exactly what I was looking for. I’ve only one problem: I cannot adjust the maximum value of the Primary Vertical Axis in Excel 2010. So, if I want to add another row, the respective data point is not drawn. Any idea what I’m missing?
Thanks in advance!!
Hi Miri,
Glad it is helping.
You should be able to change any axis maximums. Please make sure you are displaying ALL 4 axis so that you know what maximum you are changing. Also, it can be tricky to check your values positive or negative when showing all 4 axis so also check your sign and the values of your data points as it may seem strange. If you are still having problems, change your values very slowly and with small increments so that you can see exactly what you are changing and how it is affecting your chart. Good luck!
Hi Steve! I used your template and was able to replicate the chart using my own data, but I have more categories (14 vs 11) and I’m having a hard time adding them. I tried inserting rows after I have my data properly placed in your format and it simply ignores the addition. I tried on the top, in the middle, etc. I was able to get the category labels to reflect the addition on the horizontal axis but the actual numbers on both series remain at 11 categories. Thanks!
Hi Patricia, there are several changes you need to do. Here is the list.
1) Insert 3 rows in the Middle of your existing data to go from 11 to 14 categories.
2) Change Y values from 11 down to 1 to 14 down to 1 (Column C)
3) Click on the chart and click on Design Ribbon/Menu to then Add a chart element “Primary Vertical Axis”. Change the Max limit value to 14 (previously set at 11).
4) Delete Primary Vertical Axis or Make the Number Format Text Color = White.
5) Add in Bar value and X1 and X2 values.
Then you should be set. Good luck and let me know how you make out.
I like to have a simple line graph for the Barometer. At present I do this monthly horizontally but as the data is recorded vertically I would like to show the data vertically running from January to December. I use Excel on Windows 10
Hi Gerald,
Can you use a horizontal clustered column chart for this?
Hi Steve,
Is it possible to add bar errors (standar deviation) to this graphic?
Yes sure thing. Simply do this:
1) Select Chart
2) Select Data Series (i.e. Line)
3) Click on Design Ribbon > Add Chart Elements > Error Bars > Standard Error.
Excel Charts will take care of the rest.
Good luck. Steve=True
Hi, Steve.
Thanks for your answer.
But I think it is not that simple, because this way, if I customize the values of standard deviation for each dot, the bar errors come up vertical and they should be horizontal.
I think it is because of the graph type.