You've now had a comprehensive tour of Excel's formatting features. But, of course, just because the features are there doesn't mean they're easy to use. You can chew up hours digging through the different options and applying a full range of formatting choices to each and every cell. Fortunately, Excel also includes a few time-savers that let you speed up many formatting tasks. The final few sections of this chapter introduce these features. You'll see how to skip the Format Cells dialog box with a few toolbar tricks, how to copy and standardize formatting with Styles and the Format Painter, and how to add a little built-in graphical intelligence to your worksheet with conditional formatting.
To control cell formatting, you need to jump between your worksheet and the Format Cells dialog box, which is time-consuming. But what if there were a way to apply basic formatting without jumping to a new window? In fact, Excel provides just such a handy shortcut with its Formatting toolbar.
The Formatting toolbar can't duplicate every feature in the Format Cells dialog box. But it does do the next best thing: it lets you apply the most common types of formatting with one or two quick mouse clicks.
Note
The Formatting toolbar usually appears at the top of the Excel window. If you've lost yours, just select View → Toolbars → Formatting from the menu.
Using the Formatting toolbar is similar to using the Format Cells dialog box. First, move to the cell you want to change, or select a group of cells. Then click the appropriate button in the toolbar to apply a new font, fill pattern, border, or cell value format. Figure 4-14 outlines your options.
But wait…there's more! The Formatting toolbar also includes a few tools that aren't available in the Format Cells dialog box: the ability to draw borders directly on the worksheet and a way to format individual characters. The next two sections show you how.
Figure 4-14. The Formatting toolbar provides one-stop shopping for a number of formatting options. When you use the font list, you'll even see the name of the font displayed in its proper typeface (so the font entry "Times New Roman" appears in Times New Roman, which gives you a helpful preview).
The Borders button on the Formatting toolbar, which lets you quickly apply basic borders to the current selection, is an ideal way to add a column divider or outline a group of cells with column headings. But what if you want to create more elaborate borders? There's no need to head to the Format Cells dialog box, because Excel gives you the ability to draw cell borders directly onto your worksheet. It works like this:
Click the drop-down arrow next to the Borders icon in the Formatting toolbar. Choose the last option in the menu, Draw Borders.
The Borders toolbar appears automatically, with the Draw Border icon already selected (see Figure 4-15).
Choose a line style and color in the Borders toolbar.
The line and color options are similar to the Borders tab in the Format Cells dialog box.
Begin drawing your borders by single clicking on the lines between cells.
To draw a longer border, drag your pointer down through a range of cells. If you drag your pointer down and to the side, you'll create an external border around a block of cells. Or, if you want, you can choose to draw a border across a block of cells that includes the interior borders between cells by changing the drawing mode. Click the drop-down arrow next to the Draw Border icon on the Borders toolbar, and choose Draw Border Grid (instead of the standard option, Draw Border). Now, when you drag the mouse over a rectangular region of cells, you'll add border lines to all the cells you drag across.
If you want to erase a border, click the Erase icon on the Borders toolbar. The pointer changes to an eraser and you can click the border you want to remove.
To stop drawing, deselect the Draw Border icon on the Borders toolbar.
Even after you stop drawing borders, the Borders toolbar remains, ready for use. If you're finished with it, you can hide it by selecting View → Toolbars → Borders or by clicking the X on the toolbar's right side.
The Formatting toolbar lets you perform one task that isn't possible with the Format Cells dialog box: applying formatting to just a part of a cell. For example, if a cell contains the text "New low price," you could apply a new color or bold format to the word "low."
To apply formatting to a portion of a cell, follow these steps:
Move to the appropriate cell, and put it into edit mode by pressing F2.
You can also put a cell into edit mode by double-clicking it or by moving to it and clicking inside the text in the Formula bar.
Select the text you want to format.
You can select the text by highlighting it with the mouse or by holding down Shift while using the arrow keys to mark your selection.
Choose a font option from the toolbar.
You can change the size, the font, the color, or the bold, italic, or underline settings.
Note
Applying multiple types of text formatting to the same cell can get tricky. The Formula bar won't show the difference, and, when you edit the cell, you may not end up typing text in the font you want. Also, be careful that you don't apply new font formatting to the cell later; if you do, you'll wipe out all the font information you've added to the cell.
Although AutoFormat doesn't automatically take care of all the formatting you may need to do on a spreadsheet, it does take a basic, unformatted table of information and automatically apply a collection of borders, fills, and bold or italic formatting to highlight the table's structure. Figure 4-16 shows some examples of AutoFormat at work.
Figure 4-16. The first table of data shows the data before it's been formatted; the other three tables have been transformed using a variety of different AutoFormat templates.
To use AutoFormat, follow these steps:
Select the cells you want to AutoFormat.
In fact, you can move to any cell that's within the cells you want to Auto-Format. AutoFormat can automatically detect which portion of a worksheet is filled with data—as long as there are no blank rows or columns within the data. If you have a table of data that includes blank rows or columns (for example, you've left a blank row between your heading and column titles), you'll need to manually select the entire range of cells you want to AutoFormat before continuing.
Select Format → AutoFormat from the menu.
If you haven't already manually selected your cells, AutoFormat starts by selecting all the cells it thinks you want to format. Then the AutoFormat window (Figure 4-17) appears, displaying a list of preset format templates.
Figure 4-17. Each format template includes settings for number formats, fonts, alignment, borders, fill patterns, row height, and column width. To turn off one or more of these settings, click the Options button. A group of checkboxes ("Formats to apply") will appear at the bottom of the AutoFormat window, and you can clear the checkboxes corresponding to the format options you don't want to apply.
Choose one of the format templates.
Each template presents a different collection of column, border, number, and text format settings. The templates have semi-descriptive names, like Accounting 1 and Classic 2. Click the template most closely matching the look you want. Unfortunately, you can't create your own templates, but you can come pretty darn close. You can modify some of the settings in the existing templates, as shown in Figure 4-17—and you can also adjust the format settings after you apply them using the Format Cells dialog box.
Click OK.
Excel applies the selected AutoFormat template to your data.
The Format Painter is a simple yet elegant tool that lets you copy all cell format settings—including fonts, colors, patterns, and borders—from one cell to another. (Apparently the Excel team decided that the more accurate label "Format Copier" wasn't nearly as exciting as the name Format Painter.)
To use the Format Painter, follow these steps:
Move to a cell that has the formatting you want to copy.
You can use the Format Painter to copy formatting from either one cell or a whole group of cells. For example, you could copy the format from two cells that use two different fill colors, and paste that format to a whole range of new cells. These cells would alternate between the two fill colors. Although copying from two cells is a powerful trick in the right circumstances, in most cases it's easiest to copy the format from a single cell.
Click the Format Painter button on the Standard toolbar (Figure 4-18) to switch into "format painting" mode.
The pointer changes so that it now includes a paintbrush icon, indicating Excel is ready to copy the format.
Figure 4-18. Oddly enough, the Format Painter icon (a teensy paintbrush) appears on the Standard toolbar instead of the Formatting toolbar. To switch to format-painting mode, click the Format Painter icon. When you do, the pointer changes to include another paintbrush icon, indicating Excel is ready to copy the format.
Click the cell where you want to apply the format.
The moment you release your mouse button, Excel applies the formatting and your pointer changes back to its normal appearance. If you want to copy the selected format to several cells at once, just drag to select a group of cells, rows, or columns, instead of clicking a single cell.
Excel doesn't let you get too carried away with format painting: as soon as you copy the format to a new cell or selection, you exit format painting mode. If you want to copy the format to another cell, you have to backtrack to the cell that has your format and start over again. But here's a neat trick you can use if you know you're going to repeatedly apply the same format to a bunch of different cells. Instead of single-clicking the Format Painter button, double-click it. You'll remain in format-painting mode until you click the Format Painter button again to switch it off.
Note
The Format Painter is a good tool for quickly copying formatting, but it's no match for another Excel feature called Styles. With Styles, you can define a group of formatting settings and apply them wherever you need them. Best of all, if you change the style after you've created it, Excel automatically updates all cells that you've formatted using that style. Styles are described in the next section.
Styles let you create a customized combination of format settings, give it a name, and store it in a spreadsheet file. You can then apply these settings anywhere you need them.
Styles really shine in complex worksheets where you need to apply different formatting to different groups of cells. For example, say you've got a worksheet that's tracking your company's performance. You're confident that most of the data is reliable, but there are a few rows that come from your notoriously overly optimistic sales department. To highlight these sales projections, you decide to use a combination of a bold font with a hot-pink fill. And since you're pretty sure these figures aren't highly precise, you decide to use a number format without decimal places and precede the number with a tilde (~), the universal symbol for "approximately right."
You could implement all these changes manually. But that would take fourscore and seven years. Better to set up a style that includes all these settings and then apply it with a flick of the wrist whenever you need it. Styles are efficiency monsters in a few ways:
They let you reuse your formatting easily, just by applying the style.
They free you from worry about being inconsistent, because the style includes all the formatting you want.
Excel automatically saves styles with your spreadsheet file, and you can transfer styles from one workbook to another.
If you decide you need to change a style, all it takes a few mouse clicks. Then, Excel automatically adjusts every cell that uses your newly changed style.
What more could you want?
Begin by moving to a cell in your worksheet that has the formatting you want to use for your style.
The quickest way to create a new style is to use formatting you've already set up. However, you can also create a new style from scratch. In this case, you would simply move to a blank, unformatted cell in your worksheet.
Select Format → Style.
This action opens the Style dialog box, which provides a drop-down list of styles. Usually, the Normal style will be selected in this list, unless you've already applied a different style to the current cell.
Type a name for your new style into the "Style name" listbox.
The text you type replaces the current selection. For example, if you want to create a new style for column titles, you can type the style name SassyColumn-Title. Each style name needs to be unique in your spreadsheet file. Figure 4-19 shows a new style being created.
Specify the settings you want your style to include.
Excel assumes you want your style to define a number format as well as alignment, font, border, pattern, and protection settings. If you don't want your style to include all of these settings, turn off the appropriate checkboxes. For example, if you want to create a style that applies a new font, fill, and border, but you want to keep the existing alignment and number format, turn off the Number and Alignment checkboxes. As a general rule, if you don't need to explicitly set a specific style characteristic, turn off the corresponding checkbox.
Click Add to create the new style.
Your style now exists, based on the formatting settings of the current cell. The next step explains how to modify these settings.
Click Modify to specify the formatting options for the style.
When you click Modify, the familiar Format Cells dialog box appears. You can use this dialog box to change formatting just as if you were formatting an individual cell. Click OK to close the Format Cells dialog box when you're finished.
Click OK to close the Style window.
You can return to the Style window at any time to modify existing styles, or delete them using the Modify and Delete buttons.
Once you've created a style, applying it is just a matter of a few mouse clicks. Select the cell or cells you want to modify, choose Format → Style, choose your style from the list, and click OK. You'll see the changes immediately.
Keep in mind that you can still modify the formatting of a cell after you've applied a style. But if you do find yourself overriding a style fairly frequently, and always in the same way, you probably need to modify the style. Either create more than one version of the same style, each with the appropriate settings, or clear some of the Style checkboxes so that your style won't apply formatting settings that you commonly change.
Every worksheet also includes a basic set of default styles, which you can see in the Style window:
Normal is the standard style that Excel applies to all cells in a new worksheet. It sets the number format to General and the font to 10-point Arial (unless you've changed the standard font). It also turns off shading and borders and sets the vertical alignment to Bottom and the horizontal alignment to General.
Comma changes the number format to Accounting, set to two decimal places. The Accounting format always uses a comma to separate each group of three digits (for example: 385,789).
Comma [0] changes the number format to Accounting, but doesn't include any decimal places.
Currency, oddly enough, doesn't use the Currency number format. Instead, it changes the number format to Accounting, set to two decimal places and using the currency symbol defined in your regional settings (which may well be $) at the beginning of the number. The only difference between this choice and the Currency number format is that Excels lines up currency signs at the left side of the cell and pads cell values with one space on the right.
Currency [0] changes the number format to Accounting, but doesn't include any decimal places. It also uses the currency symbol (such as $) defined in your regional settings.
Percent changes the number format to Percent and doesn't include any decimal places.
With the exception of Normal, these styles apply only a single formatting characteristic: a number format. You'll notice that none of the other style checkboxes are turned on. You can modify all of these styles, and you can remove any of them except for Normal.
Once you've created a few useful styles, you'll probably want to reuse them in a variety of spreadsheet files. To reuse them, you need to copy the style information from one workbook to another. Excel makes this process fairly straightforward:
Open both files in Excel.
You'll need both the source workbook (the one that has the styles you want to copy) and the destination workbook (the one where you want to copy the styles).
Go to the destination workbook.
Choose Format → Style.
The Style dialog box appears.
Click the Merge button.
The Merge Styles dialog box appears with a list of all files that you currently have open in Excel.
Select the file that has the styles you want to copy into your active workbook, and click OK.
If there are any files that have the same name, Excel prompts you with a warning message, informing you that it will overwrite the current styles with the styles you're importing. Click OK to continue.
Click OK to close the Style dialog box.
You can now use the styles that you've imported. These styles are now an independent copy of the styles in the source workbook. If you change the styles in one workbook, the other workbook won't be affected unless you import the changed styles into it.
What if you want to flag extravagant expenses that top $100, or you want to flag a monthly sales total if it exceeds the previous month's sales by 50%? Excel provides a feature for just this problem: conditional formatting.
With conditional formatting, you set a condition that, if true, prompts Excel to apply additional formatting to a cell. This new formatting can change the text color or use any other formatting trick you've seen in this chapter, including modifying fill colors, borders, and fonts. Usually, you use conditional formatting as a way to automatically highlight something important in a spreadsheet.
To apply conditional formatting, follow these steps:
Select the cell you want to apply the conditional formatting to.
You can apply conditional formatting to any cell or combination of cells.
Select Format → Conditional Formatting.
The Conditional Formatting dialog box appears. It lets you set up to three conditions for a cell. When the window first appears, it shows only a single condition, which is usually all you need.
Using the list and text boxes, set the condition that Excel should evaluate.
The first drop-down listbox lets you choose whether you want to evaluate a formula ("Formula Is") or examine the cell value ("Cell Value Is"). The simplest option is to examine the cell value. (Formulas are covered in detail in Chapter 7).
Using the second drop-down listbox, choose the type of comparison you want to perform. You can choose to test whether the cell value equals a set number, is greater or less than a set number, or lies within some range of values (for example, the value is somewhere between 1 and 10).
Finally, type in the information you want Excel to use for the comparison. For example, if you choose to perform a less-than comparison, type in the value that Excel should compare against. If you choose to test whether a cell is between certain values, type in both values.
Figure 4-20 shows a completed Conditional Formatting dialog box.
Figure 4-20. With conditional formatting, you set a condition that, if true, prompts Excel to apply additional formatting to a cell. In this example, the cell will contain italicized, bold text and will be surrounded by a black border—if the number in the cell is between 1 and 10.
Tip
Usually, conditional formatting compares a cell value to a fixed number or constant. However, you can also create conditions that compare the cell value to other cells in your worksheet. To take this step, select the text box where you'd type in the comparison number, and then click the worksheet to select the cell that Excel should use. Excel automatically inserts a cell reference (like $D$2 for cell D2) into the text box.
Click the Format button to set the formatting you want Excel to apply if the condition is true.
An abbreviated version of the Format Cells dialog box appears featuring the Font, Border, and Patterns tab. Other settings, like the number format, can't be applied conditionally, and so they won't appear. You also can't conditionally change the font (the Font tab lets you control only the Font style). However, aside from these limitations, the tabs are the same as the ones you're familiar with from the full-blown Format Cells dialog box (Figure 4-2).
Click OK to close the Format Cells dialog box.
In the Conditional Formatting window underneath your condition, you'll see a preview of the formatting choices you made.
If you want to add a second or third condition, click Add and return to step 3.
Excel evaluates each condition separately and applies the appropriate formatting.
You can also click Delete to remove conditions. Excel will display a dialog box asking which conditions you want to remove. You can then place a checkmark next to the appropriate condition and click OK.
Click OK.
As soon as you click OK, Excel evaluates the conditions and adjusts the formatting as needed. Every time you open your spreadsheet or change the value in the conditional cell, Excel evaluates the condition.
Note
Conditional formatting is like any other type of formatting in Excel. To remove conditional formatting, select the cell and choose Edit → Clear → Formats. You can also copy conditional formatting from one cell to another using the Format Painter (although you can't make conditional formatting part of a style).
Get Excel 2003 for Starters: The Missing Manual now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
