Chapter 4. Formatting Worksheets
Creating a basic worksheet is only the first step toward mastering Excel. If you plan to print your worksheet, email it to colleagues, or show it off to friends, you need to think about whether you’ve formatted your worksheet in a viewer-friendly way. A careful use of color, shading, borders, and fonts can make the difference between a messy glob of data and a worksheet that’s easy to work with and understand.
But formatting isn’t just about deciding, say, where and how to make your text bold. Excel also lets you control the way numerical values are formatted. In fact, there are really two fundamental aspects of formatting in any worksheet:
Cell values. Cell value formatting controls the way Excel displays numbers, dates, and times. For numbers, cell formatting includes details like whether to use scientific notation, the number of decimal places Excel displays, and the use of currency symbols, percent signs, and commas. With dates, cell value formatting determines what parts of the date are shown in the cell, and in what order.
In many ways, cell value formatting is more significant than cell appearance, because cell value formatting can change the meaning of your data. For example, even though 45%, $0.45, and 0.450 are all the same number, your spreadsheet readers will see a failing test score, a cheap price for chewing gum, and a world-class batting average, respectively. Color and alignment can’t hope to compete.
Keep in mind that regardless of how you format your cell values, Excel maintains an unalterable value for every number you type in. For more on how Excel internally stores numbers, see the box on Section 18.104.22.168.
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 wildly from the numbers that it displays in the worksheet, as shown in Figure 4-1. In many cases, it makes sense to have the numbers that appear in your worksheet differ from Excel’s underlying values, since a worksheet that’s displaying numbers to, say, 13 decimal places, can look pretty cluttered.
To format a cell’s value, follow these steps:
Select the cells you want to format.
You can apply formatting to individual cells or to a collection of cells. Usually, you’ll want to format an entire column at once, because all the values in a column typically contain the same type of data. Remember, to select a column, you simply need to click the column header (the gray box at the top with the column letter).
Technically, your average numeric column contains two types of data: the values you’re storing within the actual cells and the column title in the topmost cell (where the title text is). However, you don’t need to worry about unintentionally formatting the column title because number formats are applied only to numeric cells (cells that contain dates, times, or numbers). Excel doesn’t use the number format for the column title cell because the column title cell contains text.
Select Format → Cells, or just right-click the selection and choose Format Cells.
In either case, the Format Cells dialog box appears, as shown in Figure 4-2.
Set the format options you want, and then click OK to apply them.
The options in the Number tab let you choose how Excel translates the cell value into a display value. (Number formatting choices are covered in the next section on Section 4.1.1.) The Alignment, Font, Border, and Patterns tabs are for cell appearance formatting, which is covered later in this chapter. (The Protection tab lets you hide formulas and lock your worksheet to prevent changes.)
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 is part of a cell copy, so if you copy cell A1 to cell A2, Excel copies the formatting right along with the content. 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 number format (which you’ll learn more about next), but doesn’t remove any of the cell’s content.
The Number tab in the Format Cells dialog box lets you control how Excel displays numeric data in a cell. Excel gives you a lengthy list of predefined formats (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; Excel will use it if you change the cell content to a number, date, or time).
A good way to learn about the different number formats is to choose a new number format (such as Currency) from the Format Cells dialog box’s Category list, as shown in Figure 4-3. When you do so, the Sample area of the dialog box shows how the number will be displayed if you apply that format. Excel also offers additional options you can use to customize the format. For example, if you choose the Currency format, Excel offers a list of currency symbols you can pick from—everything from dollars to kroner. To apply a format, just select the format and then click OK.
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 number 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 -1 and 1, which retain the 0 before the decimal point. For example, Excel displays the number .42 as 0.42.
As you saw in Chapter 2, the way you type in a number can change a cell’s formatting. For example, if you type in a number with a currency symbol, the number format of the cell changes automatically to Currency. Similarly, if you type in three numbers separated by dashes (-) or backward slashes (/), Excel assumes you’re typing in a date and adjusts the number format to Date.
It’s nice that Excel tries to be so helpful—but some things are best left up to people, not software. Rather than rely on this automatic process (especially when currency is involved), it’s far better just to type in ordinary numbers and set the formatting explicitly for the whole column. This approach prevents you from having different formatting in different cells (which can confuse even the sharpest spreadsheet reader), and it makes sure you get exactly the formatting and precision you want. You can apply formatting to the column before or after you type in the numbers. And it doesn’t matter if a cell is currently empty; Excel still keeps track of the number format you’ve applied.
Most of the spreadsheets you’ll create will probably contain numbers in some form or fashion. So the following sections give a quick tour of the predefined number formats available on the Number tab in the Format Cells dialog box. Figure 4-4 gives you an overview of how different number formats affect similar numbers.
The General format is Excel’s standard number format; it applies no special formatting other than the basic rules described at the beginning of this chapter. General is the only number format (other than Text) that doesn’t limit your data to a fixed number of decimal places. That means if you want to display numbers that differ wildly in precision (like 0.5, 12.334, and 0.120986398), you can do so using the General format. On the other hand, if your numbers have a similar degree of precision (for example, if you’re logging the number of hours of TV you watch each day), Number format makes more sense.
The Number format is like the General format, but with three refinements. First, the Number format uses a fixed number of decimal places (which you set). That means that the decimal point always lines up (assuming you’ve formatted an entire column). The Number format also lets you use commas as a separator between groups of three digits, which is handy if you’re working with really long numbers. Finally, you can choose to have negative numbers displayed with the negative sign, in parentheses, or in red lettering.
The Currency format closely matches the Number format, with two differences. First, you can choose a currency symbol (like the dollar sign, pound symbol, Euro symbol, and so on) from an extensive list; when you do, Excel automatically displays the currency symbol before the number. Second, the Currency format always includes commas. The Currency format also supports a fixed number of decimal places (chosen by you), and it lets you customize how negative numbers are displayed.
The Accounting format is modeled on the Currency format. It also lets you choose a currency symbol, uses commas, and has a fixed number of decimal places. The difference is that the Accounting format uses a slightly different alignment. The currency symbol always appears at the far left of the cell (away from the number), and Excel always adds an extra space to pad the right side of the cell. Also, the Accounting format always shows negative numbers in parentheses, which is an accounting standard. Finally, Excel never shows the number 0 when using the Accounting format; instead of a zero, it displays a dash (-).
There’s one trick to watch out for with the Percentage format. If you forget to start your number with a decimal, Excel quietly “corrects” your numbers. For example, if you type 4 into a cell that uses the Percentage format, Excel interprets the 4 as 4%. As a result, it actually stores the value 0.04. A side effect of this quirkiness is that if you want to store percentages larger than 100%, you can’t type them in as decimals. For example, to store 200%, you need to type in 200 (not 2.00).
The Fraction format displays your number as a fraction instead of a number with decimal places. The Fraction format doesn’t mean you have to type in the number as a fraction (although you can if you want by using the forward slash, like 3/4). Instead it means that Excel automatically converts any number you type in and displays it as a fraction. So, for example, to have 1/4 appear, you can either type in .25 or 1/4.
Lots of folks use the Fraction format for stock market quotes, but it’s also handy for certain types of measurements (like weights, temperatures, and recipes). When using the Fraction format, Excel does its best to calculate the closest fraction, which depends on a few factors, including whether an exact match exists (typing in .5 will always get you 1/2, for example) and what type of precision level you’ve picked when selecting the Fraction formatting.
You can choose to have fractions with three digits (for example, 100/200), two digits (10/20), or just one digit (1/2), using the top three choices in the Type list. For example, if you type in the number 0.51, Excel shows it as 1/2 in one-digit mode, and the more precise 51/100 in three-digit mode. In some cases, you may want all numbers to use the same denominator (the bottom number in the fraction) so that it’s easy to compare different numbers. In this case, you can choose to show all fractions as halves (with a denominator of 2), quarters (a denominator of 4), eighths (8), sixteenths (16), tenths (10), and hundredths (100). For example, the number 0.51 would be shown as 2/4 if you chose quarters.
You might feel like pulling your hair out in tufts when you’re trying to type a fraction into Excel, and Excel keeps converting your fraction to a date (both use slashes). To prevent this confusion, always start by typing 0 and then a space. For example, instead of typing 2/3, type in 0 2/3 (which means zero and two-thirds). If you have a whole number and a fraction, like 1 2/3, you’ll also be able to duck the date confusion.
The Scientific format displays numbers using scientific notation, which is ideal if you ever find yourself needing to handle numbers that range widely in size (like 0.0003 and 300) in the same column. Scientific notation displays the first non-zero digit of a number, followed by a fixed number of digits, and then indicates what power of 10 that number needs to be multiplied by to generate the original number. For example, 0.0003 becomes 3.00 10-4 (displayed in Excel as 3.00E-04). The number 300, on the other hand, becomes 3.00 102 (displayed in Excel as 3.00E02). Scientists—surprise, surprise—like the Scientific format for doing things like recording experimental data or creating mathematical models to predict when an incoming meteor will graze Earth. Most of the rest of us can safely skip the Scientific format.
Few people use the Text format for numbers, but it’s certainly possible to do so. The Text format simply displays a number as though it were text, although you can still perform calculations with it. Excel shows the number exactly as it’s stored internally, positioning it against the left edge of the column. You can get the same effect by placing an apostrophe before the number (although that approach won’t let you use the number in calculations).
Formatting Dates and Times
To format dates and times, in the Format Cells dialog box (Format → Cells), shown in Figure 4-5, choose Date or Time from the column on the left and then choose the format from the list on the right. Date and Time both provide a slew of options. You can use everything from compact styles like 3/13/05 to longer formats that include the day of the week, like Sunday, March 13, 2005. You can choose a format that modifies the date’s appearance depending on the regional settings of the computer viewing the Excel file (handy if you live in Baton Rouge and want your boss in Bangkok and your employees in Glasgow to view your worksheet), or you can choose a fixed date format. When using a fixed date format, you don’t have to stick to the U.S. standard. Instead, choose the locale (geographic location) you want from the Locale listbox. Each locale—from Albanian to Vietnamese—provides its own set of customized date formats.
Excel offers two types of date and time formats:
Formats that take the regional settings of the computer you’re using into account. With these formats, dates display differently depending on the computer that’s running Excel. This choice is a good one because it lets everyone see dates just the way they want to, no matter where in the world they are. No more time-consuming Brits-vs.-Yanks arguments about whether the “real” way to order a date is month-day-year or day-month-year.
Formats that ignore the regional settings of individual computers. These formats define a fixed pattern for month, day, year, and time components, and display date-related information in exactly the same way on all computers. If you need to make sure a date is in a certain format, no ifs, ands, or buts, you should use this choice.
The first type (date and time formats that rely on a computer’s regional settings) is the quickest to set. It includes just two date formats (a compact number-only format and a long, more descriptive format) and a single time format. You find these formats at the top of the Type list with an asterisk next to them.
Setting the second type (date and time formats that are independent of a computer’s regional settings) takes a bit more doing. To choose one of these formats, you first select a geographical region from the Locale list, and then you select the appropriate date or time format. Some examples of locales include “English (United States),” “English (United Kingdom),” and “Arabic(Iraq).”
If you type in a date without specifically formatting the cell, Excel usually uses the short, region-specific date format. That means that the order of the month and year vary depending on the regional settings of your computer (or, if it’s your bookie who’s looking at your worksheet, your bookie’s computer). But if you incorporate the month name (for example, January 1, 2005), instead of the month number (for example, 1/1/2005), Excel uses a compromise date format that includes a month abbreviation, like 1-Jan-2005.
You may remember from Chapter 2 that Excel stores a date internally as the cumulative number of days that have elapsed since a certain long-ago date that varies by operating system. If you’re curious, you can take a peek at this internal number using the Format Cells dialog box. First, type in your date. Then, format the cell using one of the number formats (like General or Number). The underlying date number appears in your worksheet where the date used to be.
Special Formats for Special Numbers
There are some types of numeric information that you wouldn’t ever want to perform mathematical operations with. For example, it’s hard to image a situation where you’d want to add or multiply phone numbers or Social Security numbers.
So when you’re typing in these kinds of numbers, you can format them as plain old text. For example, you could type in the text (555) 123-4567 to represent a phone number. Because there are parentheses and a dash (-), Excel won’t interpret this information as a number. Or, you could just precede your value with an apostrophe (') to explicitly tell Excel that it should be treated as text.
But whichever solution you choose, you’re potentially creating more work for yourself. Why? Because you have to type in the parentheses and the dash (or the apostrophe) for each phone number you type in. You also increase the likelihood of creating inconsistently formatted numbers, especially if you’re typing in a long list of them. For example, careful as you are, some phone numbers may end up in slightly similar but somewhat different formats, like 555-123-4567 and (555)1234567.
To avoid these problems, apply Excel’s Special number format, as shown in Figure 4-6, which converts numbers into common patterns. And lucky you: one of the Type options in the Special number format is Phone Number (other formats are for Zip codes and Social Security numbers).
After you apply the special Phone Number format to a cell, all you do is type in a series of digits, such as 5551234567; Excel automatically converts the digits to the proper phone number style—(555) 123-4567—without your having to type in all those pesky parentheses and dashes.
The Special format is a good idea, but it’s limited because Excel only provides a small set of special types that you can use. However, there’s no reason you can’t handle similar problems by creating your own custom formats, covered extensively in Excel: The Missing Manual.
Formatting Cell Appearance
Formatting cell values is important since it helps maintain consistency among your numbers. But to really make your spreadsheet readable, you’re probably going to want to enlist some of Excel’s tools for controlling things like alignment, color, and borders and shading.
To format the appearance of a cell, first select the single cell or group of cells that you want to work with, and then choose Format → Cells from the menu, or just right-click the selection and choose Format Cells. The Format Cells dialog box that appears (Figure 4-7, top) is the place where you adjust your settings.
Even a small amount of formatting can make a worksheet easier to interpret by drawing the viewer’s eye to important information. Of course, as with formatting a Word document or designing a Web page, a little goes a long way. Don’t feel the need to bury your worksheet in exotic colors and styles just because you can.
Alignment and Orientation
As you saw in the previous chapter, Excel automatically aligns cells according to the type of information you’ve typed in. But what if this alignment isn’t what you want? Fortunately, the Alignment tab in the Format Cells dialog box (Figure 4-7, top) lets you easily change alignment as well as control some other interesting settings, like the ability to rotate text.
Excel lets you control the position of content between a cell’s left and right borders with the following choices, some of which are shown in Figure 4-7, bottom:
General. General is the standard type of alignment; it aligns cells to the right if they hold numbers or dates and to the left if they hold text. General alignment is the type you learned about in Chapter 2.
Left (Indent). Left indicates that Excel should always line up content with the left edge of the cell. You can also choose an indent value to add some extra space between the content and the left border.
Center. Center indicates that Excel should always center content between the left and right edges of the cell.
Right (Indent). Right indicates that Excel should always line up content with the right edge of the cell. You can also choose an indent value to add some extra space between the content and the right border.
Fill. The Fill setting copies content multiple times across the width of the cell, which is almost never what you want.
Justify. Justify is the same as Left if the cell content fits on a single line. If you insert text that spans more than one line, Excel justifies every line except the last one, which means Excel adjusts the space between words to try and ensure that both the right and left edges line up.
Center Across Selection. This setting is a bit of an oddity. If you apply this option to a single cell, it has the same effect as Center. If you select more than one adjacent cell in a row (for example, cell A1, A2, A3), this option centers the value in the first cell so that it appears to be centered over the full width of all cells. However, this centering only happens as long as the other cells are blank. Because the Center Across Selection setting can lead (confusingly) to cell values displaying over cells that they aren’t stored in, try not to use it. Instead, use cell merging (as described on Section 4.2.1).
Distributed (Indent). This option creates the same result as Center if the cell contains a numeric value or a single word. If you add more than one word, Excel enlarges the spaces between words so that the text content fills the cell perfectly (from the left edge to the right edge).
Vertical alignment controls the position of content between a cell’s top and bottom border. Vertical alignment becomes important only if you enlarge a row’s height so that it becomes taller than the contents it contains. To change the height of a row, click the bottom edge of the row header (the numbered cell on the left side of the worksheet), and drag it up or down. As you resize the row, the content stays fixed at the bottom. The vertical alignment setting lets you adjust the cell content’s positioning.
Excel gives you the following vertical alignment choices, some of which are shown in Figure 4-7, bottom:
Top. Top indicates that the first line of text starts at the top of the cell.
Center. Center tells Excel to center the block of text between the top and bottom border of the cell.
Bottom. Bottom indicates that the last line of text ends at the bottom of the cell. If the text doesn’t fill the cell exactly, Excel adds some padding to the top.
Justify. Justify is the same as Top for a single line of text. If you have more than one line of text, Excel increases the spaces between each line so that the text fills the cell completely from the top edge to the bottom edge.
Distributed. This option is the same as Justify for multiple lines of text. If you have a single line of text, Distributed creates the same result as Center.
If you have a cell containing a large amount of text, you can increase the row’s height so you can display multiple lines. Unfortunately, you’ll notice that enlarging a cell doesn’t automatically cause the text to flow into multiple lines and fill the newly available space. But there’s a simple solution: just turn on the “Wrap text” checkbox (on the Alignment tab of the Format Cells dialog box). Now, long passages of text will flow across multiple lines. You can use this option in conjunction with the vertical alignment setting to control whether Excel centers a block of text or lines it up at the bottom or top of the cell. Another option is to explicitly split your text into lines. Whenever you want to insert a line break, just press Alt+Enter and start typing the new line.
After you’ve expanded a row, you can shrink it back by double-clicking the bottom edge of the row header. If you haven’t turned on text wrapping, double-clicking shrinks the row back to its standard single-line height.
Finally, the Alignment tab lets you rotate content in a cell up to 180 degrees, as shown in Figure 4-7. You can set the number of degrees you want your text to rotate in either of two ways: by dragging the “arm” that appears on the right side of the Alignment tab or by clicking the Degrees box. Rotating cell content automatically changes the size of the cell (see Figure 4-8). Usually, you’ll see it become narrower and taller to accommodate the rotated content.
Fonts and Color
As in almost any Windows program, you can customize text in Excel, applying a dazzling assortment of colors and fancy typefaces. You can do everything from enlarging headings to shrinking footnotes. Other settings you can change include:
The font size, in points. Out of the box, Excel uses point size 10, but you can choose anything from a minuscule 1-point to a monstrous 409-point. Excel automatically enlarges the row height to accommodate the font.
Various font attributes, like italics, underlining, and bold. Some fonts have complementary italic and bold typefaces, while others don’t (in which case Windows will use its own algorithm to embolden or italicize the font).
The font color. This option controls the color of the text. (The next section, “Borders and Patterns”) covers how to change the color of the entire cell.)
To change font settings, first highlight the cells you want to format, choose Format → Cells, and then click the Font tab (Figure 4-9). The Formatting toolbar also provides a number of shortcuts that let you quickly change certain font settings, including font, size, color, and attributes like boldface and italics. The Formatting toolbar sits in the row just under Excel’s main menu; you can find out more about it on Section 4.3.1. (Truth be told, the formatting toolbar is way more convenient for setting fonts, because its drop-down menu shows a long list of font names, whereas the font list in the Format Cells dialog box is limited to showing an impossibly restrictive four fonts at a time. Scrolling through that cramped space is more than a little maddening.)
Not all fonts are equal. When displaying data—especially numbers—sans-serif fonts are usually clearer and look more professional than serif fonts. (Serif fonts have little embellishments, like tiny curls, on the ends of the letters; sans-serif fonts don’t.) Arial, the default spreadsheet font, is a sans-serif font. The font used for the body text of this book, Adobe Minion, is clearly a serif font, which works best for large amounts of text.
No matter what font you apply, Excel, thankfully, always displays the cell contents in the eye-friendly Arial font in the Formula bar. That makes things easier if you happen to be working with cells that have been formatted to use graphically complex or large fonts.
Excel always assumes you want your worksheet font to be Arial unless you tell it otherwise. To change the font setting, select Tools → Options and then click the General tab. Next to the “Standard font” label are two drop-down menus where you can set the standard font and font size. The font you choose won’t apply to existing worksheets, but Excel will use it every time you create a new worksheet.
Most fonts contain not only digits and the common letters of the alphabet, but also some special symbols that you can’t type directly on your keyboard. One example is the copyright symbol ©, which you can insert into a cell by typing in the text (C) and letting AutoCorrect do its work. Other symbols, however, aren’t as readily available. One example is the special arrow character, →. To use this symbol, you’ll need the help of the Wingdings font.
Wingdings isn’t the only font with special characters (although it does have a really great selection). You can insert extended characters from any font by selecting the name of the font from the Symbol dialog’s drop-down list. (Extended characters are mostly non-English letters like Arabic or Hebrew letters.)
Wingdings is a special font included with Windows that’s made up entirely of symbols like arrows, smiley faces, and zodiac signs, none of which are found in standard fonts. You can try to apply the Wingdings font on your own, but it won’t be easy, because you won’t know which character to press on your keyboard to get the symbol you want. A better choice is to use Excel’s Symbol dialog box. Simply follow these steps:
Choose Insert → Symbol from the menu.
The Symbol dialog box opens, shown in Figure 4-10.
Choose the font that has the special character you want to insert.
In the Font box, scroll down and select Wingdings (if it’s not already selected). In addition, you can find a few predefined special characters, like the copyright symbol, on the Special Characters tab of the Symbol dialog box.
Select the character, and then click Insert.
Alternatively, if you need to insert multiple special characters, just double-click each one; doing so inserts each symbol right next to each other in the same cell without having to close the window.
When Excel inserts a character from the Symbol dialog box, it doesn’t change the font for the cell. What you’ll actually end up with is a cell that has two fonts—one for the symbol character and one for the rest of your text. This approach works perfectly well, but it can cause some confusion. For example, if you apply a new font to the cell after inserting a special character, Excel adjusts the entire contents of the cell to use the new font, and your symbol will change into the corresponding character in the new font (which usually isn’t what you want). These problems aren’t unique to using symbols; they can crop up any time you deal with a cell that has more than one font.
If you look at the cell contents in the Formula bar, you’ll always see the cell data in the standard Arial font. That means, for example, that your Wingdings symbol won’t appear as the icon that shows up in your worksheet. Instead, you’ll see an ordinary letter or some type of extended non-English character, like æ.
Borders and Patterns
The best way to call attention to important information isn’t to change fonts or alignment. Instead, place borders around key cells or groups of cells and use shading to highlight important columns and rows. Excel provides dozens of different ways to outline and highlight any selection of cells.
Removing a worksheet’s gridlines, as shown in Figure 4-11, lets you focus visually on any custom borders you’ve added (or are about to add). To remove a worksheet’s gridlines, select Tools → Options. From the Options dialog box that appears, select the View tab and then turn off the checkmark next to the Gridlines checkbox. (This grid-removing procedure affects only the current file and won’t apply to new spreadsheets.)
Once again, the trusty Format Cells dialog box is your control center. Just follow these steps:
Select the cells you want to fill or outline.
Your selected cells appear highlighted.
The Gridlines setting has no effect on whether or not Excel adds the worksheet gridlines to a printout. You can control whether your borders appear in printed versions of your worksheet through the Page Setup setting, as described on Section 22.214.171.124.
Select Format → Cells, or just right-click the selection and choose Format Cells.
The Format Cells dialog box appears.
(If you don’t want to apply any borders, skip straight to step 4.) Applying a border is a multistep process (see Figure 4-12). Begin by choosing the line style you want (dotted, dashed, thick, double, and so on), followed by the color (Automatic picks black). Both these options are on the right side of the tab. Next, choose where your border lines are going to appear. The Border box (where the word “Text” appears four times) functions as a nifty, interactive test canvas that shows you where your lines are going to appear. To make your selection, you can either click one of the eight Border buttons (which contain a single bold horizontal, vertical, or diagonal line), or you can click directly inside the Border box. If you change your mind, clicking a border line will make it disappear.
For example, if you want to apply a border to the top of your selection, click the top of the Border box. If you want to apply a line between columns inside the collection, click between the cell columns in the Border box. The line appears indicating your selection.Figure 4-12. Follow the numbered steps in this figure to choose the line style and color, and then apply the border.
The Border tab also provides two shortcuts in the Presets region of the tab. If you want to apply a border style around your entire selection, select Outline after choosing your border style and color. Choose Inside to apply the border between the rows and columns of your selection. Make a mistake? Select None to start over.
Here you can select the color and pattern of any shading you want to add to the cells in the selection (see Figure 4-13). Adding a color and pattern to selected cells is simpler than choosing borders. All you need to do is select the color you want and then, if you want, choose a pattern. You can tell Excel to draw the pattern in a different color (click Pattern, and then choose a color) and even tell it to include diagonal lines, a grid, dots, or the tight checker-board shown in Figure 4-13.
In general, patterns obscure text, and you shouldn’t apply them to cells that have content. Simple color fills tend to work better, provided you use light colors that will allow text or numbers to remain legible.
Click the No Color box to clear any current color or pattern in the selected cells.
If you don’t like the modifications you’ve just applied, you can roll back time with a quick application of the Edit → Undo command.
Smart Ways to Apply Formatting
You’ve now had a comprehensive tour of Excel’s formatting features. But, of course, just because the features are there doesn’t mean they’re easy to use. You can chew up hours digging through the different options and applying a full range of formatting choices to each and every cell. Fortunately, Excel also includes a few time-savers that let you speed up many formatting tasks. The final few sections of this chapter introduce these features. You’ll see how to skip the Format Cells dialog box with a few toolbar tricks, how to copy and standardize formatting with Styles and the Format Painter, and how to add a little built-in graphical intelligence to your worksheet with conditional formatting.
Shortcuts with the Toolbars
To control cell formatting, you need to jump between your worksheet and the Format Cells dialog box, which is time-consuming. But what if there were a way to apply basic formatting without jumping to a new window? In fact, Excel provides just such a handy shortcut with its Formatting toolbar.
The Formatting toolbar can’t duplicate every feature in the Format Cells dialog box. But it does do the next best thing: it lets you apply the most common types of formatting with one or two quick mouse clicks.
The Formatting toolbar usually appears at the top of the Excel window. If you’ve lost yours, just select View → Toolbars → Formatting from the menu.
Using the Formatting toolbar is similar to using the Format Cells dialog box. First, move to the cell you want to change, or select a group of cells. Then click the appropriate button in the toolbar to apply a new font, fill pattern, border, or cell value format. Figure 4-14 outlines your options.
But wait…there’s more! The Formatting toolbar also includes a few tools that aren’t available in the Format Cells dialog box: the ability to draw borders directly on the worksheet and a way to format individual characters. The next two sections show you how.
The Borders button on the Formatting toolbar, which lets you quickly apply basic borders to the current selection, is an ideal way to add a column divider or outline a group of cells with column headings. But what if you want to create more elaborate borders? There’s no need to head to the Format Cells dialog box, because Excel gives you the ability to draw cell borders directly onto your worksheet. It works like this:
Click the drop-down arrow next to the Borders icon in the Formatting toolbar. Choose the last option in the menu, Draw Borders.
The Borders toolbar appears automatically, with the Draw Border icon already selected (see Figure 4-15).
Choose a line style and color in the Borders toolbar.
The line and color options are similar to the Borders tab in the Format Cells dialog box.
To draw a longer border, drag your pointer down through a range of cells. If you drag your pointer down and to the side, you’ll create an external border around a block of cells. Or, if you want, you can choose to draw a border across a block of cells that includes the interior borders between cells by changing the drawing mode. Click the drop-down arrow next to the Draw Border icon on the Borders toolbar, and choose Draw Border Grid (instead of the standard option, Draw Border). Now, when you drag the mouse over a rectangular region of cells, you’ll add border lines to all the cells you drag across.
To stop drawing, deselect the Draw Border icon on the Borders toolbar.
Even after you stop drawing borders, the Borders toolbar remains, ready for use. If you’re finished with it, you can hide it by selecting View → Toolbars → Borders or by clicking the X on the toolbar’s right side.
Formatting individual characters
The Formatting toolbar lets you perform one task that isn’t possible with the Format Cells dialog box: applying formatting to just a part of a cell. For example, if a cell contains the text “New low price,” you could apply a new color or bold format to the word “low.”
To apply formatting to a portion of a cell, follow these steps:
You can also put a cell into edit mode by double-clicking it or by moving to it and clicking inside the text in the Formula bar.
Select the text you want to format.
You can select the text by highlighting it with the mouse or by holding down Shift while using the arrow keys to mark your selection.
Choose a font option from the toolbar.
Applying multiple types of text formatting to the same cell can get tricky. The Formula bar won’t show the difference, and, when you edit the cell, you may not end up typing text in the font you want. Also, be careful that you don’t apply new font formatting to the cell later; if you do, you’ll wipe out all the font information you’ve added to the cell.
Although AutoFormat doesn’t automatically take care of all the formatting you may need to do on a spreadsheet, it does take a basic, unformatted table of information and automatically apply a collection of borders, fills, and bold or italic formatting to highlight the table’s structure. Figure 4-16 shows some examples of AutoFormat at work.
To use AutoFormat, follow these steps:
Select the cells you want to AutoFormat.
In fact, you can move to any cell that’s within the cells you want to Auto-Format. AutoFormat can automatically detect which portion of a worksheet is filled with data—as long as there are no blank rows or columns within the data. If you have a table of data that includes blank rows or columns (for example, you’ve left a blank row between your heading and column titles), you’ll need to manually select the entire range of cells you want to AutoFormat before continuing.
If you haven’t already manually selected your cells, AutoFormat starts by selecting all the cells it thinks you want to format. Then the AutoFormat window (Figure 4-17) appears, displaying a list of preset format templates.Figure 4-17. Each format template includes settings for number formats, fonts, alignment, borders, fill patterns, row height, and column width. To turn off one or more of these settings, click the Options button. A group of checkboxes (“Formats to apply”) will appear at the bottom of the AutoFormat window, and you can clear the checkboxes corresponding to the format options you don’t want to apply.
Choose one of the format templates.
Each template presents a different collection of column, border, number, and text format settings. The templates have semi-descriptive names, like Accounting 1 and Classic 2. Click the template most closely matching the look you want. Unfortunately, you can’t create your own templates, but you can come pretty darn close. You can modify some of the settings in the existing templates, as shown in Figure 4-17—and you can also adjust the format settings after you apply them using the Format Cells dialog box.
The Format Painter
The Format Painter is a simple yet elegant tool that lets you copy all cell format settings—including fonts, colors, patterns, and borders—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:
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 copying from two cells is a powerful trick in the right circumstances, in most cases it’s easiest to copy the format from a single cell.
Click the Format Painter button on the Standard toolbar (Figure 4-18) to switch into “format painting” mode.Figure 4-18. Oddly enough, the Format Painter icon (a teensy paintbrush) appears on the Standard toolbar instead of the Formatting toolbar. To switch to format-painting mode, click the Format Painter icon. When you do, the pointer changes to include another 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 format to another cell, you have to backtrack to the cell that has your format and start over again. But here’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.
The Format Painter is a good tool for quickly copying formatting, but it’s no match for another Excel feature called Styles. With Styles, you can define a group of formatting settings and apply them wherever you need them. Best of all, if you change the style after you’ve created it, Excel automatically updates all cells that you’ve formatted using that style. Styles are described in the next section.
Styles let you create a customized combination of format settings, give it a name, and store it in a spreadsheet file. You can then apply these settings anywhere you need them.
Styles really shine in complex worksheets where you need to apply different formatting to different groups of cells. For example, say you’ve got a worksheet that’s tracking your company’s performance. You’re confident that most of the data is reliable, but there are a few rows that come from your notoriously overly optimistic sales department. To highlight these sales projections, you decide to use a combination of a bold font with a hot-pink fill. And since you’re pretty sure these figures aren’t highly precise, you decide to use a number format without decimal places and precede the number with a tilde (~), the universal symbol for “approximately right.”
You could implement all these changes manually. But that would take fourscore and seven years. Better to set up a style that includes all these settings and then apply it with a flick of the wrist whenever you need it. Styles are efficiency monsters in a few ways:
They free you from worry about being inconsistent, because the style includes all the formatting you want.
If you decide you need to change a style, all it takes a few mouse clicks. Then, Excel automatically adjusts every cell that uses your newly changed style.
What more could you want?
Creating a style
The quickest way to create a new style is to use formatting you’ve already set up. However, you can also create a new style from scratch. In this case, you would simply move to a blank, unformatted cell in your worksheet.
Select Format → Style.
This action opens the Style dialog box, which provides a drop-down list of styles. Usually, the Normal style will be selected in this list, unless you’ve already applied a different style to the current cell.
Type a name for your new style into the “Style name” listbox.
The text you type replaces the current selection. For example, if you want to create a new style for column titles, you can type the style name SassyColumn-Title. Each style name needs to be unique in your spreadsheet file. Figure 4-19 shows a new style being created.
Specify the settings you want your style to include.
Excel assumes you want your style to define a number format as well as alignment, font, border, pattern, and protection settings. If you don’t want your style to include all of these settings, turn off the appropriate checkboxes. For example, if you want to create a style that applies a new font, fill, and border, but you want to keep the existing alignment and number format, turn off the Number and Alignment checkboxes. As a general rule, if you don’t need to explicitly set a specific style characteristic, turn off the corresponding checkbox.
Click Add to create the new style.
Click Modify to specify the formatting options for the style.
When you click Modify, the familiar Format Cells dialog box appears. You can use this dialog box to change formatting just as if you were formatting an individual cell. Click OK to close the Format Cells dialog box when you’re finished.
Click OK to close the Style window.
Applying a style
Once you’ve created a style, applying it is just a matter of a few mouse clicks. Select the cell or cells you want to modify, choose Format → Style, choose your style from the list, and click OK. You’ll see the changes immediately.
Keep in mind that you can still modify the formatting of a cell after you’ve applied a style. But if you do find yourself overriding a style fairly frequently, and always in the same way, you probably need to modify the style. Either create more than one version of the same style, each with the appropriate settings, or clear some of the Style checkboxes so that your style won’t apply formatting settings that you commonly change.
Every worksheet also includes a basic set of default styles, which you can see in the Style window:
Normal is the standard style that Excel applies to all cells in a new worksheet. It sets the number format to General and the font to 10-point Arial (unless you’ve changed the standard font). It also turns off shading and borders and sets the vertical alignment to Bottom and the horizontal alignment to General.
Comma  changes the number format to Accounting, but doesn’t include any decimal places.
Currency, oddly enough, doesn’t use the Currency number format. Instead, it changes the number format to Accounting, set to two decimal places and using the currency symbol defined in your regional settings (which may well be $) at the beginning of the number. The only difference between this choice and the Currency number format is that Excels lines up currency signs at the left side of the cell and pads cell values with one space on the right.
Currency  changes the number format to Accounting, but doesn’t include any decimal places. It also uses the currency symbol (such as $) defined in your regional settings.
With the exception of Normal, these styles apply only a single formatting characteristic: a number format. You’ll notice that none of the other style checkboxes are turned on. You can modify all of these styles, and you can remove any of them except for Normal.
Once you’ve created a few useful styles, you’ll probably want to reuse them in a variety of spreadsheet files. To reuse them, you need to copy the style information from one workbook to another. Excel makes this process fairly straightforward:
Open both files in Excel.
You’ll need both the source workbook (the one that has the styles you want to copy) and the destination workbook (the one where you want to copy the styles).
Go to the destination workbook.
Choose Format → Style.
The Style dialog box appears.
Click the Merge button.
The Merge Styles dialog box appears with a list of all files that you currently have open in Excel.
Select the file that has the styles you want to copy into your active workbook, and click OK.
If there are any files that have the same name, Excel prompts you with a warning message, informing you that it will overwrite the current styles with the styles you’re importing. Click OK to continue.
Click OK to close the Style dialog box.
You can now use the styles that you’ve imported. These styles are now an independent copy of the styles in the source workbook. If you change the styles in one workbook, the other workbook won’t be affected unless you import the changed styles into it.
What if you want to flag extravagant expenses that top $100, or you want to flag a monthly sales total if it exceeds the previous month’s sales by 50%? Excel provides a feature for just this problem: conditional formatting.
With conditional formatting, you set a condition that, if true, prompts Excel to apply additional formatting to a cell. This new formatting can change the text color or use any other formatting trick you’ve seen in this chapter, including modifying fill colors, borders, and fonts. Usually, you use conditional formatting as a way to automatically highlight something important in a spreadsheet.
To apply conditional formatting, follow these steps:
Select the cell you want to apply the conditional formatting to.
You can apply conditional formatting to any cell or combination of cells.
Select Format → Conditional Formatting.
The Conditional Formatting dialog box appears. It lets you set up to three conditions for a cell. When the window first appears, it shows only a single condition, which is usually all you need.
Using the list and text boxes, set the condition that Excel should evaluate.
The first drop-down listbox lets you choose whether you want to evaluate a formula (“Formula Is”) or examine the cell value (“Cell Value Is”). The simplest option is to examine the cell value. (Formulas are covered in detail in Chapter 7).
Using the second drop-down listbox, choose the type of comparison you want to perform. You can choose to test whether the cell value equals a set number, is greater or less than a set number, or lies within some range of values (for example, the value is somewhere between 1 and 10).
Finally, type in the information you want Excel to use for the comparison. For example, if you choose to perform a less-than comparison, type in the value that Excel should compare against. If you choose to test whether a cell is between certain values, type in both values.
Figure 4-20 shows a completed Conditional Formatting dialog box.Figure 4-20. With conditional formatting, you set a condition that, if true, prompts Excel to apply additional formatting to a cell. In this example, the cell will contain italicized, bold text and will be surrounded by a black border—if the number in the cell is between 1 and 10.
Usually, conditional formatting compares a cell value to a fixed number or constant. However, you can also create conditions that compare the cell value to other cells in your worksheet. To take this step, select the text box where you’d type in the comparison number, and then click the worksheet to select the cell that Excel should use. Excel automatically inserts a cell reference (like $D$2 for cell D2) into the text box.
Click the Format button to set the formatting you want Excel to apply if the condition is true.
An abbreviated version of the Format Cells dialog box appears featuring the Font, Border, and Patterns tab. Other settings, like the number format, can’t be applied conditionally, and so they won’t appear. You also can’t conditionally change the font (the Font tab lets you control only the Font style). However, aside from these limitations, the tabs are the same as the ones you’re familiar with from the full-blown Format Cells dialog box (Figure 4-2).
Click OK to close the Format Cells dialog box.
In the Conditional Formatting window underneath your condition, you’ll see a preview of the formatting choices you made.
If you want to add a second or third condition, click Add and return to step 3.
Excel evaluates each condition separately and applies the appropriate formatting.
You can also click Delete to remove conditions. Excel will display a dialog box asking which conditions you want to remove. You can then place a checkmark next to the appropriate condition and click OK.
As soon as you click OK, Excel evaluates the conditions and adjusts the formatting as needed. Every time you open your spreadsheet or change the value in the conditional cell, Excel evaluates the condition.
Conditional formatting is like any other type of formatting in Excel. To remove conditional formatting, select the cell and choose Edit → Clear → Formats. You can also copy conditional formatting from one cell to another using the Format Painter (although you can’t make conditional formatting part of a style).