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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
