How to make a bar graph in excel

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.

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:

Lea Pica - Stacked Bar Chart - Side by Side Bar Chart

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.
Related text  How to be vegan

1.  Remove Chartjunk – gridlines and chart border

BarChart1

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

BarChart2

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

BarChart4

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

BarChart5

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

BarChart6

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

Related text  How to calculate calories

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

BarChart7

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

BarChart8

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

BarChart9

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

BarChart10

Right click vertical AxisSelect – Format AxisFor Major unit, Select – FixedFor the Fixed units, increase it from 200 to 300

Related text  How to save money fast

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.

Like this post? Please share to your friends: