Section 4.3. Smart Ways to Apply Formatting | Excel 2010: The Missing Manual

4.3. 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. Digging through the different options, and applying a full range of formatting choices can be a tedious task. Fortunately, Excel also includes a few timesavers that let you speed up many formatting tasks . The final few sections of this chapter introduce these features. You'll see how to skip the Format Cells dialog box with a few toolbar tricks, how to copy and standardize formatting with Styles and the Format Painter, and how to add a little built-in graphical intelligence to your worksheet with conditional formatting.

GEM IN THE ROUGH
Your Favorite Colors

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

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

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

4.3.1. Shortcuts with the Toolbars

In order to control cell formatting, you need to jump between your worksheet and the Format Cells dialog box, which can be time-consuming . But what if there were a way to apply basic formatting without jumping to a new window? In fact, Excel provides a handy shortcut with its Formatting toolbar.

The Formatting toolbar can't duplicate every feature in the Format Cells dialog box. However, it does let you apply the most common types of formatting with one or two quick clicks of the mouse.

Note: The Formatting toolbar is usually displayed at the top of the Excel window. If you've lost yours, just select View

Toolbars

Formatting from the menu.

Figure 4-15. The Formatting toolbar provides one-stop shopping for a number of formatting options. When you use the font list, you'll even see the name of the font displayed in its proper typeface (so the font entry "Times New Roman" is displayed using Times New Roman, which gives you a helpful preview).

STYLISH FRILLS A Designer Worksheet

Cells aren't the only part of a worksheet you can tweak. A little known feature in Excel lets you change the appearance of the entire worksheet by applying a custom picture as a background. Just select Format Sheet Background, and choose a picture file. The picture can be in just about any format, including .GIF, .JPEG, or .TIF, and Excel will spread it like tiles across your worksheet surface to fill the whole working area. To try it out, use some of the background pictures located in your Windows directory (these files usually have names like Feather Texture and Sante Fe Stucco).

The designer background feature is really just for fun and it won't be included in the printout. To remove a background, just choose Format Sheet Delete Background.

4.3.1.1 Drawing borders

The Borders button on the Formatting toolbar lets you quickly apply basic borders to the current selection. This 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 if you don't want to, 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-16).

Figure 4-16. The Borders toolbar looks humble enough, but it packs a lot of power. Rather than allowing you to apply only preset border styles to the current selection, the Borders toolbar lets you draw directly onto your worksheet. To activate drawing mode, make sure you've selected the Draw Border icon on the left side of the toolbar. You can also choose a line style and color.

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.
Begin drawing your borders by single clicking on the lines between cells.

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. Alternatively, you can choose to draw a border across a block of 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.

If you want to erase a border, click the Erase icon on the Borders toolbar. The pointer changes to an eraser and you can click the border you want to remove.
To stop drawing, deselect the Draw Border icon on the Borders toolbar.

Even once 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 click the X on the toolbar's right side.

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

Move to the appropriate cell, and put it into edit mode by pressing F2.

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.

You can change the size , the font, the color, or the bold, italic, or underline settings.

Note: 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 might not end up entering 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.

4.3.2. AutoFormat

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-17 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 AutoFormat. AutoFormat can automatically detect which portion of a worksheet is filled with dataas 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 you're heading and column titles), you'll need to manually select the entire range of cells you want to AutoFormat before continuing.
Select Format AutoFormat from the menu.

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 appears, displaying a list of preset format templates.

Figure 4-17. This worksheet shows the before and after effects of using Autoformat. The first table of data shows the data before it's been formatted; the other three tables have been transformed using a variety of different AutoFormat templates.

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 modify some of the settings in the existing templates, as shown in Figure 4-18. You can also adjust the format settings after you apply them using the Format Cells dialog box.
Click OK.

Excel applies the AutoFormat template to your data.

Note: AutoFormat works best if your data is arranged in a typical table layout formation, including column headings. If you have data that isn't arranged in a table, you're better off using another tool, like styles or the Format Painter, which are described in the following sections.

4.3.3. The Format Painter

The Format Painter is a simple yet elegant tool that lets you copy all cell format settingsincluding fonts, colors, patterns, and bordersfrom 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.)

Figure 4-18. Each format template includes settings for number formats, fonts, alignment, borders, fill patterns, row height, and column width. Click the Options button if you want your chosen template to not include any of these individual settings. A new group of checkboxes 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.

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.
Click the Format Painter button on the Standard toolbar (Figure 4-19), to switch into "format painting" mode.

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

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

Figure 4-19. Oddly enough, the Format Painter appears on the Standard toolbar instead of the Formatting toolbar. You can recognize the icon because it looks like a paintbrush.

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

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

4.3.4. Using Styles

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 these figures are estimated and 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'll 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 let you reuse your formatting easily, just by applying the style.
They free you from worry about being inconsistent, because the style includes all the formatting you want.
Excel automatically saves styles with your spreadsheet file, and you can transfer styles from one workbook to another.
If you decide you need to change a style, it requires just a few mouse clicks. Then, Excel automatically adjusts every cell that uses your style.

What more could you want?

4.3.4.1 Creating a style

Here's how it works.

Begin by moving to a cell in your worksheet that has the formatting you want to use for your style.

The quickest way to create a new style is by using 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" list box.

The text you type replaces the current selection. For example, if you want to create a new style for column titles, you might enter the style name ColumnTitle. Each style name needs to be unique in your spreadsheet file. Figure 4-20 shows a new style being created.
Click Add to create the new style.

Your style now exists and is based on the formatting settings of the current cell. The next step explains how to modify these settings.
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.

You can return to the Style window at any time to modify existing styles, or delete them, using the Modify and Delete buttons .

Figure 4-20. Here, a new style, WildAndCrazySalesPeople, is about to be created. This style defines a number format as well as alignment, font, border, and pattern settings. If you don't want your style to include any of these settings, turn off the checkmark in 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 checkmark.

4.3.4.2 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. These styles are:

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, set to two decimal places. The Accounting format always uses a comma to separate each group of three digits (for example: 385,789).
Comma [0] 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 (such as $) 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 [0] changes the number format to Accounting, but doesn't include any decimal places. It also uses a currency symbol (such as $).
Percent changes the number format to Percent and doesn't include any decimal places.

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.

4.3.4.3 Transferring styles

Once you've created a few useful styles, you'll probably want to reuse them in a variety of spreadsheet files. In order to do this, 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.

4.3.5. Conditional Formatting

Earlier in this chapter, you learned how to create custom format strings. Excel allows you to create up to three different format strings for the same cell. For example, you can define a format string for positive numbers, a format string for negative numbers, and another format string for zero values. Using this technique, you could create a worksheet that automatically highlights negative numbers in red lettering while leaving non-negative numbers in black. This trick saves you the trouble of having to manually find the offending cells and apply a different font color.

This ability to treat negative numbers differently from positive numbers is quite handy, but it's obviously limited. For example, 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%? Custom format strings can't help you there, but Excel does provide another feature to fill in the gap: 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, conditional formatting is used 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 list box allows you to 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 starting in Chapter 7).

Using the second drop-down list box, 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.
Finally, enter the information that Excel should 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, enter both values in this range.

Figure 4-21 shows a completed Conditional Formatting dialog box.

Figure 4-21. In this example, the cell will contain italicized, bold text and will be surrounded by a black borderif the number in the cell is between 1 and 10.

Tip: 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 enter 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 that Excel should 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 only lets you control the Font style ). However, aside from these limitations, the tabs are exactly the same as the ones you're familiar with from the full-blown Format Cells dialog box (Figure 4-2).

Note: In the Conditional Formatting dialog box, all "between" comparisons are inclusive, which means the condition will be true if you enter the number 1, 10, or anything in between. On the other hand, if you enter 0.99, Excel won't apply the conditional formatting.
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.
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.

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