How to Make Your Excel Line Chart Look Better

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 2013 version of Excel, 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.

7 thoughts on “How to Make Your Excel Line Chart Look Better”

  1. Pingback: How to Learn Excel
  2. 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
  3. 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
  4. 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
  5. I too disagree with the use of soft grey for chart axes and labels: 1) it’s hard for the eye to track across the chart to read muted numeric values for data points in the plot; 2) the light colored axes may disappear if the chart is used in a PowerPoint presentation with a dim projector or well lit room; 3) the light colored axes may disappear when a report is photocopied or scanned from a lousy copy machine, especially if it’s a second or third generation copy (i.e. copy of a copy of a copy).

    Edward Tufte & his disciples are smart guys but I strongly disagree with him/them on this point.

    Reply

Leave a Comment