How To Make A Bar Graph In Excel

One of the best parts about writing an analytics blog is…analyzing the analytics on the blog. So meta.

Recently while analyticizing, I noticed several internal site searches for "stacked bar chart". Except…they retrieved zero results.

Suddenly, a thought struck me:

Here I am blabbing about the importance of good data viz, and I don't have any tutorials about creating data viz on my blog.

To remedy that, I'm kicking off a new post series called the "Crappy Chart Cure". This series will help you create clear & clean charts that work to support your data story, not bury it under toxic crapola.

And as a devoted listener to the people, I'm starting with…a stacked bar chart!

Note: This post contains a lengthy explanation how and when to use this chart. Click here to skip directly to the tutorial.

Why should I use a stacked bar chart?

Bar charts are the most recommended choice for representing categorical comparisons. Their simplicity allows for instant comprehension of the ranking of data within a category.

They are also extensively used for trending data over time. But what happens when you want to understand the change in composition within that volume, especially over time?

That's where stacked column charts come in handy. They allow you to visualize composition and trending in a clear manner when executed well.

Stacked bar charts are also a fine choice for visualizing voice-of-customer or other survey response data, which will be the subject of a future post.

Now, there is a gaggle of blog posts on creating stacked column and bar charts on the interwebs.

Why read this one?

Because few of them show you when and how to create a stacked bar chart in a brain-friendly way. A way that incorporates data design principles that minimize cognitive load and maximize viewer comprehension.

How to Build a Brain-Friendly Stacked Bar #Chart in Excel #measure #dataviz Click To Tweet

Stacked bar charts, by default in Excel or PowerPoint, create a lot of noisy junk that can interfere with your audience's data absorption and cause their attention to fade. And when not executed carefully, they can create more confusion than clarity.

If we wanted to do that, we could just use tons of bullet points and mission accomplished. In many cases, side-by-side bar charts may be a more viable option for displaying these relationships clearly.

Now, data visualization master Stephen Few of Perceptual Edge, argues that stacked bar charts have a key disadvantage in his data viz tome, Show Me The Numbers. The disadvantage is that it is difficult to compare the segments within each bar because they aren't aligned. However, he does outline an appropriate usage as such:

Despite the disadvantages associated with stacked bars, one type of quantitative message justifies their use: when you wish to display the whole using a unit of measure other than percentage but also wish to provide an approximate sense of the relative proportions of the parts." ~ Stephen Few

And so, I've used regular stacked column charts (or vertical stacked bar charts) in the past to illustrate trends in composition change. Some cases were mobile traffic by device type and site search volume by keyword category.

Now, I gained a revised perspective on stacked bars while indulging in my new favorite data visualization book by the fire, Storytelling with Data, by my professional hero Cole Nussbaumer Knaflic. Definitely pick this read up at your first opportunity!

I had always used regular stacked column charts to visualize three variables (trending, volume and composition), but Cole raises a valid point:

[Stacked vertical bar charts] are meant to allow you to compare totals across categories and also see the subcomponent pieces within a given category. This can quickly become visually overwhelming, however…it is hard to compare the components across the various categories once you get beyond the bottom series because you no longer have a consistent baseline to use to compare." ~ Cole Nussbaumer Knaflic

Thus, I now limit my use of stacked bar charts to 100% only, strategically anchoring the segment of interest or largest segment to the y-axis.

Interestingly, there is a healthy debate occurring between Mr. Few and Mrs. Nussbaumer on this very subject. They recently lunched and discussed use cases for stacked bars, one chart where they encounter a point of contention. Get in on the action on Stephen's post and at Cole's post.

So, as with all data visualizations, exercise caution to ensure you're creating a visual that is easy to quickly gain an accurate understanding.

That includes detoxing the default chart formatting Excel unceremoniously slaps on that bad boy when you click "Insert Chart". One click and your chart is chock full of cognitive-loading junk:

Microscopic font, excessive gridlines and tick mark formatting, colors be like, whaaat? Not only that, this sort of data is likely not best visualized in a stacked bar chart because there are too many segments within each bar. That is where the art of choosing the most appropriate visualization comes into play.

Quick note: This process works in both Excel and PowerPoint. I believe that for presenting data live, native charting within PowerPoint is the gold standard rather than copying and pasting from Excel. You can learn why and how in my post on Perfect PowerPoint Charts.

Enough ruminating, let's do it!

How To Create a Brain-Friendly Stacked Bar Chart in Excel

1. Choose your data set carefully. In this example, I've chosen a general set of website objectives as categories (Purchase, Email Signup, etc), and mapped against technical platform as segments (Desktop, Mobile, Tablet). I've reduced the number of segments from the above monstrosity per Stephen Few's guidelines of maximum of 3 bar segments. Note that this is not real data.
2. Start by sorting your data descending by the member with the largest volume in your data. I like to ground the largest segment in each bar overall to the x-axis. The only exception would be a time-series chart; here, you would use a stacked column chart.
3. Highlight the data table, go to Insert → Chart → 100% Stacked Bar (Or column). I use a stacked bar chart for comparing categories across a single metric, and a column chart for trending data over time.
4. As you can see, Excel is great at adding cognitive-loading junk by default. So, we'll run this bad boy through a quick detox (and I love me some detox!)
5. Chart Border: Click the chart area to select and right-click Format Chart Area –> Border = No Line. Or, hit Ctrl+1 to quickly open the Formatting menu.
6. Font: Increase your category font size to at least 20pt in PowerPoint, or check Print Preview in Excel to ensure readability. Bonus points for choosing a readable, standard font OTHER THAN CALIBRI. Here's why. Segoe and Franklin Gothic are fine alternatives.
7. Gridlines: Click to select the gridlines and Delete to remove them
8. Axes: Click to select each axis, type Ctrl+1 to activate the Formatting menu, go to Fill & Line → Line → None.
   Try to adjust the chart area so your categories appear on one line. Don't sweat it if they're too long.
9. Bar gap width: Click to select all bars –> Right-click Format Data Series. Change Gap Width to 50% per data viz best practices. Let's take a look at where we are.
   
   Ahhhh…doesn't that already feel so much…less pukey? Now we've covered the basics. This is where your specific data story comes into play.
10. Color: Change the whole chart to a baseline gray. Then, select the series that you wish to emphasize and change to a color of your choice. It's best to avoid red and green, and default to using a colorblind-safe blue.
11. Legend: This is one rare case where a chart legend is acceptable, in order to quickly understand connect category to color. I prefer to remove the included legend and instead place color-matched labels directly above each segment:
    
12. Data Labels: Here's the tricky part. I like to use percentage labels since that's the basis of the chart. But if your chart is powered by absolute values, you can only automatically enable absolute labels. If you want percentages instead of absolute values, you'll need to create a data set that calculates percentages instead and reset the chart data selection. Since I purposely am only going to label two bars, I just add it manually as text boxes.

And voilà! Below, is your final 100% stacked bar chart that not only conforms to best practices, but also tells a clear story:

A few notes:

• You'll notice that I selectively labeled the segments that are relevant to my insight; this is a crucial aspect of effective data storytelling.
• Follow the same steps for a stacked column chart when visualizing trended segments. Here, it's especially important to ground the largest or highlighted segment to the x-axis.
• For a vertical stacked bar chart, or stacked column chart, simply select Stacked Column chart. Place the color-coded labels to the right of the most recent bar for fast comprehension.
• If you don't want to repeat all of these cleanup steps the next time around, right-click the chart and select Save As Template. This will save all of your formatting settings for all future 100% stacked bar charts!

And that's it! Because I heart you, I'm giving away a free Excel template for creating 100% stacked bar charts with full instructions. Click the image below to grab your copy:

Charting Caveats

When using 100% stacked bars, you do lose the overall volume context.  If the volume you're comparing in each category is fairly equal, this view may better suit your needs.

However if volume will play a key role in your story, you can add total labels above each bar, or an unobtrusive line chart. Example below:

However, I do not recommend using a dual axis bar / line chart. Dual axis charts can falsely imply correlation or causation when the data points intersect. They're also crowded and noisy. I've rarely seen one that's useful. This will be the subject of a future post.

Digital Analytics Use Cases for Stacked Bar and Stacked Column Charts

• Trends in composition of share of mobile device or internet browser sessions
• Deeper product purchase segmentation (such as Chairs vs Tables within a Furniture category)
• Visualizing responses to voice-of-customer or other survey response data. For Likert-style survey questions, I recommend a diverging stacked bar chart where positive and negative responses are on either side of the zero axis. Stephanie Evergreen has a great post on creating a diverging stacked bar chart in Excel.

Final Thoughts

I believe when used correctly and judiciously, 100% stacked bar charts are a fine choice in your charting toolbelt.

I hope this tutorial served you in creating stacked bar charts and stacked column charts that will help deliver your message in a brain-friendly way.

What types of data do you use stacked bar charts for?

Liked this data viz post? Please take my quick survey on your chart and graph wish list at LeaPica.com/ChartSurvey!

Photo credits:
Homer Simpson: Fox

How To Make A Bar Graph In Excel

Source: https://leapica.com/blog-stacked-bar-chart/

Posted by: hestertoeopla.blogspot.com

Share this post