EXCEL 2007 Charts: Column, Bar, Pie and Line

A. Colin Cameron, Dept. of Economics, Univ. of Calif. - Davis

This January 2009 help sheet gives information on how to construct charts It is easiest to learn chart making by hands-on experience.

CHART BASICS

To create a chart

The main types of chart used in analysis of economcis data are:

It can be useful to first modify the data for better presentation on the chart. For example, for a pie or column chart we may want to order the data by value.
Charts often automatically select chart title and/or axis or category labels from the above data, so it is useful to choose clear short names where possible.

Excel defaults usually lead to a chart that is reasonable but still needs customizing.
The general approach is to note that the chart has a number of areas:

To edit an existing chart one can select the chart (click inside the chart and the border becomes highlighted).
Then select within the chart that part of the chart you wish to change and right click to obtain the menu for reformatting the chart.
In some cases the Chart Tools group with subgroups Design, Layout and
Further details are given below.

 
COLUMN CHARTS

As an example consider categorical data on 1997 U.S. Health Care Expenditures.
The data are in file healthexpendituresdata.csv

 Health expenditures data

To create a column chart from the above

This yields

Column chart

Note that Excel uses the first series (Category) for the x-axis labels and the second series (Expenditures) for the y-axis values.
Also note the advantage to having the original data ordered in descending value of expenditures.

COLUMN CHARTS: CUSTOMIZING

 The resulting column chart is basically okay but prettier with a better chart title and the legend entry dropped.
PIE CHARTS

To create a pie chart from the above

This yields

Pie chart
 

PIE CHARTS: CUSOMIZING

The resulting pie chart lacks al the category names and it might be helpful to include the percentage breakdown.

To get all category names
We obtain

Pie chart

To get the percentage breakdown
Pie chart

pie chart

Select close and we obtain

Pie chart

LINE CHARTS

The line chart is not really helpful for categorical data as above.
The line chart is best used for numerical data that are observed over time.

We consider monthly data on the total return from the Standard and Poors 500 stock index (with reinvestment of dividends) from 1970 to 1988.
The data are in file SandP500stockpricedata.csv
The first few observations are

S and P 500 stock price data 

Here the months of 1970 are 1970 (Jan), 1970.08 (Feb), 1978.17 (March), 1978.25 (April), ...

To create a line chart from the above

This yields

Line chart

LINE CHARTS: CUSTOMIZING

The x-axes for this chart is the row number, not the date. To change to the date:
This yields the following dialog box

Line chart

Choose Select Data to get the following dialog box

Line chart

Then click on Edit to get the following dialog box

Line chart

Finally
This yields

Line chart

The displayed dates can be reduced to whole numbers 

A horiiontal axis can be added by

Add horizontal title

A vertical axis can similarly be added, where we ultimately select Rotated Axis.

We obtain

Line chart customized


PRINTING AND COPYING CHARTS

See Excel 2007: Formatting, Saving, Printing and Copying Results


CHANGE CHART SIZE AND POSITION

To resize the chart

To reposition a chart

To remove a chart from a worksheet

For further information on how to use Excel go to
   http://cameron.econ.ucdavis.edu/excel/excel.html