Overview
This guide explains how to create graphs and charts in Microsoft Excel using survey response data. It covers how to organize data, choose an appropriate chart type, create the chart, and customize basic chart elements.
Steps:
Step 1: Arrange Your Data
Before making a chart, Excel needs your data organized in one of the two ways:
- Option A: Individual Responses (Raw Data): Put the 16 Questions as columns across the top (Columns A to P) and the 12 Respondents as rows going down (Rows 2 to 13).
- Option B: Aggregated Responses (Recommended): If your questions are Multiple Choice or Rating Scales (e.g., 1–5), it is much better to create a small summary table for each question.
- Example: Column A lists the answers (e.g., Yes, No, Maybe) and Column B lists the count of how many people chose that answer.
Step 2: Create the Chart
Once your data is highlighted, follow these steps:
- Select the data you want to graph (including the text headers/labels).
- Click the Insert tab on the top ribbon.
- Click Recommended Charts. Excel will analyze your data and suggest the cleanest visual layouts.
- Select the chart style you prefer and click OK.
Other Chart Options
If you do not want to use one of Excel’s Recommended Charts, you can choose a specific chart type directly from the Insert tab.
Common options include:
- Column or Bar Chart: Best for comparing survey responses or categories.
- Pie Chart: Useful for showing how responses make up a whole when there are only a few answer choices.
- Line Chart: Best for showing changes or trends over time.
- Scatter Chart: Useful for comparing the relationship between two sets of numerical data.
Select the chart type that best represents the information you want to show.
Step 3: Choose the Right Chart Type
Since you have 16 separate questions, do not try to cram all 16 questions into a single chart. It will be unreadable. Instead, make a few small charts or one chart per question based on the question type:
- For Multiple Choice: Use a Bar or Column Chart to compare how many respondents selected each answer. A Pie or Doughnut Chart can also work when there are only a few answer choices, and you want to emphasize percentages
- For Rating Scales (Scale of 1–5 / Likert Scale): Use a Clustered Bar Chart or Stacked Bar Chart. This lets you easily see which answers received the highest or lowest ratings.
- For comparing multiple questions at once: If Questions 1 through 5 all use the same 1–5 scale, you can highlight all of them together and use a 100% Stacked Bar Chart to show the distribution of answers for each question side-by-side.
Step 4: Customize Your Chart
After creating the chart, click on it to open the Chart Design and Format options.
You can:
- Change the Chart Title to match the survey question.
- Add Data Labels to display the number or percentage of responses.
- Add Axis Titles when the meaning of the horizontal or vertical axis is not obvious.
- Change the Chart Type if another visual would represent the data better.
- Use Switch Row/Column if Excel displays your categories and responses in the wrong direction.
Keep the chart simple and remove unnecessary elements that make the results harder to read.
Step 5: Move a Chart to a Different Worksheet
If you want to keep your charts separate from your survey data, you can move a chart to another worksheet.
- Right click the chart you want to move.
- Select Move Chart.
- Choose one of the following:
- New sheet: Places the chart on its own chart sheet.
- Object in: Moves the chart into an existing worksheet.
- Click OK.
Tip: Keeping charts on a separate sheet can make your workbook easier to organize and present.
*What About Written Responses?
Open-ended responses usually cannot be graphed directly.
If respondents typed written answers, review the responses first and group similar answers into categories. You can then count how many responses fall into each category and create a Bar or Column Chart from those totals.
If the written responses are all unique, it may be better to summarize or quote them instead of creating a chart.
Make Your Chart Accessible
When creating charts:
- Give the chart a clear and descriptive title.
- Use data labels when they make the chart easier to understand.
- Do not rely only on color to communicate differences.
- Make sure labels and text are large enough to read.
- Use sufficient contrast between chart elements and the background.
- Add Alt Text when the chart will be included in a document, presentation, or other digital material.
Quick Pro-Tips for Survey Data
- Use CountIF: If you have raw data and need to count how many times "Yes" appears, use the formula =COUNTIF(B2:B13, "Yes") to quickly build your summary tables.
- Keep it clean: Double-click the Chart Title to change it to the actual survey question so your audience immediately knows what they are looking at.
- Add Data Labels: Right-click on your chart bars or pie slices and select Add Data Labels to show the exact number of respondents (out of 12) directly on the visual.