Column Charts: Further Examples

Alcohol Consumption

You may have heard that the consumption of alcohol is rising. Here are some figures from which you can make a chart and judge for yourself:

Remember to change the years into text by adding an apostrophe.

Make a column chart from this data. Change the Y axis scale to start at 9.0 so the difference between amounts is clearer.

Add a custom header with your name, centre number and today's date (automatic field) in the middle section.

Now make a second chart with two lines to show changes in the price of alcohol relative to earnings:

(These figures are relative to a base year of 1197=100.)

Remember to change the years into text by adding an apostrophe.

Change the Y axis minimum value to 90 and add a header with the same three fields as before.

Sales Data

Column charts are commonly used to display sales results, such as by quarter over time or by region over geographical space.

Make a column chart in Excel from the data on this site: BASF Regional Sales 2002

Changes in US Income Distribution

Charts may emphasise the trends in data and add force to an argument or a presentation.

Given the following data you have to choose and create an appropriate chart. The data is for the growth of median family income in the United States 1947-7 (from the US Bureau of the Census, 1998).

Copy and paste the data to Excel and create a column chart. Select all of the data, including the column headings, and click the Chart Wizard button (or choose Insert/Chart. The Chart Wizard goes through 4 steps:

1. here you can select the type of chart you want; accept the default for a column chart
2. here you can change the data range and add or remove data series; skip over these for now
3. here you can add titles and other features; the title should be 'Changes in Median Family Income 1947-97'; there is no need for a legend as there is only one data series and the title provides sufficient information; we could add data items but there is no need for now
4. here you can choose to create a new worksheet for the chart or embed it in the current worksheet; choose 'As object in sheet 1' for now

To see the settings of stage 3 again right click on a white area of the chart and choose 'Chart Options' from the popup menu.

The figures are aggregated so a column chart is a reasonable choice here and the periods are of different lengths so a line chart would give a distorted impression of change through time.

What has happened to median incomes in the United States over the last 50 years? To investigate further switch to a new worksheet and make two more charts from these data, the first for 1947-79:

And again for 1979-1997:

What additional information do these figures provide on trends in family income in the United States between 1947 and 1997?

The data in the two charts show changes in income over two fairly long periods of time and there is no question of using a line chart to show change through time. The categories for each set of data are complete, there is no question of choosing five from fifty or more.

World Population

This spreadsheet can be found at: K:\IT\FourthForm\12_countries.xls.

We could make a column chart from the population column but these data are not particularly useful, they show a fairly random collection of countries and they cover a wide range of values that results in some countries being scarcely visible in the chart.

This chart could be improved in a number of ways:

• Sort the data into order by size (select the data, choose Data/Sort and sort by Column B).
• Make the unit of measurement smaller (this requires copying the data column again and dividing the cells in column A by, say, 100,000). To make a chart based on non-adjacent columns press the Control key.
• Make the font for the country names smaller so all the names can be seen.
• Make the chart larger so all the names can be seen.

To perform these actions make a copy of the data underneath. Here is the finished chart:

Make a chart from the Names and the Annual Rate of Growth columns. To select non-adjacent columns hold down the Control key while you select the two sets of data. Do not select data in the B column.

We might want to combine the figures for growth of population with the annual percentage change but this is difficult because of the extreme differences between the figures - the population figure for India is 1,049,700,118 but its growth rate is just 0.0147 (the same as 1.47%).

If we want to put the two sets of values on the same chart we will have to scale them both, but it is difficult to find meaningful scales that bring the numbers close together. In this case, therefore, we will have to make do with the two separate charts.

Conclusion

This example suggests that we should use column charts for data that is reasonably complete and of limited extent, otherwise we end up with random data or too many columns.

Use the Back Button to Return to the previous page