How to show quiz results in an Excel chart

Excel 2016
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 Stacked Bar:

Stacked Bar in Excel 2016

Excel creates the bar chart:

Stacked bar chart in Excel 2016

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

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

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.