Excel 2003: The Missing Manual

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Excel has a long and colorful history. Microsoft released the first version of Excel for the Macintosh in 1985 and added a version for Windows two years later. By 1993, Excel had morphed into an all-purpose number-cruncher, and it quickly eclipsed competing spreadsheet programs like Lotus 1-2-3.

Although Microsoft is reluctant to admit it, most of Excel's core features were completed six or seven years ago. So what has Microsoft been doing ever since? The answer, at least in part, is spending millions of dollars on so-called usability tests, which are aimed at figuring out how easy—or not—a program is to use. In a typical usability test, Microsoft gathers a group of spreadsheet novices, watches them fumble around with the latest version of Excel, and then tweaks the program to make it more intuitive. As a result, Excel is packed full of timesaving features—some nifty, some just quirky—like menus that hide advanced options, lists that complete themselves, animated assistants, and every imaginable type of button, toolbar, and dockable window. Some of the features are genuinely useful, while others can be supremely annoying—especially when Excel hides the feature you're hunting for desperately.

The best way to avoid potential headaches like these is to take a quick tour of Excel as you start creating a spreadsheet. That's the task you'll complete in this chapter. Along the way, you'll 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. 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'll perform all your work, such as entering data, writing formulas, and reviewing the results.

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 PDF of this chapter|Buy reprint rights for this chapter

When you first launch Excel, it starts you off with a new, blank worksheet. 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'll perform all your work, such as entering data, writing formulas, and reviewing the results.

Figure 1-1: The Excel window has several parts, most notably the worksheet grid where you type in your information, and the Task Pane, which shows different options depending on the task you're currently performing.

Part of getting to know Excel is figuring out where to go when you need help. For the lowdown on Excel's online help, which ranges from indispensable to downright infuriating, see Appendix A.

Here are a few basics about Excel's grid:

The grid divides your worksheet into rows and columns. Columns are identified with letters (A, B, C ...), while rows are identified with numbers (1, 2, 3 ...).
The smallest unit in your worksheet is the cell. Cells are identified by column and row. 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 tracking blades of grass on the White House lawn—you'll need to create a new worksheet. Every spreadsheet file can hold multiple worksheets, as you'll learn 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 PDF of this chapter|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 the cell content, 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 BEDP is to manually resize the column, as shown in Figure 1-4. Chapter 2 tackles editing in more detail and shows how you can deal with these dilemmas by resizing columns (Chapter 2). 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 PDF of this chapter|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'll see the black focus box move to highlight the currently active cell.

In some cases, you might 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, and then press Home.

Most people like to identify columns with letters and rows with numbers. This system makes it easy to tell the difference between the two, and it allows you to use short cell addresses like A10, B4, H99, and so on. When you first install Excel, it uses this style of cell addressing.

However, Excel also allows you to use another cell addressing system called R1C1. In R1C1 style, columns and rows are both identified with numbers. That means the cell address A10 becomes R10C1 (read this as Row 10, Column 1). The letters R and C tell you which part of the address is the row and which part is the column. The order is reversed from conventional cell addressing.

R1C1 addressing isn't all that common. But it becomes useful if you need to deal with worksheets that have more than 26 columns. With normal cell addressing, Excel runs out of letters after column 26, and it starts using two letters (as in AA, AB, AC ...) to identify columns. This approach can become somewhat awkward. For example, if you want to find cell AX1, it isn't immediately obvious that this cell is in column 50. On the other hand, the R1C1 address for the same cell, R1C50, gives you a clearer idea of where to find the 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 PDF of this chapter|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 allows you to 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 discussed below). To use Save As, select File → Save As, or press F12. Figure 1-15 shows you the Save As dialog box.

Figure 1-15: The Save As dialog box lets you jump to common folders, or you can browse a folder tree using the drop-down "Save in" menu. Type the file name at the bottom of the window, and pick the file type Finally, choose Tools → General Options from the top-right of the window for additional options.

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 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.

Excel saves spreadsheets as .xls files (i.e., AirlineSilverware.xls). As hard-core 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 .xls files, but the underlying format is actually different—and sometimes maddeningly irreconcilable.

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 PDF of this chapter|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 is not 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).

Figure 1-20: Top: Every time Excel starts up, it looks for AutoRecover backups. If it finds a backup, that means a document was not properly saved the last time you exited from Excel. Excel then opens a bar at the side of your window with all the backup files it finds, and lists their statuses. [Recovered] means the file backup is ready and error-free, while [Original] indicates the file was saved by you, and Excel didn't make a backup after your last save. (The only time you'll see the original file in the Document Recovery window is when Excel (or your computer) crashes, but there isn't any unsaved data or backup file. In this situation, you don't need to worry since you haven't lost any 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 PDF of this chapter|Buy reprint rights for this chapter

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, 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 timesavers, including the indispensable Undo feature.

One of Excel's most important features is its ability to distinguish between 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 enter information in Excel, 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.

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 PDF of this chapter|Buy reprint rights for this chapter

One of Excel's most important features is its ability to distinguish between 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 enter information in Excel, 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, but they're useful in worksheets that include Visual Basic macro code (see Chapter 25) or that use complex formulas that evaluate conditions (see Chapter 12).

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 PDF of this chapter|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 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.

Some worksheets require that you type in the same information row after row. For example, if you're creating a table to track the value of all the Sesame Street collectibles you own, you only can type in Kermit so many times before you start turning green. Excel tries to help you out with its AutoComplete feature, which examines what you type, compares it against previous entries in the same column, and, if it recognizes the beginning of an existing word, fills it in.

For instance, in your Sesame Street worksheet, if you already have Kermit in the Characters column, when you start typing a new entry in that column beginning with the letter K, Excel automatically fills in the whole word Kermit. Excel then selects the letters that it's added (in this case, ermit). You now have two options:

If you want to accept the AutoComplete text, move to another cell. For example, if you hit the right arrow key or press Enter to move down, Excel leaves the word Kermit behind.
If you want to blow off Excel's suggestion, just keep typing. Because Excel automatically selects the AutoComplete portion of the word (

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 PDF of this chapter|Buy reprint rights for this chapter

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 PDF of this chapter|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 three selected cells (A1, B1, and C1) cover the column titles.
Bottom: This selection covers the nine cells that make up the rest of the worksheet. Notice that Excel doesn`t highlight the first cell you select. In fact, Excel knows you`ve selected it (as you can see by the thick black border that surrounds it), but it has a white background to indicate 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.")

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 PDF of this chapter|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. Excel is a champion of the basic cut-and-paste feature, and it 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 allows you to 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 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 works 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 PDF of this chapter|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 can be 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.

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:

Select the row that's immediately below where you want to place the new row.

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 PDF of this chapter|Buy reprint rights for this chapter

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 your worksheet is formatted 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, this includes details like whether to use scientific notation, the number of decimal places displayed, 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.

Cell value formatting is in many ways more significant than cell appearance, because it 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.

Keep in mind that regardless of how you format your cell values, Excel maintains an unalterable value for every number entered. For more on how Excel internally stores numbers see the box on Sidebar 4.1.

In this chapter, you'll learn about cell value formatting, and then unleash your inner artist with cell appearance formatting. Finally, you'll learn the most helpful ways to use formatting to improve a worksheet's readability and how to save time with nifty features like AutoFormat, styles, and conditional formatting.

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 PDF of this chapter|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 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: This worksheet shows how different formatting can affect the appearance of the same data. Each of the cells B2, B3, and B4 contains the exact same number: 5.18518518518519. Excel will always display in the Formula bar 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 the cell.

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, a 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 text is). However, you don't need to worry about unintentionally formatting the column title because number formats are only applied to numeric cells (cells that contain dates, times, or numbers). Excel doesn't use the number format for the column title cell because it contains text.
Select Format

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 PDF of this chapter|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.

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 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 learned in the previous chapter, Excel automatically aligns cells according to the type of information you've entered. But what if this default alignment isn't what you want? Fortunately, the Alignment tab in the Format Cells dialog box 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, offers the following choices, some of which are shown in Figure 4-8:

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. This is the type of alignment you learned about in Chapter 2.
Left (Indent). Left indicates that Excel should always line up content with the left edge of the cell. You can also choose an indent value to add some extra space between the content and the left border.

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 PDF of this chapter|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. Digging through the different options, and applying a full range of formatting choices can be a tedious task. Fortunately, Excel also includes a few timesavers 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.

Excel gives you a fairly narrow set of color choices for fonts, borders, and fills, which you can find by choosing Tools → Options and then clicking the Colors tab. The 56 colored boxes there are you basic choices. You can, however, alter those. Select one of the color boxes, and then click the Modify button. You'll see a more extensive dialog box that lets you pick an exact shade.

Bear in mind that you can't tweak any of the colors while you're formatting a cell. Instead, you need to set the color beforehand. Also, although Excel lets you modify colors, there's no way to create extra colors in a worksheet.

Color settings are specific to a single workbook file. However, you can copy them from one file to another. Just open both workbooks at once, and then, in the workbook where you want to place the copied colors, choose Tools → Options. Click the color tab, and then, in the "Copy colors from" list, find the name of the workbook that has the colors you want to copy. Excel refreshes the set of colors immediately. You can also click Reset to return your color settings to Excel's standard choices.

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 PDF of this chapter|Buy reprint rights for this chapter

So far you've learned 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. Similarly, if you try cramming a bunch of tables onto the same worksheet page, you can quickly create formatting and cell management problems, as shown in Figure 5-1.

Fortunately, a better solution exists. Excel lets you create spreadsheets with multiple pages of data, each of which can conveniently exchange information with other pages. Each page is called a worksheet , and a collection of one or more worksheets is called a workbook (which is also sometimes called a spreadsheet file). In this chapter, you'll learn how to manage worksheets and workbooks. You'll also take a look at two more all-purpose Excel features: find and replace (a tool for digging through worksheets in search of specific data) and the spell checker.

When you create a new workbook, Excel automatically fills it with three blank worksheets named Sheet1, Sheet2, and Sheet3. Often, you'll work exclusively with the first worksheet (Sheet1), and not even realize that you have two more blank worksheets to play with.

Figure 5-1:

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 PDF of this chapter|Buy reprint rights for this chapter

When you create a new workbook, Excel automatically fills it with three blank worksheets named Sheet1, Sheet2, and Sheet3. Often, you'll work exclusively with the first worksheet (Sheet1), and not even realize that you have two more blank worksheets to play with.

Figure 5-1: Top: If you put more than one table in the same worksheet, you`ll need to be careful to avoid overlapping data. Stacking tables on top of each other is usually a bad idea. If you need to add more data to the first table, you have to move the second table. You`ll also have trouble properly resizing or formatting columns because each column contains data from two different tables.
Bottom: 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. Remember, you can use the Insert → Rows and Insert → Columns commands to add new rows and columns and move the rest of your data out of the way. But most Excel masters agree, the best solution lies in using separate worksheets for each large table you create.

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.

Figure 5-2: Worksheets provide a good way to organize multiple tables of data. To move from one worksheet to another, click the appropriate Worksheet tab at the bottom of the grid. Each worksheet contains the same grid of cells—from A1 all the way to IV65536.

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.

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 PDF of this chapter|Buy reprint rights for this chapter

When you're dealing with great mounds of data, it can be 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, the find-and-replace option can be a real time-saver.

The find and replace 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 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 move 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. Excel's search works similarly to the search feature in Microsoft Word, but it's worth keeping in mind a few additional details:

Excel searches by comparing the content you enter with the content in each cell. For example, if you searched 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—the underlying value Excel actually stores—see Sidebar 2.1.)

For example, say a cell displays dates using the day-month-year format, like 2-Dec-05. You can find this particular cell by searching for any part of the displayed date (using search strings 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 PDF of this chapter|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, boxes of 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 might 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 application, 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 only check 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).

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 PDF of this chapter|Buy reprint rights for this chapter

Last chapter gave you the tools to create multiple tables, worksheets, and workbooks. While this is all well and good, 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 those 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 large amounts of data are divided the way you want.

So far, most of the worksheets in this book have included only a small amount of data. But as you expand your data with dozens of columns, and hundreds or even thousands of rows, editing becomes much trickier. The most challenging problems are keeping track of where you are 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'll 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 PDF of this chapter|Buy reprint rights for this chapter

So far, most of the worksheets in this book have included only a small amount of data. But as you expand your data with dozens of columns, and hundreds or even thousands of rows, editing becomes much trickier. The most challenging problems are keeping track of where you are 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'll 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 to 400 percent and out all the way 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. Thus, if you can see 20 rows at 100 percent, you'll see 10 rows at 200 percent.

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 PDF of this chapter|Buy reprint rights for this chapter

Printing in Excel is pretty straightforward—as long as your spreadsheet fits on a normal 8.5 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 default settings Excel uses when you click the print button:

You can change most of the settings listed; this 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 will never print part

Table of Contents