Quick, clean, and to the point

How to build a line chart

In this video, we'll look at how to create a line chart in Excel.

Here we have historical, 30-year mortgage interest rates going back to 1971.

Let's plot this data in a line chart. Line charts are especially good at visualizing trends over time.

To start off, I'll place my cursor anywhere in the data, then navigate to the insert tab and click the line chart icon.

The first option is the one I want. The result is a simple line chart.

This is a great example of the simplicity of a line chart.

Although the chart is plotting over 500 rows of data, we can see at a glance that rates range from a high of 18% to a low of just below 4%.

I'll make the chart a bit bigger, holding down the alt key to snap to the cell grid.

Now let's clean up the chart.

First, I'll work on the vertical axis. The rate data represents percentages, but if I try to format with the percent number format, it doesn't work, since these are just regular numbers.

To fix this problem, I'll use paste special to convert these numbers to values we can apply a percentage number format to.

I'll put the number 100 in any cell, and then copy to the clipboard.

Next, I'll select the entire column and use paste special with values and divide.

This divides every value in the column by 100, in place, without any formulas.

Now when I apply the percentage number format, things look good.

And the vertical axis immediately picks up this formatting, since link to source is enabled.

Next, let's de-clutter the horizontal axis.

I'll double click the axis to bring up the format task pane.

Then I can apply a number format to show the year only, using the format code "yyyy".

Next, I'll set major units to 5 years.

Finally, I'll use a formula to bring in a title from cell B2

Then I'll increase the font size a bit for the entire chart.


Related shortcuts

Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Charts video training course
Video training for Excel Tables
Dynamic Array Formulas