Excel 2007: The Missing Manual

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

Every Excel grandmaster needs to start somewhere. In this chapter, you'll create your first spreadsheet. You'll learn to move around in it, enter basic information, and save it for posterity. Along the way, you'll take a quick tour of the Excel window, and stop to meet the different tabs in the ribbon, the status bar, and the formula bar.

When you first launch Excel, it starts you off with a new, blank worksheet, as shown in Figure 1-1. A worksheet is the grid of cells where you type your information and formulas; it takes up most of the window. 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.

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,000 characters.
A worksheet can span an eye-popping 16,000 columns and 1 million rows. 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 work sheet. Every spreadsheet file can hold a virtually unlimited number of worksheets, as you'll learn in Chapter 4.
When you enter information, you enter it 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.

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, as shown in Figure 1-1. A worksheet is the grid of cells where you type your information and formulas; it takes up most of the window. 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.

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,000 characters.
A worksheet can span an eye-popping 16,000 columns and 1 million rows. 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 work sheet. Every spreadsheet file can hold a virtually unlimited number of worksheets, as you'll learn in Chapter 4.
When you enter information, you enter it 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 largest part of the Excel window is 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 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 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.

Figure 1-6: This rudimentary expense list has three items (in rows 2, 3, and 4). The alignment of each column reflects the data type (by default, numbers and dates are right-aligned, while text is left-aligned), indicating that Excel understands your date and price information.
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 get a cell into edit mode by double-clicking it.
Complete your edit.

Once you've modified the cell content, 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. 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.

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 see the black focus box move to highlight the currently active cell. In some cases, you might want to cover ground a little quicker. 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 you hold down Ctrl and press Home at the same time. Key combinations with a comma work in sequence. For example, the key combination "End, Home" means press End first, release 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)

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 in Section 1.4.2.1.) To use Save As, select Office button → Save As, or press F12. Figure 1-16 shows you the Save As dialog box as it appears on a Windows XP computer. (The Windows Vista version of the Save As dialog box has all the same features, but way more style.)

Figure 1-16: The Save As dialog box lets you jump to common folders using the big buttons on the left, or you can browse a folder tree using the drop-down "Save in" menu. Once you've found the folder you want, type the file name at the bottom of the window, and then pick the file type. Finally, click Save to finish the job.
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 Office button → Save, or press Ctrl+S. Or, look up at the top of the Excel window in the Quick Access toolbar for the tiny Save button, which looks like an old-style diskette.

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.

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. To get to the standard Open dialog box, choose Office button → Open. Using the Open 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 .xlsx format. To learn the other formats it supports, launch 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 might hide file extensions. That means that instead of seeing the Excel spreadsheet file MyCoalMiningFortune.xlsx, you'll just see the name MyCoalMiningFortune (without the .xlsx 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.

Plan to take another crack at a recent spreadsheet? You can find the most recently opened documents in Excel's Recent Documents list. To see this list, just open the Office button—it appears as a separate column on the right. The best part about the Recent Documents list is the way you can pin a document there so it stays forever, as shown in Figure 1-25.

Figure 1-25: To keep a spreadsheet around on the Recent Documents list, click the thumbtack on the right. It becomes green, and is now pinned in place. That means it won't ever leave the list, no matter how many documents you open. If you decide to stop working with it later on, just click the thumbtack again to release it. Pinning is a great trick for keeping your most important files at your fingertips.

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 spiffy new 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 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 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 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, 2007), times (like 4:30 p.m.), and combined date and time information (like Oct 3, 2007, 4:30 p.m.). 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 27) or that use complex formulas that evaluate conditions (see Chapter 13).

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 timesaving frills can make your life easier when you're entering data in a worksheet. This section covers four such features: AutoComplete, Auto-Correct, 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.

Figure 2-6: Tweaking the regional settings on your computer gives you complete control over how Excel recognizes dates. 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 could wind up with a computer that's completely counterintuitive to other people.

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 your Sesame Street collectibles, you can type in Kermit only 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,

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 cells' 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 cells' 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: When 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 chose Home → Font → Bold.)

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 let you 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 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's complete. However, Excel doesn't remove the source cells from the worksheet. Instead, it just leaves them empty. (The next section shows you what to do if you do want to remove or insert cells, not just the data they contain.)

Here's the basic procedure for 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.)

When you want to cut or copy only 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 Home → Cells → Insert → Insert Sheet 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.

That means that if you want to insert a new, blank row between rows 6 and 7, start by selecting the existing row 7. Remember, you select a row by clicking the row number header.

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 a basic worksheet with a 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, then 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, then you can quickly create formatting and cell management problems.

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 the worksheets in a workbook. 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.

Many workbooks contain more than one table of information. For example, you might have a list of your bank account balances and a list of items repossessed from your home in the same financial planning spreadsheet. You might find it a bit challenging to arrange these different tables. You could stack them (Figure 4-1) or place them side by side (Figure 4-2), but neither solution is perfect.

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

Many workbooks contain more than one table of information. For example, you might have a list of your bank account balances and a list of items repossessed from your home in the same financial planning spreadsheet. You might find it a bit challenging to arrange these different tables. You could stack them (Figure 4-1) or place them side by side (Figure 4-2), but neither solution is perfect.

Figure 4-1: Stacking tables on top of each other is usually a bad idea. If you need to add more data to the first table, then 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.

Figure 4-2: You're somewhat better off putting tables side by side, separated by a blank column, than you are stacking them, but this method can create problems if you need to add more columns to the first table. It also makes for a lot of side-to-side scrolling.

Most Excel masters agree that the best way to arrange separate tables of information is to use separate worksheets for each table. 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—not to mention the ability to add plenty more.

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 4-3.
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 information, you may have a tough time ferreting 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 worksheets. And if you need to make changes to a bunch of identical items, the find-and-replace option can be a real timesaver.

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 Section 2.1.)

For example, say a cell displays dates using the day-month-year format, like 2-Dec-05

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 probably used with Microsoft Word. As you might expect, Excel's spell checker examines only text as it sniffs its way through a spreadsheet.

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 check only one worksheet at a time.
Choose Review → Proofing → 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 asks 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 displays a Spelling window, as shown in Figure 4-16, showing 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 PDF of this chapter|Buy reprint rights for this chapter

When you create a basic workbook, you've taken only the first step toward mastering Excel. If you plan to print your data, email it to colleagues, or show it off to friends, you need to think about whether you've formatted your worksheets in a viewer-friendly way. The 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.

In many ways, cell value formatting is 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.

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 in Section 5.1.1.3.

In this chapter, you'll learn about cell value formatting, and then unleash your inner artist with cell appearance 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 5-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 5-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. In the formula bar, Excel always displays 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 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 Excel applies number formats only 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 Home → Cells → Format → Format Cells, or just right-click the selection, and then choose Format 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

Formatting cell values is important because 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 a cell's appearance, first select the single cell or group of cells that you want to work with, and then choose Home → Cells → Format → Format Cells, or just right-click the selection, and then 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, in the Format Cells dialog box, the Alignment tab 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, which is known as the horizontal alignment. Excel offers the following choices for horizontal alignment, some of which are shown in Figure 5-10:

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. You learned about this type of alignment in Chapter 2.
Left (Indent) tells Excel to 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

In the previous chapter, you took a comprehensive tour of Excel's formatting fundamentals. But of course, just because the features exist doesn't mean they're easy to use. Digging through the different options and applying a full range of formatting settings can be a tedious task. Fortunately, Excel also includes a few timesavers that let you speed up many formatting jobs.

In this chapter, you'll try out the essential formatting techniques that every Excel guru loves. They include:

The Format Painter, which provides a quick and dirty way to transfer formatting from one cell to another.
Styles, which let you standardize your favorite formatting choices so you can use them again.
Themes, which give you a toolkit with a collection of ready-to-use styles that can jazz up the dullest worksheet.
Conditional formatting, which gets Excel to do the hard work of finding values you're interested in and then highlighting them with custom formatting (or even a tiny icon).

None of these tools are new to Excel 2007, but Microsoft has vastly improved the styles, themes, and conditional formatting features so they look better, do more, and are easier to use. Once you master these four timesavers, you'll have the secret to making great-looking worksheets.

The Format Painter is a simple yet elegant tool that lets you copy all of a cell's format settings—including fonts, colors, fill, borders, and even the number format—from one cell to another. (Apparently, the Excel team decided that the more accurate label "Format Copier" wasn't nearly as exciting as the name Format Painter.)

To use the Format Painter, follow these steps:

Move to a cell that has the formatting you want to copy

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 Format Painter is a simple yet elegant tool that lets you copy all of a cell's format settings—including fonts, colors, fill, borders, and even the number format—from one cell to another. (Apparently, the Excel team decided that the more accurate label "Format Copier" wasn't nearly as exciting as the name Format Painter.)

To use the Format Painter, follow these steps:

Move to a cell that has the formatting you want to copy.

You can use the Format Painter to copy formatting from either one cell or a whole group of cells. For example, you could copy the format from two cells that use two different fill colors, and paste that format to a whole range of new cells. These cells would alternate between the two fill colors. Although this is a powerful trick, in most cases, it's easiest to copy the format from a single cell.
Choose Home → Clipboard → Format Painter to switch into "format painting" mode.

The pointer changes so that it now includes a paintbrush icon, indicating Excel is ready to copy the format.
Click the cell where you want to apply the format.

The moment you release your mouse button, Excel applies the formatting and your pointer changes back to its normal appearance. If you want to copy the selected format to several cells at once, just drag to select a group of cells, rows, or columns, instead of clicking a single cell.

Excel doesn't let you get too carried away with format painting—as soon as you copy the format to a new cell or selection, you exit format painting mode. If you want to copy the desired format to another cell, you have to backtrack to the cell that has your format, and start over again. However, there's a neat trick you can use if you know you're going to repeatedly apply the same format to a bunch of different cells. Instead of single-clicking the Format Painter button, double-click it. You'll remain in format painting mode until you click the Format Painter button again to switch it off.

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

Styles let you create a customized collection of format settings, give that collection a name, and store it in a spreadsheet file. You can then apply these settings anywhere you need them. For example, you could create a style called Great Big Header that uses the Cambria font, pumps up the font size to 46 points, and colors the text bright red.

Every Excel spreadsheet starts off with a collection of prebuilt styles. Microsoft designed these styles with two goals in mind: to give you quick access to most common and practical formatting choices, and to make great looking documents. To take a look at the styles waiting for you, choose Home → Styles → Cell Styles. Figure 6-1 shows the gallery of options that you'll see.

Figure 6-1: Excel's built-in styles are divided into separate categories according to how you might use them. The "Good, Bad and Neutral" category lets you separate the good news from the bad using the carefully shaded versions of the universal colors red, yellow, and green. The "Titles and Headings" category adds border formatting (Section 5.2.3) to make great titles. And the Themed Cell Styles category gives you a range of differently colored, differently shaded cells that are chosen to match harmoniously with one another based on the current workbook theme (Section 6.2.4).

You can apply more than one style to the same cell to get a combination of formatting options. For example, you could use the Currency style to get the right number format, and then pick the Bad style to flag a huge debt with a light red background fill. (Bad is simply the name of a prebuilt style that applies a light red background fill and a dark red font color.) If you apply more than one style and they conflict (for example, both styles use a different background color), the style you applied last takes over.

Styles use Excel's live preview feature, which gives you try-before-you-buy formatting. When you select a group of cells and then hover over one of the styles in the ribbon, your selected cells change instantaneously to reflect that style. Run your mouse over the different style options, and you see a quickly changing series of different formatting choices. To actually apply a style, click 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

A good worksheet highlights the most important information, thereby making it easy to spot. For example, if you look at a worksheet that shows the last year of a company's sales, you want to be able to find underperforming products without having to hunt through hundreds of cells. And even if you're not using Excel in the business world, you still need to hone in on key details in a spreadsheet—whether it's a budget-busting dinner in your monthly expense worksheet or a skipped week at the gym in your exercise log. All too often, these essential details are buried in an avalanche of data.

As you learned in Chapter 5, you can use formatting tricks to make important data stand out from the crowd. But the problem with formatting is that it's up to you to track down the cells that need to be formatted. Not only is this a time-devouring task, you also run into trouble when you start using formulas (as discussed in Part 2). Formulas let you set up elaborate calculations that link cells together, which means that a change to a single cell can cascade through your worksheet, altering data everywhere else. If you're highlighting important information by hand, you just might need to repeat the whole formatting process each time a value changes.

Fortunately, Excel has a feature that's designed to spare you the drudgery. It's called conditional formatting, and it allows Excel to automatically find and highlight important information. In this section, you'll learn to master conditional formatting to make sure important bits of data stick out for all to see. You'll also see how, with conditional formatting, you can use shaded bars and mini-pictures to give a

Table of Contents