Excel 2003 for Starters: The Missing Manual

Content preview·Buy reprint rights for this chapter

Creating a Basic Worksheet
Editing Data
Navigating in Excel
Saving Files
Opening Files

The best way to avoid potential headaches is to take a quick tour of Excel as you start creating a spreadsheet. That's what you do in this chapter. Along the way, you learn how to enter information in the Excel window and how to open and save spreadsheet files.

When you first launch Excel, it starts you off with a new, blank worksheet called Sheet1. A worksheet is the grid of cells where you type your information and formulas, and it takes up most of the window, as shown in Figure 1-1. This grid is the most important part of the Excel window. It's where you perform all your work, such as entering data, writing formulas, and reviewing the results. (A collection of one or more worksheets is called a workbook, which is also sometimes called a spreadsheet file).

Here are a few basics about Excel's grid:

The grid divides your worksheet into rows and columns. Excel identifies columns with letters (A, B, C …), and rows with numbers (1, 2, 3 …).
The smallest unit in your worksheet is the cell . Cells are the rectangular boxes that store your text or numbers. Excel identifies each cell using a shorthand name derived from the column and row it's sitting in. For example, C6 is the address of a cell in column C (the third column), and row 6 (the sixth row). Figure 1-2 shows this cell, which looks like a rectangular box. Incidentally, an Excel cell can hold up to 32,767 characters.
A worksheet can span up to 256 columns and 65,536 rows (giving you a grand total of 16,777,216 cells). In the unlikely case that you want to go beyond those limits—say you're naming each one of your brain cells—you need to create a new worksheet. Every spreadsheet file can hold multiple worksheets, as you'll see in Chapter 5.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

When you first launch Excel, it starts you off with a new, blank worksheet called Sheet1. A worksheet is the grid of cells where you type your information and formulas, and it takes up most of the window, as shown in Figure 1-1. This grid is the most important part of the Excel window. It's where you perform all your work, such as entering data, writing formulas, and reviewing the results. (A collection of one or more worksheets is called a workbook, which is also sometimes called a spreadsheet file).

Here are a few basics about Excel's grid:

The grid divides your worksheet into rows and columns. Excel identifies columns with letters (A, B, C …), and rows with numbers (1, 2, 3 …).
The smallest unit in your worksheet is the cell . Cells are the rectangular boxes that store your text or numbers. Excel identifies each cell using a shorthand name derived from the column and row it's sitting in. For example, C6 is the address of a cell in column C (the third column), and row 6 (the sixth row). Figure 1-2 shows this cell, which looks like a rectangular box. Incidentally, an Excel cell can hold up to 32,767 characters.
A worksheet can span up to 256 columns and 65,536 rows (giving you a grand total of 16,777,216 cells). In the unlikely case that you want to go beyond those limits—say you're naming each one of your brain cells—you need to create a new worksheet. Every spreadsheet file can hold multiple worksheets, as you'll see in Chapter 5.
When you enter information, you type it in one cell at a time. However, you don't have to follow any set order. For example, you can start by typing information into cell A40, without worrying about filling any data in the cells that appear in the earlier rows.

Figure 1-1: The Excel window has several parts, most notably the worksheet grid where you type in your information.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Every time you start typing in a cell, Excel erases any existing content in that cell. (You can also quickly remove the contents of a cell by just moving to it and pressing Delete.)

If you want to edit cell data instead of just replacing it, you need to put the cell in edit mode, like this:

Move to the cell you want to edit.

Use the mouse or the arrow keys to get to the correct cell.
Put the cell in edit mode by pressing F2.

Edit mode looks almost the same as ordinary text-entry mode. The only difference is that you can use the arrow keys to move through the text you're typing and make changes. (When you aren't in edit mode, pressing these keys just moves you to another cell.)

If you don't want to use F2, you can also place a cell in edit mode by double-clicking it.
Complete your edit.

Once you've modified whatever's in the cell, you can press Enter to commit your change or Esc to cancel your edit and leave the old value in the cell. Alternatively, you can turn off edit mode (press F2 again) and then move to a new cell to commit your change. As long as you stay in edit mode, Excel won't let you move to another cell.

If you start typing new information into a cell and you decide you want to move to an earlier position in your entry (to make an alteration, for instance), just press F2. The cell box still looks the same, but you're now in edit mode, which means that you can use the arrow keys to move within the cell (instead of moving from cell to cell). You can press F2 again to return to the normal data-entry mode.

As you enter data, you may discover the Bigtime Excel Display Problem (known to aficionados as BEDP): cells in adjacent columns can overlap one another. Figure 1-6 shows the problem. One way to fix the BEDP is to manually resize the column, as shown in Figure 1-4. Chapter 2 tackles editing in more detail. Chapter 4 shows you how to wrap multiple lines of text in a single cell.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Learning how to move around the Excel grid quickly and confidently is an indispensable skill. To move from cell to cell, you have two basic choices:

Use the arrow keys on the keyboard. Keystrokes move you one cell at a time in any direction.
Click the cell with the mouse. A mouse click jumps you directly to the cell you've clicked.

As you move from cell to cell, you see the black focus box move to highlight the currently active cell.

In some cases, you may want to cover ground a little more quickly. You can use any of the shortcut keys listed in Table 1-1. The most useful shortcut keys include the Home key combinations, which bring you back to the beginning of a row or the top of your worksheet.

Shortcut key combinations that use the + sign must be entered together. For example, "Ctrl+Home" means hold down the Ctrl key and press the Home key at the same time. Other key combinations work in sequence. For example, the key combination "End, Home" means press End first, take your finger off it, and then press Home.

Table 1-1: Shortcut Keys for Moving Around a Worksheet
KEY COMBINATION	RESULT
→ (or Tab)	Moves one cell to the right.
← (or Shift+Tab)	Moves one cell to the left.
↑	Moves one cell up.
↓ (or Enter)	Moves one cell down.
Page Up	Moves up one screenful. Thus, if the grid shows 10 cells at a time, this key moves to a cell in the same column, 10 rows up (unless you're already at the top of the worksheet).

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

As everyone who's been alive for at least three days knows, you should save your work early and often. Excel is no exception. You have two choices for saving a spreadsheet file:

Save As. This choice lets you save your spreadsheet file with a new name. You can use Save As the first time you save a new spreadsheet, or you can use it to save a copy of your current spreadsheet with a new name, in a new folder, or as a different file type (alternate file formats are covered on Section 1.4.1). To use Save As, select File → Save As (or press F12) to display the Save As dialog box shown in Figure 1-14. Excel suggests you save your file in the My Documents folder, but you can save your document in whatever folder you like. To specify a folder, either click the icons on the left-hand side of the box, or choose from the drop-down "Save in" box. When you finish choosing a place to save your file, zip to the bottom of the Save As dialog box: here you type the file name and, finally, pick the file type.

Figure 1-14: At the bottom of the Save As dialog box, you can type in a file name and choose a file type.

Save. This option updates the spreadsheet file with your most recent changes. If you use Save on a new file that hasn't been saved before, it has the same effect as Save As: Excel prompts you to choose a folder and file name. To use Save, select File → Save, or press Ctrl+S.

Resaving a spreadsheet is an almost instantaneous operation, and if you tend toward paranoia, you should get used to doing it all the time. After you've made any significant change, just hit Ctrl+S to make sure you've stored the latest version of your data. (You may also want to check out Excel's AutoRecover option, covered on Section 1.4.3.)

Excel saves spreadsheets as .xlsfiles (for example, AirlineSilverware.xls). As hardcore Excel programming gurus know, this file format has a codename, BIFF8 (which, obviously, stands for Binary Interchange File Format). This is the format used by Excel 2003, Excel 2002, Excel 2000, and Excel 97. The codename is useful to know because earlier versions of Excel also store spreadsheets as

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Opening existing files in Excel works much the same as it does in any Windows program. The only difference is that Excel gives you two different ways to get to the standard Open dialog box. Here are your options:

Select File → Open.
Use the Task Pane (Section 1.3.2). In Excel 2003, click the Open link at the bottom of the Getting Started task. Or, in Excel 2002, look under the "Open a workbook" heading in the New Workbook task, and click the "More workbooks" link.

If the Task Pane isn't currently visible, you can always choose View → Task Pane from the menu.

Both of these methods bring up the Open dialog box. Using this dialog box, you can browse to find the spreadsheet file you want and then click Open to load it into Excel.

Excel can open many file types other than its native .xls format. To learn the other formats it supports, pull up the Open dialog box, and, at the bottom, open the "Files of type" menu, which shows you the whole list. If you want to open a file but you don't know what format it's in, try using the first option on the menu, "All Files." Once you choose a file, Excel scans the beginning of the file and informs you about the type of conversion it will attempt to perform (based on what type of file Excel thinks it is).

Depending on your computer settings, Windows may hide file extensions. That means that instead of seeing the Excel spreadsheet file MyCoalMiningFortune.xls, you just see the name MyCoalMiningFortune (without the .xls part on the end). In this case, you can still tell what the file type is by looking at the icon. If you see a small Excel icon next to the file name, that means Windows recognizes that the file is an Excel spreadsheet. If you see something else (like a tiny paint palette, for example), you need to make a logical guess about what type of file it is.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Adding Different Types of Data
Quick Ways to Add Data

Now that you've created a basic worksheet, and you're acquainted with Excel and its sometimes-quirky interface, it's time to get down and dirty adding data. Whether you want to plan your household budget, build a sales invoice, keep track of birthday and holiday gifts, or graph your soaring (or plunging) net worth, you first need to understand how Excel interprets the information you put in your worksheet.

Depending on what kind of data you type into a cell, Excel classifies it as a date, a number, or a piece of text. In this chapter, you'll learn how Excel makes up its mind, and how you can make sure it makes the right decision. You'll also see how to create a sample worksheet and learn how to use Excel's best time-savers, including the indispensable Undo feature.

One of Excel's most important features is its ability to distinguish among different types of information. A typical worksheet contains both text and numbers. There isn't a lot that you can do in Excel with ordinary text (other than alphabetize a list, perform a simple spell check, and apply some basic formatting). On the other hand, Excel gives you a wide range of options for numeric data. For example, you can string your numbers together into complex calculations and formulas, or you can graph them on a chart. Programs that don't try to separate text and numbers—like Microsoft Word, for example—can't provide these features.

Most of the time, when you type information into an Excel spreadsheet, you don't explicitly indicate the type of data. Instead, Excel examines the information you've typed in and, based on your formatting and other clues, classifies it automatically. Excel distinguishes between four core data types:

Ordinary text. This data type includes column headings, descriptions, and any content that Excel can't identify as one of the other data types.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

One of Excel's most important features is its ability to distinguish among different types of information. A typical worksheet contains both text and numbers. There isn't a lot that you can do in Excel with ordinary text (other than alphabetize a list, perform a simple spell check, and apply some basic formatting). On the other hand, Excel gives you a wide range of options for numeric data. For example, you can string your numbers together into complex calculations and formulas, or you can graph them on a chart. Programs that don't try to separate text and numbers—like Microsoft Word, for example—can't provide these features.

Most of the time, when you type information into an Excel spreadsheet, you don't explicitly indicate the type of data. Instead, Excel examines the information you've typed in and, based on your formatting and other clues, classifies it automatically. Excel distinguishes between four core data types:

Ordinary text. This data type includes column headings, descriptions, and any content that Excel can't identify as one of the other data types.
Numbers. This data type includes prices, integers, fractions, percentages, and every other type of numeric data. Numbers are the basic ingredient of most Excel worksheets.
Dates and times. This data type includes dates (like Oct 3, 2004), times (like 4:30 PM), and combined date and time information (like Oct 3, 2004, 4:30 PM). You can enter date and time information in a variety of formats.
True or false values. This data type (known in geekdom as a Boolean value) can contain one of two things: TRUE or FALSE (displayed in all capitals). You don't need Boolean data types in most worksheets. (The only time you need to use these values is in those ultra-rare cases where you want to add Visual Basic macro code or certain condition-evaluating formulas to your spreadsheet. Because these topics are super-advanced and don't apply to most spreadsheets, this book doesn't cover them. If you're interested, check out

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Some of Excel's time-saving frills can make your life easier when you're entering data in a worksheet. This section covers four such features: AutoComplete, AutoCorrect, AutoFill, and AutoFit, along with Excel's top candidates for the Lifetime Most Useful Achievement award: Undo and Redo.

Excel really has two different types of automatic features. First off, there are features that do things to your spreadsheets automatically, namely AutoComplete and AutoCorrect. Sometimes that's cool and convenient, but other times it can send you running for the old manual typewriter. Fortunately, you can turn off both. Excel also has "auto" features that really aren't that automatic. These include AutoFill and AutoFit, which never run on their own.

Windows has regional settings for your computer, which affect the way Microsoft programs understand things like dates and currency. You can change the settings, and they don't have to correspond to where you live—you can set them for your company headquarters on another continent, for instance. But keep in mind that these affect all the programs on your computer.

To make a switch, go to the Start menu in Windows and choose Settings → Control Panel, then double-click Regional and Language Options, which brings up a dialog box. The Regional Options tab has the settings you want. The most important setting is in the first box, which has a drop-down list you can use to pick the region you want, like English (United States) or Swedish (Finland).

You can fine-tune the settings in your region, too. Fine tuning makes sense only if you have particular preferences about how dates should be formatted that don't match the standard options. Click the Customize button next to the region box to bring up a new dialog box, and then click the Date tab (shown here at left)

Use the pull-down menus to specify the date separator; order of month, day, and year components in a date; and how Excel should interpret two-digit years. You can mix and match these settings freely, although you may wind up with a computer that's completely counterintuitive to other people.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Selecting Cells
Moving Cells Around
Adding and Moving Columns or Rows

Simple spreadsheets are a good way to get a handle on Excel. But in the real world, you often need a spreadsheet that's more sophisticated—one that can grow and change as you start to track more information. For example, on the expenses worksheet you created in Chapter 1, perhaps you'd like to add information about which stores you've been shopping in. Or maybe you'd like to swap the order in which your columns appear. To make changes like these, you need to add a few more skills to your Excel repertoire.

This chapter covers the basics of spreadsheet modification, including how to select cells, how to move data from one place to another, and how to change the structure of your worksheet. What you learn here will make you a master of spreadsheet manipulation.

First things first: before you can make any changes to an existing worksheet, you need to select the cells you want to modify. Happily, selecting cells in Excel—try saying that five times fast—is easy. You can do it many different ways, and it's worth learning them all. Different selection techniques come in handy in different situations, and if you master all of them in conjunction with the formatting features described in Chapter 4, you'll be able to transform the look of any worksheet in seconds.

Simplest of all is selecting a continuous range of cells. A continuous range is a block of cells that has the shape of a rectangle (high school math reminder: a square is a kind of rectangle), as shown in Figure 3-1 The easiest way to select a continuous range is to click the top-left cell you want to select. Then drag to the right (to select more columns) or down (to select more rows). As you go, Excel highlights the selected cells in blue.

Once you've highlighted all the cells you want, release the mouse button. Now you can perform an action, like copying the cell's contents, formatting the cells, or pasting new values into the selected cells.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

First things first: before you can make any changes to an existing worksheet, you need to select the cells you want to modify. Happily, selecting cells in Excel—try saying that five times fast—is easy. You can do it many different ways, and it's worth learning them all. Different selection techniques come in handy in different situations, and if you master all of them in conjunction with the formatting features described in Chapter 4, you'll be able to transform the look of any worksheet in seconds.

Simplest of all is selecting a continuous range of cells. A continuous range is a block of cells that has the shape of a rectangle (high school math reminder: a square is a kind of rectangle), as shown in Figure 3-1 The easiest way to select a continuous range is to click the top-left cell you want to select. Then drag to the right (to select more columns) or down (to select more rows). As you go, Excel highlights the selected cells in blue.

Once you've highlighted all the cells you want, release the mouse button. Now you can perform an action, like copying the cell's contents, formatting the cells, or pasting new values into the selected cells.

Figure 3-1: Top: The thick black border tells you that you've selected the cells A1, B1, and C1.
Bottom: Notice that Excel doesn't highlight the first cell selected. Why? To remind you that it's the active cell: if you start typing, Excel inserts your text in this cell.

In the simple expense worksheet from Chapter 1, for example, you could first select the cells in the top row and then apply bold formatting to make the column titles stand out. (Once you've selected the top three cells, press Ctrl+B or, on the Formatting toolbar, click the bold "B.")

If you select some cells and then press an arrow key or click into another cell before you perform any action, Excel clears your selection.

Here are a few useful shortcuts for making continuous range selections (some of these shortcuts are illustrated in Figure 3-2):

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

One of the most common reasons to select groups of cells on a worksheet is to copy or move them from one place to another. (You move cells around, for example, when you change your mind about how to organize your data, or when you find yourself adding so much data to one table that it threatens to take over the rest of the tables on your spreadsheet.) Excel is a champion of the basic cut-and-paste feature and also gives you worthwhile enhancements that allow you to do things like drag and drop blocks of cells and copy multiple selections to the clipboard at the same time.

Before you get started shuffling data from one place to another, here are a few points to keep in mind:

Excel lets you cut or copy a single cell or a continuous range of cells. When you cut or copy a cell, everything goes with it, including the data or formula, and the current formatting.
When you paste cells onto your worksheet, you have two basic choices. You can paste the cells into a new, blank area of the worksheet, or you can paste the cells in a place that already contains data. In this second case, Excel overwrites the existing cells with the new pasted data.
Cutting and copying cells work almost exactly the same way. The only difference you'll see is that when you perform a cut-and-paste operation (as opposed to a copy-and-paste operation), Excel erases the source data once the operation is complete. However, Excel doesn't remove the source cells from the worksheet. Instead, it just leaves them blank. (The next section shows you what to do if you do want to remove or insert cells, not just the data they contain.)

Here is the basic process you'll follow in any cut-and-paste or copy-and-paste operation:

Select the cells you want to cut or copy.

You can use any of the tricks you learned in the previous section to highlight a continuous range of cells. (You can't cut-and-paste non-contiguous selections.) If you only want to cut or copy a single cell, just move to the cell—you don't actually need to select it.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The cut-and-paste and copy-and-paste operations let you move data from one cell (or group of cells) to another. But what happens if you want to make some major changes to your worksheet itself? For example, imagine you have a spreadsheet with 10 filled columns (A to J) and you decide you want to add a new column between columns C and D. You could cut all the columns from D to J, and then paste them starting at E. That would solve the problem, and leave the C column free for your new data. But the actual task of selecting these columns is a little awkward, and it only becomes more difficult as your spreadsheet grows in size.

A much easier option is to use two dedicated Excel commands designed for inserting new columns and rows into an existing spreadsheet. If you use these features, you won't need to disturb your existing cells at all.

Figure 3-8: The paste icon appears following the completion of every paste operation, letting you control a number of options. For example, choosing "Values and number formatting," tells Excel to copy the cell content and the number format, but ignore other formatting information like font and cell color. Chapter 4 covers formatting.

To insert a new column, follow these steps:

Select the column immediately to the right of where you want to place the new column.

That means that if you want to insert a new, blank column between columns A and B, start by selecting the existing column B. Remember, you select a column by clicking the column header.
Choose Insert → Columns.

Excel inserts a new column, and automatically moves all the columns to the right of column A (so column B becomes column C, column C becomes column D, and so on).

Inserting rows is just as easy as inserting new columns. Just follow these steps:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Formatting Cell Values
Formatting Cell Appearance
Smart Ways to Apply Formatting

Creating a basic worksheet is only the first step toward mastering Excel. If you plan to print your worksheet, email it to colleagues, or show it off to friends, you need to think about whether you've formatted your worksheet in a viewer-friendly way. A careful use of color, shading, borders, and fonts can make the difference between a messy glob of data and a worksheet that's easy to work with and understand.

But formatting isn't just about deciding, say, where and how to make your text bold. Excel also lets you control the way numerical values are formatted. In fact, there are really two fundamental aspects of formatting in any worksheet:

Cell appearance. Cell appearance includes cosmetic details like color, typeface, alignment, and borders. When most people think of formatting, they think of cell appearance first.
Cell values. Cell value formatting controls the way Excel displays numbers, dates, and times. For numbers, cell formatting includes details like whether to use scientific notation, the number of decimal places Excel displays, and the use of currency symbols, percent signs, and commas. With dates, cell value formatting determines what parts of the date are shown in the cell, and in what order.

In many ways, cell value formatting is more significant than cell appearance, because cell value formatting can change the meaning of your data. For example, even though 45%, $0.45, and 0.450 are all the same number, your spreadsheet readers will see a failing test score, a cheap price for chewing gum, and a world-class batting average, respectively. Color and alignment can't hope to compete.

Keep in mind that regardless of how you format your cell values, Excel maintains an unalterable value

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Cell value formatting is one aspect of worksheet design you don't want to ignore, because the values Excel stores can differ wildly from the numbers that it displays in the worksheet, as shown in Figure 4-1. In many cases, it makes sense to have the numbers that appear in your worksheet differ from Excel's underlying values, since a worksheet that's displaying numbers to, say, 13 decimal places, can look pretty cluttered.

Figure 4-1: Each of the cells B2, B3, and B4 contains the same number: 5.18518518518519. In the Formula bar, Excel will always display the exact number it's storing, as you see here with cell B2. However, in the worksheet itself, each cell's appearance differs depending on how you've formatted it.

To format a cell's value, follow these steps:

Select the cells you want to format.

You can apply formatting to individual cells or to a collection of cells. Usually, you'll want to format an entire column at once, because all the values in a column typically contain the same type of data. Remember, to select a column, you simply need to click the column header (the gray box at the top with the column letter).

Technically, your average numeric column contains two types of data: the values you're storing within the actual cells and the column title in the topmost cell (where the title text is). However, you don't need to worry about unintentionally formatting the column title because number formats are applied only to numeric cells (cells that contain dates, times, or numbers). Excel doesn't use the number format for the column title cell because the column title cell contains text.
Select Format → Cells, or just right-click the selection and choose Format Cells.

In either case, the Format Cells dialog box appears, as shown in Figure 4-2.
Set the format options you want, and then click OK to apply them

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Formatting cell values is important since it helps maintain consistency among your numbers. But to really make your spreadsheet readable, you're probably going to want to enlist some of Excel's tools for controlling things like alignment, color, and borders and shading.

Figure 4-6: Special number formats are ideal for formatting sequences of digits into a common pattern. For example, if you choose Phone Number in the Type list, Excel converts the sequence of digits 5551234567 into the proper phone-number style—(555) 123-4567—with no extra work required on your part.

To format the appearance of a cell, first select the single cell or group of cells that you want to work with, and then choose Format → Cells from the menu, or just right-click the selection and choose Format Cells. The Format Cells dialog box that appears (Figure 4-7, top) is the place where you adjust your settings.

Even a small amount of formatting can make a worksheet easier to interpret by drawing the viewer's eye to important information. Of course, as with formatting a Word document or designing a Web page, a little goes a long way. Don't feel the need to bury your worksheet in exotic colors and styles just because you can.

As you saw in the previous chapter, Excel automatically aligns cells according to the type of information you've typed in. But what if this alignment isn't what you want? Fortunately, the Alignment tab in the Format Cells dialog box (Figure 4-7, top) lets you easily change alignment as well as control some other interesting settings, like the ability to rotate text.

Excel lets you control the position of content between a cell's left and right borders with the following choices, some of which are shown in Figure 4-7, bottom:

General. General is the standard type of alignment; it aligns cells to the right if they hold numbers or dates and to the left if they hold text. General alignment is the type you learned about in Chapter 2.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

You've now had a comprehensive tour of Excel's formatting features. But, of course, just because the features are there doesn't mean they're easy to use. You can chew up hours digging through the different options and applying a full range of formatting choices to each and every cell. Fortunately, Excel also includes a few time-savers that let you speed up many formatting tasks. The final few sections of this chapter introduce these features. You'll see how to skip the Format Cells dialog box with a few toolbar tricks, how to copy and standardize formatting with Styles and the Format Painter, and how to add a little built-in graphical intelligence to your worksheet with conditional formatting.

To control cell formatting, you need to jump between your worksheet and the Format Cells dialog box, which is time-consuming. But what if there were a way to apply basic formatting without jumping to a new window? In fact, Excel provides just such a handy shortcut with its Formatting toolbar.

The Formatting toolbar can't duplicate every feature in the Format Cells dialog box. But it does do the next best thing: it lets you apply the most common types of formatting with one or two quick mouse clicks.

The Formatting toolbar usually appears at the top of the Excel window. If you've lost yours, just select View → Toolbars → Formatting from the menu.

Using the Formatting toolbar is similar to using the Format Cells dialog box. First, move to the cell you want to change, or select a group of cells. Then click the appropriate button in the toolbar to apply a new font, fill pattern, border, or cell value format. Figure 4-14 outlines your options.

But wait…there's more! The Formatting toolbar also includes a few tools that aren't available in the Format Cells dialog box: the ability to draw borders directly on the worksheet and a way to format individual characters. The next two sections show you how.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Worksheets and Workbooks
Find and Replace
Spell Check

So far in this book you've seen how to create and format a basic table of data. That's great for getting started, but as power users, professional accountants, and other Excel jockeys quickly learn, some of the most compelling reasons to use Excel involve multiple tables that share information and interact with each other.

For example, say you want to track the performance of your company: you create one table summarizing your firm's yearly sales, another listing expenses, and a third analyzing profitability and making predictions for the coming year. If you create these tables in different spreadsheet files, you have to copy shared information from one location to another, all without misplacing a number or making a mistake. And what's worse, with data scattered in multiple places, you're missing the chance to use some of Excel's niftiest charting and analytical tools.

On the other hand, if you try cramming a bunch of tables onto the same worksheet page, as shown in Figure 5-1, you can quickly create formatting and cell management problems. Not only do you have to be careful to avoid overlapping data, but if you stack tables on top of each other and then discover you need to add more data to the first table, you have to move the second table. And when you combine multiple tables in a single worksheet, you also have trouble properly resizing or formatting columns, because each column contains data from a different table. Putting tables side by side, separated by a blank column is a somewhat better choice, but it can create problems if you need to add more columns to the first table.

To add new rows and columns and move the rest of your data out of the way, you can use the Insert → Rows and Insert → Columns commands. But most Excel masters agree that using separate worksheets, described in the next section, for each large table you create is a better solution.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

When you create a new workbook, Excel automatically names it Book1 and fills it with three blank worksheets named Sheet1, Sheet2, and Sheet3. Each worksheet contains the same grid of cells—from A1 all the way to IV65536. Often, you'll work exclusively with the first worksheet (Sheet1) and not even realize that you have two more blank worksheets to play with.

You can tell which worksheet is currently active (selected) by taking a look at the color of each worksheet's tab. A white tab means you've selected that sheet; a gray tab means the sheet is awaiting your click.

To move from one worksheet to another, you have a few choices:

Click the worksheet tabs at the bottom of Excel's grid window (just above the status bar), as shown in Figure 5-2.
Press Ctrl+Page Down to move to the next worksheet. For example, if you're currently in Sheet1, this key sequence jumps you to Sheet2.
Press Ctrl+Page Up to move to the previous worksheet. For example, if you're currently in Sheet2, this key sequence takes you back to Sheet1.

Excel keeps track of the active cell in each worksheet. That means if you're in cell B9 in Sheet1 and then move to Sheet2, when you jump back to Sheet1 you'll automatically return to cell B9.

When you open a fresh workbook in Excel, you automatically get three cleverly named blank worksheets: Sheet1, Sheet2, and Sheet3. Adding more worksheets is easy. Just choose Insert → Worksheet, and Excel inserts a new worksheet just before the current worksheet and assigns it a new name. For example, if you move to Sheet2 and select Insert → Worksheet, Excel adds a new worksheet between Sheet1 and Sheet2. Excel names this worksheet Sheet4, because it's the fourth worksheet added to your workbook. (You can rearrange your worksheets into a more reasonable order; see Section 5.1.2.)

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

When you're dealing with great mounds of data, it's tough to ferret out the nuggets of data you need. Fortunately, Excel's find feature is great for helping you locate numbers or text, even when they're buried within massive workbooks holding dozens of complex worksheets. And if you need to make changes to a bunch of identical items, you'll discover the find-and-replace option to be a real time-saver.

The find feature includes both simple and advanced options. In its basic version, you're only a quick keystroke combo away from a word or number you know is lurking somewhere in your data pile. With the advanced options turned on, you can do things like search for cells that have certain formatting characteristics and then apply changes automatically. The next few sections dissect these features.

Excel's find feature is a little like the Go To tool described in Chapter 1, which lets you leap across a large expanse of cells in a single bound. The difference is that Go To moves to a known location, using the cell address you specify. The find feature, on the other hand, searches every cell until it finds the content you've asked Excel to look for. If you're familiar with the search feature in Microsoft Word, you'll be happy to know that Excel's search works pretty much the same way, with these few Excel-specific additions:

Excel searches by comparing the content you type in the "Find and Replace" dialog box with the content in each cell. For example, if you search for the word Date, Excel identifies as a match a cell containing the phrase Date Purchased.
When searching cells that contain numeric or date information, Excel always searches the display text. (For more information about the difference between the way Excel displays a numeric value and the underlying value Excel actually stores, see the box on Section 4.1.1.7.)
For example, say a cell displays dates using the day-month-year format, like

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

A spell checker in Excel? Is that supposed to be for people who can't spell 138 correctly? The fact is that more and more people are cramming text—column headers, commentary, lists of favorite cereal combinations—into their spreadsheets. And Excel's designers have graciously responded by providing the very same spell checker that you've used with Microsoft Word. As you may expect, Excel's spell checker examines only text as it sniffs its way through a spreadsheet.

In Office 2003 and Office XP, the same spell checker works in almost every Office program, including Word, PowerPoint, and Outlook.

To start the spell checker, follow these simple steps:

Move to where you want to start the spell check.

If you want to check the entire worksheet from start to finish, move to the first cell. Otherwise, move to the location where you want to start checking. Or, if you want to check a portion of the worksheet, select the cells you want to check.

Unlike the find-and-replace feature, Excel's spell check can check only one worksheet at a time.
Choose Tools → Spelling, or press F7.

The Excel spell checker starts working immediately, starting with the current cell and moving to the right, going from column to column. After it finishes the last column of the current row, checking continues with the first column of the next row.

If you don't start at the first cell (A1) in your worksheet, Excel will ask you when it reaches the end of the worksheet whether it should continue checking from the beginning of the sheet. If you say yes, it checks the remaining cells and stops when it reaches your starting point (having made a complete pass through all of your cells).

When the spell check finishes, a dialog box informs you that all cells have been checked. If your cells pass the spell check, this dialog box is the only feedback you receive. On the other hand, if Excel discovers any potential spelling errors during its check, it highlights the worksheet cell and displays a Spelling window, shown in Figure 5-13, indicating the offending word and a list of suggestions.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Controlling Your View
Printing

Last chapter gave you the tools you need to create multiple tables, worksheets, and workbooks. While this is all well and good—and absolutely essential, depending on what you plan to use Excel for—these features can quickly bury you in an avalanche of data. If you want to see more than one part of the workbook at once, or if you want an overview of the entire worksheet, you have to seize control of Excel's viewing features.

These features include zooming (which lets you magnify cells or just fit more information into your Excel window), panes (which let you see more than one part of a worksheet at once), and freezing (which lets you keep certain cells visible at all times). This chapter teaches you how to use these features, store a custom view, and even save a workspace (a configuration that lets you edit multiple files in one window).

No matter what your worksheets look like on a screen, sometimes the best way to review them is in print. The second half of this chapter tackles printing your worksheets. You'll learn Excel's basic printing options and a few tricks that can help you preview page breaks and make sure Excel divides large amounts of data the way you want.

So far, most of the sample worksheets in this book have included only a small amount of data. But as you expand your real-life data with dozens of columns, and hundreds or even thousands of rows, editing becomes much trickier. The most challenging problems are keeping track of your place in an ocean of information and making sure the data you want stays visible. Double that if you have multiple large worksheets in one workbook.

The following sections introduce the basic tools you can use to view your data, along with a few tips for managing large worksheets.

Excel's zoom feature lets you control how much data you see in the window. When you reduce the zoom percentage—say, from 100 percent to 10 percent— Excel shrinks your individual cells, letting you see more of them at once, which also makes it harder to read the data. Very small zoom percentages are ideal for looking at the overall layout of a worksheet. When you

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

So far, most of the sample worksheets in this book have included only a small amount of data. But as you expand your real-life data with dozens of columns, and hundreds or even thousands of rows, editing becomes much trickier. The most challenging problems are keeping track of your place in an ocean of information and making sure the data you want stays visible. Double that if you have multiple large worksheets in one workbook.

The following sections introduce the basic tools you can use to view your data, along with a few tips for managing large worksheets.

Excel's zoom feature lets you control how much data you see in the window. When you reduce the zoom percentage—say, from 100 percent to 10 percent— Excel shrinks your individual cells, letting you see more of them at once, which also makes it harder to read the data. Very small zoom percentages are ideal for looking at the overall layout of a worksheet. When you increase the zoom percentage—say, from 100 percent to 200 percent—Excel magnifies your cells, letting you see more detail but fewer cells. Larger zoom percentages are good for editing.

Excel lets you zoom in (enlarge) to 400 percent and zoom out (shrink) all the way down to 10 percent.

You can adjust the zoom for an open worksheet by selecting View → Zoom. A Zoom dialog box appears (shown in Figure 6-1) that lets you select a preset zoom percentage or type in your own percentage in the Custom box.

Figure 6-1: The standard zoom setting is 100 percent, although other factors like the size of the font you're using and the size and resolution of your computer screen help determine how many cells fit into Excel's window.

As a rule of thumb, every time you double the zoom, Excel cuts in half the number of rows you can see. So, for example, if you see 20 rows at 100 percent, you see 10 rows at 200 percent.

Changing the zoom affects how your data appears in the Excel window, but it doesn't have any effect on how Excel prints or calculates your data.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Printing in Excel is pretty straightforward—as long as your spreadsheet fits on a normal 8.5 x 11-inch piece of paper. If you're one of the millions of spreadsheet owners who don't belong to that club, then welcome to the world of Multiple Page Disorder: the phenomenon in which pages and pages of apparently unrelated and noncontiguous columns start spewing from your printer. Fortunately, Excel comes with a slew of print-tweaking tools designed to help you control what you're printing. First off, though, it helps to understand the settings Excel uses when you click the print button:

You can change most of the settings listed; the following is just a list of what happens if you don't adjust any settings before printing a spreadsheet.

In the printout, Excel uses all the formatting characteristics you've applied to the cells, including fonts, fills, and borders. However, Excel's gridlines, row headers, and column headers don't appear in the printout.
If your data is too long (all the rows won't fit on one page) or too wide (all the columns won't fit), Excel prints the data on multiple pages. If your data is both too long and too wide, Excel prints in the following order: all the rows for the first set of columns that fit on a printed page, then all the rows for the next set of columns that fit, and so on (this is known as "down, then over"). When printing on multiple pages, Excel never prints part of an individual column or row.
Excel prints your file in color if you use colors and you've got a color printer.
Excel sets margins to 1 inch on the top and bottom of the page and 0.75 inches on the left and right sides of the page. It doesn't include headers and footers (so you won't see any page numbers).
Excel doesn't include hidden rows and columns in the printout.

To print an Excel file, follow these steps:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Creating a Basic Formula
Formula Shortcuts
Copying Formulas

Most excel fans don't turn to the world's leading spreadsheet software just to create nicely formatted tables. Instead, they rely on Excel's industrial-strength computing muscle, which lets you reduce reams of numbers to neat subtotals and averages. Performing these calculations is the first step to extracting meaningful information out of raw data.

Excel provides a number of different ways to build formulas, letting you craft them by hand or point-and-click them into existence. In this chapter, you learn about all of these techniques. You start by examining the basic ingredients that make up any formula and then take a close look at the rules Excel uses when evaluating a formula.

First things first: what exactly do formulas do in Excel? A formula is simply a series of mathematical instructions that you place in a cell in order to perform some kind of calculation. These instructions may be as simple as telling Excel to add up (sum) a column of numbers, or they may be complex enough to use advanced statistical functions to spot trends and make predictions. Here are some sample formulas written in English (the rest of this chapter is devoted to writing formulas in Excel-speak):

Calculate the sum of all the numbers in Column B2.
Calculate the difference between whatever number is in Cell G3 and whatever number is in Cell G2.
Calculate the product of the number in H32 and the number in I54 (both of which, themselves, happen to be calculated by still other formulas).

As you can imagine, formulas are outrageously useful, no matter what kind of spreadsheet you're creating. Say, for example, you're in sales, and you want to create a spreadsheet that keeps track of your yearly income and expenses. You can type in your sales figures for the last month and have Excel calculate and display updated year-to-date sales and profit figures—instantly. Or, imagine that you want to create a spreadsheet to track the housing market. (You're thinking of buying a house, and you want to compare prices, mortgage rates, and loan terms on a bunch of different properties to see if you can afford anything you'd actually want to live in.) Using formulas, you can run

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Table of Contents