Chapter 6: Worksheet Formatting

Using Colors and Shading

Excel provides the tools to create some very colorful worksheets. You can change the color of the text or add colors to the backgrounds of the worksheet cells.

Note

Prior to Excel 2007, workbooks were limited to a palette of 56 colors. Excel 2010 allows a virtually unlimited number of colors. n

You control the color of the cell’s text by choosing Home Font Font Color. Control the cell’s background color by choosing Home Font Fill Color. Both of these color controls are also available on the Mini toolbar, which appears when you right-click a cell or range.

Tip

To hide the contents of a cell, make the background color the same as the font text color. The cell contents are still visible in the Formula bar when you select the cell. Keep in mind, however, that some printers may override this setting, and the text may be visible when printed. n

Even though you have access to an unlimited number of colors, you might want to stick with the ten theme colors (and their light/dark variations) displayed in the various color selection controls. In other words, avoid using the More Color option, which lets you select a color. Why? First of all, those ten colors were chosen because they “go together” (well, at least somebody thought they did). Another reason involves document themes. If you switch to a different document theme for your workbook, nontheme colors aren’t changed. In some cases, the result may be less than pleasing, aesthetically. See “Understanding Document Themes,” later in this chapter, for more information about themes.

Using Colors with Table Styles

In Chapter 5, I discuss the handy Table feature. One advantage to using tables is that it’s very easy to apply table styles. You can change the look of your table with a single mouse click.

It’s important to understand how table styles work with existing formatting. A simple rule is that applying a style to a table doesn’t override existing formatting. For example, assume that you have a range of data that uses yellow as the background color for the cells. When you convert that range to a table (by choosing Insert Tables Table), the default table style (alternating row colors) isn’t visible. Rather, the table will display the previously applied yellow background.

To make table styles visible with this table, you need to remove the manually applied background cell colors. Select the entire table and then choose Home Font Fill Color No Fill.

You can apply any type of formatting to a table, and that formatting will override the table style formatting. For example, you may want to make a particular cell stand out by using a different fill color.

127

Part I: Getting Started with Excel

Adding Borders and Lines

Borders (and lines within the borders) are another visual enhancement that you can add around groups of cells. Borders are often used to group a range of similar cells or to delineate rows or columns. Excel offers 13 preset styles of borders, as you can see in the Home Font Borders dropdown list shown in Figure 6.9. This control works with the selected cell or range and enables you to specify which, if any, border style to use for each border of the selection.

FIGURE 6.9

Use the Borders drop-down list to add lines around worksheet cells.

You may prefer to draw borders rather than select a preset border style. To do so, use the Draw Border or Draw Border Grid command from the Home Font Borders drop-down list. Selecting either command lets you create borders by dragging your mouse. Use the Line Color or Line Style commands to change the color or style. When you’re finished drawing borders, press Esc to cancel the border-drawing mode.

128

Chapter 6: Worksheet Formatting

Another way to apply borders is to use the Border tab of the Format Cells dialog box, which is shown in Figure 6.10. One way to display this dialog box is to select More Borders from the Borders drop-down list.

FIGURE 6.10

Use the Border tab of the Format Cells dialog box for more control over cell borders.

Before you display the Format Cells dialog box, select the cell or range to which you want to add borders. First, choose a line style and then choose the border position for the line style by clicking one of the Border icons (these icons are toggles).

Notice that the Border tab has three preset icons, which can save you some clicking. If you want to remove all borders from the selection, click None. To put an outline around the selection, click Outline. To put borders inside the selection, click Inside.

Excel displays the selected border style in the dialog box; there is no live preview. You can choose different styles for different border positions; you can also choose a color for the border. Using this dialog box may require some experimentation, but you’ll get the hang of it.

When you apply diagonal lines to a cell or range, the selection looks like it has been crossed out.

Tip

If you use border formatting in your worksheet, you may want to turn off the grid display to make the borders more pronounced. Choose View Show Gridlines to toggle the gridline display. n

129

Part I: Getting Started with Excel

Adding a Background Image to a Worksheet

Excel also enables you to choose a graphics file to serve as a background for a worksheet. This effect is similar to the wallpaper that you may display on your Windows desktop or as a background for a Web page.

To add a background to a worksheet, choose Page Layout Page Setup Background. Excel displays a dialog box that enables you to select a graphics file; all common graphic file formats are supported. When you locate a file, click Insert. Excel tiles the graphic across your worksheet. Some images are specifically designed to be tiled, such as the one shown in Figure 6.11. This type of image is often used for Web page backgrounds, and it creates a seamless background.

FIGURE 6.11

You can add almost any image file as a worksheet background image.

On the CD

This workbook, named background image.xlsx, is available on the companion CD-ROM. n

You also want to turn off the gridline display because the gridlines show through the graphic. Some backgrounds make viewing text difficult, so you may want to use a solid background color for cells that contain text.

Keep in mind that using a background image will increase the size of your workbook. This may be a consideration if you plan to e-mail the workbook to others.

130