EXCEL 97 Charts: Column, Bar, Pie and Line
A. Colin Cameron, Dept. of Economics, Univ. of Calif.
- Davis
This September 1999 help sheet gives information on how to construct charts
-
Chart basics
-
Column chart
-
Bar Chart
-
Pie Chart
-
Line Chart
It is easiest to learn chart making by hands-on experience.
Output from this example, including the Excel default version and an
improved version after some customizing, is given in the pdf file Excel
chart example output.
EXAMPLE
As an example consider 1997 U.S. Health Care Expenditures.
Enter the following data in billions of dollars
| Category |
Expenditures |
| Hospital |
371 |
| Physician |
218 |
| Drugs & Supplies |
109 |
| Nursing Home |
83 |
| Home Health |
32 |
| Dental |
51 |
| Eye & Equipment |
14 |
| Other Personal |
92 |
| Admin and Insurance |
50 |
| Public Health |
39 |
| Research |
18 |
| Construction |
17 |
CHART BASICS
It can be useful to first modify the data for better presentation on
the chart.
For the above data it is useful to order the data in descending order
of expenditures using Data | Sort.
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.
To create a chart click on the Chart Wizard icon on the Excel taskbar
and fill in details. Examples are given below. It is easiest to first select
the data and possibly the headings before clicking on the Chart Wizard
icon.
Excel defaults usually lead to a chart that is reasonable but still
needs customizing.
Examples of this customization are given below. The general approach
is to note that the chart has a number of areas:
-
Chart Title
-
Plot Area (the actual chart)
-
The x-axis (for charts other than pie chart) which is called a category
axis for column or line chart and a value axis for a bar chart.
-
The y-axis (for charts other than pie chart) which is called a value axis
for column or line chart and a category axis for a bar chart.
-
Legend Entry (explains the symbols used in the chart)
-
Labels for the x-axis
-
Labels for the y-axis.
To edit an existing chart one can select the chart (click inside the chart
and the border becomes highlighted with eight small dark squares).
Then click on Chart Wizard and make changes as you move through the
menu.
Easier is to simply select within the chart that part of the chart
you wish to change, e.g. click on the x-axis, then double-click or right
click to obtain the menu for reformatting the chart.
Further details are given under Chart Wizard.
COLUMN CHARTS
To create a column chart from the above highlight the data and
headings (here columns A1:B12) and click on the Chart Wizard icon on the
toolbar.
-
Select column chart, leave at the default of clustered column chart, and
hit next.
-
The data range should automatically appear if you selected it, and hit
next (if it does not appear then manually enter A1:B12).
-
Titles can be left at the default which is the column heading (cell A1),
and hit next.
-
Chart location can be left as object in current worksheet and hit finish.
The resulting column chart is basically okay but prettier with a better
chart title and the legend entry dropped.
-
Select the Chart Title by clicking on Expenditures at the top of the chart,
double-click or right click to get Format Chart Title, and then change
the Font from Arial Regular 10 to Arial Bold 12.
-
Select the Chart Title and change the name to 1997 U.S. Health Expenditures
(in $billion).
-
Select the Legend Entry by clicking on Expenditures at the right and hit
the delete key.
Column charts from this example, both the default Excel version and an
improved version after some customizing, are given on page 1 of the pdf
file Excel chart example output.
BAR CHARTS
To create a bar chart from the above highlight the data and headings
(here columns A1:B12) and click on the Chart Wizard icon on the toolbar.
-
Select bar chart, leave at the default of clustered bar chart, and hit
next.
-
The data range should automatically appear if you selected it, and hit
next (if it does not appear then manually enter A1:B12).
-
Titles can be left at the default which is the column heading (cell A1),
and hit next.
-
Chart location can be left as object in current worksheet and hit finish.
The resulting bar chart is basically okay but definitely needs all category
names to appear on the y-axis and would look prettier with vertical lines
every 50 rather than 100 units, a better chart title and the legend entry
dropped.
-
Select the y-axis (category axis) by clicking on the y-axis, double-click
or right click to get Format Axis, and then choose Scale and change Number
of Categories between tick mark labels from 2 to 1.
-
Select the x-axis (value axis) by clicking on the y-axis, double-click
or right click to get Format Axis, and then choose Scale and change Major
Unit from 100 to 50.
-
Select the Chart Title by clicking on Expenditures at the top of the chart,
double-click or right click to get Format Chart Title, and then change
the Font from Arial Regular 10 to Arial Bold 12.
-
Select the Chart Title and change the name to 1997 U.S. Health Expenditures
(in $billion).
-
Select the Legend Entry by clicking on Expenditures at the right and hit
the delete key.
Bar charts from this example, both the default Excel version and an improved
version after some customizing, are given on page 2 of the pdf file Excel
chart example output.
PIE CHARTS
To create a pie chart from the above highlight the data and headings
(here columns A1:B12) and click on the Chart Wizard icon on the toolbar.
-
Select pie chart, and change from the default to Exploded pie, and hit
next.
-
The data range should automatically appear if you selected it, and hit
next (if it does not appear then manually enter A1:B12).
-
Titles can be left at the default which is the column heading (cell A1),
and hit next.
-
Chart location can be left as object in current worksheet and hit finish.
The resulting pie chart is basically okay but would look prettier with
a better chart title and the legend entry dropped. Also it might be helpful
to include the percentage breakdown, which is presented first.
-
Select the chart area by clicking on the edge of the chart, double-click
or right click to get Format Data Series, select data labels and choose
percent.
-
For the smallest categories the percentages are too crowded, so change
the font of the Series Data labels. Select the percentage figure by clicking
on a percentage figure and then double-click or right click to get Format
Data Labels, select font and change font size to 8. This still leaves the
lowest two or three percentages to crowded, so individually drop these
by selecting each and deleting.
-
The remaining changes are as before ....
-
Select the Chart Title by clicking on Expenditures at the top of the chart,
double-click or right click to get Format Chart Title, and then change
the Font from Arial Regular 10 to Arial Bold 12.
-
Select the Chart Title and change the name to 10997 U.S. Health Expenditures
(in $billion).
Pie charts from this example, both the default Excel version and an improved
version after some customizing, are given on page 3 of the pdf file Excel
chart example output.
LINE CHARTS
The line chart is not really helpful for these data. The line chart
is best used for numerical data that are observed over time.
.
To create a line chart from the above highlight the data and
headings (here columns A1:B12) and click on the Chart Wizard icon on the
toolbar.
-
Select line chart, and change from the default to Line with Markers at
Each Displayed Item, and hit next.
-
The data range should automatically appear if you selected it, and hit
next (if it does not appear then manually enter A1:B12).
-
Titles can be left at the default which is the column heading (cell A1),
and hit next.
-
Chart location can be left as object in current worksheet and hit finish.
The resulting column chart is basically okay but definitely needs all the
x-axis labels to appear and would look prettier with a better chart title.
-
Select the x-axis (value axis) by clicking on the y-axis, double-click
or right click to get Format Axis, and then choose Scale and change Number
of Categories from 2 to 1.
-
Select the Chart Title by clicking on Expenditures at the top of the chart,
double-click or right click to get Format Chart Title, and then change
the Font from Arial Regular 10 to Arial Bold 12.
-
Select the Chart Title and change the name to 1997 U.S. Health Expenditures
(in $billion).
Line charts from this example, both the default Excel version and an improved
version after some customizing, are given on page 4 of the pdf file Excel
chart example output.
PRINTING AND COPYING CHARTS
See Excel: Formatting, Saving, Printing and Copying
Results
For further information on how to use Excel go to
http://www.econ.ucdavis.edu/faculty/cameron