Principles of Finance with Excel (Основы финансов c Excel)

Подождите немного. Документ загружается.

PFE Chapter 28, Charts and graphs in Excel page 1

CHAPTER 28: GRAPHS AND CHARTS IN EXCEL

This version: February 1, 2003

Chapter contents

Introduction..................................................................................................................................... 1

28.1. The basics of Excel charts..................................................................................................... 2

28.2. Creative use of legends ....................................................................................................... 11

28.3. Graphing non-contiguous data............................................................................................ 12

28.4. Graph titles that update ....................................................................................................... 15

Summary....................................................................................................................................... 18

Introduction

Excel has extensive facilities to do graphs.

If you’re like most Excel users and finance

majors, you’ll be using these facilities a lot.

In this short chapter, we’ll discuss the basics of graphing, assuming that—by and large—

you already know how to make a chart in Excel. We will also discuss some less well-known

techniques that have to do with charts:

Notice: This is a preliminary draft of a chapter of Principles of Finance by Simon Benninga

(benninga@wharton.upenn.edu). Check with the author before distributing this draft (though

you will probably get permission). Make sure the material is updated before distributing it. All

the material is copyright and the rights belong to the author.

In “Excelese” graphs are called “charts.” We will use both words interchangeably.

PFE Chapter 28, Charts and graphs in Excel page 2

• Making a graph with non-contiguous data series

• Changing the axis parameters of a chart

• Making a chart where the title changes when the data changes

28.1. The basics of Excel charts

Every Excel chart has its origins in the data on a spreadsheet:

ABCD

Dividends

Purchase

treasury

stock

Proceeds

from

exercise

of stock

options

1991 893 184 48

1992 1,064 863 52

1993 1,174 371 83

1994 1,434 705 139

1995 1,540 1,571 264

1996 1,729 2,493 442

1997 2,040 2,573 413

1998 2,253 3,626 490

1999 2,590 3,582 323

2000 2,798 3,545 641

MERCK & CO. 1991-2000

To create a graph that shows the dividends paid each year, we mark the relevant data:

PFE Chapter 28, Charts and graphs in Excel page 3

Clicking on the chart icon

on the toolbar brings up the chart menu, which gives a

bewildering variety of chart options. Being finance people, we’re primarily interested in the XY

(Scatter) chart option. We usually want to draw a connected line (shown here as the chosen

“Chart sub-type”):

PFE Chapter 28, Charts and graphs in Excel page 4

Two options for “connected” Excel XY Charts

This creates a “jagged” XY chart (the points

are connected by line segments. It is the

option we generally use in this book

This smooths the lines connecting the points.

Going to the next step in the chart wizard, you’ll see:

PFE Chapter 28, Charts and graphs in Excel page 5

There’s nothing much to do here, so press Next and go on to the next step, which allows

you to annotate the graph with titles:

PFE Chapter 28, Charts and graphs in Excel page 6

This author doesn’t like gridlines of any sort!:

Nor does he like legends very much ... though sometimes there’s room for one (see

Section ???):

PFE Chapter 28, Charts and graphs in Excel page 7

Pressing [Enter] gets you to:

Tell Excel where to put the graph (in this case, on the spreadsheet labeled “Merck data”, which is

also the spreadsheet where our data is stored.

Pushing Finish gives the following graph:

PFE Chapter 28, Charts and graphs in Excel page 8

More changes

Change 1: The Excel default graph has a murky gray area where the data is graphed.

This looks alright on the screen, but it looks terrible when you print it. All the graphs in this

book have this -gray graph area blanked out. To do this, mark the graph area:

Now double-click on the graph area; this brings up the following box:

PFE Chapter 28, Charts and graphs in Excel page 9

In the Format Plot Area box above, we always mark Border—None and Area—None.

Here’s the result:

PFE Chapter 28, Charts and graphs in Excel page 10

One more change

Although our data only goes from 1991 – 2000, the x-axis on our chart goes from 1990 to

2002. To change this, mark the x-axis of the graph with a gentle click on the left mouse button:

(Notice the square marks at either end of the x-axis.) Now right-click with the mouse and

Before: A checked box indicates the Excel

defaults. At this point the chart is set to show

every other year on the x-axis (Major unit =

2). Minor unit indicates the number of ticks

between the major units (not relevant here).

After. Note that we’ve changed both the

Minimum and the Maximum, as well as the

Major Unit.

Here’s the result: