O'Reilly Media | Excel 2007 for Starters: The Missing Manual

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

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

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.

Figure 1-1: The largest part of the Excel window is the worksheet grid where you type in your information.

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.

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.

Figure 1-1: The largest part of the Excel window is the worksheet grid where you type in your information.

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.

Figure 1-2: Here, the current cell is C6. You can recognize the current (or active) cell based on its heavy black border. You'll also notice that the corresponding column letter (C) and row number (6) are highlighted at the edges of the worksheet. Just above the worksheet, on the left side of the window, the formula bar tells you the active cell address.
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 worksheet. Every spreadsheet file can hold a virtually unlimited number of worksheets, as you'll learn in Chapter 4.

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

Figure 1-5: Top: The standard width of an Excel column is 8.43 characters, which hardly allows you to get a word in edgewise. To solve this problem, position your mouse on the right border of the column header you want to expand so that the mouse pointer changes to the resize icon (it looks like a double-headed arrow). Now drag the column border to the right as far as you want.
Bottom: When you release the mouse, the entire column of cells is resized to the new size.

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 leftaligned), indicating that Excel understands your date and price information.

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

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.

Table 1-1: Shortcut Keys for Moving Around a Worksheet
Key Combination	Result
→ (or Tab)	Moves one cell to the right.
← (or Shift+Tab)	Moves one cell to the left.
↑	Moves one cell up.
↓ (or Enter)	Moves one cell down.

Page Up	Moves up one screen. Thus, if the grid shows 10 cells at a time, this key moves to a cell in the same column, 10 rows up (unless you are already at the top of the worksheet).

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

Content preview·Buy 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.) To use Save As, select Office button → Save As, or press F12. Figure 1-15 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-15: 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 MyCoal-MiningFortune (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-20.

As you open multiple spreadsheets, Excel creates a new window for each one. You can easily jump from one spreadsheet to another by clicking the appropriate spreadsheet button in the Windows taskbar at the bottom of your screen.

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

Adding Different Types of Data
Quick Ways to Add Data

Now That You've Created A Basic Worksheet, and you're acquainted with Excel and its 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. Column headings, descriptions, and any content that Excel can't identify as one of the other data types.

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

Content preview·Buy 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. Column headings, descriptions, and any content that Excel can't identify as one of the other data types.
Numbers. Prices, integers, fractions, percentages, and every other type of numeric data. Numbers are the basic ingredient of most Excel worksheets.
Dates and times. 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 for programmer types and power users who want to create complex formulas.

One useful way to tell how Excel is interpreting your data is to look at cell alignment, as explained in Figure 2-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

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, AutoCorrect, AutoFill, and AutoFit, along with Excel's top candidates for the Lifetime Most Useful Achievement award: Undo and Redo.

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

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

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

No matter what the regional settings are, you can always use the international date standard, which is Year/Month/Day, though you must supply a four-digit year (as in 2008/7/4). When you use a two-digit year, Excel assumes you're trying to use the Month-Day-Year or Day-Month-Year pattern.

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.

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

Selecting Cells
Moving Cells Around
Adding and Moving Columns or Rows

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

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

First things first: before you can make any changes to an existing worksheet, you need to select the cells you want to modify. Happily, selecting cells in Excel—try saying that five times fast—is easy. You can do it many different ways, and it's worth learning them all. Different selection techniques come in handy in different situations, and if you master all of them in conjunction with the formatting features described in Chapter 5, 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 5, 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 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.)

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

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.

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

Worksheets and Workbooks
Find and Replace
Spell Check

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

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 the items you've purchased over two consecutive years. 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 the items you've purchased over two consecutive years. 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.

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.

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.

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.
Press Ctrl+Page Up to move to the previous worksheet. For example, if you're currently in Sheet2, this key sequence takes you back to Sheet1.

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 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 Dec or 2-Dec-05). But if you use the search string 12/2/2005

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-14, 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

Formatting Cell Values
Formatting Cell Appearance
Smart Ways to Apply Formatting

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

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.

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-9:

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)

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

Earlier in this 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 the following sections, 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.

None of these tools are new to Excel 2007, but Microsoft has vastly improved the styles and themes so they look better, do more, and are easier to use. Once you master these three 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.

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.

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

Controlling Your View
Printing
Controlling Pagination

The Previous Chapters Have Given You All The Tools you need to create nicely formatted worksheets. 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 your workbook at once, or if you want an overview of the entire worksheet, then 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 all these tools, and how to store a custom view so your spreadsheet looks just the way you want it.

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 get 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 cram your worksheets 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.

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 cram your worksheets 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 most easily adjust the zoom percent by using the zoom slider in the bottom-right part of the status bar. The zoom slide also displays the current zoom percentage. But if you want to specify the exact zoom percentage by hand (say, 142 percent), then you can choose View → Zoom → Zoom. A Zoom dialog box appears (Figure 6-1).

Figure 6-1: Left: Using the Zoom dialog box, you can select a preset zoom percentage or, in the Custom box, type in your own percentage.
Right: But using the Zoom slider is almost always faster than making frequent trips to the Zoom dialog box.

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, then 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 x 11-inch piece of paper. If you're one of the millions of spreadsheet owners who don't belong to that club, 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

Table of Contents