These days, everyone and their mother has “Microsoft Excel” listed as one of their skills on LinkedIn. So you probably already know that Excel is a powerful tool for data analysis, manipulation, and visualization. Working in Marketing Analytics, I literally use Excel every single day. (In fact, you might have noticed that I’ve been AWOL on the blog recently — I’ve been so busy with work that all I can even think about is Excel.) Some people are “Excel Wizards”, using tons of shortcuts and fancy formulae. If that’s you, more power to you! But in the end, it’s the output that matters: how will you communicate your work to an audience of colleagues or clients? Often you’ll present your findings in a series of charts or graphs. And when I see a professional slide deck full of ugly, unpolished, or even default-style Excel bar graphs (or any graphs), it drives me up the wall.
I want to help you.
This post serves as a quick tutorial on how to format your Excel bar graphs to make them awesome. Here’s where we’re headed in this post:
Why bar graphs, when there are so many other graphs out there? First, because they’re the most common type of graph I’ve seen in my short career so far. I’ll be focusing on stacked bar graphs in particular. If great bar graphs come in handy for me, hopefully they will for you! And second, because “How should I format my bar graph?” is one of my FAQs from colleagues at work. Now I can just send them to this link for a quick tutorial. (Hello, work friends!)
I’m also going to assume a basic knowledge of Excel, meaning I won’t necessarily provide specific paths to every command. (For the record, I’m using Excel 2013.) If you need clarification, please comment below! I’d love to help you out!
Let’s say you have some data.
On one axis of your graph, you’ll want to plot the time dimension. Traditionally, this is on the horizontal axis. And on the other, you’ll plot your values — in this case, number of baked goods. (Waddup Tim Hortons reference!)
If you plot this data as is, your stacked bar graph will look something like this:
There’s one thing you may notice already: the colours aren’t Excel’s default palette! That’s because I’ve created my own custom colour palette. Plus, my standard font colour is black, rather than the default grey. I would highly recommend deciding on a single, cohesive colour palette to represent your company or team, and use it consistently for all of your work moving forward. Check out Microsoft’s tutorial on creating “themes” here.
I’ve also deleted the horizontal lines that appear as a default option. I rarely keep them, as they just add clutter to an otherwise polished slide.
You may be wondering why I would include the “Total” bar in yellow — doesn’t that make the bar double the size it should be? Yes, it does. But as you’ll see, this is one of the tricks of the trade. Make sure you include a total in your data for these kinds of Excel bar graphs!
Adding Your Labels
Next, we add data labels. Click the + sign just outside the top right-hand corner of the graph and select the data labels option. Once they’ve appeared, you can play around with their placement. For example, I’ve moved the data labels for the “Total” bar to the “Inside Base” option. I also made those labels bold for emphasis.
Right now, the labels look pretty cluttered, and that yellow “Total” bar just looks weird. But hang tight — all will be revealed.
Making the “Total”, a Total
Let’s deal with those pesky yellow bars. Right-click on any one of them, then choose “Format Data Series”.
The panel on the right-hand side will allow you to make these yellow bars invisible. We’ll also use the series options to adjust the space between the bars. I usually set this to 50% or 75%, depending on the number of bars across the axis.
To complete the illusion of a built-in total label, we’ll manually adjust the maximum value of the y-axis:
Looking good! We’ve got labels on all of our bars, so we don’t really need the vertical axis labels anymore. But we don’t want to delete the axis so it doesn’t mess up our formatting! Rather than deleting those unnecessary y-axis labels, we’ll simply hide them in the Format Axis panel:
Finally, we have to remove the legend entry referring to the “Total” bar. Just select it and tap Delete.
The Finishing Touch
There are lots of little formatting tweaks you can make at this point, so that your graph matches the aesthetic you’re seeking. For example, you could increase the font sizes, move the legend to another side, add a title, and lots more.
My final tweak will be changing the font colour of some of the labels so that they stand out against the background in my custom colour palette. I’ll also remove the border around my chart area.
And there you have it!
Feel free to adjust to your liking, but this is a great starting point for any beginner working on their first awesome Excel bar graphs. Friends and coworkers, if you’d like more tips and tricks to take this graph to the next level, let me know! I literally already have screenshots for Part 2 of this post, haha!
What are your tips for making beautiful, polished, awesome Excel bar graphs?