In This Chapter
✓ Taking out your formatting tools
✓ Coloring and shading cells for emphasis
✓ Figuring out and formatting dates and times
✓ Formatting with conditions
✓ Formatting the worksheet background
If you hold this book for five minutes in your hands each day, Excel auto- matically starts behaving. Jokes aside, we start this chapter by pointing out some interesting formatting options, including how to control borders, shading, and patterns. We explain the mysteries behind Excel’s date and time calculations and how Excel can apply formatting for you based on a cell’s contents.
This chapter’s focus relates to features that are unique to Excel. You can find info about formatting text, text boxes, shapes, pictures, WordArt, and other objects that can be placed on worksheets in Book I, Chapter 6.
Before you can format anything, you must first select it. In general, to select something, click it. The interface responds by changing the display to indi- cate your selection, usually by changing the selection’s border or outline.
Formatting Cells
One of the things you might find you do frequently is change the way cells look, and you do so with options on the Home tab of the Ribbon, shown in Figure 4-1. Refer to Figure 4-1 as we discuss the organization of the Home tab in the following sections.
You can apply many operations such as formatting to more than one work- sheet at a time. Hold Ô down as you click on worksheet tabs to select multi- ple worksheets. As you apply formatting, the corresponding cells in the selected sheets are also formatted. Click a worksheet tab without pressing Ô to return to having a single sheet selected.
452 Formatting Cells
Figure 4-1:
Formatting cells from the Ribbon.
Fill color Alternative currency formats
Applying number and text formats
Other than the visual effect, format also means how a cell treats the content that’s typed inside it. General format is the default cell format. (Look in the Number group on the Home tab shown in Figure 4-1.) In Chapter 3 of this minibook, we provide a table that explains how General format treats char- acters when you enter them. Briefly, the General format applies formatting by using these simple rules:
✦ A cell that contains any text characters is formatted as text.
✦ A cell containing numbers only is formatted as a number or date.
✦ A cell that begins with an equals sign (=) is a formula.
You can override General format and apply any other format that you want to a cell. If you change a cell’s format from one of the number or date for- mats into Text format, you can no longer use the number value or date in formula calculations.
The Number group on the Home tab offers quick formatting options:
✦ Number Format: This is a pop-up menu that lets you apply the default format for each of the major format categories.
✦ Alternate Currency: This pop-up menu lets you apply accounting for- mats for specific currencies.
✦ Percent: Displays decimal values as a percent.
✦ Thousands: Click to toggle commas on or off as thousands separators.
✦ Reposition decimal point: Click a button to move the decimal point one position to the left or to the right.
To display the complete list of available number formats, press Ô-1 (or choose Format➪Cells from the menu bar) and in the Formal Cells dialog, click the Number tab, as shown in Figure 4-2. We have a step-by-step exam- ple using Date custom formatting codes in the section “Formatting a date,”
later in this chapter.
Book III Chapter 4
Formatting andConditionalFormatting
453
Formatting Cells
Figure 4-2:
Designing a custom cell format.
Fitting and positioning content
Similar to the controls for formatting a table, you can align and position cell contents using the Alignment group on the Ribbon’s Home tab (refer to Figure 4-1):
✦ Horizontal: Select from left, center, or right justification for a cell.
✦ Vertical: Select top, center, or bottom.
✦ Orientation: Tip and turn cell contents.
✦ Wrap Text: Choose Wrap Text from this pop-up menu to allow text to wrap within a cell. Select some contiguous rows and then select Shrink to Fit from the pop-up menu. The text in rows with more text will be made smaller to match the length of the shortest text entry among the selected rows.
✦ Merge: Select two or more cells and then click this button to merge the selection into a single cell. You retain only the content of the upper-left cell. Select an already merged cell and then click this button to unmerge the merged cell.
454 Formatting Cells
When you type text that extends past the right edge of the cell, your text dis- plays. If you then enter text or a formula into the cell immediately to the right, the contents of the second cell will cover the text in the first cell. This is normal. Adjust the column width and row heights by using the double- arrow cursor, as described in Chapter 3 of this minibook. The Wrap Text button has a pop-up menu from which you can choose to either Wrap Text or Shrink Text to Fit, which you can use to solve overlap problems.
You can access additional cell alignment options by pressing Ô-1 and click- ing the Alignment tab, as shown in Figure 4-3. The Orientation area gives you a couple fun options:
✦ Stacked letters: Click this button to stack letters as shown in Figure 4-3.
✦ Angled orientation: Drag the angle line or use the spinner control to tilt text to any angle.
Figure 4-3:
Setting cell alignment.
Formatting cell borders
Formatting cells and cell ranges is like formatting tables, as we describe in Book I, Chapter 11. In the Font group of the Ribbon’s Home tab, clicking Borders button displays a drop-down gallery of border styles.
Book III Chapter 4
Formatting andConditionalFormatting
455
Formatting Cells
Experienced Excel users may yearn for the old floating Borders toolbar.
Happily, you can find the Borders option on the Formatting toolbar, which you turn on by choosing View➪Toolbars➪Formatting from Excel’s menu.
You can find more presets for borders in the Format Cells dialog, which you can display by pressing Ô-1 and choosing the Border tab, shown in Figure 4-4.
If you select a cell range before opening the Format Cells dialog, you format the outer border as well as the inner borders.
Use the following order when you’re formatting a range of cells using the Border tab of the Format Cells dialog:
1. Color: Choose a color for your border from Excel’s color picker. The color you choose is displayed in the Style pane.
2. Style: Select a solid, dashed, thick, thin, or double-style border.
3. Border: For the border, you work in either the Presets area or the Border area.
In the Presets area, choose from these options:
• None: Clears borders from the selected cell or cell range.
• Outline: Applies a border to the selected cell or around the outside border of a range of cells.
• Inside: Applies borders to cells within a selected range, but doesn’t put a border around the entire range.
In the Border area, click in the preview, or click toggle buttons to turn individual outside, inside, or diagonal borders on and off.
You can apply multiple colors and line styles. You have to choose a new color and style for each border you turn on.
Formatting cell fill color and shading
Choosing a theme and sticking with its color set is usually a safe way to add colors to cells. Whether it’s to match a color key, corporate theme, or to add emphasis, Excel helps you by making cell fill easy to apply quickly — on the Home tab, find the Font group and click the Fill Color button and choose a color from the color palette. More fill options are available as follows:
1. Select the cell or range of cells you want to format.
2. Press Ô-1.
The Format Cells dialog appears.
456 Formatting Cells
3. Click the Fill tab, as shown in Figure 4-5.
4. Choose a background color from the color picker.
Figure 4-4:
Building better borders.
5. (Optional) Choose a Foreground Color and a Foreground Pattern from the color and pattern pickers to apply on top of the background color.
The Sample area shows you a preview of your cell. Remember, you may have text in a cell. Choose colors and patterns to complement, not con- flict with, your cell contents.
Applying and saving cell format styles
Although applying all these formatting options is fun, you might find that the built-in styles will suit you fine, or at least give you a starting point from which you can refine to your taste. In the Home tab’s Format group, click the previews in the Styles gallery, or click the downward-pointing button to display a drop-down gallery that has many preconfigured styles from which to choose.
You can give your own cell format a name, which you can save as a cell style in the workbook. At the bottom of the Ribbon’s Style gallery, choose New Cell Style to display the New Cell Style dialog shown in Figure 4-6. Choose which properties to include and give your style a name.
Book III Chapter 4
Formatting andConditionalFormatting
457
Formatting Dates and Times
Figure 4-5:
Choosing a cell fill.
Figure 4-6:
Saving a custom cell style.
At the bottom of the Ribbon’s Style gallery, choose Import Cell Styles to display the names of custom cell styles that are in other open workbooks.
Choose one of the styles to copy from the other workbook.
Formatting Dates and Times
Excel has fantastic date and time calculation capabilities. You need to know just two secrets:
✦ Each day is represented by a whole number, called a serial number.
✦ Portions of days are represented by decimal fractions.
458 Formatting Dates and Times
Finding today
Some days, you wake up and don’t even know what day it is. Excel doesn’t have this problem. To have Excel return the current date, select the cell you want Excel to show the current date in and type the cell formula =TODAY() and then click the green Enter button. The selected cell displays today’s date, and Excel automatically changes the format of the cell to Date.
Getting today’s serial number
Each day has its own serial number in Excel. If you follow the steps in the preceding section, you don’t see the serial number in the selected cell because Excel knows the formula represents a date. If you want to see the serial number instead of a date format, you can manually change the format of the selected cell to Number by selecting Number in the Ribbon’s Number Format pop-up menu (as shown earlier in Figure 4-1).
Knowing that each day is represented by a whole number makes adding and subtracting dates easy.
When you have a date serial number displayed in a cell, you can change that serial number as follows:
✦ Subtract whole numbers from the serial number to change it to an ear- lier date. If you subtract 1 from the serial number for today’s date, you get yesterday’s date. For example, the cell formula =TODAY()-5 displays the date five days ago.
✦ Add whole numbers to the serial number to advance to a later date. If you add 1 to the serial number for today’s date, you get tomorrow’s date.
In Excel, to add and subtract any number of days, just add and subtract whole numbers. You’re probably wondering how Excel arrived at the serial number of today. To find out, enter 1 in a cell and apply the Date format.
The date changes to January 1, 1900 — the first day that Excel knows about.
Every day in Excel is the number of days after 1/1/1900.
Finding the time of day
Because Excel works with days as whole numbers, you might guess that portions of days are fractions. Well, you’d be right! Starting with a whole number representing a date, append .5 (one-half day) to a date serial number to represent noon. Apply the Time number format, and the time changes to 12:00 PM. Go ahead and try some different decimals.
Book III Chapter 4
Formatting andConditionalFormatting
459
Formatting Dates and Times
Formatting a date
You have at least three different ways to apply a date format. Perhaps the fastest is to select a cell or cell range, and then click the Home tab of the Ribbon. In the Number group, click the pop-up button under the Number group title and choose Date to display the date as m/d/yy, where m repre- sents the month’s number, d represents the day number, and yy represents a two-digit year.
Excel has many more built-in date formats, which you can apply by display- ing the Format Cells dialog (shown in Figure 4-7) by pressing Ô-1 and then clicking the Number tab. You can also display the Number tab of the Format Cells dialog by clicking the Home tab on the Ribbon. Then click the pop-up button under the Number group title and choose Custom from the pop-up menu.
The first-day mystery
The beginning of time in Excel is the date with the serial number one. Starting with Excel 2011 on the Mac, Excel uses the same starting date as Excel for Windows; that date is January 1, 1900. In previous versions of Excel for Mac, that date was January 1, 1904. There used to be a difference because Excel for Mac was on the market many years before Excel for Windows. Actually, Excel for Mac was sold years before Windows even existed! During the time before Windows, Excel for Mac had to compete against the spreadsheet runaway market leader on the IBM platform, which was Lotus 1-2-3.
Microsoft knew about a leap-year bug in Lotus 1-2-3 and had to make choices. To avoid the Lotus 1-2-3 leap year bug, Excel for Mac’s first serial number date was January 1, 1904.
When making Excel for IBMs and compatibles, Microsoft could be accurate but incompatible
with Lotus and risk losing market share, or include the Lotus 1-2-3 error and be compat- ible. Microsoft chose compatibility over cor- rectness. Excel eventually eclipsed Lotus 1-2-3 to become the market leader.
Ordinarily, Excel handles the date system for you automatically, regardless of whether a workbook was created in Excel for Mac or Excel for Windows. You can manually switch the date system in a workbook; choose Excel➪Preferences. In the resultant dialog, click the Calculation button. In this sheet, in the Workbook options area, you can select the option that says Use the 1904 Date System, but only select this option if you need to fix a work- book in which all the dates displayed are off by four years.
460 Formatting Dates and Times
When the Format Cells dialog displays, select the Date category. Choose a Type from the list. Choosing a different Location (language) or Calendar type changes the date types offered.
Figure 4-7:
Choosing a Date format.
The Custom category at the bottom of the Categories in the Format Cells dialog (see Figure 4-2) displays Type as an input field. See Table 4-1 for custom formatting codes.
If a custom date format in a cell is too wide to display in the column, Excel shows hash marks (##) in the cell. To fix, click the Home tab, and in the Cell group, choose Format➪AutoFit Column Width.
If you experiment with custom formatting codes in the Format Cells dialog’s Custom tab (refer to Figure 4-2), you may notice some patterns. Table 4-1 has some example format code that you can try.
Table 4-1 Date and Time Format Examples
Format Code Result
h:mm 18:00
h:mm AM/PM 6:00 PM
Book III Chapter 4
Formatting andConditionalFormatting
461
Conditional Formatting
Format Code Result
h:mm:ss AM/PM 6:00:00 PM
Yyyy 2009
Yy 09
Mmmm February
Mmm Feb
Mm 02
M 2
Conditional Formatting
You can set Excel to change the format of a cell, cell range, table, or pivot table based on conditions you specify. You can use these settings when you want a cell’s appearance to change as the result of a formula or when someone types in a worksheet. Conditional formatting was improved in many ways for Excel 2011. There are more conditions from which to choose.
Conditions can be external to the cell you’re formatting, and conditions can be based on criteria on other worksheets. Excel 2011 has new formatting options, such as borders, solid fills, icon sets, plotting negative values, and data bars.
There are literally billions of possible combinations, so to save space we show you only a few million possibilities. There are five major styles of conditional formatting, which you can find when you go to the Home tab’s Format group and click the Conditional Formatting button, as shown in Figure 4-8. We have an example for each option in this chapter. Each option has a submenu, palette, or dialog associated with it.
If you apply a rule to a data set and then apply another rule, both rules will be in play. Use the Clear Rules option near the bottom of the Conditional Formatting pop-up menu to remove conditional formatting. We show you how to use the Manage Rules option after we discuss each style of condi- tional formatting.
If you select a cell, cell range, or table before choosing an option, the format- ting you apply will affect the selection. If you do not make a selection first, the conditional formatting applies to the entire worksheet.
462 Conditional Formatting
Figure 4-8:
Choosing a conditional format option.
We use the data set shown in Figure 4-9 for our conditional formatting exam- ples. Of course, you should try these with your own data, too!
Figure 4-9:
Our sample data.
Highlight Cells Rules
The Highlight Cells Rules option lets you format cell font, borders, and fill.
It’s the same kind of conditional formatting from previous versions of Excel and is called the Classic style. When you choose Highlight Cells Rules, you then choose from a submenu with these options:
✦ Greater Than: Highlights any value greater than a number you specify.
✦ Less Than: Highlights any value lesser than a number you specify.
✦ Between: Highlights any values between two numbers you specify.
✦ Equal To: Highlights any value equal to a number or text you specify.
✦ Text That Contains: Highlights any values that include text you specify.
✦ A Date Occurring: Highlights any values that include a date you specify.
✦ Duplicate Values: Highlights repeated values in your selection.
✦ More Rules: Displays the New Formatting Rule dialog directly.
Book III Chapter 4
Formatting andConditionalFormatting
463
Conditional Formatting
As soon as you choose from the submenu, the New Formatting Rule dialog displays using the Classic style, and the logical operator field is prepopu- lated based on your choice, as shown in Figure 4-10. The New Formatting Rule dialog changes configuration depending upon the choices you make.
When entering the argument, you can type an entry such as a number, date, or formula such as =”text” or click the little button next to the entry field, which changes the cursor as shown in Figure 4-11 so that you can select a cell or range in the workbook. Your selection can be on any worksheet.
Figure 4-10:
Making a new conditional format rule.
Using the sample data from Figure 4-9, we chose to format cells with a value greater than zero to produce the result shown in Figure 4-11. To do that, follow these steps:
1. Select the cells that you want to use for the conditional formatting.
In this example, we used the data shown in Figure 4-9 and selected cells A1:A5.
2. Click the Ribbon’s Home tab, and within the Format group, click the Conditional Formatting button to bring up the menu shown in Figure 4-8.
3. Choose Highlight Cells Rules➪Greater Than to bring up the New Formatting Rule dialog shown in Figure 4-10.
4. In the empty text box next to the pop-up menu with the “greater than”
option chosen, type 0 (the number 0). Click OK.
Figure 4-11:
Applying Classic formatting.