Several Data Interpretation Problems with Stacked Bar Chart and Their Solution
You may assume that stacked bar chart will work very well in most of data analytics scenarios.
Well, the answer is no.
Here are few exceptions:
Look at the following chart. Showing consumption of apples by variety in USA from year 2015 to year 2020. The total height of bars is easy enough to compare, but how about the grey bars for Pink Lady? Are those getting longer or shorter for year 2018, 2019 and 2020? How sure are you?
Stacked Bar Chart Discrepancies
The chart above does a great job of displaying the total consumption of apples over time, but in breaking the data down by variety, it’s hard to truly comprehend the changes for any variety except Honeycrisp because they are erratic and lack a common horizon. Try to compare the consumption of Pink Lady apples through time. Can you precisely compare Pink Lady apples in 2018 vs 2019? Both bars looks same right?
Challenges with Stacked Bar chart?
- It is less straightforward to gauge other divisions or comparisons using the secondary categorical variable.
- Stacked bar chart takes too much effort to understand the trends of varieties being displayed in the middle.
- More erratic and lack a common horizon.
Solution: Stacked Grid Chart
We can solve these problems by using multiple single bar charts. Try splitting sub categories out into multiple mini charts, each comparable and showing their bars from a common horizon.
We have, of course, taken the focus away from the total consumption of apples by presenting the data this way, but there is a way to better represent this data and it will create a better visual story as compared to ancient stacked bar chart.
Stacked Grid Chart will solve the following issues:
- In this chart subcategory comparison is straightforward.
- Easy to understand the trends of subcategory type.
- Each subcategory showing their bars from a common horizon.
- “Total consumption of apples over time” and “Consumption of apples by apple variety over time” are easy to interpret.
Data looks like as following:
How to create Stacked Grid Chart in Google Sheets?
You need to install ChartExpo for Google Sheets Add-On from the Google store.
Once installed.
Follow this video to create this chart.
- First opened Google Sheets in Chrome browser, clicked on Add-ons > ChartExpo > Open
- Next, clicked on PPC Charts and then clicked on Matrix Chart.
- Copy the sample data in sheet and select sheet from Sheet Name dropdown.
- Select a column from Metric dropdown. In our case Value (numerical value) column is selected as metric.
- Select a column from Dimension dropdown. In our case Category column is selected as dimension1, Sub-Category column is selected as dimension2 and Period column is selected as dimension3 (You can also compare current & previous period data.)
- Finally, click on Create Chart button.
Chart will look like as following:
You can create data visualization with zero coding experience with just a few clicks using ChartExpo for Google Sheets Add-On. This tool offers convenience since you can install it on Google Sheets and work from one place.
ChartExpo for Microsoft Excel & Office 365 Add-In also offers visualization library makes it easy to gain insights from a spreadsheet.
