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


wingdings 2 charthow to insert a check mark in excelmatching columns in excelhow do i compare 2 columns in excelsort duplicatesvlookup dateslibrary hours uclainventory management xls filesconditional if statement excelusing vlookup and if function togetherwhat is meant by vlookup in excelinterviercheck mark symbol microsoft wordvlookup vba excelvlookup errorhistogram generator excelexcel fuzzy matchingexcel ctrl hexcel modeling tutorialmba for dummiesifs formula excelvlookup backwardsvba lookupexcel combine tablesexcel replace duplicates with blanksthe goal eliyahu goldrattpurge excel filesymbols in microsoft exceldefinition of vlookup in excelcomparison bar chart in excelcountif command excelucla accounting departmentexcel most useful formulascustom number formats in excelexcel logical formulaexcel std deviationadvanced excel 2007 formulas with examplestick sheet exceldget excelexamples of checklists in excelhow to remove shading in excelvlookups on excelin excel by default numeric values appears inhow to generate random numbers in excel without duplicatesnormdist excel 2010how to operate vlookup in excelexcel compare multiple columns for matchesexcel index match multiple criteriawhat is ascending order in excelanderson mba tuitionmicrosoft excel formulas tutorialbuilding excel macroshow to remove first two digits in excelvlookup blankmatch command excelvlookup shortcutexcel vlookup function examplevlookup conditionexcel offset indirectexcel 2010 vlookup multiple criteriahow to insert brackets in excelxcelsius tutorialhow to subtract columns in excelshuffle namesexcel class schedule templateword tick mark symbolexcel macro worksheetduplicate remover for excelvlookup excel 2010 exampleexcel dedupe formulashortcut to excellenovo laptop for studentsvlookup between two workbooksvlookup for multiple valuesduplicate numbers in excel
\n