Product Description

Whether you are an Excel neophyte, a sophisticate who knows the program inside out, or an intermediate-level plodder eager to hone your skills, "Excel 2003: The Missing Manual" is sure to become your go-to resource for all things Excel. Covering all the features of Excel 2002 and 2003, "Excel 2003: The Missing Manual" is an easy-to-read, thorough and downright enjoyable guide to one of the world's most popular, (and annoyingly complicated!) computer programs. Never a candidate for 'the most user-friendly of Microsoft programs', Excel demands study, practice and dedication to gain even a working knowledge of the basics. "Excel 2003" is probably even tougher to use than any previous version of Excel. However, despite its fairly steep learning curve, this marvelously rich program enables users of every stripe to turn data into information using tools to analyze, communicate, and share knowledge. Excel can help you to collaborate effectively, and protect and control access to your work. Power users can take advantage of industry-standard Extensible Markup Language (XML) data to connect to business processes. To unleash the power of the program and mine the full potential of their database talents, users need an authoritative and friendly resource. None is more authoritative or friendlier than "Excel 2003: The Missing Manual". Not only does the book provide exhaustive coverage of the basics, it provides numerous tips and tricks, as well as advanced data analysis, programming and Web interface knowledge that pros can adopt for their latest project. Neophytes will find everything they need to create professional spreadsheets and become confident users. "Excel 2003: The Missing Manual" covers: worksheet basics, formulas and functions, organizing worksheets, charts and graphics, advanced data analysis, sharing data with the rest of the world, and programming. If you buy just one book about using Excel, this has GOT to be it. This book has all you need to help you excel at Excel.

Product Details

• Amazon Sales Rank: #471946 in Books
• Published on: 2004-12-22
• Original language: English
• Number of items: 1
• Binding: Paperback
• 770 pages

About the Author
is President of ProseTech, a software documentation consultancy, and a project manager at VoiceIQ (http://www.voiceiq.com/), a provider of software for interactive voice-enabled applications and services. Matthew is a coauthor of the ASP.NET in a Nutshell (O'Reilly), and a contributor to the C# in a Nutshell (O'Reilly) API reference.

Excerpted from Excel the Missing Manual by Matthew MacDonald. Copyright © 2004. Reprinted by permission. All rights reserved.
Chapter 4 Formatting Worksheets

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

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

• Cell appearance. Cell appearance includes cosmetic details like color, typeface, alignment, and borders. When most people think of formatting, they think of cell appearance first.

• Cell values. Cell value formatting controls the way Excel displays numbers, dates, and times. For numbers, this includes details like whether to use scientific notation, the number of decimal places displayed, and the use of currency symbols, percent signs, and commas. With dates, cell value formatting determines what parts of the date are shown in the cell, and in what order.

Cell value formatting is in many ways more significant than cell appearance, because it can change the meaning of your data. For example, even though 45%, $0.45, and 0.450 are all the same number, your spreadsheet readers will see a failing test score, a bargain basement price for chewing gum, and a world-class batting average, respectively.

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

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

Formatting Cell Values
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 theworksheet, as shown in Figure 4-1. In many cases it makes sense to have the numbers that appear in your worksheet differ from Excel’s underlying values, since a worksheet that’s displaying numbers to, say, 13 decimal places can look pretty cluttered.

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

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

Note: Technically, a column contains two types of data: the values you’re storing within the actual cells and the column title in the topmost cell (where the text is). However, you don’t need to worry about unintentionally formatting the column title because number formats are only applied to numeric cells (cells that contain dates, times, or numbers). Excel doesn’t use the number format for the column title cell, as this cell contains text.

2. Select Format - Cells, or just right-click the selection, and choose Format Cells.
In either case, the Format Cells dialog box appears, as shown in Figure 4-2.

3. Set the format options, and then click OK to apply them.
The options in the Number tab let you choose how the Excel translates the cell value into a display value. (Number formatting choices are covered in the next section, "Formatting Numbers.") Most of the other tabs on this dialog box are for cell appearance formatting, which is covered later in this chapter.

Note: Once you apply formatting to a cell, it retains that formatting even if you clear the cell’s contents (by selecting it and pressing Delete). In addition, formatting follows a cell copy, so if you copy the content from cell A1 to cell A2, the formatting comes with it. Formatting includes both cell value formatting and cell appearance.

The only way to remove the formatting is to highlight the cell and select Edit - Clear - Formats. This command removes the formatting, restoring the cell to its original, General format (which you’ll learn more about below), but it doesn’t remove any of the cell’s content.

Formatting Numbers
The Number tab in the Format Cells dialog box lets you control how Excel displaysnumeric data in a cell. Excel gives you a lengthy list of predefined formats (as shown in Figure 4-3), and it also lets you design your own formats. Remember, Excel uses number formats when the cell contains numeric information only. Otherwise, Excel simply ignores the number format (although the format is still there and will still be used if you change the cell content to a number, date, or time).

When you create a new spreadsheet, every cell starts out with the same number format: General. This format comes with a couple of basic rules:

• If a number has any decimal places, Excel displays them, provided they fit in the column. If there are more decimal places than Excel can display, it leaves out the ones that don’t fit. (It rounds up the last displayed digit, when appropriate). If you change a column width, Excel automatically adjusts the amount of digits it displays.

• Excel removes leading and trailing zeros. Thus, 004.00 becomes 4. The only exception to this rule occurs with numbers between negative 1 and 1, which retain the 0 before the decimal point. For example, Excel displays the number .42 as 0.42.