Excel Pivot chart – displaying values not in the source data

chartsmicrosoft excelpivot-chart

I have data from a feedback survey in an Excel spreadsheet. The questions are rated on your typical Likert scale.

1  Strongly disagree
2  Disagree
3  Neutral
4  Agree
5  Strongly agree

In the main sheet the responses are only recorded as the number. The corresponding text description is by itself in another sheet, like in a database.

So I have

Response ID  Q1  Q2  etc
001          4   5
002          5   5

I'm creating a chart from the main sheet to display the results for each question, but there are no 1 or 2 responses, so the Pivot chart only goes from 3 to 5. Is there a way to still display 1 and 2 even though these values aren't found anywhere in the source data?

Also is there a way to make the label in the chart reference the text description i.e. change "1", "2", etc, to "strongly disagree", "disagree", etc?

Edit: picture of chart
Screenshot of Excel

Best Answer

Simply add dummy data including all the values that you want in your chart:

   spreadsheet with dummy data

Alternatively, use dummy data only for the values that are missing from your real data; i.e., 1 and 2.  Then the Pivot Table and the chart derived from it trivially have all five values (or six, if you include (blank)):

Pivot Table and Clustered Column chart

The above chart is based on 100 data rows: 5 fake and 95 real.  If you have thousands of data points, the bumps (columns) for responses 1 and 2 will automatically get very short.  Otherwise, you can wave your hands and tell your customers that the bumps are an idiosyncrasy of your charting software.

Or, you can select the columns for responses 1 and 2 and format them as white:

      same chart but with columns 1 and 2 colored white

If you do this, you might especially want to insert dummy data only for the missing values (1 and 2), so the proportions of the other columns will be correct.  I don’t know whether it’s easy to do this automatically for all of your questions, but it’s probably possible to script it (in VBA).

Related Question