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?
Best Answer
Simply add dummy data including all the values that you want in your chart:
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)
):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:
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).