Read an Excerpt
The Basics of Spreadsheets: Numbers, Labels, and Formatting
In This Chapter
- What the heck is a spreadsheet?
- Typing stuff in a worksheet
- Moving around a worksheet
- Formatting a worksheet
- Sending a worksheet to the printer
Most folks who've been around computers for a few years credit (or blame) the first spreadsheet program for getting the computer revolution off the ground. By using a spreadsheet (such as Microsoft Excel 97), you can track budgets, inventories, or embezzlements on your own personal computer.
What Is a Spreadsheet, Anyway?
In the old days, accountants wrote long columns of numbers on sheets of green ledger paper divided by lines to make entering and organizing information in neat rows and columns easy. Essentially, a computer spreadsheet is just the electronic equivalent of green ledger paper. Instead of seeing rows and columns on a piece of paper, you see rows and columns on your computer screen.
Many people use the terms spreadsheet and worksheet interchangeably. When people talk about a spreadsheet, they may be talking about their program (such as Excel 97 or Lotus 1-2-3) or the actual data that they typed into their spreadsheet program. When people talk about a worksheet, they mean actual data that they typed into their spreadsheet program. Now isn't that clear?
The invention of the spreadsheet
In case you ever get asked this question during Trivial Pursuit or Jeopardy!, this is how the spreadsheet appeared. Back in 1979, two business students, Dan Bricklin and Bob Frankston, decided that performing calculations with paper, pencil, and adding machines was a complete waste of time. Instead of griping about the tedious work like other graduate students, they decided to do something about it and wrote the world's first spreadsheet program for their Apple II computer.
They called this program VisiCalc, which stands for Visible Calculator. The idea behind VisiCalc (and every spreadsheet since then) was that all you had to do was type in numbers, define how to calculate new results based on those numbers, and let the spreadsheet do the hard work of calculating multiple formulas while you do the easy work of just typing in numbers.
Every spreadsheet program, including Excel 97, owes its existence to Dan and Bob and their VisiCalc program. So if you don't like Excel 97, now you know who to blame.
A spreadsheet consists of the following items, as shown in Figure 8-1:
- A worksheet divided into rows and columns
A worksheet acts like a page where you can type numbers and labels. Each worksheet contains up to 256 vertical columns and 16,384 horizontal rows. Columns are identified by letters (A, B, C, and so on). Rows are numbered (1, 2, 3, and so on).
A cell is the intersection of a row and a column. When you type data into a worksheet, you have to type it in a cell. Cells are identified by their column letters followed by their row numbers. For example, the cell at the intersection of column G and row 12 is called cell G12.
Numbers can represent amounts, lengths, or quantities, such as $50.54, 309, or 0.094.
Labels identify what your spreadsheet numbers mean, in case you forget. Typical labels are "May," "Western Sales Region," and "Total Amount We Lost Through Fred's Stupidity."
Formulas let you calculate new results based on the numbers you type in. Formulas can be as simple as adding two numbers together or as complicated as calculating third-order differential equations that nobody really cares about. (Chapter 9 provides more information about creating formulas.)
Spreadsheets may mimic boring paper ledgers, but they also offer additional forecasting and budgeting capabilities that let you ask what-if questions such as "What would happen if the cost of oil went up 10 percent?" or "What would happen if our sales plummeted 90 percent?" or "What would happen if I gave myself a million-dollar raise despite the fact that sales have plummeted 90 percent?"
Excel 97 also lets you organize multiple worksheets in a collection called a workbook. Each workbook can hold up to 256 individual worksheets. For more information about using workbooks, see Chapter 11.
Putting Stuff in a Worksheet
Before you can type any information into Excel 97, you have to start the program. In case you've forgotten how to do this, refer to Chapter 2 to refresh your memory.
Of course, after you start Excel 97, you see an empty worksheet. An empty worksheet is useless by itself, so you need to type data into the worksheet's cells. The three types of items that you can type into a cell are numbers, labels, and formulas.
Entering information in a cell
To type data into a cell, follow these steps:
- Click the cell where you want to type data.
- Start typing a number, label, or formula.
- Do one of the following actions to make your typed data appear in your chosen cell:
If you don't want to use the mouse, use the cursor keys to select the cell where you want to type your data.
Excel 97 highlights your cell with a dark border around the edges. The highlighted cell is called the active cell and is the Excel 97 way of telling you, "If you start typing something now, this is the cell where I'm going to put it."
As you type, Excel 97 displays what you're typing in your chosen cell and in the Formula Bar.
- Press Enter.
- Click the Enter (green check mark) button, next to the Formula Bar.
- Press an arrow key to select a different cell.
- Click a different cell to select it.
If you suddenly decide that you don't want your data to appear in the cell after all, press Esc or click the Cancel (red X) button, next to the Formula Bar.
If you need to type the names of months or days in adjacent cells, Excel 97 has a handy shortcut that can save you lots of typing. To use this shortcut, follow these steps:
- Click a cell and type a month or day, such as March (or Mar.) or Tuesday (or Tue.).
- Place the mouse cursor directly over the Fill Handle so that the cursor turns into a black crosshair.
- Hold down the left mouse button and drag the mouse to the right or down.
- Release the left mouse button.
The Fill Handle -- a black box -- appears at the bottom-right corner of the cell that you just typed in.
As you move the mouse, Excel 97 displays the month or day in each cell that you highlight.
Excel 97 automatically types the months or days in the range of cells that you selected.
Deleting and editing the contents of a cell
You may sometimes need to edit what you typed in a cell, because you made a mistake or you just want to express your creative urges by typing something else in the cell. Or sometimes you just want to get that data out of there altogether.
To edit or delete data in a cell, follow these steps:
- Click or use the arrow keys to select the cell containing the data that you want to edit or delete.
- Press F2, or click the Formula Bar, or double-click the cell containing the data that you want to edit.
- Press Backspace to delete characters to the left of the insertion point or press Delete to erase characters to the right of the insertion point.
- Type any new data.
- To make your typed data appear in your chosen cell, press Enter, click the Enter (green check mark next to the Formula Bar) button, or click a different cell.
Saving your worksheets in a file
After you type numbers, labels, or formulas into a worksheet, you probably want to save the worksheet in a file so you won't have to type everything all over again.
To save a workbook, including all its worksheets, choose one of the following:
- Click the Save button (the picture of a disk) on the Standard toolbar.
- Press Ctrl+S.
- Choose File-->Save (if you're using Excel 97 as a separate program).
- Choose File-->Save Binder (if you're using Excel 97 within a binder). (See Chapter 3 for more information about using a binder.)
If you haven't saved the file before, the Save dialog box appears, asking you to choose a filename and a directory to store your file in.
Save your work at regular intervals -- say, every 10 or 20 minutes -- just to make sure you don't lose everything if the power goes out.
Navigating a Worksheet
A single worksheet can contain up to 256 columns and 16,384 rows. Obviously, your tiny computer screen can't display such a large worksheet all at once, so you can see only part of a worksheet at any given time, much like viewing the ocean through a porthole.
If you create a huge worksheet, you need a way to navigate through the whole thing. Fortunately, Excel 97 provides several different ways to use the mouse or the keyboard to jump around a worksheet.
Using the mouse to jump around a worksheet
To jump around a worksheet with the mouse, you have two choices:
- Click the vertical and horizontal scroll bars.
- Use the wheel on your Microsoft IntelliMouse.
If you don't have Microsoft's newest mouse, which has a wheel in the middle of it, your only option for using the mouse is to click the vertical scroll bar. For the lucky few who own the latest Microsoft mouse, you have two ways to scroll through a document using the IntelliMouse. You can roll the IntelliMouse wheel up or down to scroll up or down a document. Or you can click the IntelliMouse wheel, which causes Excel 97 to scroll your document slowly down from top to bottom and display a black arrow pointing down in the vertical scroll bar. To make Excel 97 scroll your document in the opposite direction, move the IntelliMouse up so the black arrow in the vertical scroll bar points upwards. To make the IntelliMouse stop scrolling your document automatically, click the wheel.
If you want to jump around a document by using the vertical or horizontal scroll bar, you have these choices:
- Click the up arrow and down arrow or right arrow and left arrow keys to scroll up and down one row or right and left one column at a time.
- Drag the scroll box in the scroll bar in the desired direction to jump to an approximate location in your document.
- Click the vertical scroll bar above or below the scroll box to page up or down one screen-length at a time.
- Click the horizontal scroll bar to the right or left of the scroll box to page right or left one screen-width at a time.
Using the keyboard to jump around a document
For those who hate the mouse or just prefer using the keyboard, here are the different ways to jump around your document by pressing keys:
- Press the down arrow key to move one row down in your worksheet.
- Press the up arrow to move one row up in your worksheet.
- Press the right arrow to move one column to the right in your worksheet.
- Press the left arrow to move one column to the left in your worksheet.
- Hold down the Ctrl key and press up arrow, down arrow, right arrow, or left arrow to jump up/down or right/left one adjacent row or column of data at a time.
- Press the PgDn key (or Page Down on some keyboards) to jump down the worksheet one screen-length at a time.
- Press the PgUp key (or Page Up on some keyboards) to jump up the worksheet one screen-length at a time.
- Hold down the Ctrl key and press the Home key to jump to the A1 cell in your worksheet, which appears in the upper-left-hand corner of every worksheet.
- Hold down the Ctrl key and press the End key to jump to the last cell in your worksheet.
- Press the End key and then press up arrow, down arrow, right arrow, or left arrow to jump to the end/beginning or top/bottom of data in the current row or column.
Open a sample Excel 97 worksheet and practice using all the different methods of navigating around a worksheet. Then memorize the commands you find most useful and forget about the rest.
Using the Go To command
When you want to jump to a specific cell in your worksheet, the Go To command is a lot faster than either the mouse or the keyboard.
To use the Go To command, follow these steps:
- Choose Edit -->Go To or press Ctrl+G.
- Type a cell reference (such as A4 or C21) and click OK.
The Go To dialog box appears, as shown in Figure 8-2.
Naming cells and ranges
If you don't like referring to cells as E4 or H31, you can assign more meaningful names to a single cell or range of cells. Assigning names can make finding portions of a worksheet much easier. For example, finding your budget's 1997 income cell is a lot easier if it's called "income97" instead of F22.
To assign a name to a cell or range of cells, follow these steps:
- Click the cell that you want to name, or select the range of cells that you want to name by dragging (holding down the left mouse button while moving the mouse) over the cells.
- Click the Name Box.
- Type the name that you want to assign to the cell or cell range.
- Press Enter.
The cell is highlighted as the active cell. (Or the range is highlighted, and the first cell in the range becomes the active cell.) The active cell's address appears in the Name Box.
Excel 97 highlights the cell address.
The name that you assigned appears in the Name Box.
Names must start with a letter and must be one word. "MyIncome" is a valid cell name, but "My Income for 1997" is not, because of the spaces between the words.
To see a list of all the named cells or cell ranges in a worksheet, click the downward-pointing arrow in the Name Box.
Jumping to a named cell or cell range
After you name a cell or cell range, you can jump to it by following these steps:
- Click the downward-pointing arrow to the right of the Name Box.
- Click the cell name that you want to jump to.
Excel 97 displays a list of all named cells or cell ranges in the current workbook, as shown in Figure 8-3.
Excel 97 highlights the cell represented by the name you chose.
Deleting a named cell or cell range
You may later decide that you don't need a name to represent a particular cell or cell range. To delete a cell name, follow these steps:
- Choose Insert -->Name-->Define.
- Click the cell name that you want to delete; then click Delete. Repeat this step as often as necessary.
- Click OK.
The Define Name dialog box appears.
Making Your Worksheet Pretty with Formatting
Rows and columns of endless numbers and labels can look pretty dull. Because a plain, boring worksheet can be as hard to understand as a tax form, Excel 97 gives you the option of formatting your cells.
By formatting different parts of your worksheet, you can turn a lifeless worksheet into a powerful persuasion tool that can convince your boss to approve your budget proposals and give him the impression that you gave it more thought than you really did.
Excel 97 offers an almost unlimited variety of formatting options. You can change fonts, borders, number styles, and alignment to make your worksheets look pretty.
If you aren't a designer but want fancy formatting without a lot of effort on your part, use the Excel 97 AutoFormat feature. AutoFormat can automatically format a range of cells for you, according to one of many formatting styles.
To use AutoFormat, follow these steps:
- Select the two or more adjacent cells that you want to format.
- Choose Format -->AutoFormat.
- Click a format in the Table format box.
- Click OK.
The AutoFormat dialog box appears, as shown in Figure 8-4.
The Sample box shows you what formatting you chose.
Excel 97 automatically formats the range of cells that you selected in Step 1.
If you want to restrict the types of formatting that AutoFormat can apply, click Options in the AutoFormat dialog box and then deselect the Formats to apply options that you don't want AutoFormat to use. For example, if you don't want Excel 97 to change fonts, remove the check from the Font check box by clicking it.
Changing fonts in your cells
AutoFormat makes formating cells easy, but you may like the freedom to choose your own fonts for your cells. For example, you may want to display your profits in a large font and your losses in a much smaller font (so nobody notices that your company has been losing money consistently for the past three years).
To change the font, follow these steps:
- Select the cell or range of cells that you want to format.
- Click the Font list box and choose a font.
- Click the Font Size list box and choose a size (such as 12 or 16).
- Click the Bold, Italic, or Underline button on the Formatting toolbar.
Displaying numbers in different ways
Normally, when you type a number in a cell, Excel 97 displays it as a plain, simple number, like 54 or 908.83. This option is okay for most purposes, but sometimes you may want to display numbers as currency (such as $3.90), percentages (such as 83.2%), fractions (such as 1/2), or as scientific notation (such as 5.09E+05).
To format your numbers, follow these steps:
- Select the cells containing the numbers that you want to format.
- Choose Format -->Cells or press Ctrl+1.
- Click the Number tab.
- Click the Category list box and choose the type of format you want to use to display your numbers, such as Currency, Percentage, or Scientific.
- Click OK.
The Format Cells dialog box appears, as shown in Figure 8-5.
Adjusting column widths
Unless you specify otherwise, Excel 97 displays all columns in equal widths. However, you may soon find that some of your data appears truncated, scrunched, weird, or otherwise not displayed the way you intended. This problem occurs when your columns are too narrow.
To fix this problem, you can adjust columns to make them wider or narrower. To adjust the column widths in your worksheet, follow these steps:
- Place the mouse cursor directly over one of the vertical borders of the column heading that you want to modify.
- Hold down the left mouse button and drag the mouse.
- Release the left mouse button when the column is the width you want.
For example, if you want to adjust the width of column B, move the mouse cursor over the border between columns B and C.
The mouse cursor appears as a double-headed arrow. Excel 97 also displays a dotted vertical line to show you the approximate width of your column.
If you double-click the border between column headings, Excel 97 automatically modifies the column on the left to make it just wide enough to display the longest entry in that column.
Printing your numbers
When you decide to print out your worksheet, Excel 97 gives you a variety of ways to do so. Follow these steps:
- Make sure your printer is turned on, properly connected to your computer, loaded with paper, hasn't been drop-kicked through the third-story window out of frustration, and so on.
- Choose one of the following:
- Click the Print button on the Standard toolbar.
- Press Ctrl+P.
- Choose File-->Print (if you're using Excel 97 as a separate program).
- Choose Section-->Print (if you're using Excel 97 within a binder).
If you want to print your entire worksheet right away, click the Print button on the Standard toolbar. If you want to specify which pages you want to print and how many copies, choose one of the other methods (Ctrl+P, File-->Print, or Section-->Print).