Line charts are well suited to showing changes in values through time.
Here are some figures for the population of England and Wales:
| 1811 | 10,164,256 |
| 1821 | 12,000,236 |
| 1831 | 13,896,797 |
| 1841 | 15,914,148 |
| 1851 | 17,927,609 |
| 1861 | 20,066,224 |
| 1871 | 22,712,266 |
| 1881 | 25,974,439 |
| 1891 | 29,002,525 |
| 1901 | 32,527,843 |
| 1911 | 36,070,492 |
| 1921 | 37,886,699 |
| 1931 | 39,952,377 |
Select them and then copy and paste them into a new worksheet in Excel, starting
at cell A1. Insert two new rows above the first data row and add a title and
labels (UK Population 1821-1931 and Year/Population). Use the data to draw a line
chart in Excel now.
The chart you produce will have a problem: there will be two lines but you only
want one.
The problem is that Excel reads the years as numbers and creates a line for them on the chart. To stop this happening you need to delete the chart and start again. This time stop at Stage 2 and choose the Series tab.
The Year column is selected in the Series box - click Remove. Now click the button at the right side of the Category (X) axis labels box. The spreadsheet is shown and you can drag over the cells in column A (year) to select them.
Note that you don't select the column title in row 1. Press Enter to confirm the selection
Click Next to move to step 3 of the chart wizard and complete the chart headings.
For practice create a chart for this set of data, showing rates of incarceration in the United States 1850-1990:
| Year | Rate |
| 1850 | 29 |
| 1860 | 60.7 |
| 1870 | 85.3 |
| 1880 | 61 |
| 1890 | 72 |
| 1900 | 69 |
| 1910 | 75 |
| 1920 | 79 |
| 1930 | 104 |
| 1940 | 131 |
| 1950 | 109 |
| 1960 | 117 |
| 1970 | 96 |
| 1980 | 138 |
| 1990 | 293 |
Unfortunately the data cannot be used directly in this format, the years have to be converted to text (in the first example above the years were of the form '1947-67', which Excel treated as text). If the years are left as numbers they will form a second series on the chart instead of a row of years along the X axis. There is an option in Format/Cells to convert data to text but this does not produce the desired result. Instead you have to put an apostrophe (') in front of each year to force it top behave as text.
How does this line chart compare with the column chart you produced earlier? Which is the best choice for this data set?
What has happened to rates of incarceration in the United States over the past 150 years? Given that the population has risen considerably, what are the implications for the prison building programme?
With time-series data like these it is sometimes appropriate to use a line chart as there is a sense of continuity and change between years. On the other hand the data may not be continuous, the values for years not shown may fluctuate widely. In this case it is probably safest to choose a column chart and note the clear trend in the rate of incarceration towards the end of the 20th century.
There is a further example here.
Use the Back Button to Return to the previous page