Section 4.2. Formatting Cell Appearance | Excel 2010: The Missing Manual

4.2. Formatting Cell Appearance

Formatting cell values is important since it helps maintain consistency among your numbers . But to really make your spreadsheet readable, you're probably going to want to enlist some of Excel's tools for controlling things like alignment, color , and borders and shading.

To format the appearance of a cell, first select the single cell or group of cells that you want to work with, and then choose Format Cells from the menu, or just right-click the selection and choose Format Cells. The Format Cells dialog box that appears is the place where you adjust your settings.

Note: Even a small amount of formatting can make a worksheet easier to interpret by drawing the viewer's eye to important information. Of course, as with formatting a Word document or designing a Web page, a little goes a long way. Don't feel the need to bury your worksheet in exotic colors and styles just because you can.

4.2.1. Alignment and Orientation

As you learned in the previous chapter, Excel automatically aligns cells according to the type of information you've entered. But what if this default alignment isn't what you want? Fortunately, the Alignment tab in the Format Cells dialog box lets you easily change alignment as well as control some other interesting settings, like the ability to rotate text.

Excel lets you control the position of content between a cell's left and right borders, offers the following choices, some of which are shown in Figure 4-8:

General . General is the standard type of alignment; it aligns cells to the right if they hold numbers or dates and to the left if they hold text. This is the type of alignment you learned about in Chapter 2.
Left (Indent) . Left indicates that Excel should always line up content with the left edge of the cell. You can also choose an indent value to add some extra space between the content and the left border.
Center . Center indicates that Excel should always center content between the left and right edges of the cell.
Right (Indent) . Right indicates that Excel should always line up content with the right edge of the cell. You can also choose an indent value to add some extra space between the content and the right border.
Fill . The Fill setting copies content multiple times across the width of the cell, which is almost never what you want.
Justify . This is the same as Left if the cell content fits on a single line. If you insert text that spans more than one line, Excel justifies every line except the last one, which means Excel adjusts the space between words to try and ensure that both the right and left edges line up.
Center Across Selection . This setting is a bit of an oddity. If you apply this option to a single cell, it has the same effect as Center. If you select more than one adjacent cell in a row (for example, cell A1, A2, A3), this option centers the value in the first cell so that it appears to be centered over the full width of all cells. However, this only happens as long as the other cells are blank. Because this setting can lead (confusingly) to cell values displaying over cells that they aren't stored in, it's usually not a good idea to use. A better approach to centering large text titles and headings is to use cell merging (as described on Sidebar 4.3).
Distributed (Indent) . This is the same as Center if the cell contains a numeric value or a single word. If you add more than one word, Excel enlarges the spaces between words so that the text content fills the cell perfectly (from the left edge to the right edge).

Vertical alignment controls the position of content between a cell's top and bottom border. Vertical alignment only becomes important if you enlarge a row's height so that it becomes taller than the contents it contains. To change the height of a row, click on the bottom edge of the row header (the numbered cell on the left side of the worksheet), and drag it up or down. As you resize the row, the content stays fixed at the bottom. The vertical alignment setting lets you adjust the cell content's positioning.

Figure 4-8. Left : This shows horizontal alignment options.
Right : This sheet shows how vertical alignment and cell wrapping work with cell content.

Excel gives you the following vertical alignment choices, some of which are shown in Figure 4-8:

Top . Top indicates that the first line of text starts at the top of the cell.
Center . Center indicates that the block of text is centered between the top and bottom border of the cell.
Bottom . Bottom indicates that the last line of text ends at the bottom of the cell. If the text doesn't fill the cell exactly, Excel adds some padding to the top.
Justify . This is the same as Top for a single line of text. If you have more than one line of text, Excel increases the spaces between each line so that the text fills the cell completely from the top edge to the bottom edge.
Distributed . This is the same as Justify for multiple lines of text. If you have a single line of text, this is the same as Center.

If you have a cell containing a large amount of text, you might want to increase the row's height so you can display multiple lines. Unfortunately, you'll notice that enlarging a cell doesn't automatically cause the text to flow into multiple lines and fill the newly available space. But there's a simple solution: just turn on the "Wrap text" checkbox (on the Alignment tab of the Format Cells dialog box). Now, long passages of text will flow across multiple lines. You can use this option in conjunction with the vertical alignment setting to control whether Excel centers a block of text gets, or lines it up at the bottom or top of the cell. Another option is to explicitly split your text into lines. Whenever you want to insert a line break, just press Alt+Enter, and start typing the new line.

Tip: After you've expanded a row, you can shrink it back by double-clicking the bottom edge of the row header. If you haven't turned on text wrapping, this shrinks the row back to its standard single-line height.

FREQUENTLY ASKED QUESTION
Shrinking Text and Merging Cells So You Can Fit More Text into a Cell

I'm frequently writing out big chunks of text that I'd love to scrunch into a single cell. Do I have any options other than text wrapping?

Yes. If you need to store a large amount of text in one cell, text wrapping is a good choice. But it's not your only option. You can also shrink the size of the text or merge multiple cells, both of which you can do from the Alignment tab of the Format Cells dialog box.

To shrink a cell's contents, select the "Shrink to fit" checkbox. Be warned , however, that if you have a small column that doesn't use wrapping, this option can quickly reduce your text to vanishingly small proportions .

Joining multiple cells together removes the cells' shared borders and creates one mega- sized cell. Usually, you'll do this to accommodate a large amount of content that can't fit in a single cell (like a long title that you want to display over every column). For example, if you merge cells A1 and B1, you'll end up with a single cell named A1 that stretches over the full width of the A and B columns , as shown here.

To merge cells, select the cells you want to join, choose Format Cells, and on the Alignment tab, turn on the "Merge cells" checkbox. There's no limit to how many cells you can merge. (In fact, you can actually convert your entire worksheet into a single cell if you so desire .) And if you change your mind, don't worryyou simply need to select the single merged cell, choose Format Cells again, and clear the checkmark in the "Merge cells" checkbox to redraw the original cells.

Finally, the Alignment tab allows you to rotate content in a cell up to 180 degrees, as shown in Figure 4-9. You can set the number of degrees in the Orientation box on the right of the Alignment tab. Rotating cell content automatically changes the size of the cell. Usually, you'll see it become narrower and taller to accommodate the rotated content.

Tip: Thanks to Excel's handy Redo feature, you can repeatedly apply a series of formatting changes to different cells. After you make your changes in the Format Cells dialog box, simply select the new cell you want to format in the same way and then hit Ctrl+Y to repeat the last action.

Figure 4-9. Keep in mind that on a computer screen, the rotated text looks a little blurry since Excel needs to convert your text into a graphic that it can display. However, when you print your worksheet, the text will look much clearer than it does on screen.

4.2.2. Fonts and Color

As in almost any Windows program, you can customize the text in Excel, applying a dazzling assortment of colors and fancy typefaces . You can do everything from enlarging headings to shrinking footnotes. Other settings you can change include:

The font style . (For example, Arial, Times New Roman, or something a little more shocking, like Futura Extra Bold). Arial is the standard font for new worksheets.
The font size, in points . The default point size is 10, but you can choose anything from a minuscule 1-point to a monstrous 409-point. Excel automatically enlarges the row height to accommodate the font.
Various font attributes, like italics, underlining, and bold . Some fonts have complimentary italic and bold typefaces, while others don't (in which case Windows will use its own algorithm to embolden or italicize the font).
The font color . This option controls the color of the text. (The next section (Section 4.2.3) covers how to change the color of the entire cell.)

To change font settings, first highlight the cells you want to format, choose Format Cells, then click the Font tab (Figure 4-10). The Formatting toolbar also provides a number of shortcuts that let you quickly change certain font settings, including font, size, color, and attributes like boldface and italics. The Formatting toolbar sits in the row just under Excel's main menu and is described in more detail in Section 4.3.1. (Truth be told, the formatting toolbar is way more convenient for setting fonts, because its drop-down menu shows a long list of font names , whereas the font list in the Format Cells dialog box is limited to showing an impossibly restrictive four fonts at a time. Scrolling through that cramped space is more than a little maddening.)

Note: No matter what font you apply, Excel, thankfully, always displays the cell contents in the Arial font in the Formula bar. That makes things easier if you happen to be working with cells that have been formatted to use graphically complex or large fonts.

Figure 4-10. Here's an example of what happens when you apply an exotic font through the Format Cells dialog box. Keep in mind that, when displaying data and especially numbers, sans-serif fonts are usually clearer and look more professional than serif fonts. (Serif fonts have little embellishments, like tiny curls, on the ends of the letters ; sans-serif fonts don't.) Arial, the default spreadsheet font, is a sans-serif font. The font used for the body text of this book, Adobe Minion, is clearly a serif font, which works best for large amounts of text.

Tip: The default font in an Excel worksheet is Arial but you can change this setting easily. To do so, select Tools

Options, and then click the General tab. Next to the "Standard font" label are two drop-down menus where you can set the standard font and font size. The font you choose won't apply to existing worksheets, but Excel will use it every time you create a new worksheet.

4.2.2.1 Special characters

Most fonts contain not only digits and the common letters of the alphabet, but also some special symbols that you can type directly on your keyboard. One example is the copyright symbol , which you can insert into a cell by entering the text (C), and letting AutoCorrect do its work. Other symbols, however, aren't as readily available. One example is the special arrow character . To use this symbol, you'll need the help of the Wingdings font.

Wingdings is a special font included with Windows that's made up entirely of symbols like arrows and icons, none of which are found in standard fonts. You can try and apply the Wingdings font on your own, but it won't be easy, because you won't know which character to press on your keyboard to get the symbol you want. A better choice is to use Excel's Symbol dialog box. Simply follow these steps:

Choose Insert Symbol from the menu.

The Symbol dialog box opens, as shown in Figure 4-11.

Figure 4-11. The Symbol dialog box allows you to insert one or more special characters, such as the graphical icons from the Wingdings font. Alternatively, you can insert extended characters from any other font. (Extended characters are mostly non-English letters like Arabic or Hebrew letters.)

Choose the font that has the special character.

Excel selects the Wingdings font automaticallywhich is good news, because it has the most interesting symbols for you to use. In addition, you can find a few predefined special characters, like the copyright symbol, on the Special Characters tab of the Symbol dialog box.
Select the character in the character map, and then click Insert.

Alternatively, if you need to insert multiple special characters, just double-click each one; doing so inserts each symbol right next to each other in the same cell without having to close the window.

When Excel inserts a character from the Symbol dialog box, it doesn't change the font for the cell. What you'll actually end up with is a cell that has two fontsone for the symbol character and one that's used for the rest of your text. This works perfectly well, but it can cause some confusion. For example, if you apply a new font to the cell after inserting a special character, Excel adjusts the entire contents of the cell to use the new font, and your symbol will change into the corresponding character in the new font (which usually isn't what you want). These problems can crop up any time you deal with a cell that has more than one font.

Note: If you look at the cell contents in the Formula bar, you'll always see the cell data in the standard Arial font. That means, for example, that your Wingdings symbol won't appear as the icon that shows up in your worksheet. Instead, you'll see an ordinary letter or some type of extended non-English character, like .

4.2.3. Borders and Patterns

The best way to call attention to important information isn't to change fonts or alignment. Instead, place borders around key cells or groups of cells and use shading to highlight important columns and rows. Excel provides dozens of different ways to outline and highlight any selection of cells.

Once again, the trusty Format Cells dialog box is your control center. Just follow these steps:

Select the cells you want to fill or outline.

Your selected cells appear highlighted (see Figure 4-12).

Figure 4-12. You can remove a worksheet's gridlines, as shown here, which is handy when you want to more easily see any custom borders you've added. Select Tools Options from the menu, select the View tab, and then turn off the checkmark next to the Gridlines checkbox. (This affects only the current file, and wont apply to new spreadsheets.)

Note: The Gridlines setting has no effect on whether or not Excel adds the worksheet gridlines to a printout. You can control whether your borders appear in printed versions of your worksheet through the Page Layout setting, as described on Section 6.2.2.4.

Select Format Cells, or just right-click the selection, and choose Format Cells.

The Format Cells dialog box appears.

Head directly to the Border tab.

(If you don't want to apply any borders, skip straight to step 4.) Applying a border is a multistep process (see Figure 4-13). Begin by choosing the line style you want ( dotted , dashed, thick, double, and so on), followed by the color (Automatic picks black). Both these options are on the right side of the tab. Next, choose where your border lines are going to appear. The Border box (where the word "Text" appears four times) functions as a nifty interactive test canvas that shows you where your lines are going to appear. To make your selection you can either click one of the eight Border buttons (which contain a single bold horizontal, vertical, or diagonal line), or you can click directly inside the Border box. If you change your mind, clicking a border line will make it disappear.

For example, if you want to apply a border to the top of your selection, click the top of the Border box. If you want to apply a line between columns inside the collection, click between the cell columns in the Border box. The line appears indicating your selection.

Figure 4-13. Follow the numbered steps in this figure to choose the line style and color, and then apply the border. In this picture, a dashed vertical border will be applied between columns in the selection, and a thick solid border will be applied at the top edge of the selection.

Tip: The Border tab also provides two shortcuts in the Presets region of the tab. If you want to apply a border style around your entire selection, select Outline after choosing your border style and color. Choose Inside to apply the border between the rows and columns of your selection.

Click the Patterns tab.

Here you can select the color and pattern of any shading you want to add to the cells in the selection (see Figure 4-14). Click the No Color box to clear any current color or pattern in the selected cells.

Figure 4-14. Adding a pattern to selected cells is simpler than choosing borders. All you need to do is select the color you want and optionally choose a pattern. The pattern is always drawn in black, and can include diagonal lines, a grid, dots, or the tight checkerboard shown in this figure. Generally, patterns obscure text, and you shouldn't apply them to cells that have content. Fills tend to work better, provided you use light colors that will allow text or numbers to remain legible.

Click OK to apply your changes.

If you don't like the modifications you've just applied, you can roll back time with a quick application of the Edit Undo command.