The Home tab of the Ribbon provides quick access to the most commonly used formatting options. Start by selecting the cell or range; then use the appropriate tool in the Font, Alignment, or Number groups.

Using these tools is very intuitive, and the best way to familiarize yourself with them is to experiment. Enter some data, select some cells, and then click the controls to change the appearance. Note that some of these controls are actually drop-down lists. Click the small arrow on the button, and the button expands to display your choices.

When you right-click a cell or a range selection, you get a shortcut menu. In addition, a mini toolbar appears above the shortcut menu. Figure 7.2 shows how this toolbar looks.

Figure 7.2. The Mini toolbar appears above the right-click shortcut menu.

The Mini toolbar contains controls for common formatting:

If you use a tool on the Mini toolbar, the shortcut menu disappears, but the toolbar remains visible so you can apply other formatting if you like. Also, notice that the Mini toolbar gradually fades away if you move the mouse pointer away from it. To hide the Mini toolbar, just click in any cell.

The formatting controls available on the Home tab of the Ribbon are sufficient most of the time, but some types of formatting require that you use the Format Cells dialog box. This tabbed dialog box lets you apply nearly any type of stylistic formatting, as well as number formatting. The formats that you choose in the Format Cells dialog box apply to the cells that you have selected at the time. Later sections in this chapter cover the tabs of the Format Cells dialog box.

After selecting the cell or range to format, you can display the Format Cells dialog box by using any of the following methods:

The Format Cells dialog box contains six tabs: Number, Alignment, Font, Border, Patterns, and Protection. The following sections contain more information about the formatting options available in this dialog box.

You can use different fonts, sizes, or text attributes in your worksheets to make various parts, such as the headers for a table, stand out. You also can adjust the font size. For example, using a smaller font will allow more information on a single page.

By default, Excel 2007 uses the 11-point Calibri font. A font is described by its typeface (Calibri, Cambria, Arial, Times New Roman, Courier New, and so on), as well as by its size, measured in points. (Seventy-two points equal one inch.) Excel’s row height, by default, is 15 points. Therefore, 11-point type entered into a 15-point rows leaves a small amount of blank space between the characters in adjacent rows.

Use the Font and Font Size tools in the Home tab of the Ribbon (or in the Mini toolbar) to change the font or size for selected cells.

You also can use the Font tab in the Format Cells dialog box to choose fonts, as shown in Figure 7.3. This tab enables you to control several other font attributes that aren’t available elsewhere. Besides choosing the font, you can change the font style (bold, italic), underlining, color, and effects (strikethrough, superscript, or subscript). If you click the check box labeled Normal Font, Excel displays the selections for the font defined for the Normal style. I discuss styles later in this chapter. See “Using Named Styles for Easier Formatting.”

Figure 7.3. The Font tab of the Format Cells dialog box gives you many additional font attribute options.

Figure 7.4 shows several different examples of font formatting. In this figure, the gridlines were turned off to make seeing the underlining easier. Notice, in the figure, that Excel provides four different underlining styles. In the two non-accounting underline styles, only the cell contents are underlined. In the two accounting underline styles, the entire width of the cells is always underlined.

Figure 7.4. You can choose many different font-formatting options for your worksheets.

If you prefer to keep your hands on the keyboard, you can use the following shortcut keys to format a selected range quickly:

These shortcut keys act as a toggle. For example, you can turn bold on and off by repeatedly pressing Ctrl+B.

The contents of a cell can be aligned horizontally and vertically. By default, Excel aligns numbers to the right and text to the left. All cells use bottom alignment, by default.

Overriding these defaults is a simple matter. The most commonly used alignment commands are in the Home Alignment group of the Ribbon. Use the Alignment tab of the Format Cells dialog box for even more options (see Figure 7.5).

Figure 7.5. The full range of alignment options are available in the Alignment tab of the Format Cells dialog box.

Merging worksheet cells to create additional text space

Excel also enables you to merge two or more cells. When you merge cells, you don’t combine the contents of cells. Rather, you combine a group of cells into a single cell that occupies the same space. The worksheet shown in Figure 7.6 contains four sets of merged cells. For example, range C2:I2 has been merged into a single cell, and so has range J2:P2. In addition, ranges B4:B8 and B9:B13 have also been merged. In the latter two cases, the text direction has been changed (see “Displaying text at an angle,” later in this chapter).

Figure 7.6. Merge worksheet cells to make them act as if they were a single cell.

You can merge any number of cells occupying any number of rows and columns. In fact, you can merge all 17,179,869,184 cells in a worksheet into a single cell. However, the range that you intend to merge should be empty except for the upper-left cell. If any of the other cells that you intend to merge are not empty, Excel displays a warning. If you continue, all the data (except in the upper-left cell) will be deleted. To avoid deleting data, click Cancel in response to the warning.

You can use the Alignment tab in the Format Cells dialog box to merge cells, but using the Merge And Center control on the Ribbon (or on the Mini toolbar) is simpler. To merge cells, select the cells that you want to merge and then click the Merge And Center button. This button acts as a toggle. To unmerge cells, select the merged cells and click the Merge And Center button again.

After you’ve merged cells, you can change the alignment to something other than Center.

Displaying text at an angle

In some cases, you may want to create more visual impact by displaying text at an angle within a cell. You can display text horizontally, vertically, or at an angle between 90 degrees up and 90 degrees down.

The Home Alignment Orientation drop-down lets you apply the most common text angles. But for more control, use the Alignment tab of the Format Cells dialog box. In the Format Cells dialog box, use the Degrees spinner control—or just drag the pointer in the gauge. You can specify a text angle between –90 and +90 degrees.

Figure 7.7 shows an example of text displayed at a 45-degree angle.

Figure 7.7. Rotate text for additional visual impact.

Note

Often, rotated text may look a bit distorted on-screen, but the printed output is usually of much better quality.

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.

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.

Even though you have access to an unlimited number of colors, you might want to stick with the 60 theme colors 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 60 colors were chosen because they “go together” well. 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.

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 Border drop-down list shown in Figure 7.8. 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 7.8. Use the Border 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 on the Home Font Border drop-down list. Selecting either of these commands 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.

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

Figure 7.9. 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.

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 7.10. This type of image is often used for Web page backgrounds, and it creates a seamless background.

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

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.

The designers of Excel 2007 have revamped this underutilized feature significantly and Excel now includes a good selection of predefined named styles. Figure 7.11 shows the effect of choosing Home Styles Cell Styles. Note that this display is a “live preview”—as you move your mouse over the style choices, the selected cell or range temporarily displays the style. When you see a style you like, click it to apply the style to the selection.

Figure 7.11. Excel 2007 displays samples of available cell styles.

After you apply a style to a cell, you can apply additional formatting to it by using any formatting method discussed in this chapter. Formatting modifications that you make to the cell don’t affect other cells that use the same style.

You have quite a bit of control over styles. In fact, you can do any of the following:

The following sections describe these procedures.

To change an existing style, choose Home Styles Cell Styles. Right-click the style you want to modify and choose Modify from the shortcut menu. Excel displays the Style dialog box, shown in Figure 7.12. In this example, the Style dialog box shows the settings for the Office them Normal style—which is the default style for all cells. (The style definitions vary, depending on which document theme is active.)

Figure 7.12. Use the Style dialog box to modify named styles.

Here’s a quick example of how you can use styles to change the default font used throughout your workbook.

The font for all cells that use the Normal style changes to the font that you specified. You can change any formatting attributes for any style.

In addition to using Excel’s built-in styles, you can create your own styles. This flexibility can be quite handy because it enables you to apply your favorite formatting options very quickly and consistently.

To create a new style, follow these steps:

After you perform these steps, the new custom style will be available when you choose Home Styles Cell Styles. Custom styles are available only in the workbook in which they were created. To copy your custom styles, see the section that follows.

It’s important to understand that custom styles are stored with the workbook in which they were created. If you’ve created some custom styles, you probably don’t want to go through all the work to create copies of those styles in each new Excel workbook. A better approach is to merge the styles from a workbook in which you previously created them.

To merge styles from another workbook, open both the workbook that contains the styles that you want to merge and the workbook into which you want to merge styles. From the workbook into which you want to merge styles, choose Home Styles Cell Styles and choose Merge Styles. Excel displays the Merge Styles dialog box that shows a list of all open workbooks. Select the workbook that contains the styles you want to merge and click OK. Excel copies styles from the workbook that you selected into the active workbook.

When you start Excel, it loads with several default settings, including the settings for stylistic formatting. If you spend a lot of time changing the default elements, you should know about templates.

Here’s an example. You may prefer to use 10-point Calibri rather than 11-point Calibri as the default font. And maybe you prefer Wrap Text to be the default setting for alignment. Templates provide an easy way to change defaults.

The trick is to create a workbook with the Normal style modified to the way that you want it. Then, save the workbook as a template in your XLStart folder. After doing so, you choose Office New to display a dialog box from which you can choose the template for the new workbook. Template files also can store other named styles, providing you with an excellent way to give your workbooks a consistent look.

Figure 7.15 shows the theme choices that appear when you choose Page Layout Themes. This display is a live preview. As you move your mouse over the theme choices, the active worksheet displays the theme. When you see a theme you like, click it to apply the theme to all worksheets in the workbook.

When you specify a particular theme, the gallery choices for various elements reflect the new theme. For example, the chart styles that you can choose from vary, depending on which theme is active.

Because themes use different fonts and font sizes, changing to a different theme may affect the layout of your worksheet. For example, after applying a new theme, a worksheet that printed on a single page may spill over to a second page. Therefore, you may need to make some adjustments after you apply a new theme.

Notice that the Page Layout Themes group contains three other controls: Colors, Fonts, and Effects. You can use these controls to change just one of the three components of a theme. For example, if you like the Urban theme, but would prefer different fonts, apply the Urban theme and then specify your preferred font set by using the Page Layout Themes Font control.

Each theme uses two fonts (one for headers, and one for the body), and in some cases, these two fonts are the same. If none of the theme choices is suitable, choose Page Layout Themes Font Create New Theme Fonts to specify the two fonts you prefer (see Figure 7.16).

Figure 7.16. Use this dialog box to specify two fonts for a theme.

Use the Page Layout Themes Colors control to select a different set of colors. And, if you’re so inclined, you can even create a custom set of colors by choosing Page Layout Themes Colors Create Theme Colors. This command displays the dialog box shown in Figure 7.17. Note that each theme consists of 12 colors. Four of the colors are for text and backgrounds, six are for accents, and two are for hyperlinks. As you specify different colors, the preview panel in the dialog box updates.

Figure 7.17. If you’re feeling creative, you can specify a set of custom colors for a theme.

If you’ve customized a theme using different fonts or colors, you can save the new theme by choosing Page Layout Themes Save Current Theme. Your customized themes appear in the theme list in the Custom category. Other Office 2007 applications, such as Word and PowerPoint, can use these theme files.