5.2. Formatting Cell AppearanceFormatting cell values is important because 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 a cell's appearance, first select the single cell or group of cells that you want to work with, and then choose Home Tip: 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. 5.2.1. Alignment and OrientationAs 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, in the Format Cells dialog box, the Alignment tab 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, which is known as the horizontal alignment . Excel offers the following choices for horizontal alignment, some of which are shown in Figure 5-10:
Vertical alignment controls the position of content between a cell's top and bottom border. Vertical alignment becomes important only 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 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. Excel gives you the following vertical alignment choices, some of which are shown in Figure 5-10:
If you have a cell containing a large amount of text, you may 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 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, 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. When you haven't turned on text wrapping, this action shrinks the row back to its standard single-line height. Finally, the Alignment tab allows you to rotate content in a cell up to 180 degrees, as shown in Figure 5-11. 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: You can use the Home ![]() | ||||||
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? You betcha. When 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 from the Format Cells dialog box's Alignment tab. 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, B1, and C1, you end up with a single cell named A1 that stretches over the full width of the A, B, and C columns , as shown in Figure 5-11. To merge cells, select the cells you want to join, choose Home |
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 colorizing big numbers. Here are the individual font details you can change:
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 uses its own algorithm to make the font bold or italicize it).
The font color . This option controls the color of the text. (Section 5.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 Home Cells
Format
Format Cells, and then click the Font tab (Figure 5-12).
|
Rather than heading to the Format Cells dialog box every time you want to tweak a font, you can use the ribbon's handy shortcuts. The Home Font section displays buttons for changing the font and font size. You also get a load of tiny buttons for applying basics like bold, italic, and underline, applying borders, and changing the text and background colors. (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 six fonts at a time. Scrolling through that cramped space is like reading the phone book on index cards.)
Without a doubt, the most useful ribbon formatting feature is live preview , a frill that shows you the result of a change before you've even applied it. Figure 5-13 shows live preview in action.
|
POWER USERS' CLINIC Formatting Individual Characters |
The ribbon lets you perform one task that you can't 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:
Applying multiple types of text formatting to the same cell can get tricky. The formula bar doesn't show the difference, and, when you edit the cell, you may not end up entering 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. |
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, youll need the help of Excel's symbols. Simply follow these steps:
Choose Insert Text
Symbol .
The Symbol dialog box opens, as shown in Figure 5-14. Now it's time to hunt for the symbol you need.
Choose the font and subset (the group of symbols you want to explore) .
If you're looking for a fairly common symbol (like a mathematical sign, an arrow, an accented letter, or a fraction), you probably don't need to change the font. In the Font box, keep the default selection of "(normal text)", and then, from the Subset box at the right, choose the type of symbol. For example, choose the Arrows subset to see arrow symbols that point in different directions.
|
If you want funkier alternatives, choose a fancy font from the Font box on the left. You should be able to find at least one version of the Wingdings font in the list. Wingdings has the most interesting symbols to use. It's also the most likely to be on other people's computers, which makes a difference if you're planning to email your worksheet to other people. If you get your symbols from a really bizarre font that other people don't have, they won't be able to see your symbols.
Select the character, 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.
There's one idiosyncrasy that you should be aware of if you choose to insert symbols from another font. For example, if you insert a symbol from the Wingdings font into a cell that already has text, then you actually end up with a cell that has two fontsone for the symbol character and one that's used for the rest of your text. This system 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 changes 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.
On the other hand, if you kept the font selection on "(normal text)" when you picked your symbol, then you won't see this behavior. That's because you picked a more commonplace symbol that's included in the font you're already using for the cell. In this case, Excel doesn't need to use two fonts at once.
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.
Select Home Cells
Format
Format Cells, or just right-click the selection, and then 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 5-15). 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 left 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 will appear. Make your selection either by clicking one of the eight Border buttons (which contain a single bold horizontal, vertical, or diagonal line), or click directly inside the Border box. If you change your mind, clicking a border line makes 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 selection, click between the cell columns in the Border box. The line appears indicating your choice.
Click the Fill tab .
Here you can select the background color, pattern color, and pattern style to apply shading to the cells in the selection (see Figure 5-16). Click the No Color box to clear any current color or pattern in the selected cells. When picking a pattern color, you may notice that certain colors are described as theme colors . These theme colors are a set of coordinated colors that change whenever you pick a new theme for your workbook, as described in Section 6.2.4.
To get a really fancy fill, you can use a gradient , which is a blend of two colors. For example, with gradients you can create a fill that starts out white on one side of a cell and gradually darkens to blue on the other. To use a gradient fill, click the Fill Effects button, and then follow the instructions in Figure 5-17.
|
|
Click OK to apply your changes .
If you don't like the modifications you've just applied, you can roll back time by pressing Ctrl+Z to trigger the indispensable Undo command.
If you need to add a border around a cell or group of cells, the Format Cells dialog box's Border tab does the trick (see Figure 5-15). However, you could have a hard time getting the result you want, particularly if you want to add a combination of different borders around different cells. In this situation, you have a major project on your hand that requires several trips back to the Format Cells dialog box.
Fortunately, there's a little-known secret that lets you avoid the hassle: Excel's Draw Border feature. The Draw Border feature lets you draw border lines directly on your worksheet. This process is a little like working with a painting program. You pick the border style, color, and thickness , and then you drag to draw the line between the appropriate cells. When you draw, Excel applies the formatting settings to each affected cell, just as if you'd used the Borders tab.
Here's how it works:
Look in the ribbon's Home Font section for the border button .
The name of the border button changes to reflect whatever you used it for last. You can most easily find it by its position, as shown in Figure 5-18.
Click the border button, choose Line Style, and then pick the type of line you want .
You can use dashed and solid lines of different thicknesses, just as you can in the Format Cells dialog box's Borders tab.
Click the border button, choose Line Color, and then pick the color you want .
Now you're ready to start drawing.
Click the border button, and then choose Draw Border .
When you choose Draw Border, your mouse pointer changes into a pencil icon.
Using the border pencil, click a grid line where you want to place your border (Figure 5-19) .
You can also drag side to side or up and down to draw a longer horizontal or vertical line. And if you drag your pointer down and to the side, you create an outside border around a whole block of cells.
|
|
To stop drawing, head back to the border menu, and then choose Draw Border again .
If you make a mistake, you can even use an eraser to tidy it all up. Just click the border button, and then choose Erase Border. The mouse pointer changes to an eraser. Now you can click the border you want to remove.
DESIGN TIME A Designer Worksheet |
Cells aren't the only part of a worksheet you can tweak. A little-known feature in Excel lets you change the appearance of the entire worksheet by applying a custom picture as a background. Just select Page Layout The designer background feature's really just for fun and Excel doesn't include it in the printout. To remove a background, just choose Page Layout |