Using Template Files to Store Formatting
WHEN creating a worksheet in Microsoft Excel 2010, you probably don’t ask yourself the question, why use formats? But we’ll answer it anyway. Compare Figure 9-1 to Figure 9-2, and we need say no more. Although the data is the same in both worksheets, the worksheet in Figure 9-2 takes advantage of the formatting features available in Excel 2010, and as you can see, it’s much easier to read and interpret. In this chapter, you’ll learn how to apply basic formatting to help turn your data into information. We also discuss advanced formatting features such as themes, cell styles, and conditional formatting.
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 the important stuff, adding visual cues and clues, and enhancing overall readability and organization. Be careful, though—over formatting can be as distracting as none at all. Usually, the goal of a good worksheet is to call attention to the right information, not to showcase Excel’s formatting features (or your mastery of them).
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 can 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.
Here are some fundamental rules of formatting in Excel:
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 do some reformatting no matter what, so give yourself the freedom to rearrange until the layout becomes clear for your purposes.
The concept of “tables” in Excel took on fresh meaning with the 2007 release, and it continues in the 2010 release. Tables are 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 gallery, shown in Figure 9-4, applies predefined combinations of these formatting criteria.
Note
When you use Format As Table, you apply more than just formatting—you are actually transforming a region of your worksheet into a kind of 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 22, covers tables in more detail. You’ll want to check out that chapter.
Figure 9-4. The Format As Table gallery offers a selection of predefined formats you can apply with one click.
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 from the gallery. When you do, 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 gallery.
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, and 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 Table Style Light 4 format. Figure 9-6 shows the result.
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 define a table using the Format As Table command, you can then use the Format As Table gallery to preview other predefined formats. (It doesn’t work on raw data.) Rest the pointer on any format in the gallery, and the associated formatting is temporarily reflected in the table you have already created, but it 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.
The Design tab contains formatting commands in the Table Style Options and Table Styles groups. The latter group contains the same gallery as 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 Total Row check box adds a double border at the bottom of the table and adds another row containing summary formulas. If you add the summary row, 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.
Note
For more information, see Formatting Conditionally on page 309 and Creating Conditional Formatting Rules on page 315.
You can insert and delete rows in a table, even at the edges, and the table automatically does 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 cell in the lower-right 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.
When you right-click an item in the Format As Table gallery, 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 gallery item.
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 format attributes previously applied to the selected cells. This is handy if you have special number formats or conditional formats you want to preserve.
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 gallery.
If the built-in gallery 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 gallery, 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 included in the Table Element list. 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 gallery in the Custom category.
If you like the formatting you get from tables but don’t want or need the special features of a table, use the Convert To Range button in the Tools group on the Table Tools Design tab. When you do so, all the special features applied to the selected table are removed, leaving most of the formatting intact. (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.
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 applied remains (including the totals row), but the automatic table functionality disappears. Even the banding formats remain, but they are no longer dynamic or automatic. Converting your table in this way makes sense if you don’t want to deal with the automatic accoutrements such as Filter menus. This technique is a great way to take advantage of automatic table formatting features for cell ranges that you just want to format uniformly.
One of the most useful tools on your formatting tool belt is the Format Painter button. The Clipboard group on the Home tab 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, 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, 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 Home tab of 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 220.