How to Make Your Excel Bar Chart Look Better

by Matthew Kuo on April 4, 2013

in Excel, Visual Design

To learn more about Excel, go to the organized listing of all my Excel tutorial posts or review the most popular Excel books on Amazon
 

One of the most important skills in Visual Design is being able to improve the formatting of your Excel charts.  Even if your displaying the exact same data set, the formatting of a chart can make a huge difference in terms of effectively communicating your data.

For any given chart, there is always some level of artistic interpretation in your formatting decisions.  The key to Visual Design is that you should have a logic behind every decision you make.  Just note that, the same logic won’t apply in every situation.  Always keep in mind how your audience will use the data and choose your formatting accordingly.

We’ll use this simple stacked bar chart showing two data series over a period time  as a baseline.

BarChart0

1.  Remove Chartjunk – gridlines and chart border

BarChart1

Right click Chart
Select – Format Chart Area
Select – Border Color
Select – No Line 

Click on chart gridlines
Hit Delete

Logic: Removing Chartjunk makes your data stand out.

2.  Use Soft Gray Lines for the Axes

BarChart2

Right click vertical Axis
Select – Format Axis
Select – Line Color
Select – Solid line
Select – Color (the paint bucket)
Select – the third shade of gray in the left column

Select the horizontal axis
Hit F4

Logic: Axes are rarely the focus of what you’re trying to convey.  Deemphasizing them helps your data stand out. 

3.  Widen the Data Bars

BarChart3

Right click one of your data series
Select – Format Data Series
For Gap Width, decrease it to 50%

Logic: Wider bars make it easier to visually compare successive periods

4.  Move the Legend to the Bottom of the Chart

BarChart4

Right click the legend
Select – Format Legend
For 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 chart
In the Chart Tools section of the ribbon, Select – Layout
Select – Chart Title
Select – Above Chart
In 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 series
Select – Format Data Series
Select – Fill
Select – Color (the paint bucket)
Select – any color other than the default one
Repeat 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

BarChart7

Right click one of your data series
Select – Format Data Series
Select – Border Color
Select – Color (the paint bucket)
Select – White

Click your other data series
Hit F4

Logic: The white border around the data bars makes it easier to see where the stacked data points are separated.

Conditional Edits – Make these changes only if necessary

8.  Add Axes Titles

BarChart8

Click on the chart
In the Chart Tools section of the ribbon, Select – Layout
Select – Axis Titles
Select – Primary Vertical Axis Title
Select – Rotated Title
Click 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 Axis
Select – Format Axis
For Major unit, Select – Fixed
For 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.

{ 7 comments… read them below or add one }

site May 4, 2014 at 7:39 am

Greetings! Very helpful advice within this article! It’s the little changes that will make the biggest changes. Many thanks for sharing!

Reply

Leslie October 14, 2014 at 9:16 am

This article really made a diffeence in the way the information was presented. Same data. Different layout. What an improvement! Thank you very much for these tips. I have saved them to keep them on hand for future presentations.

Reply

Priyanka January 15, 2015 at 1:41 am

This was an awesome article, it gave an instant makeover to my otherwise dull/drab looking data. Thanks a ton !

Reply

cristina February 23, 2015 at 8:00 am

I’m using my chart as a gantt chart so my bars display horizontally. I’m having a problem with the text in my vertical axis, which displays project names, is cut off. How do I adjust the width of the vertical access so that the complete project name is displayed?

Reply

Caetano Pires March 30, 2015 at 5:59 am

Hi Mathew,

Congratulations on such a value based tips. Applied most of the tips and every change applied made my charts visually appealing.

Thank you so much.

Rgds,
Cajjie

Reply

Rosa April 21, 2015 at 10:23 am

i have a bar stacked chart and a line chart in one graph. i have names at the bottom. i need to put Quarter Labels, Q1 14, Q2 14 Across the bottom how do i do that without making it too busy.

thanks

rosa

Reply

Jiwan July 8, 2016 at 1:41 pm

Hi, i would like to have my graph colored in different section horizontally. for example it should be colored red when between 1-50, Orange 51-100, Blue 101-150 and green 150+.
all this limit is on Y axis.

Reply

Leave a Comment

{ 1 trackback }

Previous post:

Next post:

\n