How to plot survey data in a bar chart

Here we have data from a survey we ran recently.

Let’s plot the data in a chart.

The first step is to build a summary table.

First, I’ll convert the data to an Excel table.

This step is not required, but it will make the formulas easier to write.

For the first formula, I need to count all responses.

Since we have a table, I can use the ROWS function with the table name.

Now, to count the responses already in column E, we’ll use COUNTIF function.

I can start with the total count, then subtract everything already counted above, using the SUM function.

Now let’s calculate percentage.

Now we have a clean summary to use for the chart.

On the insert tab of the ribbon, click Recommended Charts.

A bar chart is a good choice here because there is plenty of room for each response.

Now overall this chart looks pretty good, but we can tidy things up and simplify.

For the title, we can use a formula.

Generally, these bars will look better if they’re thicker.

Double click to select and launch the Format Task Pane.

A gap width of about 80% usually works well.

They are really just clutter that we don’t need.

Finally, bar charts are easiest to read when the data is sorted.

If I sort the data in ascending order, Excel plots largest to smallest.

Now the bars are easy to compare.