How to show Likert scale chart (integrated neutral)

Excel 365
When you analyze quiz answers, you need to see the numbers and the difference between all negative and positive answers. This is not easy, if you have more than 3 possible answers - positive, neutral and negative. However, you can create a chart that shows the whole picture:
Chart with quiz results in Excel 2016

The main feature of this chart is the positioning of bars relative to the center.

To create an Excel chart like the one above, do the following:

   1.   Add an additional data to your chart data:

Chart data in Excel 2016

   1.1.   For correct positioning, calculate the maximum of all negative marks plus half of neutral marks (if you don't have neutral marks, calculate the maximum of negative marks):

Additional data 1 in Excel 2016

   1.2.   Add a new column to calculate the difference between the maximum and the sum of negative marks and half of neutral marks:

Additional data 2 in Excel 2016

   2.   Create a plain bar chart using all data (for this example, B2:H7):

   2.1.   On the Insert tab, in the Charts group, click the Insert Column or Bar Chart button:

Insert Column or Bar Chart in Excel 2016

Under 2-D Bar, choose the Stacked Bar Stacked Bar in Excel 2016 chart.

Excel creates the bar chart:

Stacked bar chart in Excel 2016

   2.2.   Select the chart data by doing one of the following:

  • Under Chart Tools, on the Design tab, in the Data group, choose Select Data:
    Select Data in Excel 2016
  • Right-click in the chart area and choose Select Data... in the popup menu:
    Select Data in popup menu Excel 2016

   2.3.   On the Select Data Source dialog box, click the Switch Row/Column button:

Select Data Source in Excel 2016

   2.4.   Click OK:

Switched chart in Excel 2016

   3.   Format the horizontal axis:

   3.1.   Right-click on the horizontal axis and choose Format Axis... in the popup menu:

Format Axis in popup menu Excel 2016

   3.2.   On the Format Axis pane, on the Axis Options tab, in the Axis Options section, under Vertical axis crosses:

  • Check the Axis value radio button,
  • Type the maximum value that you calculated in the step 1.1.:
Format Axis pane in Excel 2016

   4.   Format the vertical axis:

   4.1.   Right-click on the vertical axis and choose Format Axis... in the popup menu.

   4.2.   On the Format Axis pane, on the Axis Options tab:

  • In the Axis Options section, check the Categories in reverse order checkbox:
Format Axis Options in Excel 2016
  • In the Labels section, in the Label Position drop-down list, choose Low:
Format Labels in Excel 2016

   5.   Format the data series:

   5.1.   Right-click on the first data series (in this example, Empty) and choose Format Data Series... in the popup menu:

Format Data Series in popup menu Excel 2016

   5.2.   On the Format Data Series pane, on the Series Options tab:

  • In the Fill section, select the No fill option,
  • In the Border section, select the No line option:
No fill and No line in Format Data Series Excel 2016

Excel hides the first data series:

Chart with hidden data series in Excel 2016

Add any other adjustments you prefer.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.