I have a habit of creating charts in Excel that include both a bar chart and line chart together. They mystify people as there is no option in Excel do do such a thing. Oh, but indeed there is. Follow along my dear friends and I’ll show you.
First of all, create a bar chart as you normally would. A column for labels, a column for the first set of numbers, and a column for the second set of numbers.
Now the tricky part.
Click inside the chart on one set of data. You’ll see here that I clicked on the blue bars. All the blue bars have the little circles on the corners to indicate that I have indeed selected all of them.
Now, in the top excel menu, click the option that let’s you choose the design of your charts. In this case, it’s simply called design.
At the very left of my menu, there’s now an option that says change chart type. Click!
This opens up all the chart types and I’m going to click on the line chart. Now click OK.
And you’re done! You should now have a chart with both lines and bars in it.
This same process will work for any number of bars or lines. You could create a chart with one set of bars and 14 lines, or 3 sets of bars and 1 line. In the end, all that matters is that the chart is readable and doesn’t misrepresent the results.
Enjoy!
Very often people create this kind of chart to try to show the relationship between the two variables, when in fact, an X-Y Scatter chart shows the relationship best.
Yup, one of the most common uses I have for this chart is to show average sentiment and volume of chatter by month, silly to do in a scatterplot when you’re really trying to show trends over time.
Sure, I guess it always depends on what you’re trying to show.