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
 

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 }

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:


Related pages


graph charts in excelexcel 2013 stacked bar chartpaste special exceldefine column heading in excelremove duplicate data excelcomplex excel formulafree random name drawing generatorvba lookup functionexcel vba templatecheck mark for excelvlookup conditional formattinglearn formulas in excelexcel formula will not copy downvirtual lookup excelhow to round off numbers in excel formulavlookup on excelexcel offset from current celluse of vlookup and hlookupexcel lookup not workingexcel lottery number generatorhistogram chartsexcel eliminate duplicate rowseasy vlookup formulaexcel using if statementsrandom name selector for teachersadding columns excelprobability formula in excelexcel iferror vlookuphow to use a vlookuptick in a box symbolshortcut of excellogical formulas in excelexcel create macropower query in excelexcel lottery checkerrevenue model template excelexcel vba cells offsethow to build a macro in excelrandomly generate numbers in excelif statements excelreverse lookup excelmatch index vlookupbin range histogram excelmicrosoft excel query tutorialvlookup testexample vlookup formulastacked chartscreate a histogram in excel 2010name picker generatorhow to draw a line graph in excel 2010excel formulas trickshlookup in excelexcel string compareexcel remove duplicate rowshow to find duplicates in 2 excel spreadsheetsexcel matching data in two columnsvloopup exampleucla student discountsexcel vlookup countcompare 2 columns in excelif cell is not blank excelhow to remove duplicate values in excelexcel sigma functionhow to label columns in excelprimo pdf converterhow to create if statements in excelsensitivity analysis excel templateduplicacy meaningexcel eliminate duplicateswhat is the use of vlookup function in excelchart templates excelmultiple vlookup criteriarandom number picker excelexcel filter unique valuesbuying microsoft excel
\n