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


formula for countifr side by side barplotmultiple if then statements in excelexcel combining columnsinsert icon in excelexcel 2010 custom number formathow to use countifsexcel cell reference functionexcel two dimensional lookupexcel remove rows containingv lookup for dummiesarrays excelsimple tax refund estimatorexcel formula greater thanms excel 2007 vlookupgraph a circle in excelstacked graph excelworking at zs associatespivot table count unique valuesvlookup examples excel 2010bell graph excelwhen to use vlookuphow to remove columns in excelbuilding financial models with microsoft excelwhat is col index num in vlookupdata generator excelpayroll tax calculator exceldefinition of formulas in excelexcel compare two columns and highlight differencesautocorrelation coefficient excelmicrosoft tick symbolbin range excelsort formula excelhow to dedupe in excelhow to title columns in excelexcel macro worksheethow to prepare bar chart in excel 2010excel diary templateexcel vba cell referencewhat does num mean in excelvba excel macro tutorialhow to run sensitivity analysis in excellookup and reference in excelcosting model excelcheckmark microsoft wordexcel lookup tutorialvlookup from different sheethow to remove shading in excelalexa ranking of websiteshow to make a random number generator in excelexcell if statementsvlookup tutorial pdfcheap ucla apparelexcel formula transposeexcel iferrorucla bruin loginvlookup return textvlookup trimcolumn names in excelvlook up examplesdefine formula in excelexcel rngtypes of excel spreadsheetsdget excelsumif offsetcell ranges in excelucla software shortcutucla portal loginif formula in excel 2007 with examplepassword generator excelchart excel templatehow to do random numbers in excelcreating random numbers in excellooking for duplicates in excelrandom name selector downloadcomparing rows in exceliferror excel functiondedupe in excelremove duplicates from column in excelexcel vlookup example download
\n