March 12, 2011, 9:06 a.m.
posted by antir
Formatting Cell Appearance
Formatting 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 Cells Format Format Cells, or just right-click the selection, and then choose Format Cells. The Format Cells dialog box that appears is the place where you adjust your settings.
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.
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, 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:
Left (Indent) tells Excel to 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.
Right (Indent) tells Excel to 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.
Figure. Left: Horizontal alignment options in action.
Right: This sheet shows how vertical alignment and cell wrapping work with cell content.
Justify is the same as Left if the cell content fits on a single line. When 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 is a bit of an oddity. When 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 happens only as long as the other cells are blank. This setting may confuse you a bit at first because it can lead to cell values being displayed over cells in which they aren't stored. Another approach to centering large text titles and headings is to use cell merging (as described in Section 5.2.2).
Distributed (Indent) is the same as Centerif the cell contains a numeric value or a single word. If you add more than one word, then 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 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:
Center tells Excel that the block of text should be centered between the top and bottom border of the cell.
Bottom tells Excel that the last line of text should end at the bottom of the cell. If the text doesn't fill the cell exactly, then Excel adds some padding to the top.
Justify is the same as Top for a single line of text. When 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 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 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. 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 Alignment section of the ribbon to quickly change alignment, indenting, rotation, and wrapping, without opening the Format Cells dialog box.
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 colorizing big numbers. Here are the individual font details you can change:
|FREQUENTLY ASKED QUESTION|
Shrinking Text and Merging Cells So You Can Fit More Text into a Cell
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.
To merge cells, select the cells you want to join, choose Home Cells Format Format Cells, and then, 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 want to go crazy.) And if you change your mind, don't worryyou simply need to select the single merged cell, choose Home Cells Format Format Cells again, and then turn off the "Merge cells" checkbox to redraw the original cells.
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).
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.
|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."
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.
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 shows live preview in action.
Note: No matter what font you apply, Excel, thankfully, always displays the cell contents in the formula bar in easy-to-read Calibri font. That makes things easier if you're working with cells that've been formatted using difficult-to-decipher script fonts, or really large or small text sizes.
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 Excel's symbols. Simply follow these steps:
Choose Insert Text Symbol.
The Symbol dialog box opens, as shown in Figure. Now it's time to hunt for the symbol you need.
Figure. The Symbol dialog box lets you insert one or more special characters. You can choose extended characters that are supported by most fonts (like currency symbols, non-English letters, arrows, and so on). Alternatively, you can use a font that's all about fancy characters, like the Wingdings font that's chock full of tiny graphical icons.
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.
Note: Wingdings is a special font included with Windows that's made up entirely of symbols like happy faces and stars, none of which you find in standard fonts. You can try and apply the Wingdings font on your own (by picking it from the font list), but you won't know which character to press on your keyboard to get the symbol you want. You're better off using Excel's Symbol dialog box.
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.
Tip: If you're looking for an extremely common special character (like the copyright symbol), you can shorten this whole process. Instead of using the Symbols tab, just click over to the Special Characters tab. Then, look through the small list of commonly used symbols. If you find what you want, just select it, and then click Insert.
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, 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.
Note: When you look at the cell contents in the formula bar, you always see the cell data in the standard Calibri font. This consistency means, for example, that a Wingdings symbol doesn't appear as the icon that shows up in your worksheet. Instead, you see an ordinary letter or some type of extended non-English character, like æ
Borders and Fills
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.
Applying a border is a multistep process (see Figure). 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.
Figure. Follow the numbered steps in this figure to choose the line style and color, and then apply the border. In this picture, Excel will apply a solid border between the columns and at the top edge of the selection.
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.
Tip: The Border tab also provides two shortcuts in the tab's Presets section. 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. Choosing None removes all border lines.
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). 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 22.214.171.124.
Figure. Adding a pattern to selected cells is simpler than choosing borders. All you need to do is select the colors you want and, optionally, choose a pattern. The pattern can include a grid, dots, or the diagonal lines shown in this figure.
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.
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.
Tip: You can remove a worksheet's gridlines, which is handy when you want to more easily see any custom borders you've added. To remove gridlines, select View Show/Hide Gridlines. (This action affects only the current file, and won't apply to new spreadsheets.)
Drawing Borders by Hand
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). 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.
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 gridline where you want to place your border (Figure).
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.
Figure. When you click the border button (circled), you see a list of border-customizing commands. Before you draw any borders, it makes sense to customize the border style. For example, you could choose Line Style, as shown here, and for color, you'd choose Line Color.
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.
Tip: If you don't want to use the Draw Border feature, you can still make good use of the border button. Just pick a line style and line color, select some cells, and then choose an option from the border menu. For example, if you pick Bottom Border, Excel applies a border with the color and style you chose to the bottom of the current cell selection.