Here we have data from a survey we ran recently. We’ve got around 3900 responses to the question “What version of Excel do you use most?". 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. Range is the Response column in the table, and we get criteria from column E To get a count for Other, I need a different approach, since Other isn’t actually a value that appears in the table, but rather a bucket of responses not listed above. I can start with the total count, then subtract everything already counted above, using the SUM function. Now I’m going to hardcode this one value here, so I can sort the data later, without worrying about formula references changing. As a quick sanity check, if I now select the counts you can see we have the correct total. Now let’s calculate percentage. This is simply the count divided by total count, locked with F4, and then formatted as a percent. Now we have a clean summary to use for the chart. In this case, I only want to plot percentages, so I’ll hold down the control key, and select the responses and percentages separately. 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. First, I’ll resize and align the chart with the alt key down - this causes the chart to snap to the grid below, and makes it easier to align things. For the title, we can use a formula. Select the title, type equal sign, then click to on the worksheet to pick up the title text. Generally, these bars will look better if they’re thicker. Double click to select and open the Format Task Pane. A gap width of about 80% usually works well. Now to further streamline, I can add data labels to show the percentage for each bar, then I can remove both the horizontal axis, and the gridlines. 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
