Formatting Fundamentals

Worksheet editing involves creating and modifying the content, layout, and organization of data, while worksheet formatting deals with the appearance and readability of that data. With formatting, you can take mind-numbing detail and turn it into information by highlighting important data, adding visual clues, and enhancing overall readability and organization.

Formatting in Excel is easy: Select the cell or range, and use the appropriate buttons and commands on the Ribbon to apply formatting. Many of the most often used formatting features appear on the Home tab on the Ribbon for quick access, as shown in Figure 9-3. In fact, formatting commands dominate the Home tab; all seven of its Ribbon groups include formatting commands (even the Editing group). Figure 9-3 also shows the Format Cells dialog box, which you access by clicking the Dialog Box Launcher in the Font, Alignment, or Number group on the Home tab on the Ribbon. (The Dialog Box Launcher is the small arrow icon to the right of the title in many Ribbon groups.)

Figure 9-3: The Home tab on the Ribbon and the Format Cells dialog box are your formatting toolboxes.

Note 

To quickly access the Format Cells dialog box, press Ctrl+1.

Here are some fundamental rules of formatting in Excel:

• A formatted cell remains formatted until you remove the format or apply a new format.

• When you overwrite or edit a cell entry, you need not reformat the cell.

• When you copy or cut a cell, the formats applied to that cell travel with it.

Note 

Build and edit the worksheet first; apply formatting later. Sometimes, the least efficient step you can take is to apply your formatting too soon. Applying, removing, and then reapplying formatting is at least three times the work. Trust us, you'll be doing some reformatting no matter what, so give yourself the freedom to rearrange until the layout becomes clear for your purposes.

Formatting Tables

In Excel 2007, the concept of a table takes on fresh meaning. Tables are now special objects in Excel that include many features beyond formatting, but you can use the Format As Table button on the Home tab to apply specific font, border, and fill attributes to all the elements of a table at once. The Format As Table palette, shown in Figure 9-4, applies predefined combinations of these formatting criteria.

Figure 9-4: The Format As Table palette offers a selection of predefined formats you can apply with one click.

Note 

When you use Format As Table, you apply more than just formatting-you are actually transforming a region of your worksheet into kind of a self-contained entity that not only adds Filters to all column headings but also has special properties that offer extreme editing while preserving the integrity and format of the table structure. Chapter 21, "Managing Information in Tables," covers tables in more detail. You'll want to check out that chapter.

You can apply the Format As Table command to any region of cells (that is, a contiguous block of cells on a worksheet). You select a cell anywhere within the region, click Format As Table, and then select one of the sample table formats in the palette. When you do so, Excel displays the Format As Table dialog box, which lets you adjust the selection, as shown in Figure 9-5.

Figure 9-5: The Format As Table dialog box appears after you select a format in the Format As Table palette.

If your table includes headers (as most do), select the My Table Has Headers check box in the Format As Table dialog box. Excel then selects the entire table automatically and applies the selected table format to it.

Here are a few tips to keep in mind when using Format As Table:

• If you don't like the way something looks, click the Undo button on the Quick Access Toolbar (or press Ctrl+Z).

• The boundaries of a table are defined by blank rows and columns or the edges of the worksheet. Try adding blank columns or rows around your table to effectively fence off areas you don't want Format As Table to touch.

• Select more than one cell before issuing the command so Format As Table affects only the selected cells.

Although Format As Table does a pretty good job with simple tables, you usually need to make a few adjustments afterward. For example, starting with the raw data shown in Figure 9-1, we applied the Dark 8 table format. Figure 9-6 shows the result.

Figure 9-6: In seconds, you can transform a raw worksheet into something more presentable.

As you can see in Figure 9-6, the title and subtitle in cells A1 and A2 were not part of the table, and therefore were not formatted, so we applied additional formatting manually to arrive at the result shown in Figure 9-2. In addition, we applied number formatting to the cells containing data. Nonetheless, using Format As Table speeds up the formatting process and provides at least one formatting feature that is otherwise unavailable: automatic row and column banding, which was one attribute of the automatic format we applied in Figure 9-6. Another cool part of using Format As Table is the automatic preview feature. After you have defined a table using the Format As Table command, you can then use the Format As Table palette to preview other predefined formats. Rest the pointer on any format in the palette, and the associated formatting is temporarily reflected in the table you have already created but is not actually applied unless you click.

After you create a table, a context-triggered tab appears on the Ribbon only when you select a cell or cells within the table. Figure 9-7 shows the Table Tools Design tab.

Figure 9-7: The Table Tools Design tab appears on the Ribbon whenever you select a cell in a table.

The Design tab contains formatting commands in the Table Style Options and Table Styles groups. The latter group contains the same palette from the Format As Table command on the Home tab. In Figure 9-7, we selected both the First Column and Last Column check boxes in the Table Style Options group, which, in this particular predefined format, applied bold formatting to the fonts in those columns. You can select and clear check boxes in this group and view the changes immediately. The Header Row check box actually adds or removes the header row from the table. The Totals Row check box adds a double border at the bottom of the table and adds another row containing summary formulas. If you do so, you can select which summary function you want to use by clicking the summary formula in the totals row and then clicking the menu arrow that appears. The menu offers a selection of functions including Sum (the default), Average, Max, and Min, or you can select More Functions to display the Insert Function dialog box.

TROUBLESHOOTING 

Did your Design tab disappear?

The Table Tools Design tab appears only when you select a cell that is part of a table. When you select any cell outside the table, this context-triggered tab disappears, and the Home tab is activated.

The two "Banded" check boxes on the Table Tools Design tab-Banded Rows and Banded Columns-are useful. In large worksheets, row banding often makes it easier to track long rows of data across a screen or printed page. In previous versions of Excel, banding required you to construct an esoteric conditional formatting formula using the MOD function. Banding is now easier than ever to apply in Excel thanks to these two options, and unlike the old MOD function approach, these table banding options are smart enough to survive just about any kind of editing, including inserting and deleting rows and columns.

For more information, see "Formatting Conditionally" on page 284 and "Creating Conditional Formatting Formulas" on page 294.

You can insert and delete rows in a table, even at the edges, and the table will automatically do the right thing with formats and formulas. Another great feature of tables is that you can make them bigger just by dragging. As Figure 9-8 shows, the lower-rightmost cell in the lower-rightmost corner of the table contains a small triangular indicator (similar to a cell comment indicator) that you can drag horizontally or vertically to increase (or decrease) the size of the table.

Figure 9-8: Drag the lower-right corner of a table to make it bigger.

Options for Applying Table Formats

When you right-click an item in the Format As Table palette, you'll find a few more tools you can employ when you are working with table formats. Figure 9-9 shows the shortcut menu that appears when you right-click a palette item.

Figure 9-9: Right-click any table format thumbnail to reveal a shortcut menu containing helpful options.

The following are the options:

• Apply And Clear Formatting sounds backward, because what really happens is that Excel removes the existing formatting first before applying the selected table format.

• Apply (And Maintain Formatting) gives you the option of using the selected table format without disturbing any existing format attributes previously applied to the selected cells.

• Duplicate copies the selected table format; opens the Modify Table Quick Style dialog box (which is identical to the New Table Quick Style dialog box shown in Figure 9-10), letting you make modifications; and places the resulting format in the Custom category at the top of the Format As Table palette.

Figure 9-10: Click the New Table Style button on the Format As Table palette to create your own table styles.

Creating Custom Table Formats

If the built-in palette of table styles doesn't do it for you, you can create your own. To do so, click the Format As Table button on the Home tab, scroll to the bottom of the palette, and click New Table Style to display the New Table Quick Style dialog box shown in Figure 9-10.

In the New Table Quick Style dialog box, you can assign font, border, and fill formats to each item listed in the Table Element box. Select the element you want to format, and click the Format button to display the Format Cells dialog box shown on the right in Figure 9-10. You can click the Clear button to remove the formatting from a selected element; select the Set As Default Table Quick Style For This Document check box to make yours the go-to style whenever you create tables in the current workbook. After you finish specifying formats and click OK, your custom style appears at the top of the Format As Table palette in the Custom category.

Removing the Automatic Table Features

You can use the Convert To Range button in the Tools group on the Table Tools Design tab to remove all the special features applied to a selected table without removing the formatting. (Remember, you need to click a cell in the table to display the Table Tools Design tab.) Figure 9-11 shows the message that appears when you click Convert To Range.

Figure 9-11: The Convert To Range command removes the automatic table features but leaves the formatting.

Convert To Range removes the Filter menus from the selected table, discontinues displaying the Table Tools Design tab, turns off any additional table features such as automatic cell banding and totals, and relegates the range formerly known as Table to simple "formatted cells" status. Any visible table formatting you have already applied remains (including the totals row), but the automatic table functionality disappears. Converting your table in this way makes sense if you don't want to deal with the automatic accoutrements such as Filter menus. It's a great way to take advantage of the automatic table creation features just to apply formatting.

Painting Formats

One of the most useful tools on your formatting tool belt is the Format Painter button. The Clipboard group on the Home tab on the Ribbon is home to the Format Painter button, which looks like a little paintbrush. Select the cell or range from which you want to copy formatting, and click the Format Painter button. (A small paintbrush icon appears next to the pointer.) Then select the cell or drag through the range of cells to which you want to copy the formatting. It's that simple.

If you copy formats from a range of cells and then select a single cell when you paste, the Format Painter selects and formats the same size range, from the selected cell down and to the right. However, if you select a range of cells when you paste formats, the Format Painter limits the pasted formats to the shape of the destination range you select. If the range you want to format is a different shape from the copied range, the pattern is repeated or truncated as necessary.

Note 

To remove all formatting, select a cell or range, click the Clear menu (located in the Editing group on the Ribbon), and click Clear Formats. To remove the values as well as the formatting in selected cells, click Clear All on the menu. For more information, see "Clearing Cells" on page 202.