How to Make Your Excel Line Chart Look Better

by Matthew Kuo on March 26, 2013

in Excel, Visual Design

To learn more about Excel, go to the organized listing of all my Excel tutorial posts or review the
 

The chart on the bottom is Excel’s default formatting while the chart on the top has been heavily updated.  Looking at both charts together, most people would agree that the chart on the top looks better.  The purpose of Visual Design is to explain why the first chart looks better and do so from a scientific perspective, rather than just a personal judgment call.  While there will always be some wiggle room in terms of how you format your charts, there are established visualization principles that can help guide your choices.

LineChart7

LineChart0

The key to Visual Design is that you should always have a logic behind every formatting decision you make.  For example, you would use soft gray lines for your axes because it helps your actual data stand out more.  Please note that none of the rules proposed below are necessarily set in stone; if you can find a justification or logic to format otherwise, you should do so.

The following tutorial is designed for people who are still using the 2010 version of Excel and want to apply Visual Design principles to their line charts.  Please note that for the , they’ve gone even further in improving the default formatting of their charts and a number of the formatting updates below will not be necessary.

1.  Remove Chartjunk – gridlines, chart border, and legend

LineChart1

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

Click on chart gridlines
Hit Delete

Click on chart legend
Hit Delete

Logic:  Any component of the chart that doesn’t convey some sort of information is extraneous.  Removing such components will help the data being presented stand out to the viewer.  The chart border doesn’t serve much purpose.  Gridlines are rarely utilized in a chart and should always be removed or marginalized.  The legend is redundant with the title.

2.  Use Soft Gray Lines for the Axes

LineChart2

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: Any component that is necessary but does not represent the data itself should be de-emphasized.  Using soft gray lines rather than dark ones help the data being presented stand out to the viewer.

3.  Make the Line Wider

LineChart3

Right click line representing the data
Select – Format Data Series
Select – Line Style
For Width, Enter – 3 pt

Logic:  Making the line wider makes it easier to see

4.  Use a Non-Default Line Color

LineChart4

Right click line
Select – Format Data Series
Select – Line Color
Select – Color (the paint bucket)
Select – any color other than the default blue

Logic:  There’s nothing inherently row with Excel’s default color scheme.  The problem is, because so many people use it, doing the same with your chart conveys laziness.  Use a color other than the default to differentiate your chart from 90% of the other charts out there.

5.  Add an Appropriate Title

LineChart5

Select chart title
Click on the chart title again to bring up input cursor
Update Chart Title with a more descriptive name
In the main ribbon, decrease the Chart Title Font Size to 16 pt

Logic:  The title of the chart should concisely present the purpose of the chart.  In this example, we also add a description of the units of the chart into the title.  If that wasn’t included in the title, we’d have to convey that information on the vertical axis.

6.  Add Data Labels

LineChart6

Select the line representing the data
Click again on data point where you need a label
Right click the same data point
Select – Add Data Label

Right click data label
Select – Format Data Labels
Under Label Position, Select – Above
Input Ctrl + B to make the label bold
In the main ribbon, increase label font size to 12 pt

Logic:  Within line charts, data labels can be added to all points.  However, if we don’t want to the chart to become too numbers heavy, we can simply choose to emphasize the peak, trough, or period end of the trend.  In this example, we will assume that we want to emphasize both the peak and end of the trend on display.

7.  IF Specific Data Points are More Important: Emphasize the Line’s Data Points

Option 1

LineChart7

Right click line
Select – Format Data Series
Select – Marker Options
Select – Built-in
For Type, Select  the circle
For Size, Select – 8

Select – Marker Fill
Select – Solid Fill
Select – Color (the paint bucket)
Select – White

Select – Marker Line Color
Select – Solid Line
Select – the same color you used for the main line
Select – Marker Line Style
For Width, increase to 2 pt

Option 2

LineChart8

Right click line
Select – Format Data Series
Select – Marker Options
Select – Built-in
For Type, Select – the circle
For Size, Select – 9

Select – Marker Fill
Select – Solid Fill
Select – Color (the paint bucket)
Select  the same color you used for the main line 

Select – Marker Line Color
Select – Solid Line
Select – Color (the paint bucket)
Select – White
Select – Marker Line Style
For Width, increase to 1 pt

Logic:  For certain charts, the point to point variation of the data is important and will be scrutinized by the viewer.  In that case, emphasize these points to make them stand out.  The examples above are two different ways to do so.

8.  IF the Overall Trend is More Important: Emphasize the Line Trend

LineChart9

Select the line representing the data
Right click the line
Select – Format Data Series
Select – Line Style
Check – the box for Smoothed line

Select the line representing the data
Click again on data point to be edited
Right click data point to be edited
Select – Format Data Point
Select – Marker Options
Select – Built-in
For Type, Select – the circle
For Size, Select – 8

Select – Marker Fill
Select – Solid Fill
Select – Color (the paint bucket)
Select – White
Select – Marker Line Color
Select – Solid Line
Select – Color (the paint bucket)
Select – the same color you used for the main line
Select – Marker Line Style
For Width, increase to 2 pt

Logic:  For certain charts, the most important information to be communicated is the overall trend of the line we’ve drawn.  In this case, smooth the line to make the overall trend more apparent and take the focus away from the point to point variation.

{ 5 comments… read them below or add one }

April 1, 2013 at 7:37 am

You should avoid using curved lines. They do not emphasize the trend, they may in fact obscure the trend. The curved lines may also extend to where it is impossible for there to be data.
Suppose I have data that can only range between 0 and 100. Plot the following data with curved lines:
X(0,1,2,3), Y(0,0,100,100)
The curved line extends from about -7 to about 107.
By the same token, you should include data markers as much as possible, to indicate where actual data has been measured. If the actual points are not too important, use small markers.

Reply

Andreas January 15, 2015 at 2:38 am

Dear Sir good day,

How do I put colors in the levels I want?
for example:
from level 60-100 I want blue, 100-130 red?

thanks.

Reply

kanil gunewardena March 28, 2015 at 10:58 pm

Excellent Excel 2013 Plot Guide to line plots – had a quantum leap in my presentations

Had to show a CG (center of gravity) symbol on the plot – so plotted a single point (data series) and plotted a circle around the CG. But the circle looked like an ellipse due to auto x, y scales. Could not find Equal Scale option – so my circle will look like a circle- How do do that?
2) I plot zones closed loops – how do I fill the plotted close loop with colors??
Have a great day

Reply

Dan December 3, 2015 at 11:22 am

big help! thanks.

Reply

Tom Stickland April 21, 2016 at 3:27 am

I disagree with using soft grey for axes. When I look at a graph then the zero points are significant and in most cases that means the axes. The first thing I do with Excel 2013 is put the axes back to dark black from the grey default.

Reply

Leave a Comment

{ 1 trackback }

Previous post:

Next post:


Related pages


replace command excelvlookup excel 2003 exampleexcel if cell equals thenvlookup formulasexcel dividinglookup function on excelpadfolio interviewmacro to delete duplicate rows in excelucla student discountszs associates headquartershow to construct a normal probability plot in excelexcel match 2 columnsexcel formulas greater thanvlookup based on two conditionsexcel compare two lists and return differencesrandom number generator no repeats exceltwo way lookup excelnormal plot in excelhistogram template excelcse formulaharvard case study formatmost popular webstiesexcel vlookup traininghow to create a vlookup function in excelparentheses in excelbuying microsoft excelcompare columns in excel for differencescalendar in excel templateluvalle bookstore uclaexcel shortcut chartnorm inv functionexcel norminv functionquick excel formulashow to draw a chart in excelmuuclaif functions in excel 2010myucla mailmultiple vlookup resultsmicrosoft excel vlookup examplemultiple vlookup resultshow to plot a distribution curve in exceliferror vlookup excelvlookup duplicateputting a tick in excelrandom numbers in excel without duplicatesucla anderson interviewhow to make a random number generator in excelif statement in ms excelmaking graph excelwhat does this excel formula meanexcel ampersandexcel vlookup multiple sheetsvlookup formula in excel with exampleexcel remove duplicate recordswhat is the difference between lookup and vlookuphow to write excel formulasucla online mbaif statement vlookupquery tables in excelexcel lookup and reference functionsms word tick markdelete duplicates from a tablesalary uclahow to write vlookup formula in excelvlookup all matchesexcel macro buildervlookup with multiple criteriahow to add pivot table in excelmultiple vlookup in excelshortcut for symbols in excelvba chart axisvlookup in two columns
\n