Section 5.3. Smart Ways to Apply Formatting | Excel 2007 for Starters. The Missing Manual

5.3. Smart Ways to Apply Formatting

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.

5.3.1. The Format Painter

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

To use the Format Painter, follow these steps:

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

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

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

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

Excel doesn't let you get too carried away with format 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.

Tip: 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 then 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.

5.3.2. Styles and Themes

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

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

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

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

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

Tip: Longtime Excel users will recognize the Number Format group of styles shown at the bottom of Figure 5-19. These styles are the only ones that Excel included in previous versions. The Number Format styles simply apply a different number format (Section 5.1.1), like Currency, Percentage, and so on. They don't change other appearance details. However, they're still useful. For example, you could use Currency style for all the dollar figures in your worksheet. Then, at some later point, you could modify that style to set a different currency symbol, alignment, or number of decimal places. This one change updates every cell that uses the Currency stylein this case, all the prices across your entire worksheet.

TIMESAVING TIP
Turning Off Live Preview

Most of the time, live preview is a great way for wishy-washy spreadsheet writers to see formatting possibilities without committing. However, if you're using a heavily formatted workbook, you might find the live preview feature slows you down when you're scrolling through a lot of options. In this case, it might make sense to turn off live preview.

To turn it off, choose Office button Excel Options, and then pick the Popular section. Under the "Top options for working with Excel, turn off the Enable Live Preview setting, and then click OK. Now you can zip around the ribbon, but you need to actually apply a formatting change (by clicking the appropriate button in the ribbon) before you see what it looks like.

5.3.2.1. Themes: A package of styles

As nice as the prebuilt styles are, they don't suit everyone. For example, the standard style colors favor subdued shades of red, gray, brown, and green, which make sense for the company accountant but aren't the most exciting choice for an urban hipster. To jazz things up, you can choose a different theme that features livelier colors. When you do, your entire worksheet gets an immediate faceliftyou don't need to track down each individual cell and reformat it.

Technically, a theme is a combination of three ingredients :

Fonts . Every theme has one font that's used for headings and another one that's used for everything else. These two fonts might be different sizes of the same typeface, or two complementary typefaces .
Colors . Every theme has a palette of 12 complementary colors. The cell styles that appear under the Themed Cell Styles heading (see Figure 5-19) draw upon these colors for text and background fills. Best of all, these colors don't reflect the preferences of Cheeto-munching programmers. Instead, bona fide artsy types chose themin this case, professional designers on the Microsoft payroll.
Effects . Effects are fine alterations that pretty up shapes and other hand-drawn graphics that you can create with Excel's drawing tools. If you don't have any shapes on your worksheet, the effect settings don't do anything.

To choose a theme, choose Page Layout Themes Themes to see a gallery of choices (Figure 5-20).

Figure 5-20. Every workbook begins using the crowd -pleasing Office theme, but you have a long list of other options. You can even search Microsoft's Office Online Web site for hot new themes. As you hover over a new theme, your workbook adjusts itself automatically, thanks to the magic of Excel's live preview feature.

The secret to understanding themes is realizing how changing the theme affects your worksheet. In other words, how does Excel apply the theme's fonts, colors, and effects to your worksheet? The following sections break it down.

Fonts . Every workbook has a standard body font that it uses in every cell. Excel uses this standard font unless you explicitly choose a different font using the ribbon's Home Font section or the Format Cells dialog box.

In a brand new Excel spreadsheet, everything you type starts out in easy-on-the-eyes 11-point Calibri font. If you apply a new theme, you get a new standard font. For example, switch to the traditionally styled Apex theme, and you'll get the elegant Book Antiqua font instead.

Note: All the fonts used in Excel themes are installed as part of Microsoft Office.

The same sort of magic works with the heading font , but it's more limitedin fact, the only style that uses the heading font is Title. If you use the Title cell style and switch from one theme to another, Excel updates your cell to use the heading font from that style. In some themes, the heading font is the same as the standard body font (as it is for the standard Office theme). In other themes, these two fonts are different, but complementary. For example, the Apex font uses the stylish Lucida Sans font for all titles.

Note: You might assume that the heading styles (Heading 1, Heading 2, Heading 3, and so on) use the heading font. Oddly enough, that's not how it works. All the heading styles use the body font. Title is the only style that uses the heading font.

If you're feeling a bit reckless, you can override the default font that Excel uses for all new workbooks. To override it, select Office button Excel Options, and then choose the Popular section. Under the "When creating new workbooks heading is a "Use this font" and a "Font size" setting where you can set the standard font and font size. Ordinarily, the default font isn't set to a specific font at allinstead, it's set to the special value "Body Font." This tells Excel to apply the standard font from the current theme. Usually, this is the choice you want because it lets you quickly adapt your entire spreadsheet to a theme of your choosing.

Colors . Every theme relies on 12 key colors. When you move from one theme to another, Excel swaps in the new set of colors. Excel alters any place where you've used the 12 theme colors. However, other colors aren't affected.

Note: Although there are only 12 base colors in a theme, Excel varies the saturation of the color to make it bolder or lighter, based on the style you use. For example, the Office theme includes a steel blue color that you can use at full strength (with the style named Accent 1), or lighten to a faint grayblue mist (with the style named 20% - Accent 1).

To make this system a bit clearer, imagine a designer runs amok, formatting cells with different background fills. Some of these cells are filled with theme colors, while others are filled with custom colors. (Figure 5-21 shows the difference.) When you switch themes, the cells that have the theme colors are changed, while the other cells aren't.

Figure 5-21. When you set the background fill in a cell, you can pick one of the theme colors (at different saturations), you can use a standard color (which gives you the standard red-green-blue lineup), or you can click More Colors to pick a custom color of your own design. You have the same choices when picking the foreground color for your text.

Tip: You're always better off using the theme colors rather than picking a new custom color. That way you can give your workbook a facelift by switching from one theme to another, and the colors still match. On the other hand, if you choose custom colors that look nice with a specific theme, they're likely to clash horribly when you change to another theme.

Experienced Excel workers rarely waste time picking background and foreground colors out of the ribbon. Instead, they use styles. Any time you use one of the styles from the Themed Cell Styles category (Home Styles Cell Styles; see Figure 5-19), youre applying a theme-specific color. As a result, if you pick another theme, all the themed cell styles change to use the new color.

The theme system works well because each color in a theme plays a specific role. In other words, some colors are intended for text, while others are designed to play the role of a complementary background; a few more add eye-catching accents. To see the intended purpose of each color, hover over it in the ribbon (Figure 5-22).

Figure 5-22. Here, the mouse is over one of the six accent colors that are present in every theme. You can also choose from four colors that are intended for standard text and background fills (on the left).

Effects . Effects are the simplest part of any theme because Excel applies them with no work on your part (all you need to do is switch themes, and then the Effects kick in). Excel automatically applies effects to any graphics you've created.