Formatting Spreadsheets

Now you re ready to focus on making your worksheet look good. Adding special formats, cool fonts, attention-getting graphics, and lines and borders may not be the most important thing about your worksheet ”but it lends a professional touch that may help others give your work a closer look.

Aha! ” Color -Coding Worksheet Tabs

Color-coding your worksheet tabs can help people viewing the workbook understand what the different tabs mean. To set the color of a tab, right-click the tab, choose Tab Color, and select the color you want from the Format Tab Color palette. You may want to create a key on the opening workbook page so others know what the colors represent.

 

Fast Formatting

If you want a quick-and-easy spreadsheet that looks professional and will get at least a few appreciative blinks from the crowd , try AutoFormat. Excel s AutoFormat is a set of worksheet formats you can apply to your data. To use AutoFormat, follow these steps:

1. Begin by selecting the area of the worksheet to which you want to apply the format. If you want to apply the format to the entire worksheet, click Ctrl+A.

2. Choose AutoFormat from the Tools menu.

3. Scroll through the list of formats in the AutoFormat dialog box (see Figure 8-2). Click the format you want and click OK.

   
   Figure 8-2: AutoFormat gives you a number of fast formats to apply to selected data.

By default, AutoFormat applies its own format for number, border, font, pattern, alignment, and width/height settings. If you want to turn off the automatic formatting for any of those items, click the Options button in the AutoFormat dialog box and click the check box of the item you want to clear. Then click OK to apply the format to your worksheet.

Aha! ”Formatting Conditionally

Excel also includes a feature called Conditional Formatting, which enables you to apply formats to data based on certain conditions. For example, you could specify that a cell highlight in red if its value falls below a certain level ”this would be good for an inventory tracking worksheet or spreadsheet that monitors your cash flow. To use this feature, choose Conditional Formatting from the Format menu.

 

Choosing Data Formats

The most common formatting task you ll do in Excel is formatting the data you enter. When you first type numbers , text, dates, and times, they are placed in a generic format based on the data type Excel recognizes. (For more about this, see Chapter 7, Creating and Saving a Spreadsheet. ) As you work with your worksheets, you will need to know how to display data in the necessary format. To set the format of cell data, follow these steps:

1. Select the cell you want to format. (You can also choose a range, column, or row if you want to apply the same format to multiple cells .)

2. Right-click the selected cell(s). Choose Format Cells from the context menu.

3. The Number tab is displayed by default. Click the format you want in the Category list on the left. Each time you choose a different format, options related to that format, as well as a general description of the format, appear on the tab (as shown in Figure 8-3).

   
   Figure 8-3: You can control all kinds of formatting tasks in the Format Cells dialog box.

4. Select the format you want, specify any options you need, and then click OK.

Aha! ”Using Formatting Tools

This section shows you how to use the Format Cells dialog box to apply different formats to the data in your worksheet ”but remember that you can apply many format changes quickly by selecting the cells and clicking the appropriate formatting tool on the Formatting toolbar. You can change font, style, alignment, and color by using tools on the toolbar. Additionally, you can change common number formats, add borders and lines, and fill worksheet areas with a color of your choosing. If you re not sure which formatting tool is which, position your pointer over the tool and a ScreenTip will display the tool s name .

 

Changing Alignment, Font, Style, and Size

As you can see, the Format Cells dialog box is a one-stop shop for all your Excel formatting needs. The various tabs in the dialog box contain options related to a specific task. You ll use the options in the Alignment tab to control how the data appears in the cells of the selected area. And the Font tab ”well, that s obvious, isn t it? ”allows you to choose a different font, style, size, and color for the data in your worksheet.

To set the alignment for your data, display the Format Cells dialog box by right-clicking in the selected cell and choosing Format Cells. Then click the Alignment tab and change any necessary settings. You may not use Alignment much for ordinary worksheets, but it does come in handy when you want to create special effects, such as vertical or skewed text labels or worksheet titles.

To change the font, style, size, and color of your data, follow these steps:

1. Display the Format Cells dialog box and click the Font tab.

2. Scroll through the Font list to find a typeface you like, then choose the Font Style and Size you want by clicking them (see Figure 8-4).

3. If you want to change the color of your data, click the Color down arrow and click the color you want from the displayed palette.

4. Set other options and effects as needed; then click OK.

   
   Figure 8-4: Make your changes for text font, style, size, and color on the Font tab of the Format Cells dialog box.

Limiting Changes with Rights Management

Office System now includes a feature that enables you to control how, when, and by whom your worksheets are modified. You can block users from changing formatting styles; you can disable the printing feature; you can keep worksheets from being distributed and restrict distribution to only those people who need access to the information. To use Rights Management in Excel, choose Permission from the File menu. Then choose Do Not Distribute and in the Permission dialog box, click the Restrict Permission to the Workbook checkbox.

Next, add the email addresses of users who can read (but not change, print, or copy) worksheet information. Additionally, you can specify those users who will be able to view, modify, and save changes. If you want to see further options for restricting what users can do with your worksheet file, click More Options. After you set up the permissions the way you want them, click OK to return to the worksheet.

 

Adding Borders and Shading

Once you ve mastered the formatting of cells and labels, you ll start thinking about sprucing up your worksheet in new ways. Borders can add a professional look and give you more flexibility than simply drawing boxes around worksheet areas or charts . You can use Excel s Border feature to add horizontal or vertical lines, grid lines, surrounding borders, or specialty lines. You can work with borders three ways:

• Select the area to which you want to apply the border, and then click the Borders tool, shown below, to display the Borders palette and select the tool you want.

  

• Display the Borders toolbar, shown below, by clicking the Borders tool and choosing Draw Borders. You can then select the cells you want to border, select the Draw Border tool, choose your line style and color, and draw the border by dragging the pencil tool in the areas you want to draw. Press Esc to turn off border drawing.

  

• Select the cells you want to border, right-click the selection, and choose Format Cells. Click the Borders tab in the Format Cells dialog box and choose one of the Presets, or click one of the Border lines to create a custom border. Choose the line style and color you want and click OK. The border is applied to the selected cells.

Shading Worksheet Areas

Another feature that goes hand-in-hand with bordering your worksheet cells is colorizing them. You can change the background color and pattern of cell areas using the Patterns tab in the Format Cells dialog box. You might want to change the background color of column labels or row labels, for example, or perhaps highlight an area of your worksheet that you want to stand out in a unique way. To change the color of your worksheet cells, follow these steps:

1. Select the cells you want to change, right-click, and choose Format Cells from the shortcut menu.

2. Click the Patterns tab in the Format Cells dialog box. Click the color you want in the Cell Shading area.

3. Click the Pattern drop-down arrow, producing a palette that offers you additional patterns you can apply in the color you selected, as shown below.

4. Click the pattern you want, and then click OK to apply the colored pattern to the selected cells.

 

Aha! ”Filling Quickly with Color

If you want to apply color to cells without opening the Format Cells dialog box, you can simply select the cells you want to color, and then click Fill Color on the Formatting toolbar. This applies the currently selected color to the highlighted cells. If you want to use a different color, click the Fill Color down arrow and choose the color you want from the displayed palette.