C:\Core\Apache\Apache2\htdocs\uploadedFiles\v0R6ncfFKgeLiq265tuLzhtm

Microsoft Works 4.5

Spreadsheet

Ewing SeniorNet Computer Literacy Center

June, 2001

Prepared by: J. Joel May

Table of Contents

I. Introduction to Spreadsheets........................................................................................................I-1

A. What You’ll Learn ....................................................................................................................I-1

B. The Opening Screen (Task Launcher).....................................................................................I-1

C. Open a Spreadsheet File ........................................................................................................I-2

D. The Works Spreadsheet Screen .............................................................................................I-2

E. Learning About Rows, Columns and Cells ..............................................................................I-3

F. The Dialog Boxes.....................................................................................................................I-3

G. Moving Around in the Spreadsheet.........................................................................................I-4

H. Entering Text in a Spreadsheet ...............................................................................................I-5

I. Entering Numbers in a Spreadsheet.........................................................................................I-5

J. Entering Series in a Spreadsheet ............................................................................................I-5

K. Entering Formulas in a Spreadsheet .......................................................................................I-6

L. Entering Functions in a Spreadsheet.......................................................................................I-6

M. Saving the Results and Ending the Session ...........................................................................I-7

II. Editing, Moving, and Printing the Data .......................................................................................II-1

A. What You’ll Learn ...................................................................................................................II-1

B. Open the Spreadsheet File.....................................................................................................II-1

C. Replacing an Entry in a Cell ...................................................................................................II-1

D. Editing the Contents of a Cell in the Entry Bar .......................................................................II-1

E. Clearing Cell Entries...............................................................................................................II-1

F. Copying Cell Entries................................................................................................................II-2

G. Relative vs. Absolute Cell References ...................................................................................II-2

H. Finding and Replacing Cell Entries ........................................................................................II-3

I. Printing a Spreadsheet ............................................................................................................II-3

J. Saving the Results and Ending the Session ...........................................................................II-4

III. Formatting Spreadsheets .........................................................................................................III-1

A. What You’ll Learn ..................................................................................................................III-1

B. Open an Existing Spreadsheet..............................................................................................III-1

C. Changing Margins .................................................................................................................III-1

D. Changing Page Orientation...................................................................................................III-1

E. Inserting Rows and Columns.................................................................................................III-1

F. Change Column Widths.........................................................................................................III-2

G. Formatting Cells....................................................................................................................III-3

H. Formatting Numbers in a Spreadsheet..................................................................................III-3

I. Changing the Alignment of Cell Entries ..................................................................................III-3

J. Saving the Results and Ending the Session ..........................................................................III-4

IV. More Formatting, Headers and Footers, Hiding and Protecting Data...................................... IV-1

A. What You’ll Learn ................................................................................................................. IV-1

B. Open an Existing Spreadsheet............................................................................................. IV-1

C. Changing Font Size and Style.............................................................................................. IV-1

D. Adding Borders..................................................................................................................... IV-1

E. Adding Shading.................................................................................................................... IV-2

F. Adding Headers and Footers ................................................................................................ IV-2

G. Hiding and Displaying Gridlines ........................................................................................... IV-2

H. Hiding and Displaying Formulas........................................................................................... IV-3

I. Hiding and Displaying Columns and Rows ............................................................................ IV-3

J. Adding Cell Protection........................................................................................................... IV-4

K. Saving the Results and Ending the Session......................................................................... IV-4

V. Sorting Spreadsheet Data and Creating Charts........................................................................ V-1

A. What You’ll Learn .................................................................................................................. V-1

B. Sorting Spreadsheet Data ..................................................................................................... V-1

C. Creating Charts ..................................................................................................................... V-2

D. Adding a Series to a Chart .................................................................................................... V-3

E. Adding Chart Titles and Labels.............................................................................................. V-3

F. Formatting a Chart ................................................................................................................. V-4

G. Printing a Chart ..................................................................................................................... V-5

H. Naming and Saving a Chart .................................................................................................. V-5

I. Inserting a Chart into a Document .......................................................................................... V-5

J. Saving the Results and Ending the Session .......................................................................... V-5

VI. Class Practice Problem........................................................................................................... VI-1

A. What You’ll Review............................................................................................................... VI-1

B. Building a Spreadsheet ........................................................................................................ VI-1

C. Adjust the Page Setup ......................................................................................................... VI-1

D. Enter the Column Headings and Format the Columns......................................................... VI-1

E. Enter the Initial Values.......................................................................................................... VI-2

F. Enter the Formulas................................................................................................................ VI-2

G. Formatting the Spreadsheet................................................................................................. VI-3

H. Adding Cell Protection.......................................................................................................... VI-3

I. Saving the Results and Ending the Session .......................................................................... VI-4

Microsoft Works v4.5 - Spreadsheet

I. Introduction to Spreadsheets

A. What You’ll Learn

1. How to open a spreadsheet

2. How to enter Data (Text, Numbers, a Series, Formulas and Functions)

3. How to copy information from one place to another on a spreadsheet

4. How to save a copy of the spreadsheet

B. The Opening Screen (Task Launcher)

1. When Works starts, the first screen displayed is the Task Launcher Screen

2. There are three tabs on this screen:

a) TASK WIZARDS (the default) displays a list of Tasks which Works will help you to

perform (we’ll cover these later in the course)

b) EXISTING DOCUMENTS displays a list of documents previously created in Works.

This makes it easy to reload a document you previously worked on for additions or

improvements. As you create more and more documents, you may wish to make

this tab your default.

c) WORKS TOOLS displays four buttons which create new documents for you. There

are four type of documents which can be created in Works:

Works 4.5 Spreadsheet

Prepared for: Ewing SeniorNet Computer Literacy Center

Page I-1

Prepared by: J. Joel May 6/19/01

C. Open a Spreadsheet File

1. Let’s start by working with a spreadsheet that has already been partially created

for you.

a) Click on the Existing Documents Tab

b) The document we want to use is not (yet) listed here (take my word for it), so click on

the Open A Document Not Listed Here Button

c) Since the file you want to use is on the floppy disk in your Drive A: (if the disk is not

in place, insert it now), you must change the Look In choice to 3 ½ inch Floppy (A:)

d) Highlight ESNSL1.WKS and click on the OPEN Button

2. You just opened a spreadsheet file called ESNSL1.WKS. A spreadsheet is a

versatile tool you can use for many personal and business tasks. For example: maintain

your check register, manage club or team records, create a home budget, or track your

financial transactions. You can use a spreadsheet to make simple or complex

calculations. The editing, formatting and charting features allow for the creation of

professional-looking and easy to read documents.

D. The Works Spreadsheet Screen

Title

Menu

Tool

Entry

Work

Resize, Minimize

Bar

File Name

Area

Help Menu

and Close Buttons

Columns

Cursor

Rows

Vertical

Cell (D29)

Scroll

Bar

View Controls

Task Bar

Status Bar

Horizontal

Help Menu

Scroll Bar

Controls

Works 4.5 Spreadsheet

Prepared for: Ewing SeniorNet Computer Literacy Center

Page I-2

Prepared by: J. Joel May 6/19/01

E. Learning About Rows, Columns and Cells

1. A spreadsheet is made up of horizontal Rows and vertical Columns that intersect

to form individual Cells. The information contained in the cells can be text,

numbers, dates, times, formulas, or functions

2. Each Column in a spreadsheet is identified by a letter or group of letters. A Works

spreadsheet has 256 columns labeled A through IV.

3. Each Row in a spreadsheet is identified by a number. The rows in a Works

spreadsheet are numbered from 1 to 16,384.

4. Each Cell in a spreadsheet is identified by the Column and Row whose intersection

it is. For example, the Cell known as E8 is the one at the intersection of Column E

and Row 8. In our example it is Total Expenses

F. The Dialog Boxes

1. As you use the Works spreadsheet (and all other Windows 95 and Windows 98

programs as well), you will encounter Dialog Boxes

2. Dialog boxes are used to provide information to the program regarding the

appearance of your document, the options you want to have in effect, etc.

3. They can contain one or more of the following components:

Text Box

Scrolling

Drop Down

Help

List Box

Action Buttons

Radio Buttons

Preview Box

Check Boxes

Works 4.5 Spreadsheet

Prepared for: Ewing SeniorNet Computer Literacy Center

Page I-3

Prepared by: J. Joel May 6/19/01

G. Moving Around in the Spreadsheet

1. In order to enter data (or change data) in a Cell, the Cursor (the heavy black

rectangle or the solid black highlight) must be located in that Cell.

2. To move the cursor directly to a Cell, you can

a) Move the mouse pointer to the Cell and click, or

b) Open the Edit Menu and choose GoTo. Then, in the GoTo Text Box type the

address of the Cell you want to move to. (If you have assigned ranges to your

spreadsheet, you can highlight the range name in the scrolling list box by clicking on

it – we’ll discuss ranges later in the course). Then click the OK Button (or press

ENTER), or

c) Press the F5 Key and follow the directions in b) above

3. You can use the Arrow Keys to move the cursor around in the spreadsheet.

a) The Up-Arrow Key moves the cursor to the Cell immediately above the current one

b) The Down-Arrow Key moves the cursor to the Cell immediately below the current

one

c) The Right-Arrow Key moves the cursor to the Cell immediately to the right of the

current one

d) The Left-Arrow Key moves the cursor completely off the spreadsheet (just kidding!).

It moves the cursor to the Cell immediately to the left of the current one

4. You can use the “Enhanced” Arrow Keys to move the cursor around in the

spreadsheet

a) CTRL Up-Arrow moves the cursor to the next non-blank cell above the current one

(or to the top of the Column if there are no non-blank cells)

b) CTRL Down-Arrow moves the cursor to the next non-blank cell below the current

one (or to the bottom of the Column if there are no non-blank cells)

c) CTRL Right-Arrow moves the cursor to the next non-blank cell to the right of the

current one (or to the right end of the Row there are no non-blank cells)

d) CTRL-Left Arrow moves the cursor to the next non-blank cell to the left of the current

one (or to the left end of the Row if there are no non-blank cells)

5. You can use the Home and End Keys and the “Enhanced” Home and End Keys to

move the cursor around in the spreadsheet

a) The Home Key moves the cursor to the left end of the current Row

b) The End Key moves the cursor to the right-most cell in the currently active area of

the spreadsheet

c) CTRL-Home moves the cursor to the upper left corner of the spreadsheet (Cell A1)

d) CTRL-End move the cursor to the lower right corner of the currently active area of

the spreadsheet

6. You can use the Page Up and Page Down Keys and the “Enhanced” Page Up and

Page Down Keys to move the cursor around in the spreadsheet

a) The Page Up Key moves the cursor up one screen

b) The Page Down Key moves the cursor down one screen

c) CTRL-Page Up moves the cursor right one screen

d) CTRL-Page Down moves the cursor left one screen

Works 4.5 Spreadsheet

Prepared for: Ewing SeniorNet Computer Literacy Center

Page I-4

Prepared by: J. Joel May 6/19/01

H. Entering Text in a Spreadsheet

1. The spreadsheet we are using (ESNSL1.WKS) is a partially completed revenue

analysis for the Ewing SeniorNet Education Company’s first quarter sales. Our job

is to turn it into a professional-looking report for management.

2. You use text in a spreadsheet to provide descriptive labels for Rows or Columns or

to give instructions

3. Move the cursor to Cell A2 using one or more of the techniques described above

and type “Revenue Analysis” (no quotes) and press ENTER

4. NOTICE:

a) The text you typed appears in the Entry Bar Text Box preceded by a quotation mark.

This simply means (to Works) that the entry is a text entry.

b) Text is always left-justified in the cell

c) IMPORTANT: If you ever want to go back and change any of this text, you will make

the changes IN THE ENTRY BAR TEXT BOX , not in the cell itself. We will cover

this point several more times during the course.

d) It looks as if the text we have entered is not only in Cell A2, but also spills over into

Cell A3. That, however, is not the case. The Entry Bar Text Box always displays the

text in the highlighted cell (the one the cursor is located in). If you move the cursor

to Cell A3, you’ll see by watching the Entry Bar Text Box that it still empty.

5. Now move the cursor to Cell A4 and type “Sales” (no quotes). If you press the

Down-Arrow Key (rather than the ENTER key), the entry will still be made, but the

cursor will move down to Cell A5. This is often a convenient way of entering

several pieces of information in contiguous cells

6. In Cell A5, type “Cost” (no quotes) and press ENTER

I. Entering Numbers in a Spreadsheet

1. You use numbers in a spreadsheet to make calculations. Numbers can be whole

numbers, decimal numbers, fractions, dates or times. Furthermore, as we shall

see, they can be formatted in a number of ways

2. The data for January and February have already been entered in Columns B and C.

We will enter the data for March in Column D

3. Move the cursor to Cell D4 and type “25120” (no quotes). Then press the Down-

Arrow Key

4. In Cell D5, type “10920” (no quotes) and press ENTER

5. In Cell D8, type “5966” (no quotes) and press ENTER

6. NOTICE:

a) Numbers are always right-justified

b) They ARE NOT preceded by a single quotation mark as are text entries

J. Entering Series in a Spreadsheet

1. Move the cursor to Cell B3, type “Jan” (no quotes) and press ENTER

2. NOTICE that it is automatically expanded to “January” and right-justified. Glance

up at the Entry Bar Text Box. What does Cell B3 actually contain? If you wanted

to enter the short month name “Jan” how would you do it?

3. We could now move to Cell C3 and type “Feb” then press the left arrow and, in Cell

D3 type “Mar,” but that is too much work. Instead

Works 4.5 Spreadsheet

Prepared for: Ewing SeniorNet Computer Literacy Center

Page I-5

Prepared by: J. Joel May 6/19/01

4. Place the mouse pointer on the small black box at the lower right corner of the

cursor (this is called a “handle”). The pointer will change to a black cross and the

word “Fill”

5. Press down the left mouse button and drag the handle to the right to include Cells

C3 and D3, then release the mouse button.

6. Voila! You have entered a series (of month names from January to March)

7. NOTICE:

a) Other series that Works makes available to you include days of the week (either

Mon - Sun or Monday - Sunday)

b) If you wanted to enter a series such as 5, 10, 15, 20, … or 1,3,5,7,9,11, … you

could enter the first two numbers (5 and 10 or 1 and 3) in adjacent cells, highlight

both cells and drag the fill handle to create the desired series

c) Series can be entered either horizontally (as we have just done) or vertically, labeling

either columns or rows

K. Entering Formulas in a Spreadsheet

1. Formulas perform calculations on numbers that already exist in a spreadsheet.

Formulas MUST begin with an equal sign ( = ) and can contain numbers, cell

references, other formulas, and mathematical operators ( +, -, *, /, or ^ )

2. Let’s start by entering a formula for Gross Profit into our spreadsheet. Gross

Profit is equal to Sales minus Cost so, for the month of January, Cell B7 should

display a number that is equal to the number if B4 minus the one in B5.

a) The formula for this is “=B4-B5” (no quotes), so type this into Cell B7

b) NOTICE : Cell B7 now displays 15050 which is 24080 - 9030 (or Gross Profit =

Sales - Cost), which is correct. But what are the CONTENTS of Cell B7?

3. Revenue is equal to Gross Profit minus Expenses, so Cell B10 should contain the

formula “=B7-B8.” Let’s enter this in a slightly different way. (Watch the Entry Bar

Text Box as you perform the following actions)

a) In Cell B10 type an equal sign “=” (no quotes)

b) Click on Cell B7

c) Type a minus sign “-“ (no quotes)

d) Click on Cell B8

e) Press ENTER

L. Entering Functions in a Spreadsheet

1. Functions are predefined formulas that perform special calculations or

comparisons. A function is a built-in equation that you can use in a spreadsheet.

2. Works makes available 76 functions: 9 Data and Time functions, 11 Financial

functions, 6 Logical functions, 17 Math and Trigonometry functions, 10 Information

and Reference functions, 7 Statistical Functions, and 16 Text functions.

3. Don’t Panic! We’ll cover SOME of these as we proceed through the course and, if

you want more information, click on the Contents Tab in Help Index, then choose

Spreadsheet and Spreadsheet Functions. Here you will find a list of all the

functions and instructions on how (and when) to use each of them

4. First, let’s put a Math function into Cell E4 that will display the total sales for the

months of January through March. Here, the Function we want to use is SUM

a) In Cell E4, type “=SUM(B4:D4)” (no quotes)

(1) All functions start with an equal sign “=” just like formulas

Works 4.5 Spreadsheet

Prepared for: Ewing SeniorNet Computer Literacy Center

Page I-6

Prepared by: J. Joel May 6/19/01

(2) SUM is the Math function to compute an sum (or total) of the specified range of

cells

(3) (B4:D4) specifies the range of cells over which we want to compute the average.

We want to use the contiguous cells beginning with B4 and continuing across

through D4. The cell references MUST be separated by a colon.

b) Press Enter

c) The number displayed in Cell E4 is 76960, which is the total sales for the months of

January through March, but the CONTENTS of Cell E4 is the function we just

entered

5. NOTE: There is a quicker way of doing this.

a) Place the cursor in Cell F4, click on the AutoSum Button on the Toolbar (the one with

the summation sign - Σ )

b) The cells immediately to the left are highlighted and the Sum Function is

automatically inserted

c) Press ENTER to see the results

6. Now, let’s compute the average sales for the months of January through March.

The Function we want to use is (not surprisingly) AVG.

a) In Cell F4 type “=AVG(B4:D4)” (no quotes)

b) Press Enter

c) Once again, note that the number displayed in the cell, 25653.333, is the result of

the function which is the actual contents on the cell as which is displayed in the

Entry Bar Text Box. (We’ll take care of the extra decimal places in Lesson Three)

M. Saving the Results and Ending the Session

1. Let’s now save our work so that we can pick up next time where we left off today

a) Open the File Menu and choose Save As

(1) We use Save As because it allows us to give our work a new name and save it

as a new file.

(2) If we were to use Save, our work would be saved, but the previous version of the

spreadsheet (the one we began with today) would be over-written and erased.

b) The Save As Dialog Box appears with the current file name highlighted in the File

Name Text Box

c) Type “ESNSL1xx,” (no quotes) where xx is your two initials as the new file name and

press ENTER. Works will add the .WKS file extension automatically

d) You now have two versions of the spreadsheet: the original named ESNSL1.WKS

and the changed version named ESNSL1xx.WKS. Note that Works displays the

new name for your file in the Title Bar.

2. Exit Works and Windows

Works 4.5 Spreadsheet

Prepared for: Ewing SeniorNet Computer Literacy Center

Page I-7

Prepared by: J. Joel May 6/19/01

II. Editing, Moving, and Printing the Data

A. What You’ll Learn

1. Replacing Cell Entries

2. Editing Cell Entries in the Entry Bar

3. Clearing Cell Entries

4. Copying and Moving Cell Entries

5. Absolute and Relative Cell References

6. Finding and Replacing Cell Entries

7. Printing a Spreadsheet

B. Open the Spreadsheet File

1. Let’s start by opening the same spreadsheet we worked on last time.

a) Click on the Existing Documents Tab

b) Any document we have worked on lately is listed here

c) Highlight ESNSL1xx.WKS (where xx represents your initials – this is the file we

worked on last time) and click on the OPEN Button

C. Replacing an Entry in a Cell

1. Let’s assume we have entered an incorrect Sales figure for January

a) Move the cursor to Cell B4

b) Type the correct value for January Sales which is “23885” (no quotes)

2. the new entry replaces the previous one AND the formulas you created in Cells B7

and B10 are automatically recalculated.

D. Editing the Contents of a Cell in the Entry Bar

1. Let’s further assume that the Expenses figure for February is incorrect. But we

want to change only one digit in the number (not the entire number).

2. To do this we will use the EDIT mode

a) Move the cursor to Cell C8. Note that the contents of the cell appears in the Entry

Bar

b) Click to the right of the number in the Entry Bar (or press F2). An insertion point

appears and you have entered the EDIT mode.

c) We want to change the Cell entry from 9022 to 9122, so position the insertion point

just after the character to be changed (in this case, between the 0 and the first 2),

press the backspace key to delete the incorrect character, and then type “1” (no

quotes).

d) When you press ENTER, the corrected entry will appear in Cell C8 and you will

leave the EDIT mode.

E. Clearing Cell Entries

1. Assume that we have decided that we don’t need to show the averaged for each

row of data.

2. We can delete this information by clearing the unneeded entries from the cells

a) Move the cursor to Cell F3, then drag down across F3 and F4 to highlight those

cells.

b) Press the DELETE Key to clear the entries from the cells

Works 4.5 Database

Prepared for: Ewing SeniorNet Computer Literacy Center

Page II-1

Prepared by: J. Joel May 4/5/1999

F. Copying Cell Entries

1. Last time, we entered formulas for Gross Profit and Revenue for the month of

January (in Cells B7 and B10, respectively). We now must enter the appropriate

formulas to compute the equivalent figures for the months of February and March.

2. We could re-enter the complete formulas in each cell, but it is much easier to copy

the formulas we have already entered. One of the neater tricks Works can perform

is to change all the necessary cell references when a formula is copied to a new

location.

a) Move the cursor to Cell B7 and copy the contents. There are at least four ways to

do this

(1) Open the Edit Menu and choose Copy, or

(2) Click on the Copy Button on the Tool Bar, or

(3) Press CTRL-C

(4) Right Click to open the Context Menu and choose Copy

b) The contents of the cell will be copied to the Windows Clipboard

c) Now highlight Cells C7 and D7

d) Paste the formula from the Windows Clipboard into the highlighted cells. There are

at least three ways to do this, as well

(1) Open the Edit Menu and choose Paste, or

(2) Click on the Paste Button on the Tool Bar, or

(3) Press CTRL-V

(4) Right Click to open the Context Menu and choose Paste

e) The formula stored in the Windows Clipboard will be pasted into the highlight cells

AND all cell references will be adjusted to that they apply to the new location. This

process is called Creating Relative Cell References. Check out the new formulas by

moving the cursor to the appropriate cell and viewing the formula in the Entry Bar

3. We’ll use a different technique to copy the formula in Cell B10 (January Revenue)

to Cells C10 and E10 (February and March Revenue as well as Total Revenue for

the Quarter)

a) Either

(1) Highlight Cells B10 through E10

(2) Open the Edit Menu and choose Fill Right, or

b) Or

(1) Highlight Cell B10

(2) Drag the Fill Handle (see Section I.J. above) to the right, highlighting Cells B10

through E10

c) The formula in Cell B10 (the first highlighted cell) is copied to the others and all cell

references are adjusted so that they apply to the new location, creating Relative Cell

References

G. Relative vs. Absolute Cell References

1. Most cell and range references in a spreadsheet are relative references such as

the ones we created above. A relative reference is Works is like referring saying

“two rows up and one column over”

a) In our example, the formula we typed into Cell B10 was “=B7-B8” or “three rows up

minus two rows up”

b) When we copied it to Cells C10 through E10, the relative reference to “three rows up

minus two rows up” remained the same, only the column references changed.

Works 4.5 Database

Prepared for: Ewing SeniorNet Computer Literacy Center

Page II-2

Prepared by: J. Joel May 4/5/1999

2. An absolute reference always points to the same cell, even if the formula

containing the reference is copied or moved to another location in the spreadsheet

a) To make a reference absolute, place a dollar sign ($) in front of the row and/or

column reference which you want to make absolute. Some examples are

(1) $B$10

Absolute column, absolute row

(2) B$10

Relative column, absolute row

(3) $B10

Absolute column, relative row

(4) B10

Relative column, relative row

b) As an example

(1) Type a “2” (no quotes) in Cell A17 and type a “3” (no quotes) in Cell B17

(2) In Cell A18 type “=2*A17” (no quotes). The result is 4 (which is 2 times the

contents of Cell A17). No surprises so far.

(3) Now copy the formula in Cell A18 to Cell B18. We used a relative reference, so

the result is 6 (which is 2 times the contents of Cell B17)

(4) Clear the contents of Cells A18 and B18

(5) In Cell A18 type “=2*$A$17” (no quotes). The result is as before.

(6) Now copy the formula in Cell A18 to Cell B18 and watch what happens

3. Can you think of some examples in which we would want to use relative

references? Some in which we would want to use absolute references?

H. Finding and Replacing Cell Entries

1. Let’s assume that we want to replace the word “Revenue” with the word “Income”

in our spreadsheet. This is, in our case, an easy task which we learned how to do

in Section II.C. above. But suppose we were working with a large spreadsheet in

which the word “Revenue” appeared many time and we wanted to replace all

occurrences of the word.

2. We would use the Find and Replace Feature

a) Open the Edit Menu and choose Replace

b) In the Find What Text Box enter “Revenue” (no quotes)

c) In the Replace With Text Box enter “Income” (no quotes)

d) Click the Replace All Button and Works replaces all occurrences of “Revenue” with

“Income”

3. WARNING: Indiscriminate use of the Replace All Button may be dangerous to your

spreadsheet’s health.

a) Suppose you wanted to replace all occurrences of the word “his” with the word

“hers” and you followed the steps outlined above.

b) Not only would you get all the replacements you had hoped for, you would also

change “this” to “thers,” “history” to “herstory,” “whistle” to “wherstle”, etc.

c) To avoid this sort of disaster, when in doubt, use the Replace Button instead. This

will allow you to choose to replace (or not replace) each occurrence of the word one

at a time.

I. Printing a Spreadsheet

1. To see how a spreadsheet will look when it is printed, use the Print Preview

function

a) Open the File Menu and choose Print Preview or click on the Print Preview Button

on the Tool Bar

b) Now you can Zoom in and out of the view, move from page to page using the Next

and Previous Buttons, and Print the spreadsheet directly

Works 4.5 Database

Prepared for: Ewing SeniorNet Computer Literacy Center

Page II-3

Prepared by: J. Joel May 4/5/1999

c) When you are finished examining the spreadsheet, click on the Cancel Button

2. To print a spreadsheet

a) Open the File Menu and choose Print or press CTRL-P

b) Make the appropriate choices in the Print Dialog Box

(1) Change printers, if you want, in the Printer Drop Down List Box

(2) In the Print Range Box you can choose to print the entire document or a range of

pages

(3) Click the Draft Quality Printing Check Box if you want to save ink or toner

(4) Choose the number of copies to print (and the order in which to print them) in the

Copies Box

c) NOTE: If you want to accept all the defaults in the Print Dialog Box and save a step

or two, you can simply click on the Print Button on the Tool Bar to print your

spreadsheet immediately

J. Saving the Results and Ending the Session

1. Let’s now save our work so that we can pick up next time where we left off today

a) Open the File Menu and choose Save

(1) We use Save because this time we are not concerned with overwriting the

previous version of our spreadsheet. We will Save it with the same name and

the new version will replace the previous one.

(2) If we were to use Save As, we would be asked for a new name and our work

would be saved with that new name, preserving the original version as well.

2. Exit Works and Windows

Works 4.5 Database

Prepared for: Ewing SeniorNet Computer Literacy Center

Page II-4

Prepared by: J. Joel May 4/5/1999

III. Formatting Spreadsheets

A. What You’ll Learn

1. Changing the page setup and page orientation

2. Formatting Rows and Columns

3. Inserting Rows and Columns

4. Changing Column Widths

5. Formatting Cells

6. Changing Number Formats

7. Changing Alignment of Cell Entries

B. Open an Existing Spreadsheet

1. Click on the Existing Documents Tab

2. The document we want to use is not (yet) listed here (take my word for it), so click

on the Open A Document Not Listed Here Button

3. Since the file you want to use is on the floppy disk in your Drive A: (if the disk is

not in place, insert it now), you must change the Look In choice to 3 ½ inch Floppy

(A:)

4. Highlight ESNSL2.WKS and click on the OPEN Button

C. Changing Margins

1. Open the File Menu and choose Page Setup

2. The Page Setup Dialog Box has three Tabs (Margins; Source, Size & Orientation;

and Other Options) as well as a Preview Box

a) Click on the Margins Tab

b) Set the Left Margin to 1.75”

c) Watch the changes in the Preview Box

d) Note that you can also set the Top, Bottom and Right Margins as well as the Header

and Footer Margins (we don’t know what these are yet, but we’ll come to it next time)

e) Note also that you can click on the Reset Button to change everything back to its

original setting

D. Changing Page Orientation

1. Our spreadsheet might look better if the page were turned horizontally (this is

called “Landscape Mode”)

a) Click on the Source, Size & Orientation Tab

b) Click on the Landscape Radio Button in the Orientation Box

c) Watch the changes in the Preview Box

d) Note that you can also set the paper size and the printer tray from which the paper

is to be selected (these settings, however, usually make sense only in a situation in

which a very powerful (and expensive) printer is in use).

e) Click the OK Button

2. Check out your changes so far by using Print Preview (either Open the File Menu

and choose Print Preview or click on the Print Preview Button on the Tool Bar.

E. Inserting Rows and Columns

1. Inserting a Row

Works 4.5 Spreadsheet

Prepared for: Ewing SeniorNet Computer Literacy Center

Page III-1

Prepared by: J. Joel May 4/5/1999

a) You can insert blank rows to increase the amount of space between the spreadsheet

title and the month names, and between the month names and the Sales data in

order to improve the appearance of your spreadsheet

b) Click on the Row Label (the gray box on the left) for Row 4. This will highlight the

entire row.

c) Open the Insert Menu and choose Insert Row. Works inserts a new, blank row

ABOVE the highlighted row.

2. The number of rows you highlight will determine how many new rows are inserted

a) Drag across the Row Labels for Rows 3 and 4 to highlight the rows

b) Open the Insert Menu and choose Insert Row. Works inserts TWO new blank rows

above the highlighted rows – creating two blank lines between the spreadsheet title

and the month names.

3. Inserting a Column

a) You might have noticed that the data for the month of May has been left out of the

spreadsheet. We need to make room for it.

b) Click on the Column Label (the gray box at the top) for Column F. This will highlight

the entire column

c) Open the Insert Menu and choose Insert Column. (Did you notice how the menu

has changed?) Works inserts a new, black column BEFORE the highlighted column

4. Adding the New Data. As you add the data below, watch your spreadsheet to see

how the formula entries are automatically updated

a) In Cell F5, type “May” (no quotes)

b) In Cell F7 type “31000” (no quotes)

c) In Cell F8 type “12468” (no quotes)

d) In Cell F11 type “5820” (no quotes)

e) Copy the contents of Cell E10 to Cell F10.

(1) Instead of using any of the four methods described in Section II.F.2.(a) above,

use the Fill Handle as described in Section I.J.4.

(2) Highlight Cell E10, use the Fill Handle at the lower right hand corner to drag the

cursor over to Cell F10 and release the mouse button.

(3) Now you’ve learned a fifth way to copy data from one cell to another.

f) Copy the contents of Cell E13 to Cell F13

g) Your data for May have not all been entered.

F. Change Column Widths

1. If cell entries are too wide for a column, or if you want to add space between

columns, you can change column widths. NOTE: When a number is too wide for

its column, Works displays #####).

2. Use the Print Preview feature to examine the appearance of your spreadsheet. The

columns could be a little wider, couldn’t they? Here’s one way to do it:

a) Drag across the Column Labels for Columns B through G to highlight them.

b) Open the Format Menu and choose Column Width. The Column Width Dialog Box

appears. The standard width for all columns is 10 characters.

c) Type “12” (no quotes) in the Column Width Text Box

d) Click on the OK Button (or press ENTER)

e) The columns are widened

3. Here’s another way:

a) To widen Column H so that it is as wide as the other columns,

b) Place your mouse pointer on the right edge of the Column Label for Column H

Works 4.5 Spreadsheet

Prepared for: Ewing SeniorNet Computer Literacy Center

Page III-2

Prepared by: J. Joel May 4/5/1999

c) Drag the edge of the Column to the right until it is as wide as the others.

4. Here’s still another way:

a) Double Click on the Column Label of Column A

b) Works automatically adjusts the width of the column to accommodate the widest

entry. This feature is called “best fit”

c) NOTE: The “best fit” feature is also available in the Column Width Dialog Box

d) Check out your results using the Print Preview feature

5. Whoops!

a) The column containing the Totals appears to have vanished. Where is it?

b) When we widened Column A, we forced Column H (containing the Totals) onto the

second page of the report. Not a good idea.

c) So let’s fix it by placing the mouse pointer on the right edge of Column A and

dragging it to the left (making it narrower). About an inch should do it.

G. Formatting Cells

1. You can change the way a spreadsheet cell displays the data in it by formatting the

cell either before or after you enter the data.

2. Formatting the data in a cell DOES NOT change the data in any way, it only

changes the way it is displayed.

H. Formatting Numbers in a Spreadsheet

1. Unless you specify a different format, Works displays numbers in what is called

General Format, e.g. 1234.567

2. Other formats include

a) fixed (2 decimal places)

1234.57

b) currency

$1,234.57

c) comma (2 decimal places)

1,234.57

d) percent

123456.70%

e) exponential (5 decimal places)

1.234567E+03

f) leading zeros (6 digits)

001235

g) fraction

1234 55/97

h) true/false

True (0 = False)

i)

date

05/18/1903

j)

time

01:36 PM

3. Let’s format all the numbers in our spreadsheet with the Comma format and 0

decimal places

a) Highlight the range from Cell B7 to Cell H17

b) Open the Format Menu and choose Number

c) Click on the Comma Radio Button in the Format Box

d) Type “0” (no quotes) in the Decimal Places Text Box in the Options/Comma Box

e) Click on the OK Button (or press ENTER)

4. CPA’s and similar compulsive people like to have dollar signs on numbers in the

first row and all rows containing

totals, so …

a) Click on the Row Label for Row 7 to highlight the Row

b) Now do the same for Row 10 (Gross Profit) and Row 13 (Income)

c) Check out the results using Print Preview.

I. Changing the Alignment of Cell Entries

Works 4.5 Spreadsheet

Prepared for: Ewing SeniorNet Computer Literacy Center

Page III-3

Prepared by: J. Joel May 4/5/1999

1. Changing the alignment of a cell entry affects only the appearance of the cell, the

actual values remain unchanged

2. Let’s change the alignment of the Totals label in Cell H5 to make it aligned right like

the names of the months

a) Highlight Cell H5

b) Open the Format Menu and click on the Right Radio Button in the Horizontal Box (or

click on the Right Alignment Button on the Tool Bar)

c) Click on the OK Button (or press ENTER)

3. NOTE that you can align text OR numbers in several different ways:

a) Horizontally

(1) General

Aligns text to the left and number to the right

(2) Left

Aligns both to the left

(3) Right

Aligns both to the right (duh!)

(4) Center

Aligns both in the center

(5) Fill

Repeats the entry until the cell is full

(6) Center across selection

See Section III.I.4. below

b) Vertically

(1) top

Aligns the text at the top of a row

(2) center

Aligns the text at the center of a row

(3) bottom

Aligns the text at the bottom of a row

c) In addition, you can click the Wrap Text Check Box to cause the text to wrap within

the cell, adjusting to the current width

4. Now let’s center the report title (both lines of it) over the whole report

a) Highlight the range of cells from A1 to H2

b) Open the Format Menu and click on the Center Across Selection Radio Button in

Horizontal Box

c) Click on the OK Button (or press ENTER)

d) Check out the results using Print Preview

J. Saving the Results and Ending the Session

1. Let’s now save our work so that we can pick up next time where we left off today

a) Open the File Menu and choose Save As

(1) We use Save As because it allows us to give our work a new name and save it

as a new file.

(2) If we were to use Save, our work would be saved, but the previous version of the

spreadsheet (the one we began with today) would be over-written and erased.

b) The Save As Dialog Box appears with the current file name highlighted in the File

Name Text Box

c) Type “ESNSL2xx,” (no quotes) where xx is your two initials as the new file name and

press ENTER. Works will add the .WKS file extension automatically

d) You now have two versions of the spreadsheet: the original named ESNSL2.WKS

and the changed version named ESNSL2xx.WKS. Note that Works displays the

new name for your file in the Title Bar.

2. Exit Works and Windows

Works 4.5 Spreadsheet

Prepared for: Ewing SeniorNet Computer Literacy Center

Page III-4

Prepared by: J. Joel May 4/5/1999

IV. More Formatting, Headers and Footers, Hiding and Protecting Data

A. What You’ll Learn

1. Changing Font Size and Style

2. Adding Borders and Shading

3. Headers and Footers

4. Hiding and Displaying Gridlines

5. Hiding and Displaying Columns and Rows

6. Cell Protection

B. Open an Existing Spreadsheet

1. Let’s start by opening the same spreadsheet we worked on last time.

a) Click on the Existing Documents Tab

b) Any document we have worked on lately is listed here

c) Highlight ESNSL2xx.WKS (where xx represents your initials – this is the file we

worked on last time) and click on the OPEN Button

C. Changing Font Size and Style

1. Highlight Cell A1. The data that looks like it is in Cells C1, D1, and E1 is really in A.

Why?

2. Open the Format Menu and choose Font and Style.

a) In the Size Drop Down List Box choose 16 (NOTE, you could also choose 16 in the

Drop Down List Box on the Tool Bar)

b) In the Style Box, click on the Bold Check Box (NOTE, you could also click the Bold

Button on the Tool Bar)

c) Click on the OK Button (or press ENTER)

3. The first title line on your spreadsheet is now large and bold

4. Highlight Cell A2

5. Open the Format Menu and choose Font and Style

a) In the Style Box, click on the Italic Check Box (NOTE, you could also click the Italic

Button on the Tool Bar)

b) Click on the OK Button (or press ENTER)

6. The second title line on your spreadsheet is now italicized

7. Use the Print Preview feature to check out the results.

D. Adding Borders

1. You can add borders all around a cell (or group of cells) or just on one or more

sides. Let’s draw a border at the bottom of Cells B8 through H8 to create an

underline showing that the next number is a total

a) Highlight the range of cells from Cell B8 to Cell H8

b) Open the Format Menu and choose Border

c) When the Border Dialog Box appears,

(1) In the Line Style Box, click on the line style you want to use (the second one

from the top – the default – should work fine for us)

(2) In the Border Box, click on Bottom

(3) We could choose a color from the Color Scrolling List Box, but let’s not overdo it

here.

Works 4.5 Database

Prepared for: Ewing SeniorNet Computer Literacy Center

Page IV-1

Prepared by: J. Joel May 4/5/1999

(4) Click on the OK Button (or press ENTER

d) Check out your results using Print Preview

2. Now add the same bottom border to Cells B11 to H11

3. For Cells B13 to H13, use a different line style. We want to have a double underline

here so, when the Border Dialog Box appears, in the Line Style Box, click on

double line style (the sixth one from the top), then proceed as above.

4. Check out your results

E. Adding Shading

1. To make our report easier to read, let’s add shading to the titles of the rows and

the columns

a) Highlight the range of cells from Cell B5 to Cell H5 (the month names and the total)

b) Open the Format Menu and choose Shading

c) Experiment with the Patterns available in the Shading Box and the Foreground and

Background Colors available in the Colors Box until you find a look you like. You

can see a preview of the results of your choices in the Sample Preview Box. NOTE:

to apply a solid color shading to the chosen cells, first choose ANY pattern, then

choose the same Foreground and Background Color.

d) When you have what you want, click on the OK Button (or press ENTER).

2. Check out the results using Print Preview

F. Adding Headers and Footers

1. A Header is text that appears at the top of every page of a printed document. For

example, in a book, this might be the title of the chapter

2. A Footer is text that appears at the bottom of every page of a printed document.

For example, in a book, this might be the page number

3. Let’s add a Footer to our report (even though it has only one page)

a) Open the View Menu and choose Headers and Footers

b) In the Footer Text Box, type the following EXACTLY as shown (no questions

asked!!): &l &f &c Income Report as of &t &n &r Page &p

c) Check out the results using Print Preview

4. What you entered was interpreted by Works as follows:

(1) &l

Align Left

(2) &f

The File Name

(3) &c

Align Center

(4) Income Report as of

Text

(5) &t

Current Time

(6) &n

Current Date in long format (&d for short format)

(7) &r

Align Right

(8) Page

Text

(9) &p

Current Page Number

5. These same codes can be used in the Header

6. Note that in the Header and Footer Dialog Box you can also use Check Boxes to

suppress the Header and/or the Footer on the first page of the printed document

G. Hiding and Displaying Gridlines

1. Gridlines are the horizontal and vertical lines separating rows and columns on a

spreadsheet. They are usually quite useful while creating and editing a

spreadsheet, but if you would like to turn them off, open the View Menu and

Works 4.5 Database

Prepared for: Ewing SeniorNet Computer Literacy Center

Page IV-2

Prepared by: J. Joel May 4/5/1999

choose Gridlines (This works like a toggle switch. Click it once and they are gone,

click it again and they return).

2. NOTE: this does not in any way affect the way your document will look when it is

printed. It ONLY affects the way it looks in the document window on your

computer screen

H. Hiding and Displaying Formulas

1. It is sometimes useful to be able to see the formulas in a spreadsheet directly in

the cells rather than only on the Entry Bar. To do so, open the View Menu and

choose Formulas. To hide them, repeat the process.

2. NOTE: this DOES affect the way your document will look when it is printed.

I. Hiding and Displaying Columns and Rows

1. When you hide a column or a row, even though you can’t see the information, all

the values and formulas remain active and are calculated along with the rest of the

spreadsheet

2. To hide a column, you must set its column width to zero

a) Let’s hide Column C so that we can more easily compare the January results with

the March results. One way is to

(1) Highlight any cell in Column C (or highlight the Cell Label)

(2) Open the Format Menu and choose Column Width

(3) Set the Column Width to zero

(4) Click on the OK Button (or press ENTER)

b) Alternatively, you could place the mouse pointer on the right edge of the Column

Label and drag it to the left until the Column disappears

c) Check out the results of your actions using Print Preview

3. To Display a Column, you must set its column width back to some positive number

a) To re-display Column C, Open the Edit Menu and choose Go To (or press ALT-G)

b) In the Go To Dialog Text Box type the address of any cell in Column C (e.g. C3)

c) Click on the OK Button (or press ENTER)

d) Open the Format Menu and choose Column Width

e) Type a positive number (e.g. 12) in the Column Width Text Box

f) Click on the OK Button (or press ENTER)

4. To hide a row, you must set its row height to zero

a) Let’s hide Row 11 so we can more directly compare Gross Profit with Income. One

way is to

(1) Highlight any cell in Row 11

(2) Open the Format Menu and choose Row Height

(3) Set the Row Height to zero

(4) Clock on the OK Button (or press ENTER)

b) Alternatively, you could place the mouse pointer on the bottom edge of the Row

Label and drag it upward until the Row disappears

c) Check out the results of your actions using Print Preview

5. To Display a Row, you must set its row height back to some positive number

a) To re-display Row 11, Open the Edit Menu and choose Go To (or press ALT-G)

b) In the Go To Dialog Text Box type the address of any cell in Row 11 (e.g. B11)

c) Click on the OK Button (or press ENTER)

d) Open the Format Menu and choose Row Height

e) Type a positive number (e.g. 12) in the Row Height Text Box

Works 4.5 Database

Prepared for: Ewing SeniorNet Computer Literacy Center

Page IV-3

Prepared by: J. Joel May 4/5/1999

f) Click on the OK Button (or press ENTER)

J. Adding Cell Protection

1. You can safeguard your spreadsheet against accidental changes or deletions by

using cell protection (locking some cells and unlocking other).

2. Works locks all cells in the spreadsheet by default, but the lock is ineffective until

the Protect Data option is turned on.

3. To protect some cells but not others, unlock the cells you do NOT want to protect,

then turn on the Protect Data option.

4. Let’s protect all the cells in our worksheet containing formulas, so that they cannot

be accidentally changed (this would involve Cells B10 through G10 (monthly Gross

Profit figures), B13 through G13 (monthly Income figures), and Cells H7 through

H13 (the Total figures).

5. Thus, we must first UNLOCK all the other cells in our spreadsheet. Here’s how to

do it:

a) Highlight the range of cells from Cell B7 through Cell G8

b) Open the Format Menu and choose Protection

c) The Locked Check Box in the Data Box is shaded. Click it once to turn it on, then

click it again to turn it off. The check mark will disappear from the Check Box and

the highlighted cells are unlocked.

d) Highlight the range of cells from Cell B11 through Cell G11

e) Open the Format Menu and choose Protection

f) Proceed as in c) above to unlock the highlighted cells

6. Now we must turn on the Protect Data Option

a) Open the Format Menu and choose Protection

b) In the Protection Box, click on the Protect Data Check Box

c) The locked cells are now protected from changes or deletions, but the unlocked cells

can be changed at will

d) Experiment!!

K. Saving the Results and Ending the Session

1. Let’s now save our work so that we can pick up next time where we left off today

a) Open the File Menu and choose Save

(1) We use Save because this time we are not concerned with overwriting the

previous version of our spreadsheet. We will Save it with the same name and

the new version will replace the previous one.

(2) If we were to use Save As, we would be asked for a new name and our work

would be saved with that new name, preserving the original version as well.

2. Exit Works and Windows

Works 4.5 Database

Prepared for: Ewing SeniorNet Computer Literacy Center

Page IV-4

Prepared by: J. Joel May 4/5/1999

V. Sorting Spreadsheet Data and Creating Charts

A. What You’ll Learn

1. Sorting Spreadsheet Data

2. Creating Charts

3. Adding a Series to a Chart

4. Adding Chart Titles and Labels

5. Formatting a Chart

6. Printing a Chart

7. Naming and Saving a Chart

8. Inserting a Chart into a Document

B. Sorting Spreadsheet Data

1. We’ll start by opening an existing spreadsheet

a) Click on the Existing Documents Tab

b) The document we want to use is not (yet) listed here (take my word for it), so click on

the Open A Document Not Listed Here Button

c) Since the file you want to use is on the floppy disk in your Drive A: (if the disk is not

in place, insert it now), you must change the Look In choice to 3 ½ inch Floppy (A:)

d) Highlight SortFile.WKS and click on the OPEN Button

2. This spreadsheet contains two columns of data. Column A contains years and

Column B contains expenditures . Let’s first sort the file by year.

a) Highlight the data in Column A by clicking on the Column Label

b) Open the Tools Menu and choose Sort. The Sort Dialog Box will open

c) Click on the Sort All the Information Radio Button. (If you chose to sort only the

highlighted information, Column A would be sorted, but Column B would be left

untouched. This is USUALLY NOT what you want to do.)

d) Click on the OK Button (or press ENTER). A second Sort Dialog Box will open

e) In the Sort By Box, be sure that Column A is chosen in the Drop Down List Box and

that the Ascending Radio Button is chosen. We choose an Ascending sort because

we want to sort the years from the earliest (1997) to the latest (2000). If we want a

sort from the highest to the lowest (e.g. Z to A), we would choose a Descending sort.

f) NOTE: In the My List Has Box you can specify whether or not the column contains a

Header Row (a title for the column). If it does, the header row will be omitted in the

upcoming sort.

g) Click on the Sort Button

h) Examine the results. The data are now sorted by year beginning with the lowest

(1997) and ascending to the highest (2000)

3. Now we’ll do a more complex sort operation, sorting the file by year (again) AND,

within a year, by expenditure (from highest to lowest)

a) Highlight the data in both Columns A and B by dragging across the Column Labels

b) Open the Tools Menu and choose Sort. The Sort Dialog Box will open

c) As before, click on the Sort All Information Radio Button

d) Click on the OK Button (or press ENTER). A second Sort Dialog Box will open.

e) Click on the Advanced Button. Now you can specify a sort operation on up to three

different columns at once.

f) In the Sort By Box, specify Column A and Ascending

Works 4.5 Database

Prepared for: Ewing SeniorNet Computer Literacy Center

Page V-1

Prepared by: J. Joel May 4/5/1999

g) In the upper Then By Box, specify Column B and Descending (since we want to view

our expenditures from highest to lowest by year)

h) Click on the Sort Button

i)

Examine the results. The data are now sorted not only by year from the lowest to

the highest, but also, within each year, the expenditures are displayed in order from

highest to lowest.

4. Discard this spreadsheet

a) Open the File Menu and choose Close.

b) In the Dialog Box that opens click on the No Button (choosing NOT to save the

changes made to the file)

C. Creating Charts

1. Open the file named ESNSL3.WKS which is located on your Practice Problems

diskette in Drive A:

2. You create a chart by highlighting the spreadsheet data and letting Works draw the

chart for you. You can create as many as eight charts in each spreadsheet,

choosing from 12 different types of charts.

3. The purpose of our chart is to display the relationship between two series of

values: Sales and Income. Since those values are not adjacent on the

spreadsheet, we cannot highlight them at the same time. So we’ll start with by

making a simple bar chart of Sales

a) Highlight the range of cells from Cell A7 through Cell G7 (the Sales figures)

b) Open the Tools Menu and choose Create New Chart (or click on the New Chart

Button on the Tool Bar). The New Chart Dialog Box will open

c) Here you can choose from one of 12 different chart styles

(1) An Area Chart (or its 3D equivalent) Note: this is sometimes called a Histogram

(2) A Bar Chart (or its 3D equivalent)

(3) A Line Chart (or its 3D equivalent)

(4) A Pie Chart (or its 3D equivalent)

(5) A Scatter Chart

(6) A Radar Chart

(7) A Stacked Line Chart (when you have two data series you want to compare)

(8) A Combination Chart (when you have two data series you want to compare)

d) You can enter a Title for your chart in the Finishing Touches Title Text Box either by

typing the Title you want to use or by entering a cell reference

e) You can add a Border or Gridlines to your chart using the appropriate Finishing

Touches Check Boxes.

f) You can see the effects of each of your choices in the Your Chart Preview Box

g) We’ll choose a Bar Chart which does not have borders or gridlines and we’ll skip the

Title for the time being

h) Click on the OK Button (or press ENTER) to see your chart.

i)

NOTE: A new Tool Bar appears at the top of your screen. In addition to the Font

Style and Size Drop Down List Boxes, the available Tool Bar Buttons are (from left to

right)

(1) Task Launcher

(2) Save

(3) Print

(4) Print Preview

(5) Copy

Works 4.5 Database

Prepared for: Ewing SeniorNet Computer Literacy Center

Page V-2

Prepared by: J. Joel May 4/5/1999

(6) Bar Chart

(7) Line Chart

(8) Pie Chart

(9) Scatter Chart

(10) Mixed Chart

(11) 3D Area Chart

(12) 3D Bar Chart

(13) 3D Line Chart

(14) 3D Pie Chart

(15) Go To First Series

(16) Address Book

D. Adding a Series to a Chart

1. Recall that the purpose of our chart is to display the relationship between two

series of values: Sales and Income. Since those values are not adjacent on the

spreadsheet, we could not highlight them at the same time. We’ll add the second

series (Income) now

a) Open the Edit Menu and choose Series. Note that the first series we entered into

the chart (B7 through G7) is already listed here

b) Enter “B13:G13” (no quotes) to add the second series (these are the cells that

contain the Income values

c) If you can’t remember which cells contain the information you want to include in the

second series, you can instead:

(1) Open the Window Menu and choose the spreadsheet window

(2) Highlight the range of Cells from B13 to G13

(3) Copy this range to the Windows Clipboard using one of the methods you learned

in Section II.F.2.a) above

(4) Open the Window Menu again and choose the chart window

(5) Open the Edit Menu and choose Series

(6) Position the mouse pointer in the Text Box for the series you want to add

(7) Click on the Paste Button

E. Adding Chart Titles and Labels

1. To add a Title to the chart:

a) open the Edit Menu and choose Titles

b) In the Dialog Box which opens, you can enter a Chart Title and Subtitle, titles for

each of the two axes of the chart, and even one for the right X axis if there is one

c) In the Chart Title Text Box type “Ewing SeniorNet Education Company” (no quotes)

OR you can simply type A1 which is the cell which contains the text

d) In the Subtitle Text Box type “Sales and Income” (no quotes)

e) Click on the OK Button (or press Enter)

2. To add Data Labels to the chart:

a) Open the Edit Menu and choose Data Labels

b) In the 1 Text Box in the Value Series (Y) Box, type “B5:G5” (no quotes). These are

st

the cells which contain the names of the months

c) If you can’t remember which cells contain the information you want to include as

data labels, you can instead:

(1) Open the Window Menu and choose the spreadsheet window

(2) Highlight the range of Cells from B5 to G5

Works 4.5 Database

Prepared for: Ewing SeniorNet Computer Literacy Center

Page V-3

Prepared by: J. Joel May 4/5/1999

(3) Copy this range to the Windows Clipboard using one of the methods you learned

in Section II.F.2.a) above

(4) Open the Window Menu again and choose the chart window

(5) Open the Edit Menu and choose Series

(6) Position the mouse pointer in the Text Box for the series you want to add

(7) Click on the Paste Button

d) Click on the OK Button (or press ENTER)

3. To add Legend Labels to the chart:

a) Open the Edit Menu and choose Legend/Series Labels

b) In the Series Labels Box

(1) In the 1 Value Series Text Box type “Sales” (no quotes) OR you can simply type

st

A7 which is the cell that holds the name Sales

(2) In the 2 Value Series Text Box type “Income” (no quotes) OR you can simply

nd

type A13 which is the cell that holds the name Income

(3) Click the OK Button (or press ENTER)

F. Formatting a Chart

1. You can format each part of the chart separately

2. To format the Title

a) Click on the Title. A box appears around it.

b) Right click anywhere in the box. A context menu appears

c) Click on Font and Style

d) Increase the Size to 16 points and turn on the Bold Check Box

e) Click on the OK Button (or press ENTER)

3. To format the Subtitle

a) Click on the Subtitle. A box appears around it.

b) Right click anywhere in the box. A context menu appears

c) Click on Font and Style

d) Increase the Size to 12 points and turn on the Bold Check Box

e) Click on the OK Button (or press ENTER)

4. To Format the Data and Legend Labels

a) Click on any one of the Data Labels (the month names) or the Legend Labels. A

box appears around each of them

b) Right click anywhere in any of the boxes. A context menu appears

c) Click on Font and Style

d) Change the Font Style to Times New Roman and turn on the Bold Check Box

e) Click on the OK Button (or press ENTER)

5. To Change the Color and/or Patterns used in the Chart

a) Open the Format Menu and choose Shading and Color

b) Use the Series Radio Buttons to choose which series to Format

c) Use the Colors Scrolling List Box to choose the color of the pattern (Auto will allow

Works to choose its own color combinations)

d) Use the Pattern Scrolling List Box to choose a color density or pattern for the series

e) Use the Marker Scrolling List Box (in the case of line, stacked line, scatter, and radar

charts only) to choose the style of marker to be used

f) When you are satisfied with your choices, click on the Format Button. (If you want

all the Series to have the same formatting, set your choices and then click on the

Format All Button)

6. To Change the Orientation of a Chart

Works 4.5 Database

Prepared for: Ewing SeniorNet Computer Literacy Center

Page V-4

Prepared by: J. Joel May 4/5/1999

a) Use Print Preview to see how your chart will look when it is printed

b) Let’s change the orientation to landscape (so the chart will print horizontally on the

page)

c) Open the File Menu and choose Page Setup

d) Click on the Source, Size and Orientation Tab and then on the Landscape Radio

Button

e) Use Print Preview again to see the results

7. Different Chart Types

a) Use the Buttons on the Tool Bar to see how your chart would look as a Line Chart or

as an Area Chart or as a Combination Chart, a 3D Bar Chart, etc. NOTE: a Pie

Chart can display only one series. Since our chart contains two series, it results in a

meaningless pie chart.

G. Printing a Chart

1. The chart looks pretty good by now. Printing it is a simple matter

a) Open the File Menu and choose Print, then specify the printer you want to use, the

number of copies, etc and click the OK Button (or press ENTER), or

b) Click on the Print Button on the Tool Bar and print the chart directly

H. Naming and Saving a Chart

1. Open the Tools Menu and choose Rename Chart

a) You are “re-naming” the chart rather than “naming” it because Works has already

given it a default name – Chart 1

b) Type a descriptive name in the Type a Name Below Text Box (e.g. Sales and Income

Bar Chart) and click on the Rename Button

c) The chart will automatically be saved with its new name when you save the

spreadsheet

I. Inserting a Chart into a Document

1. To insert a chart into another document, both the spreadsheet containing the chart

and the destination document must be open

2. Our spreadsheet is already open, so let’s open a document in which we’ll insert the

chart

a) Using the File Menu and the Dialog Boxes, open the file names Words.WPS which

is on your Practice Problems diskette. This is a Works word processing document.

3. Position the insertion point on the blank line between the first and second

paragraphs

4. Open the Insert Menu and choose Chart. A list of all the open spreadsheet

documents will appear in the Select a Spreadsheet Box. Highlight ESNSL3.WKS

and then, in the Select a Chart Box, highlight the chart you have just created and

named. Then click OK (or press ENTER)

5. Your chart is inserted into the word processing document. Note that, using the

little boxes at each corner and in the middle of each side of the box enclosing your

chart you can change its size and move it around in the document

6. When you’re done experimenting, close the word processing document (Open the

File Menu and choose Close, then click on the No Button when you are asked if

you want to save the changes)

J. Saving the Results and Ending the Session

Works 4.5 Database

Prepared for: Ewing SeniorNet Computer Literacy Center

Page V-5

Prepared by: J. Joel May 4/5/1999

1. Let’s now save our work

a) Open the File Menu and choose Save

(1) We use Save because this time we are not concerned with overwriting the

previous version of our spreadsheet. We will Save it with the same name and

the new version will replace the previous one.

(2) If we were to use Save As, we would be asked for a new name and our work

would be saved with that new name, preserving the original version as well.

2. Exit Works and Windows

Works 4.5 Database

Prepared for: Ewing SeniorNet Computer Literacy Center

Page V-6

Prepared by: J. Joel May 4/5/1999

VI. Class Practice Problem

A. What You’ll Review

1. How to create a spreadsheet

2. How to enter Text, Numbers, and Formulas

3. How to format cells in a spreadsheet

4. How to use Functions

B. Building a Spreadsheet

1. When this Practice Problem is completed, you will be able to use it as your own

personal check register. Simply replace the sample data with your own personal

check book data. To maintain the confidentiality of your own personal finances, it

is recommended that you do not enter your personal data in class, but rather on

your own time.

2. This is what the final result will look like:

My Check Register as of 4/15/1999

Check/

Deposit/

Chk#

Date

Payee

Debit

Credit

Balance R