How to show Likert scale chart with extra neutral

Sometimes only positive responses are important (yes! negative ones are often neglected); in other cases, only negative ones, to work on mistakes, correct plans, or change the strategy. Many people believe that neutral assessments do not carry any useful information; however, according to experts, they are just as important as negative and positive ones.

You can create an Excel chart with neutral values, without them, or display them separately on the chart:

See more about visualizing survey results.

The main feature of this chart is the positioning of bars with negative and positive values relative to the center. A such chart is called a divergent bar chart, also known as a centered stacked bar chart, while the neutral values are displayed separately.

For example, the survey results are presented in the following table:

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

   1.   Add additional data to the chart data

For example, the survey results are presented in the following table:

   1.1.   Move the neutral values to the end of the table.

   1.2.   To position the chart relative to the center, calculate the maximum of all negative values:

Note: See below how to create an array MAX () formula.

   1.3.   Add a new column to calculate the difference between the calculated maximum and the sum of negative values:

   1.4.   To position the neutral values at the right, calculate the maximum of all values and add some amount for space between two parts of the chart (in this example, 5):

   1.5.   Add a new column to calculate the difference between the calculated width of the first part of the chart:

   2.   Create a regular bar chart using all data

   2.1.   Select the data range (for this example, B2:I7), then on the Insert tab, in the Charts group, click the Insert Column or Bar Chart button:

From the Insert Column or Bar Chart dropdown list, under 2-D Bar, choose the Stacked Bar chart:

Excel creates the bar chart:

   2.2.   Switch rows and columns by doing one of the following:

• On the Chart Design tab, in the Data group, click the Switch Row/Column button:
  
• Right-click on the chart plot area and choose Select Data... in the popup menu:
  

  In the Select Data Source dialog box, click the Switch Row/Column button:

  

Excel changes the chart:

   3.   Format the horizontal axis

To move the horizontal axis to the correct position, do the following:

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

   3.2.   On the Format Axis pane, on the Axis Options tab, in the Axis Options section:

• Optionally, under Bounds, correct Minimum and Maximum values for the axis,
• Under Vertical axis crosses:
  • Check the Axis value option,
  • Type the maximum value that you calculated in step 1.1.:

   4.   Format the vertical axis

To move labels to the correct position in the reverse order, do the following:

   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, select the Categories in reverse order checkbox:
  
• In the Labels section, from the Label Position dropdown list, choose Low:
  

   5.   Hide the empty data series

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

   5.2.   On the Format Data Series pane, on the Fill & Line tab:

• In the Fill section, select the No fill option,
• In the Border section, select the No line option:

   5.2.   Repeat the previous steps to hide the second empty data series:

   6.   Add data labels

To add data labels to the chart, do one of the following:

• Click on the Chart Elements button, select the Data Labels list, then select the position of the labels:
  
• On the Chart Design tab, in the Chart Layouts group, click the Add Chart Element button:
  

  From the Add Chart Element dropdown list, choose Data Labels, then select the place for the labels:

  

   7.   Format data labels

To format data labels, right-click on any of them for each data series, then select Format Data Labels... in the popup menu:

On the Format Data Labels pane, on the Label Options section, in the Label Contains group:

• Check the Value From Cells option,
• Choose the appropriate cells in the Data Label Range dialog box:
  
• Unselect all other checkboxes.

   8.   Format the chart

Optionally, hide the horizontal axis and type the chart title. Also, fill the data series as you prefer (see more about filling options in Microsoft).We recommend creating your own Color Theme and using it to avoid any color misunderstanding after any chart changes.

For example:

Note:

• In many cases, the total number of "Satisfied"/"Unsatisfied" ("Agree"/"Disagree") is more important than the individual "Very satisfied" ("Strongly agree") and "Very unsatisfied" ("Strongly disagree") values. See how to add subtotals for more details.
• Of course, positive or negative opinions are more important than the absence of opinions. Therefore, indicating the neutral category in gray, faded color is recommended. Often the neutral category is integrated into the chart: