Flat 2D graphs are boring, 3D graphs are hard to read. 2D graphs made to look 3D are the best of both worlds. We’re going to do is add a 3-color linear gradient to the bars and then if desired throw in a background texture to add a little more depth.
Experiment with the colors yourself and the background texture to get something that appeals to you, but most importantly makes your data easy to read so you can make your point with the graph.
And yes, I know, Excel calls them charts but they’re graphs, darn it!
This tutorial was created using Office 2011 for Mac.
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.
1. Remove Chartjunk – gridlines and chart border
Right click ChartSelect – Format Chart AreaSelect – Border ColorSelect – No Line
Click on chart gridlinesHit Delete
Logic: Removing Chartjunk makes your data stand out.
2. Use Soft Gray Lines for the Axes
Right click vertical AxisSelect – Format AxisSelect – Line ColorSelect – Solid lineSelect – Color (the paint bucket)Select – the third shade of gray in the left column
Select the horizontal axisHit F4
Logic: Axes are rarely the focus of what you’re trying to convey. Deemphasizing them helps your data stand out.
4. Move the Legend to the Bottom of the Chart
Right click the legendSelect – Format LegendFor Legend Position, Select – Bottom
Logic: Having the chart on the right side is creates a ton of unused white space. Move it to the bottom to more effectively use the chart area.
5. Add an Appropriate Title
Click on the chartIn the Chart Tools section of the ribbon, Select – LayoutSelect – Chart TitleSelect – Above ChartIn the main ribbon, decrease the Chart Title Font Size to 16 pt
Logic: The title tells you the purpose of the chart.
6. Change the Default Data Bar Colors
Right click one of your data seriesSelect – Format Data SeriesSelect – FillSelect – Color (the paint bucket)Select – any color other than the default oneRepeat this process for the second data series, but choosing a different color
Logic: There’s nothing particularly wrong with the default Excel colors, but most people who create charts are too lazy to change them. Changing your data bar colors will differentiate it from the vast majority of other charts out there.
7. Add a White Border to the Data Bars
Right click one of your data seriesSelect – Format Data SeriesSelect – Border ColorSelect – Color (the paint bucket)Select – White
Click your other data seriesHit F4
Logic: The white border around the data bars makes it easier to see where the stacked data points are separated.
8. Add Axes Titles
Click on the chartIn the Chart Tools section of the ribbon, Select – LayoutSelect – Axis TitlesSelect – Primary Vertical Axis TitleSelect – Rotated TitleClick on the new title and give an appropriate name
Logic: You should only add axis titles if it provides additional, relevant information. In this example, the horizontal axis is clearly months, so a title is not necessary
9. Add Data Labels
Click here for a tutorial on how to add total data labels to stacked bar chart
Logic: Add data labels where you believe the user should focus their attention. In this example, I’ve assumed that the total of the stacked bars is more important than the individual components.
10. Update Vertical Axis Major Unit Intervals
Right click vertical AxisSelect – Format AxisFor Major unit, Select – FixedFor the Fixed units, increase it from 200 to 300
Logic: Sometimes the number intervals provided by Excel make your axes look too dense. Reduce the amount of numbers in the axes to drive attention toward your actual data.