Last Updated on November 19, 2018 by Dave Farquhar
My boss’ management is clamoring for metrics. They want to know, at a glance, what we’re doing and how far along we are. Sounds like a job for stacked bar charts in Excel to me. So here’s how to create a stacked bar chart in Excel.
Figuring out a way to track our progress was fairly easy. Figuring out how to make Excel display that chart in a meaningful fashion… Well, that took about five hours. I’ll try to make it easier for you than it was for me.
Nothing makes it simpler than an example. So let’s say we’re fixing up four houses. One house is on Washington Street, one on Adams, one on Jefferson, and one on Madison. Each one needs a roof, painting, plumbing, and electrical work. Open up Excel, and enter in the following.
A value of 0 represents a subproject, user story, or sprint (whatever terminology you use) that isn’t started. A value of 25 represents one that’s finished, and 12.5 represents one in progress. This is adequate to feed some numbers into Excel to get a chart for a visual. You can decide how you want to weigh the separate components of your project–for now, just get some data in there so you have something to chart.
My goal in this data is to have four things that would total up to 100 if all of them were finished, if that isn’t clear. You can use the same methodology when you make your own charts using this method. Since I have four items, a completed subproject is worth 25 each. If you had 20 items, each completed subproject would be worth five each. That’s assuming each item is roughly equal in effort. You may wish to make some items worth more than others, if they are more work.
After you get this chart built, try plugging your own data into it and adjusting it and watch the results.
How to create a stacked bar chart in Excel
Now that you’ve entered your data, it’s time to chart it. Highlight your data, and click on Insert. Click the little arrow under the little column button and select the Stacked Column option. You may have to hover your mouse over that option until the caption comes up. That little arrow is easy to overlook, and it’s where most of Office’s more obscure power hides. Remember that arrow.
Now you’ll have a stacked bar chart with four bars, one for each project. Each bar will have a blue, red, green, and purple portion, representing a portion of the project. You can see that Washington is furthest along; Adams is barely started, and Jefferson and Madison are neck-in-neck but the remaining work is different.
The chart tells a story at a glance, which is what good metrics do. This may be good enough to use as-is. If that’s good enough for you, call it done, say you know how to create a stacked bar chart in Excel, and plug in your own data to replace mine.
But if you want to label each component, on each bar, you can do that too.
How to label the chart
Click the blue portion of one of the bars, then right click and select Add Data Labels. Then repeat for the red, green, and purple portions of the labels.
To me, the numbers are meaningless. I want to know what sprint each bar represents. You can make each bar component display the series title in it, but there’s a problem with that option. If you have a project with multiple portions still at 0%, they just stack up on top of each other like a jumbled mess.
So highlight the row of numbers next to “roof,” right click and select Format Cells. Under category, highlight “Custom,” and then, under Type, enter the following: “Roof”;;;
Yes, that’s the word “Roof” in quotes, followed by three semicolons.
This causes Excel to display the word “Roof” when there’s a positive number, and nothing when there’s anything else in the cell.
Repeat the process above for painting, plumbing, and electrical.
Now the chart should look like this.
It looks good, except for the left axis.
Cleaning up the left axis
Click the left axis, then right click and select Format Axis. Click Number, then under category, select Number. Under Decimal Places, you can specify the number of decimal places to display. I always choose 0.
So now you have a chart that visually displays how far multiple similar projects are from completion in a clear, uncluttered fashion.
Re-size it until it looks good by clicking on one of the corners of the chart and dragging it to make it bigger. You want the chart to be big enough that the bars engulf the text, instead of letting the text sprawl out into the whitespace.
But that’s it. Now you have a chart suitable to put in a Powerpoint presentation, e-mail, Word document, or however you deliver your metrics.
The problem with 3D charts
Notice I selected a 2D chart, rather than the nicer-looking 3D chart. 3D charts look nicer, but my college statistics professor hated them. He cited them as a very good, subtle way to lie with statistics. If you have a 3D bar set at 100%, followed by one at 90%, followed by one at 98 or 99%, the third bar looks like it’s also 100%. In 2D, you can still see the visual difference. If it’s clarity and honesty that you’re after, avoid the 3D charts.
That’s how to create a stacked bar chart in Excel.
Also, if you end up with unwanted trailing zeroes in your chart, here’s how to fix that.
One more thing
Just one more thing. If you remember nothing else from this, remember that step where you clicked the little arrow in the corner to bring up the stacked columns option. Any time you can’t find something in Excel or any other Microsoft Office application, it’s probably hiding under one of those arrows. Look for something related to whatever it is you want to do, and see if its button has that arrow in it. If it does, press that arrow. Chances are the functionality you want was hiding there.
Knowing this trick can make you look like an expert, even when you’re not, and can help you figure out other things, not just how to create a stacked bar chart in Excel.
Other Excel problems and solutions
There are lots of Excel problems that have relatively easy solutions, even if the solutions aren’t obvious. Here are a few more.
- Superscripting in Excel
- Data analysis with Countif
- Removing the last octet of an IP address in Excel
- Extracting data in Excel
- What to do when Excel won’t scroll
- Excel row height
David Farquhar is a computer security professional, entrepreneur, and author. He started his career as a part-time computer technician in 1994, worked his way up to system administrator by 1997, and has specialized in vulnerability management since 2013. He invests in real estate on the side and his hobbies include O gauge trains, baseball cards, and retro computers and video games. A University of Missouri graduate, he holds CISSP and Security+ certifications. He lives in St. Louis with his family.