Here we data that shows answers to the survey question “How do you rate your skill with Excel?” Let’s plot this data in a bar chart. A bar chart in Excel shows horizontal bars, and it’s a good option when you want to compare data with longer labels, since there is plenty of room for text to the left of the bars. You can see we have the count for each response, with a little over 1900 responses total. I’m going to add formulas to calculate percentages as well, so we can work with both in the chart. Now let’s plot just the counts. I’ll select only the count data, excluding totals, then click Recommended charts. Then I’ll select the bar option. I then just have to select the bar chart and click OK. This is a good start. Now let’s add a title. Now, since we already have a good title in cell B2, we can use a little formula trick to bring that title into the chart. Just select the title, type “=”, and click B2. Excel will add the reference and pick up the title. Now let’s make the bars thicker. Double click a bar to open the Format Task Pane, then try a gap width of about 60% in the Series Options area. Bar charts are easier to read when the bars are plotted from largest to smallest, so let’s do that next. Now, if I sort largest to smallest, Excel plots the bars in reverse order. This seems wrong to you, but it makes some sense if you notice that the first items on the vertical axis start at the origin, and move out from there. In other words, the origin is the beginning. A simple way to handle this is to just sort the data in the opposite order, in this case, smallest to largest. Excel will then place the longer bars at the top of the chart. OK next, let’s change this chart to plot percentage values instead of counts. An easy way to do this is to select the chart, then simply move the data series on the worksheet over the percentage values. Notice the value axis picks up the percentage number format, because number formatting is linked to the source data, as we can verify in the Format Task pane. Finally, since this chart is very simple, let’s remove some unnecessary visual clutter. I’ll select and delete the gridlines, and the value axis. And then I’ll add data labels. Now we have a simple bar chart that clearly represents the data.
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.
