How-to Make an Excel Vertical Likert Line Chart with Categories

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:image

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:image

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


SPECIAL - SAVE 10% until July 20th. Use code EDT.


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:image





2) Create a XY Scatter Straight Line Chart with Markers

Highlight C3:E13 and then create a XY Scatter Straight Line Chart with Markers.image

Your chart will now look like this:image

Go ahead and delete the legend as we won’t be needing it.

3) Change the XY Scatter Chart series references.


SPECIAL - SAVE 10% until July 20th. Use code EDT.


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 buttonSelectDataMenu

From the Select Data Series dialog box, choose the Series1 Legend Entry and click on the Edit button:SNAGHTML4fdfd8a

Your edit series dialog box should look like this (I have switched the Xs and Ys so that the line is vertical):SNAGHTML503de76

Repeat the same step for the second series so that it looks like this:SNAGHTML50520a4





Your chart will now look like this:image

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

Then select the chart and from the Home Ribbon, you need to select PASTE SPECIAL:image


SPECIAL - SAVE 10% until July 20th. Use code EDT.


then press the OK button from the Paste Special dialog box:SNAGHTML50d6ffb

Your chart will now look like this:image

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:ChangeChartTypeMenu





Then choose a Clustered Bar Chart from the Change Chart Type dialog box:InsertBarChart

Your chart will now look like this:image

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


SPECIAL - SAVE 10% until July 20th. Use code EDT.


Maximum = 11

Major = 1

SNAGHTML51a0ee8

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):image

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

Maximum = 5

Major unit = 1

Major tick mark type = None


SPECIAL - SAVE 10% until July 20th. Use code EDT.


SNAGHTML51feb43

Your chart should now look like this:image

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

SNAGHTML5273398

Then choose the Line Color Options for this axis and set the Line Color to “No Line”SNAGHTML5293b46

Your chart will now appear as follows:image

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:image

Your chart is now really close and will look like this:image

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

SNAGHTML530049c

Your chart should now look like this:image

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:image

Your final Likert chart will now look like this:image





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:image

 

Lets compare the two charts side by side:

imageimage





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





19 COMMENTS

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

    • Hi Micahel,

      Glad it helped and thanks for the nice comment.

      Do you mean labels or vertical axis categories?

      Please advise.

      Steve=True

        • 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

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

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

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

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

LEAVE A REPLY

Please enter your comment!
Please enter your name here