Here's part of "The Secret Guide to Computers," copyright by Russ Walter, 29th edition. For newer info, read the 33rd edition at www.SecretFun.com.

Spreadsheets

Any table of numbers is called a spreadsheet. For example, this spreadsheet deals with money:

           January   February

Income   $9,030.95 $12,486.99

Expenses $7,000.55  $9,210.75

-----------------------------

Profit   $2,030.40  $3,276.24

A spreadsheet can show how many dollars you earned (or spent or plan to spend), how many goods you have in stock, how people scored in a test (or survey or scientific experiment), or any other numbers you wish!

A spreadsheet program helps you create spreadsheets, edit them, and analyze them.

 

How spreadsheets arose

The first spreadsheet program was invented in 1979. It was designed by Dan Bricklin and coded by Bob Frankston. (That means Dan Bricklin decided what features and menus the program should have, and Bob Frankston wrote the program.) They called the program VisiCalc because it was a “visible calculator”. The original version of VisiCalc ran on the Apple 2 computer and required 64K of RAM. Later versions of VisiCalc ran on the Radio Shack TRS-80 and IBM PC.

The second spreadsheet program was called SuperCalc because it was superior to VisiCalc. It was invented by a company called Sorcim (which is “micros” spelled backwards). The original version of SuperCalc ran on computers using the CP/M operating system. The most popular CP/M computer — the Osborne 1 — came with a free copy of SuperCalc. Later versions of SuperCalc ran on the Apple 2 and IBM PC. Eventually, Sorcim became part of a big conglomerate called Computer Associates.

Multiplan was the first spreadsheet program that could handle multiple spreadsheets simultaneously — and the relationships among them. Invented by Microsoft, it ran on a greater variety of computers than any other spreadsheet program: it ran on CP/M computers, the Radio Shack TRS-80, Commodore 64, Texas Instruments 99/4, IBM PC, Apple 2, and Apple Mac.

Context MBA was the first spreadsheet program that had “extras”: besides handling spreadsheets, it also handled graphs, databases, word processing, and telecommunications. But it ran too slowly, its word processing was too limited (it couldn’t center and wouldn’t let you set tab stops), and it required a strange operating system (the PASCAL P System). It was invented in 1981 by Context Management Systems, which later invented an MS-DOS version called Corporate MBA.

All those spreadsheet programs became irrelevant in 1983, when a much better spreadsheet program was invented. It was designed by Mitch Kapor and coded by Jonathan Sachs for the IBM PC. They called the program
1-2-3, because it ran fast and was supposed to handle three things: spreadsheets, graphs, and word processing; but when Jonathan examined Context MBA, he realized that putting a good word processor into 1-2-3 would consume too much RAM and make the program run too slowly. He omitted the word processor and replaced it with a stripped-down database processor instead. So 1-2-3 handled spreadsheets (well), graphs (okay), and databases (poorly). Mitch and Jonathan called their company Lotus Development Corporation, because Mitch was a transcendental-meditation instructor who got entranced by contemplating lotus flowers.

After inventing 1-2-3, Jonathan Sachs tried to invent a program called 1-2-3-4-5, which was to handle the same five tasks as Context MBA: spreadsheets, graphs, databases, word processing, and telecommunications.

While developing it, he realized it was becoming too large and confusing, so he stopped developing it and quit the company. Other Lotus employees finished that program and renamed it Symphony; but as he feared, it turned out to be too big and confusing, and its word processor was awful. Most businesses bought just 1-2-3 instead.

Like Jonathan, Mitch began feeling that Lotus Development Corporation and its products were becoming too big and confusing, so Mitch quit too.

Afterwards, Lotus Development Corporation was run by Jim Manzi, who was young, rich, vain, egotistical, and nasty. The rest of the computer industry hated him, though his employees were nice. Finally, he sold Lotus to IBM, which gave lots of money to him (and the other shareholders and employees). Then he quit, a rich man!

Other companies invented cheap imitations of 1-2-3. The imitations were called 1-2-3 clones or 1-2-3 twins. The first 1-2-3 twins were The Twin (published by Mosaic Software) and VP-Planner (published by Paperback Software). Lotus sued both of those publishers and put them out of business.

In 1983 — the same year that Lotus invented 1-2-3 — Apple invented Lisa Calc. It was the first spreadsheet program to use a mouse. It ran just on the Lisa computer, which was too expensive ($8,000). When Apple began selling the Mac computer the next year (1984), Microsoft began selling Multiplan for the Mac, which ran on the Mac and combined the best features of Multiplan and Lisa Calc. The next year, 1985, Microsoft invented a further improvement, called Excel because it’s excellent. Like 1-2-3, Excel handles spreadsheets, graphs, and databases.

Apple wanted to sue Microsoft for inventing the Windows operating system, which makes the IBM PC resemble a Mac. To avoid the suit, Microsoft agreed to put Excel on only the Mac for a year. Exactly one year later, when that agreement expired, Microsoft put Excel on the IBM PC.

So now Excel runs on both the Mac and the IBM PC. It’s the best spreadsheet program. The newest version, called Excel 2003, requires that you buy modern Windows.

Another fine spreadsheet program is called Quattro, because it’s what comes after 1-2-3. It was invented by Borland, which later invented an improved version, called Quattro Pro. In 1994, Borland sold Quattro Pro to another company, Novell, which later sold it to Corel. So now Quattro Pro is published by Corel.

 

What to do

Get a spreadsheet program!

The best spreadsheet program is Excel, which requires that you buy Windows or a Mac.

The DOS and Windows versions of 1-2-3 and Quattro Pro are popular alternatives that cost less. To pay even less, use the stripped-down spreadsheet programs that are part of Microsoft Works (for DOS, Windows, and the Mac) or AppleWorks (which has sometimes been called Claris Works and is available for the Apple 2, Mac, and Windows).

This chapter explains how to use the 3 most popular spreadsheet programs: Excel, Quattro Pro, and Microsoft Works. I’ll explain these versions:

Excel 97                                (which is part of Microsoft Office 97)

Excel 2000                            (which is part of Microsoft Office 2000)

Excel 2002                            (which is part of Microsoft Office XP)

Excel 2003                            (which is part of Microsoft Office 2003)

Quattro Pro 8                       (which is part of Corel WordPerfect Suite 8)

Quattro Pro 9                       (which is part of WordPerfect Office 2000)

Quattro Pro 10                     (which is part of WordPerfect Office 2002)

Works 4.5 spreadsheet    (which is part of Microsoft Works 4.5)

Works 5 spreadsheet        (which is part of Microsoft Works 5)

Works 6 spreadsheet       (which is part of Microsoft Works 6)

Works 7 spreadsheet        (which is part of Microsoft Works 7)

All those versions run in Windows. (Other versions are similar.)

Some of those versions have alternative names:

Works 5 is also called Works 2000.

Works 6 is also called Works 2001.

Works 7 is also called Works 2003.

Excel 2002 is also called Excel XP.


Prepare yourself

Before using spreadsheet programs, practice using word-processing programs, which are simpler and explained in my word-processing chapter. Each program comes on a CD-ROM disk; the word-processing chapter explains how to copy the CD-ROM disk to your hard disk.

Launch the spreadsheet program

Here’s how to start using your spreadsheet program.…

Quattro Pro 8 Click “Start” then “Corel WordPerfect Suite 8” then “Corel Quattro Pro 8”.

Quattro Pro 9 Click “Start” then “Programs” then “WordPerfect Office 2000” then “Quattro Pro 9”.

Quattro Pro 10 Click “Start” then “Programs” then “WordPerfect Office 2002” then “Quattro Pro 10”.

Excel 97 Click “Start” then “Programs” then “Microsoft Excel”. (If the computer shows a button labeled “Start using Microsoft Excel”, click that button.)

Excel 2000 Click “Start” then “Programs” then “Microsoft Excel”. (If the computer shows a button labeled “Start using Microsoft Excel”, click that button.)

Do this unmask procedure:

Click “View” then “Toolbars” then “Customize” then “Options”. Remove check marks from the first two boxes (“Standard and Formatting toolbars share one row” and “Menus show recently used commands first”) by clicking. Press ENTER.

Excel 2002 Click “Start” then “Programs” then “Microsoft Excel”.

Do this unmask procedure:

Click “View” then “Toolbars” then “Customize” then “Options”. Put check marks in the first two boxes (“Show Standard and Formatting toolbars on two rows” and “Always show full menus”) by clicking. Press ENTER.

Close the New Workbook window (which is at the screen’s right edge) by clicking its X button.

Excel 2003 Click “start”.

If you see “Microsoft Office Excel 2003”, click it. (Otherwise, click “All Programs” then “Microsoft Office” then “Microsoft Office Excel 2003”.)

Do this unmask procedure:

Click “View” then “Toolbars” then “Customize” then “Options”. Put check marks in the first two boxes (“Show Standard and Formatting toolbars on two rows” and “Always show full menus”) by clicking. Press ENTER.

Close the Getting Started window (which is at the screen’s right edge) by clicking its X button.

Works 4.5 Turn the computer on, so you see the Start button. Double-click the icon that says Shortcut to Microsoft Works. (If the computer says “Click the OK button to see a short demonstration”, click the Cancel button.)

Click Works Tools then the Spreadsheet button.

At the screen’s top, you see the menu bar, which looks like this:

File   Edit   View   Insert   Format   Tools   Window   Help

Click the menu bar’s last word, which is “Help”. You see a Help menu. From that menu, choose Hide Help (by clicking it).

Works 5&6&7 Turn the computer on, so you see the Start button. Double-click the Microsoft Works icon (which Works 6 might call the Microsoft Works Suite icon). Click Programs (which is near the screen’s top left corner) then Works Spreadsheet (which is near the screen’s left edge) then Start a blank Spreadsheet.

At the screen’s right edge, if you see a Works Help window, close it (by clicking its X button).


Fill in the cells

The screen shows a grid that begins like this:

┌────┬────────┬────────┬────────┬────────┬────────┬────────┐

│    │   A    │   B    │   C    │   D    │   E    │   F    │

├────╔════════╗────────┼────────┼────────┼────────┼────────┤

│  1 ║        ║        │        │        │        │        │

├────╚════════╝────────┼────────┼────────┼────────┼────────┤

│  2 │        │        │        │        │        │        │

├────┼────────┼────────┼────────┼────────┼────────┼────────┤

│  3 │        │        │        │        │        │        │

├────┼────────┼────────┼────────┼────────┼────────┼────────┤

│  4 │        │        │        │        │        │        │

└────┴────────┴────────┴────────┴────────┴────────┴────────┘

The grid’s columns are labeled A, B, C, D, E, etc.

A cheap screen (800-by-600 Super VGA)

shows up through column J in Works, K in Quattro Pro 9&10, L in other spreadsheets.

A fancy screen (1024-by-768 XVGA)

shows up through column M in Works, O in other spreadsheets.

The grid’s rows are labeled 1, 2, 3, etc.

A cheap screen (800-by-600 Super VGA)

shows 23 rows in Works 4.5&7 and Excel 2003 and Quattro Pro 9&10,
25 rows in Quattro Pro 8, 24 rows in other spreadsheets.

A fancy screen (1024-by-768 XVGA)

shows 33 rows in Works 4.5&7 and Excel 2003 and Quattro Pro 10,
36 rows in Quattro Pro 8, 34 rows in other spreadsheets.

The grid is called a spreadsheet or worksheet.

Notice that the computer puts a box in column A, row 1. If you tap the right-arrow key, that box moves to the right, so it’s in column B. If you tap the down-arrow key, the box moves down, to row 2. By tapping the four arrow keys, you can move the box in all four directions, to practically anywhere on the grid. Try it!

Each possible position of the box is called a cell.

The box’s original position (in column A, row 1) is called cell A1. If you move the box there and then tap the right-arrow key, the box moves to column B, row 1; that position is called cell B1.

Just move the box from cell to cell, and put into each cell whatever words or numbers you wish!

For example, suppose you run a small business whose income is $7000 and expenses are $5000. Those are the figures for January; the figures for February aren’t in yet. Let’s put the January figures into a spreadsheet, like this:

┌────┬────────┬────────┬────────┬────────┬────────┬────────┐

│    │   A    │   B    │   C    │   D    │   E    │   F    │

├────┼────────┼────────┼────────┼────────┼────────┼────────┤

│  1 │        │January │        │        │        │        │

├────┼────────┼────────┼────────┼────────┼────────┼────────┤

│  2 │Income  │    7000│        │        │        │        │

├────┼────────┼────────┼────────┼────────┼────────┼────────┤

│  3 │Expenses│    5000│        │        │        │        │

├────┼────────┼────────┼────────┼────────┼────────┼────────┤

│  4 │Profit  │        │        │        │        │        │

└────┴────────┴────────┴────────┴────────┴────────┴────────┘

To begin, move the box to cell A2. Type the word Income. As you type that word, you see it appearing in cell A2. It also appears temporarily at the screen’s top, in an input line (which Works calls the entry bar and Excel calls the formula bar).

Press the down-arrow key, which moves the box down to cell A3. Type the word Expenses.

Press the down-arrow key (to move to cell A4). Type the word Profit.

Move the box to cell B1 (by pressing the up-arrow three times and then the right-arrow once). Type the word January.

Press down-arrow. Type 7000.

Press down-arrow. Type 5000.

Press down-arrow again.


BACKSPACE key

If you make a mistake while typing the words and numbers, press the BACKSPACE key to erase the last character you typed.

Alternate keys

Instead of pressing the right-arrow key, you can press the TAB key. Instead of pressing the down-arrow key, you can press the ENTER key (except in Works 4.5).

Type a formula

Although the computer’s screen shows the words you typed (Income, Expenses, and Profit), the computer doesn’t understand what those words mean. It doesn’t know that “Profit” means “Income minus Expenses”. The computer doesn’t know that the number in cell B4 (which represents the profit) ought to be the number in cell B2 (the amount of income) minus the number in cell B3 (the dollars spent).

You must teach the computer the meaning of Profit, by teaching it that the number in cell B4 ought to be the number in cell B2 minus the number in cell B3. To do that, move the box to cell B4, then type this formula:

=B2-B3

Notice that every formula begins with an equal sign. The rest of the formula, B2-B3, tells the computer to subtract the number in cell B3 from the number in cell B2 and put the answer into the box’s cell (which is cell B4).

Quattro Pro prefers that you type “+” instead of “=”. For example, instead of “=B2-B3”, Quattro Pro prefers that you type “+B2-B3”. If you type “=B2-B3”, Quattro Pro will automatically turn your typing into “+B2-B3” when you press ENTER at the end of the formula.

When you’ve finished typing the formula, press the ENTER key. Then the computer automatically computes the formula’s answer (2000) and puts that number into the box’s cell (B4), so the screen looks like this:

┌────┬────────┬────────┬────────┬────────┬────────┬────────┐

│    │   A    │   B    │   C    │   D    │   E    │   F    │

├────┼────────┼────────┼────────┼────────┼────────┼────────┤

│  1 │        │January │        │        │        │        │

├────┼────────┼────────┼────────┼────────┼────────┼────────┤

│  2 │Income  │    7000│        │        │        │        │

├────┼────────┼────────┼────────┼────────┼────────┼────────┤

│  3 │Expenses│    5000│        │        │        │        │

├────┼────────┼────────┼────────┼────────┼────────┼────────┤

│  4 │Profit  │    2000│        │        │        │        │

└────┴────────┴────────┴────────┴────────┴────────┴────────┘

The formula “=B2-B3” remains in effect forever. It says that the number in cell B4 will always be the B2 number minus the B3 number. If you ever change the numbers in cells B2 and B3 (by moving the box to those cells, retyping the numbers, and pressing ENTER), the computer automatically adjusts the number in cell B4, so the number in cell B4 is still B2 minus B3 and still represents the correct profit.

For example, suppose you move the box to cell B2, then type 8000 (to change the January income to $8000), and then press ENTER. As soon as you press ENTER, the profit in cell B4 immediately changes to 3000, right in front of your eyes!

A typical spreadsheet contains dozens of numbers, totals, subtotals, averages, and percentages. Each cell that contains a total, subtotal, average, or percentage is defined by a formula. Whenever you retype one of the numbers in the spreadsheet, the computer automatically readjusts all the totals, subtotals, averages, and percentages, right before your eyes.


Remember to begin each formula with an equal sign. The rest of the formula can contain these symbols:

Symbol   Meaning

+              plus

-              minus

*               times

/              divided by

.              decimal point

It can also contain E notation and parentheses. For details about how to use those symbols, E notation, and parentheses, read pages 365-369, which explain QBASIC’s fundamentals and math.

Less typing When you’re creating a formula such as “=B2-B3”, you do not have to type the “B2”. Instead, you can choose one of these shortcuts:

Instead of typing “B2”, you can type “b2” without bothering to capitalize. When you’ve finished typing the entire formula (“=b2-b3”), press the ENTER key. Then the computer will capitalize your formula automatically!

Instead of typing “B2”, you can move the mouse pointer to the middle of cell B2, then press the mouse’s button. That’s called “clicking cell B2”. When you click cell B2, the computer automatically types “B2” for you! So to create the formula “=B2-B3”, you can do this: type the equal sign, then click cell B2, then type the minus sign, then click cell B3. When you’ve finished creating the entire formula, press ENTER.

Instead of typing “B2”, you can move the box to cell B2 by using the arrow keys. When you move the box to cell B2, the computer automatically types “B2” for you! So to create the formula “=B2-B3”, you can do this: type the equal sign, then move the box to cell B2 (by using the arrow keys), then type the minus sign, then move the box to cell B3. When you’ve finished creating the entire formula, press ENTER.

Edit old cells

To edit what’s in a cell, move the box to that cell. Then choose one of these editing methods.…

Method 1: press the DELETE key. That makes the cell become totally blank.

Method 2: retype the entire text, number, or formula that you want to put into the cell.

Method 3: in the input line (at the top of the screen), look at what you typed, find the part of your typing that you want to change, and click that part (by using the mouse). Then edit your typing as if you were using a word processor: you can use the left-arrow key, right-arrow key, BACKSPACE key, DELETE key, and mouse. When you finish editing, press the ENTER key.


Functions in Excel & Works

Here’s how to perform functions in Excel and Works. (If you’re using Quattro Pro, skip ahead to the next section.)

Sum of a column To make a cell be the sum of cells B2 through B9, you can type this formula:

=B2+B3+B4+B5+B6+B7+B8+B9

Instead of typing all that, you can type just this:

=SUM(B2:B9)

A function is a word that makes the computer calculate (such as SUM). After each function, you must put parentheses. For example, you must put parentheses after SUM.

Since the computer ignores capitalization, you can type:

=sum(b2:b9)

Here’s how to type the formula =sum(b2:b9) quickly. Begin by typing:

=sum(

Then drag from cell B2 to cell B9. To do that, move the mouse to cell B2, then hold down the mouse button while moving to B9. That makes the computer type the “B2:B9”. Here’s what to do next.…

Works: type “)”, then press ENTER.

Excel: press the ENTER key, which makes the computer automatically type the “)”.

AutoSum button Here’s an even faster way to type the formula =SUM(B2:B9). Click the AutoSum button, which is near the screen’s top center and has the symbol S on it. (The symbol S is called “sigma”; it’s the Greek version of the letter S, and mathematicians use it to stand for the word “sum”.) Clicking the AutoSum button makes the computer type “=SUM()”. It also makes the computer guess what you want the sum of. The computer puts that guess inside the parentheses. If the computer’s guess differs from what you want (B2:B9), fix the guess (by dragging from cell B2 to cell B9). When you finally see the correct formula, =SUM(B2:B9), press ENTER.

Sum of a row To find the sum of cells B2 through H2 (which is B2+C2+D2+E2+F2+G2+H2), type this:

=sum(b2:h2)

Sum of a rectangle To find the sum of all cells in the rectangle that stretches from B2 to C4 (which is B2+B3+B4+C2+C3+C4), type this:

=sum(b2:c4)

Average To find the average of cells B9 through B13, you can type this:

=(b9+b10+b11+b12+b13)/5

But this way is shorter.…

Excel:    =average(b9:b13)

Works:  =avg(b9:b13)

Here’s how to type that quickly.…

Excel: begin by typing “=average(”, then drag from cell B9 to cell B13, then press the ENTER key, which makes the computer automatically type the “)”.

Works: begin by typing “=avg(“, then drag from cell B9 to cell B13, then type  “)”, then press ENTER.

Excel 2002 provides this faster way to type the formula @AVG(B9..B13): click the u that’s next to the S button, then click “Average”.

To find the average of cells C7, B5, and F2, you can ask for (c7+b5+f2)/3, but a nicer way is to type this.…

Excel:    =average(c7,b5,f2)

Works:  =avg(c7,b5,f2)


Functions in Quattro Pro

Here’s how to perform functions in Quattro Pro. (If you’re using Excel or Works, skip this section.)

Sum of a column To make a cell be the sum of cells B2 through B9, you can type this formula:

+B2+B3+B4+B5+B6+B7+B8+B9

Instead of typing all that, you can type just this:

+@SUM(B2..B9)

A function is a word that makes the computer calculate (such as SUM). Put the symbol @ before each function: say @SUM instead of SUM.

After each function, you must put parentheses. For example, you must put parentheses after SUM.

The first parenthesis, “(”, temporarily turns red. When you type the “)”, both parentheses turn green.

Since the computer ignores capitalization, you can type:

+@sum(b2..b9)

You can omit the plus sign and the second period, and type just this:

@sum(b2.b9)

Here’s how to type the formula “@sum(b2.b9)” quickly. Begin by typing:

@sum(

Then drag from cell B2 to cell B9. To do that, move the mouse to cell B2, then hold down the mouse button while moving to B9. That makes the computer type the “B2..B9”. Finally, press the ENTER key, which makes the computer automatically type the “)”.

QuickSum button Here’s an even faster way to type the formula @SUM(B2..B9). Click the QuickSum button, which is near the screen’s top center and has the symbol S on it.

(The symbol S is called “sigma”. It’s the Greek version of the letter S, and mathematicians use it to stand for the word “sum”. In versions 9&10, if you don’t see the symbol S, make it appear by clicking the u that’s left of the A..Z button.)

Clicking the QuickSum button makes the computer do all this:

Type “@SUM()”.

Guess what you want the sum of and put that guess inside the parentheses.

Press the ENTER key for you, so the number that’s the sum appears instantly!

Sum of a row To find the sum of cells B2 through H2 (which is B2+C2+D2+E2+F2+G2+H2), type this:

@sum(b2.h2)

Sum of a rectangle To find the sum of all cells in the rectangle that stretches from B2 to C4 (which is B2+B3+B4+C2+C3+C4), type this:

@sum(b2.c4)

Average To find the average of cells B9 through B13, you can type this:

+(b9+b10+b11+b12+b13)/5

But this way is shorter:

@avg(b9.b13)

To type that quickly, begin by typing “@avg(”. Then drag from cell B9 to cell B13. Then press the ENTER key, which makes the computer automatically type the “)” for you.

Versions 9&10 provides this faster way to type the formula @AVG(B9..B13): click the u that’s left of the A..Z button, then click the first “Average”.

To find the average of cells C7, B5, and F2, you can ask for (c7+b5+f2)/3, but a nicer way is to type:

@avg(c7,b5,f2)


Undo

If you make a big mistake, click the Undo button, which is near the top of the screen, under the word “Window”, and shows an arrow turning back to the left. (In Microsoft Works, press Ctrl with Z instead.)

That makes the computer undo your last activity, so your spreadsheet returns to the way it looked before you made your boo-boo.

To undo your last two activities, click the Undo button twice. (Exception: Microsoft Works lets you undo just one activity.)

Redo If you click the Undo button, the computer might undo a different activity than you expected. If clicking the Undo button accidentally makes the spreadsheet look even worse instead of better, and you wish you hadn’t clicked the Undo button, here’s how to “undo the undo”.…

Quattro Pro 8: click the Undo button again.

Works: press Ctrl with Z again.

Excel & Quattro Pro 9&10: click the Redo button (which is to the right of the Undo button and shows an arrow bending forward to the right).

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Hop far

Here’s how to be quick as a bunny and hop far in your spreadsheet.

Farther rows

The screen shows just a few rows, which are numbered 1, 2, 3, etc. Row 1 is at the top of the screen. Row 15 is near the bottom of the screen.

Try this experiment. Move the box down to row 15 (by pressing the down-arrow key repeatedly). Then press the down-arrow key several more times. Eventually, you’ll get to row 30, and later to row 100, and much later to row 1000. (The largest row number you can go to is 8192 in Quattro Pro 8, 16384 in Works, 65536 in Excel, 1000000 in Quattro Pro 9&10.)

To make room on the screen for those new rows, row 1 disappears temporarily. If you want to get back to row 1, press the up-arrow key repeatedly.

Farther columns

The screen shows just a few columns, which are lettered A, B, C, etc. If you press the right-arrow key repeatedly, you’ll eventually get to column Z.

Altogether, the computer lets you have 256 columns. The first 26 columns are lettered from A to Z. The next 26 columns are lettered from AA to AZ. The next 26 columns are lettered from BA to BZ. And so on. (The last column is ZZZ in Quattro Pro 9, IV in other spreadsheets.)

AutoRepeat

Here’s a shortcut: instead of pressing an arrow key repeatedly, just hold down the key awhile.

Mouse

To move the box to a distant cell even faster, use the mouse: just click in the middle of the cell you wish.

Screenfuls

To move far down, press the PAGE DOWN key. To move far up, press the PAGE UP key. To move far to the right, do this.…

Quattro Pro:    press the right-arrow        key while holding down the Ctrl key.

Works:         press the PAGE DOWN   key while holding down the Ctrl key.

Excel:          press the PAGE DOWN   key while holding down the Alt key.

To move far to the left, do this.…

Quattro Pro:    press the left-arrow   key while holding down the Ctrl key.

Works:         press the PAGE UP   key while holding down the Ctrl key.

Excel:          press the PAGE UP   key while holding down the Alt key.

Each of those keys moves the box far enough so that you see the next screenful of rows and columns.

HOME key

Cell A1 is called the home cell, because that’s where life and your spreadsheet begin: at home! Column A is called the home column.

Your keyboard has a HOME key. Here’s how to use it.…

Quattro Pro:          pressing the HOME key makes the box move to cell A1.

Excel & Works:  pressing the HOME key makes the box move far left, so it lands in column A.

                           If you press the HOME key while holding down the Ctrl key, the box moves to cell A1.

Spreadsheet’s edge

Here’s how to move to the spreadsheet’s edge.…

Excel & Works:  while holding down the Ctrl key, press an arrow key.

Quattro Pro:          press the END key and then an arrow key.

For example, here’s how to move the box to the spreadsheet’s right edge:

Excel & Works:  while holding down the Ctrl key, press the right-arrow key.

Quattro Pro:          press the END key and then the right-arrow key.

That moves the box moves to the right, until it reaches the final column (IV or ZZZ) or a boundary cell (a cell containing data and next to an empty cell).

F5 key

To make the box go to a distant cell immediately: press the F5 key (or press Ctrl with G), then type the name of the cell where you want to go (such as C9) followed by ENTER.

Excel lets you also use this alternative:

Above column A, you see the Name box, which tells you the name of the cell where the box is. For example, while the box is at cell B4, the name box says “B4”. To move the box to a distant cell immediately, you can click in the name box, then type the name of the cell where you want to go (such as C9) followed by ENTER.


Adjust rows & columns

How many rows and columns are in your spreadsheet, and how big are they? Here’s how to adjust them.

Widen a column

When you start a new spreadsheet, here’s what happens.…

Works:                          each cell is wide enough to hold a 9-digit number

Quattro Pro 8&9:         each cell is wide enough to hold an 8-digit number

Quattro Pro 10 & Excel:  each cell is wide enough to hold an 8-digit number;

                                     if you type a longer number, the column widens to fit it

Here’s how to make column D be wider, so each cell in column D can hold long numbers and long words:

At the top of column D, you see the letter D. Move the mouse until its pointer is between the letters D and E, and on the vertical gridline that separates them. The pointer’s shape turns into a double-headed arrow. (In Works, the pointer is also labeled “ADJUST”.) Then drag that vertical gridline toward the right (to make the column wider) or left (to make the column narrower).

Widen several columns Excel and Quattro Pro let you widen columns D, E, F, and G simultaneously. Here’s how:

Drag from the letter D to the letter G. All those columns turn dark. (In Excel 2002&2003, they turn blue; in Excel 2000, they turn purple; in other spreadsheet programs, they turn black.)

Look at the vertical gridline to the right of the D. Drag the top of that gridline toward the right. That widens column D; and when you release your finger from the mouse’s button, all the other columns you selected will widen also.

Perfect width Here’s how to make column D just wide enough to hold the widest data in it.…

Works:         double-click the D at the top of column D.

Quattro Pro:    double-click the D at the top of column D, or else do this:

                    click the D at the top of column D, then click the QuickFit button

                    (which is under the word “Help” and shows “¡¢” in a cell).

Excel:          double-click the gridline that separates the letter D from E.

(If the column doesn’t contain data yet, the computer will leave the column’s width unchanged.)

Excel and Quattro Pro let you make columns D, E, F, and G have perfect widths simultaneously. Here’s how. Drag from the letter D to the letter G, so all those columns turn dark. Then do this.…

Excel:              double-click the gridline that separates the letter D from E.

Quattro Pro:       click the QuickFit button

                       (which is under the word “Help” and shows “¡¢” in a cell).

Long numbers If you try to type a long number in a cell that’s too narrow to hold the number, the cell might display symbols instead of the number.

For example, try typing a long number in a cell that’s just 4 characters wide. Instead of displaying the long number, Excel and Works display 4 number signs (like this: ####); Quattro Pro displays 4 asterisks (like this: ****).

Although the cell displays just those symbols, the computer remembers the long number you typed. To see the long number, widen the cell (by widening its column).

So if you see symbols in a cell, the computer is telling you that the cell is too narrow and should be widened.


Long words Try this experiment. Make cell B1 be just 4 characters wide. Then try to type the word “January” in that cell.

That cell, B1, might show just the first 4 letters (Janu). But if the next cell (C1) is blank, cell B1 will temporarily widen to hold “January”, then contract to its original size (4 characters) when you enter data in cell C1.

Delete a column

Here’s how to delete column D:

Quattro Pro: click the D at the top of column D, then click the Delete Cells button (which is under the word “Help” and shows “-” in a cell).

Works: right-click anywhere in column D (by using the mouse’s right button instead of the left), then choose Delete Column from the menu that appears.

Excel: right-click the D at the top of column D (by using the mouse’s right button instead of the left); then choose Delete from the menu that appears.

The computer erases all the data from column D, so column D becomes blanks, which the computer immediately fills by shifting some data from other columns. Here’s how.…

Into column D, the computer moves the data from column E. Then into column E, the computer moves the data from column F. Then into column F, the computer moves the data from column G. And so on.

At the end of the process, the top of the screen still shows all the letters (A, B, C, D, E, F, G, etc.); but now column D contains the data that used to be in column E; and column E contains the data that used to be in column F; etc.

After rearranging the spreadsheet, the computer fixes all formulas. For example, after column E’s data has moved to column D, the computer hunts through all formulas in the spreadsheet and fixes them by changing each “E” to “D”. The computer also changes each “F” to “E”, each “G” to “F”, etc.

Delete several columns You’ve learned how to delete column D. Here’s how to delete several columns. To delete columns D, E, F, and G, drag from the D to the G, then do the following.…

Quattro Pro: click Delete Cells button.

Works: right-click anywhere in columns D through G (by using the mouse’s right button instead of the left), then choose Delete Column from the menu that appears.

Excel: right-click anywhere in columns D through G (by using the mouse’s right button instead of the left); then choose Delete from the menu that appears.

Delete a row

Here’s how to delete row 2:

Quattro Pro: click the 2, then click the Delete Cells button.

Works: right-click anywhere in row 2 (by using the mouse’s right button instead of the left), then choose Delete Row from the menu that appears.

Excel: right-click the 2 (by using the mouse’s right button instead of the left); then choose Delete from the menu that appears.

Then the computer erases all the data from row 2, so row 2 becomes empty; but then the computer immediately fills that hole, by shifting the data from other rows. Here’s how.…

Into row 2, the computer moves the data from row 3. Then into row 3, the computer moves the data from row 4. Then into row 4, the computer moves the data from row 5. And so on.

At the end of the process, the left edge of the screen still shows all the numbers (1, 2, 3, 4, 5, etc.); but now row 2 contains the data that used to be in row 3; and row 3 contains the data that used to be in row 4; etc.

The computer fixes all formulas.

Insert a column

Here’s how to insert an extra column in the middle of your spreadsheet:

Quattro Pro: click where you want the extra column to appear. For example, if you want the extra column to appear where column D is now, click the D. Then click the Insert Cells button (which is under the word “Help” and has “+” on it).

Works: right-click where you want the extra column to appear. For example, if you want the extra column to appear where column D is now, right-click in column D. Then choose Insert Column from the menu that appears.

Excel: right-click where you want the extra column to appear. For example, if you want the extra column to appear where column D is now, right-click the D. Then choose Insert from the menu that appears.

The computer will move other columns out of the way, to make room for the extra column. The computer will also fix each formula.

Insert a row

Here’s how to insert an extra row in the middle of your spreadsheet:

Quattro Pro: click where you want the extra row to appear. For example, if you want the extra row to appear where row 2 is now, click the 2. Then click the Insert Cells button.

Works: right-click where you want the extra row to appear. For example, if you want the extra row to appear where row 2 is now, right-click in row 2. Then choose Insert Row from the menu that appears.

Excel: right-click where you want the extra row to appear. For example, if you want the extra row to appear where row 2 is now, right-click the 2. Then choose Insert from the menu that appears.

The computer will move other rows out of the way, to make room for the extra row. The computer will also fix each formula.

Zoom

You can make your screen show twice as many rows and columns.

Here’s how in Excel:

Near the screen’s top right corner, you see a percentage, which is normally 100%. That percentage is in a white box, called the Zoom box. Click its down-arrow.

You’ll see the Zoom menu. From that menu, choose 50%. The computer will make all the screen’s characters tiny (half as tall and half as wide), so twice as many rows and twice as many columns fit on the screen.

To make the screen return to normal, click the Zoom box’s down-arrow again, then click 100%.

If you wish, you can click different percentages, such as 75% (which shrinks the screen’s characters just slightly) or 200% (which enlarges the screen’s characters, so you can read them even if you’re sitting far away from the screen).

Try this trick: start at one cell, and drag to another cell far away. All the cells between them turn dark. Then click the Zoom box’s down-arrow and click Selection. That shrinks or enlarges the characters just enough so all the dark cells fit on the screen.

Here’s how in Quattro Pro:

Near the screen’s top right corner, you see the Zoom button, which looks like a magnifying glass. Click it.

You’ll see the Zoom menu. From that menu, choose 50%. The computer will make all the screen’s characters tiny (half as tall and half as wide), so twice as many rows and twice as many columns fit on the screen.

To make the screen return to normal, click the Zoom button again, then choose 100% from the Zoom menu.

If you wish, you can click different percentages, such as 75% (which shrinks the screen’s characters just slightly) or 200% (which enlarges the screen’s characters, so you can read them even if you’re sitting far away from the screen).

Try this trick: start at one cell, and drag to another cell far away. All the cells between them turn black. Then click the Zoom button and choose Selection from the Zoom menu. That shrinks or enlarges the characters just enough so that all the black cells fit on the screen.


Here’s how in Works:

Near the screen’s bottom left corner, you see the word Zoom. Next to it, you normally see 100%.

Near it, you see a plus sign. If you click that plus sign, the computer makes the screen’s characters be enlarged, so their size is 150% as wide and 150% as tall as normal, and the Zoom box says “150%” instead of “100%”.

If you click the plus sign again, the computer makes the screen’s characters be even larger, so their size is 200%. If you click the plus sign again, the computer makes the screen’s characters be even larger: 400%.

If you click the minus sign instead, the characters become smaller. By clicking the plus or minus sign repeatedly, you can choose these sizes: 50%, 75%, 100%, 150%, 200%, 400%.

50% and 75% are very useful: they make the characters smaller, so more characters fit on the screen and you see more rows and columns.

For further choices, click the word Zoom. Then you see this Zoom menu:

50%

75%

100%

150%

200%

400%

Custom

Click whichever choice you wish.

If you prefer a different percentage, choose Custom then type the percentage you want (such as 90) and press ENTER.

All those Zoom choices affect just what you see on the screen. They do not affect what’s printed on paper.

Panes

On your screen, you see a window that contains part of your spreadsheet. (That window is big enough to usually show columns A through I on a cheap screen, and more columns on a fancier screen.)

You can divide that window into two windowpanes, so that each windowpane shows a different part of your spreadsheet.

Vertical panes You can divide your window into two windowpanes, so that the left pane shows columns A, B, and C, while the right pane shows columns X, Y and Z.

Here’s how in Quattro Pro:

Press the HOME key (so the box moves to cell A1). Click View then Split Window then Vertical then OK.

The screen splits into two panes. The left pane shows columns A, B, C, etc. The right pane shows columns that are farther to the right.

Here’s how in Excel:

Get column A onto the screen (by pressing the HOME key).

Near the screen’s bottom right corner, you see the symbol 4 (or >), which points at a vertical bar. Put your mouse pointer on that vertical bar; when you do, the pointer becomes this symbol: ¡¢. Drag that vertical bar to the left. As you drag, you’ll see a vertical gray bar move across your spreadsheet. Drag until the vertical gray bar is in the middle of the spreadsheet. For best results, drag until that bar is slightly to the right of column C’s right edge.

That bar splits the screen into two panes. The left pane shows columns A through C; the right pane shows column D and beyond.

Here’s how in Works:

Get column A onto the screen (by pressing the HOME key).

Near the screen’s bottom left corner, you see the word “Zoom”. Left of it, you see a vertical bar. Put your mouse pointer on that bar; when you do, the pointer becomes the symbol ¡¢ and is labeled “ADJUST”. Drag that vertical bar to the right. As you drag, you’ll see a vertical gray bar move across your spreadsheet. Drag until the vertical gray bar is in the middle of the spreadsheet. For best results, drag until that bar is slightly to the right of column C’s right edge.

That bar splits the screen into two panes. The left pane shows columns A through C; the right pane shows column D and beyond.

Then click anywhere in the right pane. That puts the box in the right pane, and makes the right pane active. Press the right-arrow key several times, until you reach columns X, Y, and Z.


If you want to move the box back to the left pane, just click the left pane.

Here’s how to stop using vertical panes.…

Excel and Works:   double-click the vertical gray bar.

Quattro Pro:              click View then Split Window then Clear then OK.

Horizontal panes You can divide your window into two panes, so that the top pane shows rows 1, 2, and 3, while the bottom pane shows rows 97, 98, and 99.

Here’s how in Quattro Pro:

Press the HOME key (so the box moves to cell A1). Click View then Split Window then Horizontal then OK.

The screen splits into two panes. The left pane shows rows 1, 2, 3, etc. The bottom pane shows rows that have bigger numbers.

Here’s how in Excel and Works:

Get row 1 onto the screen (by pressing the PAGE UP key several times).

At the spreadsheet’s top right corner, you’ll see the scroll bar’s up-arrow pointing at a horizontal bar. Put the mouse pointer on that bar; when you do, the pointer becomes the symbol ¤£ (and in Works is labeled “ADJUST”). Drag that bar down. As you drag, you’ll see a horizontal gray bar move down your spreadsheet. Drag until the horizontal gray bar is in the middle of the spreadsheet. For best results, drag until that bar is slightly under row 3’s bottom edge.

That bar splits the screen into two panes. The top pane shows rows 1 through 3; the bottom pane shows row 4 and beyond.

Then click anywhere in the bottom pane. That puts the box in the bottom pane, and makes the bottom pane active. Press the down-arrow key several times, until you reach rows 97, 98, and 99.

If you want to move the box back to the top pane, just click the top pane.

Here’s how to stop using vertical panes:

Excel and Works:   double-click the horizontal gray bar.

Quattro Pro:              click View then Split Window then Clear then OK.

Freeze title panes You should put a title at the top of each column. For example, if column B contains financial information for January, and column C contains financial information for February, you should put the word January at the top of column B, and the word February at the top of column C. Since the words January and February are at the top of the columns, they’re in row 1. They’re called the column titles.

If row 2 analyzes Income, and row 3 analyzes Expenses, you should put the word Income at the left edge of row 2, and the word Expenses at the left edge of row 3. Since the words Income and Expenses are at the left edge of the spreadsheet, they’re in column A. They’re called the row titles.

So in a typical spreadsheet, the column titles are in row 1, and row titles are in column A.

Unfortunately, when you move beyond column L or beyond row 24 (by pressing the arrow keys repeatedly), the titles normally disappear from the screen, and you forget the purpose of each row and column. Here’s how to solve that problem.…

In Quattro Pro, do this:

Get cell A1 onto the screen (by pressing the HOME key).

Click cell B2. Click View then Locked Titles.

Now the window is divided into two panes, separated by blue gridlines. One pane contains the column titles (January, February, etc.) and row titles (Income, Expenses, etc.); the other pane is huge and contains all the spreadsheet’s data.

In Excel and Works, do this:

Get cell A1 onto the screen (by pressing CONTROL with HOME).

Click cell B2. In Excel, choose Freeze Panes from the Window menu; in Works, choose Freeze Titles from the Format menu.

Now the window is divided into four panes, separated by thick black gridlines. The main top pane contains the column titles (January, February, etc.); the main left pane contains the row titles (Income, Expenses, etc.); a tiny pane in the upper-left corner contains a blank cell; and a huge pane contains all the spreadsheet’s data.

Then move through the huge pane, by using the arrow keys or mouse. As you move, the column and row titles stay fixed on the screen, since they’re not in the big pane.

Here’s how to stop using freeze title panes.…

Excel: choose Unfreeze Panes from the Window menu.

Works: click Format, then remove the check mark in front of Freeze Titles (by clicking Freeze Titles again).

Quattro Pro: click View, then remove the check mark in front of Locked Titles (by clicking Locked Titles again).

Move

On your spreadsheet, find these cells: B2, B3, B4, C2, C3, and C4. Those six cells are next to each other. In fact, they form a giant rectangular area, whose top left corner is B2.

Here’s how to take all the data in that rectangle and move it to a different part of your spreadsheet.

Drag from the rectangle’s first cell (B2) to the rectangle’s last cell (C4). The entire rectangle turns dark (except for the first cell, which stays white).

Surrounding the rectangle, you’ll see four walls. Those walls are the four sides of the rectangle.

Using your mouse, point at one of the rectangle’s walls. (Do not point at a corner.) When you’ve pointed correctly, here’s what happens.…

Excel 97&2000 and Works: the mouse pointer turns into an arrow (not a cross).

Excel 2002&2003 and Quattro Pro: the mouse pointer turns into 4 arrows, pointing in all 4 directions.

Then hold down the mouse’s button and drag the wall. While you drag the wall, the rest of the rectangle drags along with it. Drag until the entire rectangle is at a part of the spreadsheet that was blank. Then lift your finger from the mouse’s button.

That’s how you move a rectangle of data to a new place in your spreadsheet that had been blank.

Try it!

After moving the rectangle of data, the computer automatically adjusts all formulas mentioning the moved cells. For example, if the data in cell B2 has moved to cell E7, the computer searches through the entire spreadsheet and, in each formula, changes “B2” to “E7”.


Copy

Spreadsheet programs let you copy info in several ways.

Fill to the right

Here’s how to make lots of love with the computer!

In a cell, type the word “love”.

Click in that cell (to make sure the cell is highlighted), then take your finger off the mouse’s button. With your finger still off the mouse’s button, move the mouse until the mouse’s pointer is at that cell’s bottom right corner. When the pointer is exactly at the corner, the pointer changes to this thin cross: Ç.

Then hold down the mouse’s left button, and drag toward the right, until you’ve dragged across several cells. (In Quattro Pro, make sure you drag across cells that are all blank.)

When you lift your finger off the mouse’s button, all those cells will contain copies of the word in the first cell. They’ll all say “love”!

Go ahead! Try turning your computer into a lovemaking machine! Do it now! This is an important exercise to try before you get into more advanced computer orgies!

Here’s another example:

In a cell, type the word “tickle”. To make lots of tickles, click in that cell, then point at that cell’s bottom right corner (so you see Ç) and drag it to the right. (In Quattro Pro, make sure the cells you’re dragging across are blank.) The cells you drag across will all say “tickle”.

Fill down

When you point at a cell’s bottom right corner and drag, you usually drag to the right. But if you prefer, you can drag down, so you’re copying to the cells underneath (instead of the cells to the right).

Extend a series

You’ve learned that if the original cell said “love”, the adjacent cells will say “love”; and if the original cell said “tickle”, the other cells will say “tickle”.

But if the original cell said “January”, the adjacent cells will not say “January”. Instead, the computer makes them say “February”, “March”, “April”, “May”, etc.

So here’s how to put the words “January”, “February”, “March”, “April”, etc., across the top of your spreadsheet:

Begin by typing “January” in cell B1. Then drag that cell’s bottom right corner to the right, to column H or I or even farther! The farther you drag, the more months you’ll see!

Your computer performs fundamental tricks:

If you start with January,        the computer will say February, March, April, etc.

If you start with October,       the computer will say November, December, January, etc.

If you start with 12/29/99,      the computer will say 12/30/99, 12/31/99, 1/1/00, etc.

If you start with Monday,    the computer will say Tuesday, Wednesday, Thursday, etc.

If you start with Mon,            the computer will say Tue, Wed, Thu, etc.

If you start with 10:00 AM,    the computer will say 11:00 AM, 12:00 PM, 1:00 PM, etc.

If you start with Q2,               the computer will say Q3, Q4, Q1, etc.

If you start with Idiot 1,         the computer will say Idiot 2, Idiot 3, Idiot 4, etc.

If you start with Year 1991,       the computer will say Year 1992, Year 1993, Year 1994, etc.

Works performs these extra tricks:

If you start with July 29,         the computer will say July 30, July 31, August 01, etc.

If you start with July 29, 1999,  the computer will say July 30, 1999, July 31, 1999, August 01, 1999, etc.

If you start with October 1999,  the computer will say November 1999, December 1999, January 2000, etc.

If you start with 10:00,           the computer will say 11:00, 12:00, 13:00, etc.

If you start with 22:00,           the computer will say 23:00, 0:00, 1:00, etc.

If you start with 2Q,               the computer will say 3Q, 4Q, 1Q, etc.

Limitation: if you start with just a number (such as 1), the computer will just copy that number; it will not say 2, 3, 4, etc. If you start with just the plain number 1991, the computer will just copy that number; it will not say 1992, 1993, 1994, etc. To make the computer do more than just copy, put a word before the number. For example, instead of saying just 1, say “Idiot 1”; then the computer will say “Idiot 2”, “Idiot 3”, “Idiot 4”, etc. Instead of saying just 1991, say “Year 1991” or “People We Accidentally Shot In 1991”; then the computer will generate similar headings for 1992, 1993, etc.

Excel performs these extra tricks:

If you start with Jan,               the computer will say Feb, Mar, Apr, etc.

If you start with 29-Jan,         the computer will say 30-Jan, 31-Jan, 1-Feb, etc.

If you start with Oct-98,         the computer will say Nov-98, Dec-98, Jan-99, etc.

If you start with 29-Dec-98,   the computer will say 30-Dec-98, 31-Dec-98, 1-Jan-99, etc.

If you start with 29-Dec-99,   the computer will say 30-Dec-99, 31-Dec-99, 1-Jan-00, etc.

If you start with 10:00,           the computer will say 11:00, 12:00, 13:00, etc.

If you start with 22:00,           the computer will say 23:00, 0:00, 1:00, etc.

If you start with 2nd Quarter, the computer will say 3rd Quarter, 4th Quarter, 1st Quarter, etc.

If you start with 2nd Qtr,        the computer will say 3rd Qtr, 4th Qtr, 1st Qtr, etc.

If you start with 2 Q,              the computer will say 3 Q, 4 Q, 1 Q, etc.

If you start with Quarter 2,     the computer will say Quarter 3, Quarter 4, Quarter 1, etc.

If you start with 1st,               the computer will say 2nd, 3rd, 4th, etc.

If you start with 1st Idiot,       the computer will say 2nd Idiot, 3rd Idiot, 4th Idiot, etc.

If you start with 1991 Results,   the computer will say 1992 Results, 1993 Results, 1994 Results, etc.

Limitation: if you start with just a plain number (such as 1), the computer will just copy that number; it will not say 2, 3, 4, etc. If you start with just the plain number 1991, the computer will just copy that number; it will not say 1992, 1993, 1994, etc. To make the computer do more than just copy, include a word. For example, instead of saying just 1, say “Idiot 1”; then the computer will say “Idiot 2”, “Idiot 3”, “Idiot 4”, etc. Instead of saying just 1991, say “Year 1991” or “1991 Results” or “People We Accidentally Shot In 1991”; then the computer will generate similar headings for 1992, 1993, etc.

Quattro Pro performs these extra tricks:

If you start with Jan,               the computer will say Feb, Mar, Apr, etc.

If you start with 29-Jan,         the computer will say 30-Jan, 31-Jan, 1-Feb, etc.

If you start with Oct-98,         the computer will say Nov-98, Dec-98, Jan-99, etc.

If you start with 29-Dec-98,   the computer will say 30-Dec-98, 31-Dec-98, 1-Jan-99, etc.

If you start with 29-Dec-99,   the computer will say 30-Dec-99, 31-Dec-99, 1-Jan-00, etc.

If you start with October 1998,   the computer will say November 1998, December 1998, January 1999, etc.

If you start with 10:00,           the computer will say 11:00, 12:00, 1:00, etc. (just in Quattro Pro 9&10)

If you start with 2nd Quarter, the computer will say 3rd Quarter, 4th Quarter, 1st Quarter, etc.

If you start with 2nd Qtr,        the computer will say 3rd Qtr, 4th Qtr, 1st Qtr, etc.

If you start with 2 Q,              the computer will say 3 Q, 4 Q, 1 Q, etc.

If you start with Quarter 2,     the computer will say Quarter 3, Quarter 4, Quarter 1, etc.

If you start with 1st,               the computer will say 2nd, 3rd, 4th, etc.

If you start with 1st Idiot,       the computer will say 2nd Idiot, 3rd Idiot, 4th Idiot, etc.

If you start with 1,                  the computer will say 2, 3, 4, etc.

If you start with 7,                  the computer will say 8, 9, 10 etc.

If you start with -3,                the computer will say -2, -1, 0, etc.

If you start with 1991 Results,   the computer will say 1992 Results, 1993 Results, 1994 Results, etc.

If you start with 0.1,               the computer will say 0.2, 0.3, 0.4, etc.

If you start with 0.2,               the computer will say 0.4, 0.6, 0.8, etc.

If you start with 0.31,             the computer will say 0.62, 0.93, 1.24, etc.


Copy a formula’s concept

If you ask the computer to copy a formula, the computer will copy the concept underlying the formula.

Here’s an example:

Suppose you put this formula in cell B4: =B2+B3. That means cell B4 contains “the sum of the two numbers above it”. If you drag that cell’s bottom right corner to the right, the computer will copy that formula’s concept to the adjacent cells (C4, D4, E4, etc.).

For example, the computer will make C4’s formula be “the sum of the two numbers above it”, by making C4’s formula be =C2+C3. The computer will make D4’s formula be =D2+D3. The computer will make E4’s formula be =E2+E3.

Here’s another example:

Suppose cell B4 contains the formula =2*B3, so that B4 is “twice the cell above it”. When the computer copies that concept to cell C4, the computer will make C4’s formula be “twice the cell above it”; the computer will make C4’s formula be =2*C3.

Here’s another example:

Suppose cell B4 contains the formula =2*A4, so that B4 is “twice the cell to the left of it”. When the computer copies cell B4 to C4, the computer will make C4’s formula be “twice the cell to the left of it”; the computer will make C4’s formula be =2*B4.

Absolute addresses Notice again how copying from B4 to C4 turns the formula =B2+B3 into =C2+C3: it turns each B into a C.

If you want to prevent those changes, put dollar signs in the original formula. For example, if you want to prevent B3 from turning into D3, put dollar signs around the B3, so cell B4 contains this formula:

=B2+$B$3

When you copy that cell to C4, the dollar signs prevents the computer from turning the B3 into C3; C4’s formula will become =C2+$B$3 (instead of =C2+C3).

Here’s how to type “=B2+$B$3” quickly. Type the “=” sign, then move the box to cell B2, then type the “+” sign. Finally, create the $B$3 by using this trick: move the box to cell B3, then press the F4 key. (In Quattro Pro, press the F4 key five times instead of once.) When you’ve finished creating the entire formula, press ENTER.

A cell’s name (such as B3) is called the cell’s address, because the cell’s name tells you where to find the cell. An address that contains dollar signs (such as $B$3) is called an absolute address, because the address is absolutely fixed and will never change, not even when you copy the formula. An address that lacks dollar signs is called a relative address, because when you copy that address you’ll be copying the cell’s relationship to the other cells.

After you’ve finished

Finished creating your spreadsheet? Here’s how to copy it to the disk and printer and move on to another task.

Find the buttons

Most spreadsheet programs have 4 buttons near the screen’s top left corner:

The first          is the New   button. It looks like a new blank sheet of paper.

The second      is the Open  button. It looks like a file folder pried open.

The third         is the Save   button. It looks like a 3½-inch floppy disk.

The fourth      is the Print  button. It looks like a printer, printing on paper.

Each spreadsheet program is peculiar.

Excel:              the buttons are under the word “File”

Excel 2000:     the buttons are under the word “File”; Excel 2000 also inserts an E-mail button

Excel 2002:     the buttons are under the word “File”; Excel 2002 also inserts E-mail and Search buttons

Quattro Pro:       the buttons are under the word “File”;  the New button is rather useless

Works 5&6&7:   the buttons are under the word “Tools”; the New and Open buttons are rather useless

Works 4.5:      Save and Print buttons are under the word “Window”; New and Open buttons are missing

Here’s how to use the helpful buttons.…

Save button

To save the spreadsheet (copy it onto the disk), click the Save button.

If you haven’t saved the spreadsheet before, the computer will say “File Name”. Invent a name for your spreadsheet. Type the name and press ENTER.

That makes the computer copy the spreadsheet onto the hard disk.

For example, if you named the spreadsheet “mary”, here’s what happens:

Excel makes that spreadsheet be a file called mary.xls (meaning “Mary’s Excel spreadsheet”).

The computer puts that file into the My Documents folder.

Quattro Pro 9&10 make that spreadsheet be a file called mary.qpw (meaning “Mary’s Quattro Pro workbook”). The computer puts that file into the My Documents folder.

Quattro Pro 8 makes that spreadsheet be a file called mary.wb3 (meaning “Mary’s workbook, type 3”).

The computer will puts that file into the MyFiles folder.

Works 6&7 makes that spreadsheet be a file called mary.xlr.

The computer puts that file into the My Documents folder.

Works 5 makes that spreadsheet be a file called mary.wks (meaning “Mary’s worksheet”).

The computer puts that file into the My Documents folder.

Works 4.5 makes that spreadsheet be a file called mary.wks (meaning “Mary’s worksheet”).

The computer puts that file into the Documents folder that’s in the MSWorks folder (which is in the Program Files folder), so the file will actually be called “C:\Program Files\MSWorks\Documents\mary.wks”.

Afterwards, if you change your mind and want to do more editing, go ahead! When you finish that extra editing, save it by clicking the Save button again.

Save often If you’re typing a long document, click the Save button about every 10 minutes. Click it whenever you get to a good stopping place and think, “What I’ve typed so far looks good!”

Then if an accident happens, you’ll lose at most 10 minutes of work, and you can return to the last version you felt good about.

Print button

To make print your spreadsheet onto paper, click the Print button. (If you’re using Quattro Pro, then press ENTER.)


Page Setup

Here’s a trick. Before clicking the Print button, try choosing Page Setup from the File menu. Then tell the computer what kind of printing you prefer. Here’s how.…

Works Click the Margins tab.

Normally, the computer leaves 1-inch margins at the top and bottom of the paper and 1¼-inch margins at the sides. To change those sizes, press the TAB key and type the number of inches you want for the Top Margin, then do the same for the Bottom Margin, Left Margin, and Right Margin.

Click the tab called “Source, Size & Orientation”.

For Orientation, click either Portrait or Landscape. Normally, the computer does Portrait. If you click Landscape instead, the computer will rotate the spreadsheet 90 degrees, so more columns will fit on the paper.

Click Other Options.

Normally, the computer doesn’t bother to print the spreadsheet’s gridlines (the lines that separate the columns from each other and the rows from each other). If you insist that the computer print the gridlines, put a check mark in the Print Gridlines box, by clicking that box.

Normally, the computer doesn’t bother to print the column names (A, B, C) and row names (1, 2, 3). If you insist that the computer print those names, put a check mark in the Print Row and Column Headers box, by clicking that box.

Quattro Pro Click Paper Type.

Click either Portrait or Landscape. Normally, the computer does Portrait. If you click Landscape instead, the computer will rotate the spreadsheet 90 degrees, so more columns will fit on the paper.

Click Header/Footer.

If your spreadsheet is several pages long, here’s how to make the computer print “Budget, page 1” at the top of page 1, print “Budget, page 2” at the top of page 2, etc. Put a check mark in the first Create box, by clicking that box. To the right of that box is a bigger box; click it. In that bigger box, type “Budget, page #”.

Click Print Margins.

Normally, the computer leaves 0.33-inch margins at the top and bottom of the paper and 0.40-inch margins at the sides. Here’s how to change those sizes. (If you’re using version 8 or 9, click “Print Margins” again, then press the TAB key.) Type the number of inches you want for the Top margin, press TAB, type the number of inches for the Bottom margin, press TAB, type the Left margin, press TAB, and type the Right margin.

Click Print Scaling.

Have you ever taken a photo and asked for an “enlargement”? The computer can do the same thing: when it prints your spreadsheet onto paper, it can produce an enlargement (so you can read the spreadsheet even if you’re standing far away from the sheet of paper). The computer can also produce a reduction (so the spreadsheet is made of tiny characters and consumes less paper). Enlargements and reductions are called scaling.

Normally, the computer does not do scaling: it prints at 100% of original size. To make the computer do scaling, click the Scale To button (which versions 8&9 call the “Print to %” button), then double-click the number in the box to its right, then type a percentage different from 100%. For example, if you want the spreadsheet to look gigantic (twice as tall and twice as wide), type 200. If you want the spreadsheet to look tiny (miniaturized), type 50.

If the spreadsheet has many rows and columns and you want to make the characters small enough so the entire spreadsheet fits on one sheet of paper, click the Shrink To button instead (which versions 8&9 call the Print to desired width” button).

Click Options.

Normally, the computer doesn’t bother to print the spreadsheet’s gridlines (the lines that separate the columns from each other and the rows from each other). If you insist that the computer print the gridlines, put a check mark in the Gridlines box, by clicking that box.

Normally, the computer doesn’t bother to print the column names (A, B, C) and row names (1, 2, 3). If you insist that the computer print those names (and fill the top left corner by putting an extra A there), put a check mark in the Row/Column Borders box, by clicking that box.

Excel Click Page.

For Orientation, click either Portrait or Landscape. Normally, the computer does Portrait. If you click Landscape instead, the computer will rotate the spreadsheet 90 degrees, so more columns will fit on the paper.

Have you ever taken a photo and asked for an “enlargement”? The computer can do the same thing: when it prints your spreadsheet onto paper, it can produce an enlargement (so you can read the spreadsheet even if you’re standing far away from the sheet of paper). The computer can also produce a reduction (so the spreadsheet is made of tiny characters and consumes less paper). Enlargements and reductions are called Scaling. Normally, the computer does not do scaling: it prints at 100% of original size. To make the computer do scaling, click the Adjust To button, then type a percentage different from 100%. For example, if you want the spreadsheet to look gigantic (twice as tall and twice as wide), type 200. If you want the spreadsheet to look tiny (miniaturized), type 50. If the spreadsheet has many rows and columns and you want to make the characters small enough so the entire spreadsheet fits on one sheet of paper, click the Fit To button instead.

Click Margins.

Normally, the computer leaves 1-inch margins at the top and bottom of the paper and 3/4-inch margins at the sides. To change those sizes, press the TAB key and type the number of inches you want for the Top margin, then do the same for the Bottom margin, Left margin, and Right margin.

Normally, the computer starts printing the spreadsheet near the paper’s top left corner. If you want the spreadsheet to be centered instead, put a check mark in the Center Horizontally and Center Vertically boxes, by clicking those boxes.

Click Header/Footer.

If your spreadsheet is several pages long, here’s how to make the computer print a page number at the top of each page: click the Header box’s down-arrow, then click Page 1.

If instead you want the top of each page to have this header —

Annual blood drive             1999 results                by Count Dracula

do this: click Custom Header, then type the left part (“Annual blood drive”), press the TAB key, type the center part (“1999 results”), press TAB again, type the right part (“by Count Dracula”), and click the OK button above the right part.

Click Sheet.

Normally, the computer doesn’t bother to print the spreadsheet’s gridlines (the lines that separate the columns from each other and the rows from each other). If you insist that the computer print the gridlines, put a check mark in the Gridlines box, by clicking that box.

Normally, the computer doesn’t bother to print the column names (A, B, C) and row names (1, 2, 3). If you insist that the computer print those names, put a check mark in the Row and column headings box, by clicking that box.

Final step When you finish expressing all your preferences to the computer, click OK. Then click the Print button. (For Quattro Pro, then press ENTER.)

Those preferences affect the printing of just the current spreadsheet. They don’t affect other spreadsheets you create later.

Leave the spreadsheet

When you finish working on a spreadsheet, do this.…

Quattro Pro Choose Exit or Close from the File menu.

If you choose Exit, the computer stops using Quattro Pro.

If you choose Close instead of Exit, you see a blank spreadsheet. Fill it in (to construct a new spreadsheet) or click the Open button.

If you click the Open button, you see a list of old spreadsheets. If you want to use one of those spreadsheets, double-click the spreadsheet’s name; the computer will put that spreadsheet onto the screen and let you edit it. If you want to delete one of those spreadsheets, click the spreadsheet’s name and then press the DELETE key and then the ENTER key; the computer will move that spreadsheet to the Recycle Bin.


Excel Choose Exit or Close from the File menu.

If you choose Exit, the computer stops using Excel.

If you choose Close instead of Exit, the computer lets you work on another spreadsheet. Then click the New button or the Open button.

If you click the New button, the computer lets you start typing a new spreadsheet.

If you click the Open button, you see a list of old spreadsheets. If you want to use one of those spreadsheets, double-click the spreadsheet’s name; the computer will put that spreadsheet onto the screen and let you edit it. If you want to delete one of those spreadsheets, click the spreadsheet’s name and then press the DELETE key and then the ENTER key; the computer will move that spreadsheet to the Recycle Bin.

Works 4.5 Choose Exit Works or Close from the File menu.

If you choose Exit Works, the computer stops using Microsoft Works.

If you choose Close instead of Exit Works, the computer says “Works Task Launcher”. Then click the Spreadsheet button or Existing Documents.

If you click the Spreadsheet button, the computer lets you start typing a new spreadsheet.

If you click Existing Documents and then double-click the name of an old spreadsheet, the computer puts that spreadsheet onto the screen and lets you edit it.

Works 5&6 Click the X at the screen’s top right corner. Then you have three choices:

If you click the X at the screen’s top right corner again, the computer stops using Microsoft Works.

If you click Programs then Start a blank Spreadsheet, the computer lets you start typing a new spreadsheet.

If you click History, you see a list of old spreadsheets (and other Works creations). To use one of those spreadsheets, click the spreadsheet’s name.

Didn’t save? If you didn’t save your document before doing those procedures, the computer asks, “Save changes?” (In Excel, the computer asks “Do you want to save?” instead.)

If you click “Yes”, the computer copies your spreadsheet’s most recent version to the hard disk; if you click “No” instead, the computer ignores and forgets your most recent editing.

Congratulations! You’ve learned all the fundamental spreadsheet commands!

 

 

Beautify your cells

Here’s how to make the cells in your spreadsheet look beautiful.

First, if you’re in the middle of typing a number or word, finish typing it and then press the ENTER key.

Next, select which cells you want to beautify. Here’s how.

To select one cell, click it. To select several adjacent cells, drag from the first cell you want to the last cell. To select a whole rectangular area, drag from one corner of rectangle to the opposite corner.

To select column D, click the D.

To select columns D through G, point at the D and drag to the G.

To select row 2, click the number 2 at the left edge of row 2.

To select rows 2 through 5, point at the 2 and drag to the 5.

To select the entire spreadsheet, click the empty box that’s left of the letter A.

When doing one of those selections, use the mouse.

The part of the spreadsheet you’ve selected is called the selection (or range). It’s turned entirely dark, except for the cell where the box is. (In Excel 2002&2003, “dark” is blue; in Excel 2000, “dark” is purple; in other spreadsheet programs, “dark” is black.)

If your selection includes at least 2 numbers, Quattro Pro makes the screen’s bottom right corner show you their statistics (sum, average, count, maximum, and minimum); Excel makes the screen’s bottom show you just one statistic (either their sum, average, count, maximum, or minimum), which you can right-click to see a list of other statistics, from which you can click your favorite.

After you’ve made your selection, tell the computer how to beautify it. Choose one of the following forms of beauty.…

Italic

To make all writing in the selection be italicized (like this), click the I button, which is near the screen’s top.

In Quattro Pro 8&9, the I button is above column C.

In Quattro Pro 10,    the I button is above column D.

In Excel,                   the I button is above columns C and D.

In Works 4.5,            the I button is above column E.

In Works 5&6&7,     the I button is above column F.

Clicking the I button pushes the I button in. If you change your mind and want the writing not to be italicized, select the writing again (so it turns dark again), then click the I button again (so the button pops back out).

Bold

To make all writing in the selection be bold (like this), push in the B button, which is near the screen’s top and next to the I button.

In Excel and Quattro Pro,   the B button is above column C.

In Works 4.5,                      the B button is above column E.

In Works 5&6&7,               the B button is above column F.

If you change your mind and want the writing not to be bold, select the writing again (so it turns dark again), then click the B button again (so the button pops back out).

To get bold italics, push in the bold button and also the italic button.

Underline

To make all writing in the selection be underlined (like this), push in the U button, which is near the screen’s top and next to the I button.

In Excel and Quattro Pro,   the U button is above column D.

In Works 4.5,                      the U button is above columns E and F.

In Works 5&6&7,               the U button is above column F.

If you change your mind and want the writing not to be bold, select the writing again (so it turns dark again), then click the U button again (so the button pops back out).

Font size

Above column B, and below the word Format, you see the number 10.

To make all writing in the selection get bigger (like this), click the down-arrow to the right of that 10, then click a font size bigger than 10. (For example, click 14 or 16.)

To make your spreadsheet easier to read, use big writing for the column headings (such as January), the row headings (such as Income, Expenses, and Profit), any totals, and the bottom-line results (such as the $2000 profit).


Align

Here’s how to make all writing in the selection be nudged slightly to the left or slightly to the right.

Works Click one of these three buttons:

 

                          ──────            ──────            ────── 

                          ────               ────               ──── 

                          ──────            ──────            ────── 

                          ────               ────               ──── 

                          ──────            ──────            ────── 

                          ────               ────               ──── 

Those buttons are near the top of the screen, above column F in Works 4.5, column G in Works 5&6&7. (That’s how the buttons look in Works 6&7. In Works 4.5&5, they look slightly different.) Here’s what those buttons do:

                                                              ┌───────────────────────────┐

clicking the left button makes each cell’s writing be flush left      │like this                  │

                                                              └───────────────────────────┘

                                                              ┌───────────────────────────┐

clicking the center button makes each cell’s writing be centered     │         like this         │

                                                              └───────────────────────────┘

                                                              ┌───────────────────────────┐

clicking the right button makes each cell’s writing be flush right  │                  like this│

                                                              └───────────────────────────┘

If you don’t click any of the buttons, here’s what happens:

If the cell contains a number (or date or month), the computer puts it flush right.

If the cell contains plain words instead, the computer puts them flush left.

In a simple spreadsheet, row 1 usually contains words that are column headings. Below those headings are numbers, which are flush right. To align the headings with the numbers beneath them, make the headings be flush right also. To do that, select row 1 (by clicking the 1), then click the right button.

Excel Click one of these three buttons:

 

                          ──────            ──────            ────── 

                          ────               ────               ──── 

                          ──────            ──────            ────── 

                          ────               ────               ──── 

                          ──────            ──────            ────── 

                          ────               ────               ──── 

Those buttons are near the top of the screen, above columns D and E. Here’s what those buttons do:

                                                              ┌───────────────────────────┐

clicking the left button makes each cell’s writing be flush left      │like this                  │

                                                              └───────────────────────────┘

                                                              ┌───────────────────────────┐

clicking the center button makes each cell’s writing be centered    │         like this         │

                                                              └───────────────────────────┘

                                                              ┌───────────────────────────┐

clicking the right button makes each cell’s writing be flush right  │                  like this│

                                                              └───────────────────────────┘

If you don’t click any of the buttons, here’s what happens:

If the cell contains a word, the computer puts the word flush left.

If the cell contains a number instead, the computer puts the number flush right.

In a simple spreadsheet, row 1 usually contains words (such as January, February, and March). Those words are headings for columns of numbers. The numbers are flush right. To align the headings with the numbers beneath them, make the headings be flush right also. To do that, select row 1 (by clicking the 1), then click the right button.

Quattro Pro Type L, R, or E, while holding down the Ctrl key:

                                                                  ┌───────────────────────┐

Pressing Ctrl with L makes each cell’s writing be flush left           │like this              │

                                                                  └───────────────────────┘

                                                                  ┌───────────────────────┐

Pressing Ctrl with R makes each cell’s writing be flush right          │              like this│

                                                                  └───────────────────────┘

                                                                  ┌───────────────────────┐

Pressing Ctrl with E makes each cell’s writing be centered (equidistant) │       like this       │

                                                                  └───────────────────────┘

If you don’t click any of the buttons, here’s what happens:

If the cell contains a word, the computer puts the word flush left.

If the cell contains a number instead, the computer puts the number flush right.

In a simple spreadsheet, row 1 usually contains words (such as January, February, and March). Those words are headings for columns of numbers. The numbers are flush right. To align the headings with the numbers beneath them, make the headings be flush right also. To do that, select row 1 (by clicking the 1), then press Ctrl with R.

Delete

To make all writing in the selection vanish (so it’s erased), press the DELETE key.


Money

The computer can handle money.

Works To make each number in the selection look like dollars-and-cents, click the $ button. That makes the computer put a dollar sign before each number and put two digits after the decimal point. If the number is big, the computer inserts commas.

For example, if the number is 1538.4, the computer turns it into:

┌─────────┐

│$1,538.40│

└─────────┘

If the number is .739, the computer rounds it to:

┌─────────┐

│    $0.74│

└─────────┘

If a number is negative (because you lost money instead of gained), the computer follows the tradition of accountants and the Internal Revenue Service: it puts the number in parentheses (instead of writing a minus sign). For example, if the number is -974.25, the computer writes:

┌──────────┐

│ ($974.25)│

└──────────┘

Excel To make each number in the selection look like dollars-and-cents, click the $ button. That makes the computer put a dollar sign before each number and put two digits after the decimal point. If the number is big, the computer inserts commas.

For example, if the number is 1538.4, the computer turns it into:

┌─────────┐

│$1,538.40│

└─────────┘

If the number is .739, the computer rounds it to:

┌─────────┐

│$    0.74│

└─────────┘

If a number is negative (because you lost money instead of gained), the computer follows the tradition of accountants and the Internal Revenue Service: it puts the number in parentheses (instead of writing a minus sign). For example, if the number is -974.25, the computer writes:

┌──────────┐

│$ (974.25)│

└──────────┘

When writing a number, the computer puts the dollar sign at the cell’s left edge (flush left), so all dollar signs in that column will line up. The computer puts the digits (and parentheses) flush right, and widens the cell if necessary to make them all fit.

Near the $ button, you see a button that has a comma on it. Clicking the comma button has the same effect as clicking the $ button, except that the comma button does not make the computer write a dollar sign.


Quattro Pro To the right of the U button, you see a word (such as “Normal”). If you click that word, you see the format-style menu.

To make each number in the selection look like dollars-and-cents, choose “Currency” from the format-style menu.

For example, if the number is 1538.4, the computer turns it into:

┌──────────┐

│ $1,538.40│

└──────────┘

If the number is .739, the computer rounds it to:

┌──────────┐

│     $0.74│

└──────────┘

If the number is -974.25, versions 9&10 put the minus sign before the dollar sign —

┌──────────┐

│  -$974.25│

└──────────┘

but version 8 writes parentheses instead of a minus sign:

┌──────────┐

│ ($974.25)│

└──────────┘

When writing a dollar-and-cents number, the computer widens the cell if necessary to make the dollar sign, commas, and digits all fit.

From the format-style menu, instead of choosing “Currency” you can choose one of these variants:

Variant      What the cell will show

“Currency”  money

“Comma”    money, but don’t show any dollar sign

“Currency0”    money, rounded to the nearest dollar

                    (so show no pennies, no decimal point)

“Comma0”  money, rounded to the nearest dollar,

                    but don’t show any dollar signs

“Fixed”        money,

                    but show no dollar sign and no commas,

                    show a minus sign instead of parentheses

Percent

The computer can handle percentages.

Excel To make each number in the selection look like percentage, click the % button. For example, if the number is .74, the computer turns it into 74%.

When writing the percentage, the computer doesn’t write any decimal point. For example, if the number is .519, the computer rounds it to 52%.

Quattro Pro To make each number in the selection look like percentage, choose “Percent” from the format-style menu.

For example, if the number is .74, the computer turns it into 74%. When writing a percent, the computer shows two digits after the decimal point, so the computer shows:

┌────────┐

│  74.00%│

└────────┘

If the number is .51429, the computer turns it into 51.429% then rounds it to two digits after the decimal point, so you see:


┌────────┐

│  51.43%│

└────────┘

Works To make each number in the selection look like percentage, click “Format” then “Number” then “Percent” then OK.

For example, if the number is .74, the computer turns it into 74%. When writing a percent, the computer shows two digits after the decimal point, so the computer shows:

┌─────────┐

│   74.00%│

└─────────┘

If the number is .51429, the computer turns it into 51.429% then rounds it to two digits after the decimal point, so you see:

┌─────────┐

│   51.43%│

└─────────┘

Negatives If the number is negative the computer puts a negative sign in front.

Decimal places in Excel

I said that if you click Excel’s $ or comma button, the computer normally puts two digits after the decimal point. If you click the % button, the computer normally puts no digits after the decimal point.

If you click the Increase Decimal button (which shows a .0 becoming a .00), the computer will put an extra digit after the decimal point. If you click it several times, the computer will put several extra digits after the decimal point.

If you click the Decrease Decimal button (showing a .00 becoming a .0) several times, the computer will put fewer digits after the decimal point. For example, here’s how to round to the nearest dollar: click the $ button (which produces dollars and cents) and then twice click the Decrease Decimal button (which gets rid of the cents by rounding).

Font

Normally, the characters you type are in a font called Arial. To make all writing in the selection have a different font (such as Times Roman), click the down-arrow that’s next to “Arial” then click whichever font you want.

These four fonts are especially popular:

This font is Arial. It’s the normal font for spreadsheets.

It’s plain and simple.

This font is Arial Narrow. It’s thinner than Arial.

It lets you fit more characters in each cell, or fit more columns on each page.

This font is Arial Black. It’s an extra-bold version of Arial.

It’s good for column titles.

This font is Times New Roman. It’s the easiest to read.

It’s especially good if you’re writing lots of words instead of numbers.

Arial and Times New Roman come free as part of Windows. Arial Black comes free as part of Windows 98&Me&XP. Arial Narrow comes free as part of Microsoft Microsoft Office & Microsoft Works Suite.

Text color

Normally, the characters you type are black. Here’s how to make all characters in the selection be a different color (such as red).

Quattro Pro 9&10 Click the Text Color button, which shows a can of paint pouring onto an “a”. You’ll see 42 colors; click the color you want.

Quattro Pro 8 Look at the Text Color button, which shows a see-through can of paint pouring onto an “a”. Notice the color of the paint in the can.

If it’s the color you want, click the “a”.

If it’s not the color you want, do this instead: click the down-arrow that’s to the right of the “a”; you’ll see 16 colors; click the color you want.

Excel Near the screen’s top right corner, you see the Font Color button, which has an underlined A on it. Notice the color of the A’s underline.

If it’s the color you want, click the underline.

If it’s not the color you want, do this instead: click the down-arrow that’s to the right of the A’s underline; you’ll see 40 colors; click the color you want.

Works 6&7 Click Format then Font, then click the “Select font color” box’s down-arrow. Look through the list of 15 colors, by using that list’s scroll arrows. Click whichever color you want, then click OK.

Works 4.5&5 Click Format then “Font and Style”, then click the Color box’s down-arrow. Look through the list of 15 colors, by using that list’s scroll arrows. Click whichever color you want, then click OK.

Distorted color Excel 2000&2002&2003 show the text color perfectly. If you used a different spreadsheet program instead and selected several cells, some of those cells temporarily show distorted colors, until you click a single cell.

Background color

Normally, you type on a white background. Here’s how to make the entire selection’s background become a different color (such as yellow).

Quattro Pro 9&10 Click the Cell Color button, which shows a can of paint pouring onto a cell. You’ll see 42 colors; click the color you want.

Quattro Pro 8 Look at the Background Color button, which shows a see-through can of paint pouring onto a cell. Notice the color of the paint in the can.

If it’s the color you want, click that paint can.

If it’s not the color you want, do this instead: click the down-arrow that’s to the right of the paint can; you’ll see 42 colors; click the color you want.

Excel Near the screen’s top right corner, you see the Fill Color button, which shows a paint can pouring onto a floor. Look at the floor’s color.

If it’s the color you want, click the paint can.

If it’s not the color you want, do this instead: click the down-arrow that’s to the right of the paint can; you’ll see 40 colors; click the color you want.

Works 6&7 Click Format then Shading. Underneath “Pattern color”, click the background color you want (after using the scroll arrows to see the complete list of 15 colors). Underneath “Pattern”, click “Solid”. Then click OK.

Works 4.5&5 Click Format then Shading. Underneath Pattern, click the solid black bar. Below the word “Foreground” (not “Background”), click the background color you want (after using the scroll arrows to see the complete list of 15 colors). Then click OK.

Distorted color If you selected several cells, some of them temporarily show distorted colors, until you click a single cell.

Sort

This spreadsheet shows how three students (Zelda, Al, and Pedro) scored on a test:

┌────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┐

│    │   A    │   B    │   C    │   D    │   E    │   F    │   G    │   H    │

├────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┤

│  1 │Student │Score   │        │        │        │        │        │        │

├────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┤

│  2 │Zelda   │      42│        │        │        │        │        │        │

├────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┤

│  3 │Al      │       7│        │        │        │        │        │        │

├────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┤

│  4 │Pedro   │     100│        │        │        │        │        │        │

└────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┘

Alphabetize In that list of students, Zelda is on the top; Pedro is on the bottom.

Here’s how to rearrange the rows, to put the students in alphabetical order (from A to Z).

Excel: click any student’s name, then click the Sort Ascending button (which has an A above a Z).

Quattro Pro: click any student’s name, then Tools, then Sort. Put b in the “Selection contains a heading” box, then press ENTER.

Works: the rows involved in the sorting (rows 2, 3, and 4) are called the data rows. Make the data rows become black, by dragging from the 2 (at the beginning of row 2) to the 4 (at the beginning of row 4). Click Tools then Sort. (If the computer says “First-time Help”, click OK.) Press ENTER.

That makes the spreadsheet become:

┌────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┐

│    │   A    │   B    │   C    │   D    │   E    │   F    │   G    │   H    │

├────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┤

│  1 │Student │Score   │        │        │        │        │        │        │

├────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┤

│  2 │Al      │       7│        │        │        │        │        │        │

├────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┤

│  3 │Pedro   │     100│        │        │        │        │        │        │

├────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┤

│  4 │Zelda   │      42│        │        │        │        │        │        │

└────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┘

Increasing scores Here’s how to rearrange the rows, to put the scores in numerical order (starting with the lowest score and ending with the highest).

Excel: click any score, then click the Sort Ascending button (which has an A above a Z).

Quattro Pro: click any score, then Tools, then Sort. Put b in the “Selection contains a heading” box, then press ENTER.

Works: blacken the data rows, by dragging from the 2 (at the beginning of row 2) to the 4 (at the beginning of row 4). Click Tools then Sort. (If the computer says “First-time Help”, click OK.) Make the (first) wide box say “Column B” (by clicking that box’s down-arrow and then clicking “Column B”). Press ENTER.

That makes the spreadsheet become:

┌────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┐

│    │   A    │   B    │   C    │   D    │   E    │   F    │   G    │   H    │

├────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┤

│  1 │Student │Score   │        │        │        │        │        │        │

├────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┤

│  2 │Al      │       7│        │        │        │        │        │        │

├────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┤

│  3 │Zelda   │      42│        │        │        │        │        │        │

├────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┤

│  4 │Pedro   │     100│        │        │        │        │        │        │

└────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┘

Decreasing scores Here’s how to make the computer put the scores in reverse numerical order (from highest score to lowest score).

Excel: click any score, then click the Sort Descending button (which has a Z above an A).

Quattro Pro: click any score, then Tools, then Sort. Put b in the “Selection contains a heading” box, remove b from the first Ascending box (by clicking), then press ENTER.

Works: blacken the data rows, by dragging from the 2 (at the beginning of row 2) to the 4 (at the beginning of row 4). Click Tools then Sort. (If the computer says “First-time Help”, click OK.) Make the (first) wide box say “Column B” (by clicking that box’s down-arrow and then clicking “Column B”). Click the nearby Descending button. Press ENTER.

That makes the spreadsheet become:

┌────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┬────────┐

│    │   A    │   B    │   C    │   D    │   E    │   F    │   G    │   H    │

├────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┤

│  1 │Student │Score   │        │        │        │        │        │        │

├────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┤

│  2 │Pedro   │     100│        │        │        │        │        │        │

├────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┤

│  3 │Zelda   │      42│        │        │        │        │        │        │

├────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┼────────┤

│  4 │Al      │       7│        │        │        │        │        │        │

└────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┴────────┘

That list is useful, since it puts the winners at the top and the losers at the bottom.

 

Chart

You can graph your data. In modern spreadsheet programs (such as Excel, Quattro Pro, and Works), graphs are called charts.

For example, suppose you want to graph the data from a company you run. Your company sells Day-Glo Pink Hair Dye. (Your motto is: “To brighten your day, stay in the pink!”)

You have two salespeople, Joe and Sue. Joe’s worked for you a long time, and sells about $8,000 worth of dye each month. Sue joined your company recently and is rapidly improving at encouraging people to turn their hair pink. She does that by inventing slogans for various age groups, such as:

“Feminine babes wear pink!”

“You look so sweet, hair as pink as cotton candy!”

“Don’t be a dink! Think pink!”

“Pink is punk!”

“Pink means I’ll be your Valentine, but lighten up!”

“Be what you drink — a Pink Lady!”

“Let the sexy, slinky, pink panther inside you glow!”

“Love is a pink Cadillac — with hair to match!”

“When in a sour mood, look like a pink grapefruit!”


This spreadsheet shows how many dollars worth of dye Joe and Sue sold each month:

 

 

 

 

 

The spreadsheet shows that Joe sold $8000 worth of dye in January, $6500 in February, and $7400 in March.

Sue’s a trainee. She sold just $2000 worth in January, but her monthly sales zoomed up to $12500 by March.

Here’s how to turn that spreadsheet into a graph (chart).

Chart in Excel

First, type the spreadsheet.

Next, format the numbers. To do that, drag from the first number (cell B2) to the last number (cell D3), click the $ button (to put dollar signs in front of the numbers), then twice click the Decrease Decimal button (to round to the nearest dollar). The spreadsheet becomes this:

 

 

 

 

 

Tell the computer which cells to graph. To do that, drag from the blank starting cell (A1) to the last number (cell D3). Drag just to that cell, since the computer gets confused if you drag across extra cells or rows or columns.

Click the Chart Wizard button, which is near the screen’s top right corner and shows colored vertical bars.

Press the ENTER key 4 times. Then the computer draws the graph. (If part of it is covered by a Chart window, make that window disappear, by clicking its X button.)

The graph is part of your spreadsheet, so your spreadsheet looks like this:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Edit If you change the numbers in the spreadsheet’s cells, the graph will change too, automatically!

The entire graph is inside a white box. Try this experiment: click inside that white box, but near the box’s outer edge. Then you’ll see 8 tiny black squares at the white box’s edges. Those tiny black squares are called handles; they mean the white box is selected. Four of those handles are at the corners; they’re called the corner handles.

To change the size of the box (and the graph inside it), drag one of the corner handles.

To delete the box (and the graph inside it), press the DELETE key.

To move the box (and the graph inside it), put the mouse inside the box and near (but not on) a corner handle, then drag in the direction you want to box to move.


Print If you click in the graph’s box then click the Print button, your printer will print the graph.

If you click outside the graph’s box and then click the Print button, your printer will print entire spreadsheet, including the graph! (But before you do that procedure, you should move the graph’s box closer to the screen’s left edge and closer to the spreadsheet’s numbers, to avoid wasting paper.)

Save If you click the Save button, your hard disk will store a copy of the entire spreadsheet, including the graph.


Chart in Quattro Pro

First, type the spreadsheet.

Tell the computer which cells to graph. To do that, drag from the blank starting cell (A1) to the last number (cell D3). Drag just to that cell, since the computer gets confused if you drag across extra cells or rows or columns.

Click the QuickChart button. (It has vertical bars on in. In Quattro Pro 9&10, it’s under the word “Window”; in Quattro Pro 8, it’s under the word “Tools”.)

Click in a blank cell where you want the graph’s top left corner to be. (For example, click in cell A4.) Then the computer draws the graph.

Versions 8&9 draw a bar graph. Version 10 draws a confusing pie chart; here’s how to turn it into a simple bar graph: click in the chart’s middle, then right-click there, then click Gallery then Bar then 3-D (to remove 3-D’s check mark) then OK.

Edit If you change the numbers in the spreadsheet’s cells, the graph will change too, automatically!

The entire graph is in a white box. Try this experiment: click outside the white box, then click the white box’s edge. You’ll see 8 tiny black squares at the white box’s edges. Those tiny black squares are called handles; they mean the white box is selected. After you’ve selected the white box, you can do this:

To change the size of the box (and the graph inside it), drag one of the handles.

To move the box (and the graph inside it), drag one of the edges (but not at one of the handles).

To delete the box (and the graph inside it), press the DELETE key.

Print `If you click the Print button (and then press ENTER), your printer is supposed to print the entire spreadsheet, including the graph.

Warning: version 10 is unreliable about printing charts: if you attempt to print a chart, the printer might refuse to do any printing until you turn the computer off and restart the computer. I hope Corel fixes version 10 soon!

Save If you click the Save button, your hard disk will store a copy of the entire spreadsheet, including the graph.

Chart in Works

First, type the spreadsheet.

Next, format the numbers. To do that, drag from the first number (cell B2) to the last number (cell D3), then click the $ button (to put dollar signs in front of the numbers). The spreadsheet becomes this:

┌─────┬─────────┬─────────┬─────────┬──────────┬─────────┬─────────┬─────────┐

│     │    A    │    B    │    C    │    D     │    E    │    F    │    G    │

├─────┼─────────┼─────────┼─────────┼──────────┼─────────┼─────────┼─────────┤

│  1  │         │  January│ February│     March│         │         │         │

├─────┼─────────┼─────────┼─────────┼──────────┼─────────┼─────────┼─────────┤

│  2  │Joe      │$8,000.00│$6,500.00│ $7,400.00│         │         │         │

├─────┼─────────┼─────────┼─────────┼──────────┼─────────┼─────────┼─────────┤

│  3  │Sue      │$2,000.00│$4,300.00│$12,500.00│         │         │         │

└─────┴─────────┴─────────┴─────────┴──────────┴─────────┴─────────┴─────────┘

Tell the computer which cells to graph. To do that, drag from the blank starting cell (A1) to the last number (cell D3). Drag just to that cell, since the computer gets confused if you drag across extra cells or rows or columns.

Click the New Chart button, which is near the screen’s top right corner and shows vertical bars. (If the computer says “First-time Help”, press ENTER.)

The computer says “New Chart”. Press ENTER. Then the computer draws the graph on the screen, in a window called Chart1. That window covers up the spreadsheet, so you can’t see the spreadsheet. To see the spreadsheet again, choose Spreadsheet from the View menu. Then you see the spreadsheet again, but don’t see the chart. To see the chart again, choose Chart from the View menu (then press ENTER in Works 4.5&5).

Edit If you change the numbers in the spreadsheet’s cells, the graph will change too, automatically!

Print While the graph is on the screen, you can print it onto paper by clicking the Print button. On paper, the computer makes the graph be taller, so it consumes nearly the entire sheet of paper.

Save If you click the Save button, your hard disk will store a copy of the entire spreadsheet, including the graph.