When you open an Excel worksheet, all the cells are the same size. Specifically, they’re 1.04 inches wide (the factory-set width of an Excel column) and 0.18 inches tall (the height of an Excel row). The good news is that there are several ways to set a cell’s height and width. Here’s a rundown:

Figure 13-2. The Format Painter can take everything but the data from the cells on the top, and apply it to the cells on the bottom.

There are any number of reasons why you may want to hide or show certain columns or rows in your spreadsheet. Maybe the numbers in a particular column are used in calculations elsewhere in the spreadsheet, but you don’t need them taking up screen space. Maybe you want to preserve several previous years’ worth of data, but don’t want to scroll through them. Or maybe the IRS is coming for a visit.

In any case, it’s easy enough to hide certain rows or columns. Start by highlighting the rows or columns in question. (Remember: To highlight an entire row, click its gray row number; to highlight several consecutive rows, drag vertically through the row numbers; to highlight nonadjacent rows, ⌘-click their row numbers. To highlight certain columns, use the gray column letters at the top of the spreadsheet in the same way.)

Figure 13-4. Choose Format → Conditional Formatting to make cells style their contents according to the conditions you set in this dialog box. Let Excel tell you when your finances are “in the red” by formatting any negative numbers with that eye-catching color. Use the pop-up menus in the first line to set the condition and then click the Format button to tell Excel how to format the cell when that condition is met.

Next, choose Format → Row → Hide, or Format → Column → Hide. That’s all there is to it: The column or row disappears completely, leaving a gap in the numbering or letter sequence at the left or top edge of the spreadsheet. The row numbers or column letters surrounding the hidden area turn blue.

Making them reappear is a bit trickier, since you can’t exactly highlight an invisible row or column. To perform this minor miracle, use the blue-colored row numbers or column headers as clues. Select cells on either side of the hidden row or column. Then choose Format → Row → Unhide, or Format → Column → Unhide.

Alternatively, you can also select a hidden cell (such as B5) by typing its address in the Name box on the Formula bar, and then choosing Format → Row → Unhide, or Format → Column → Unhide.

The light gray lines that form the graph-paper grid of an Excel spreadsheet are an optical illusion. They exist only to help you understand where one column or row ends and the next begins, but they don’t print (unless you want them to; see Sheet tab).

If you’d like to add solid, printable borders to certain rows, columns, or cells, Excel offers three different methods: the old, but most versatile Format Cells dialog box, the “Borders and Shading” section of the Formatting Palette, and the very similar Border Drawing toolbar. All techniques let you control how lines are added to the cell’s edges, but only the Formatting Palette and the toolbar let you change borders and shading without first opening a dialog box to make the changes.

To add cell borders using the time-honored Format Cells command, highlight some cells and then choose Format → Cells (or press ⌘ -1). The Format Cells dialog box appears; now click the Border tab to show the border controls. In this tab, you’ll see three sections: Presets, Border, and Line (see Figure 13-5).

To use Excel’s Formatting Palette to draw borders, select the cells to work with, and then open the “Borders and Shading” portion of the palette by clicking the “Borders and Shading” Title Bar.

In this section of the palette, you’ll see six controls that help you box in your cells and apply colors and patterns (Figure 13-6).

Figure 13-6. The Formatting Palette’s “Borders and Shading” section makes adding borders to your spreadsheet painless. Click the flippy triangle to the left of “Borders and Shading (or anywhere on that bar) to show or hide the controls. If you don’t see the Formatting Palette, choose its name from the View menu, or click the Toolbox button in the toolbar and then click its Formatting Palette button.

Here’s what each control does:

The first one, Draw Border, pops up so you can choose between two modes: Draw Border and Draw Border Grid. The Draw Border tool lets you create a border that encloses an otherwise unaffected block of cells just by dragging diagonally in your spreadsheet; the border takes on the line characteristics you’ve specified using the other tools in the toolbar. The Draw Border Grid tool works similarly, except that it doesn’t draw one master rectangle. Instead, it adds borders to every cell within the rectangle that you create by dragging diagonally, “painting” all four walls of every cell inside.

To erase borders from the spreadsheet, click Erase Border and then drag across any unwanted borders (or those painted in by mistake). Press Esc to cancel the eraser cursor. (The middle button, Merge Cells, is described on Merging cells.) The active extremity of the eraser cursor is the very bottom—which appears to sparkle.

Number formats in Excel add symbols, such as dollar signs, decimal points, or zeros, to whatever raw numbers you’ve typed. For example, if you apply Currency formatting to a cell containing 35.4, it appears in the spreadsheet as $35.40; if you apply Percentage formatting, it becomes 3540.00%.

What may strike you as odd, especially at first, is that this kind of formatting doesn’t actually change a cell’s contents. If you double-click the aforementioned cell that says $35.40, the trappings of currency disappear instantly, leaving behind only the 35.4 that you originally entered. All number formatting does is add the niceties to your numbers to make them easier to read.

To apply a number format, select the cells on which you want to work your magic, and then select the formatting that you want to apply. Excel comes prepared to format numbers using eleven broad categories of canned formatting. You get at them in any of three ways:

Each method gives the same broad categories of formatting; however, options in the Formatting Palette and toolbar are far fewer. Instead, they apply the most popular choice (for example, $ signs when you choose the currency formatting) without asking your opinion. The Format Cells dialog box, on the other hand, gives you more control over each format, along with a helpful preview of the result.

Figure 13-7. The Number section of the Formatting Palette provides quick access to common number formatting options via the Format pop-up menu. It also lets you increase or decrease the number of decimal places shown by clicking the Increase Decimal or Decrease Decimal buttons.

The following descriptions identify which additional controls are available in the Format Cells dialog box:

To add or remove decimal places, turning 34 and 125 into 34.00 and 125.00, for example, click the Decimal buttons in the Formatting Palette, as shown in Figure 13-7. Each click on the Increase Decimal button (on the left) adds decimal places; each click on the Decrease Decimal button (on the right) decreases the level of displayed precision by one decimal place.

Excel lets you control the fonts used in its sheets via the Font portion of the Formatting Palette. As always on the Macintosh, highlight what you want to format, and then apply the formatting—in this case using the Formatting Palette.

Of course, you can highlight the cell(s) you want to format using any of the techniques described on Selecting Cells (and Cell Ranges). But when it comes to character formatting, there are additional options; Excel actually lets you apply different fonts and font styles within a single cell (but not for formulae). The trick is to double-click the cell and then use the I-beam cursor to select just the characters in the cell that you want to work with—or select the characters in the Edit box of the Formula bar. As a result, any changes you make in the Formatting Palette affect only the selected characters.

Once you’ve highlighted the cells or text you want to change, open the Fonts section of the Formatting Palette (Figure 13-9) to reveal its four main controls:

Whether you want a funky new font to lighten up your serious number crunching, or you want to switch back to the Geneva 9-point of your childhood, you can make that your standard font choice with a quick trip to the Excel → Preferences → General panel (Figure 13-10). After you change the Standard font and Size (the controls are right in the middle of the General panel) and click OK, Excel displays a warning message, noting that you have to quit and restart Excel before the new formatting takes effect in new worksheets.

If you want to start all your new Excel spreadsheets with more than just a different font, you can create a template containing a variety of fonts, default text (your company name in the header, for example), formulas, and any other kind of custom formatting. Create a template exactly the way you’d like to see every new Excel spreadsheet begin life, choose File → Save As, name it Workbook, and turn off the “Append file extension” checkbox. Choose Excel Template (.xltx) from the Format pop-up menu and save the file in the Microsoft Office 2008 → Office → Startup → Excel folder. You can create a similar template for new worksheets by making a one-worksheet template named Sheet and saving it in the same location. From now on all new workbooks started by pressing ⌘ -N or worksheets added when you choose Insert → Worksheet are based on those files.

If you later decide to change your standard workbook or worksheet template, follow the same procedure and replace the Workbook and Sheet Templates with new ones. If you’d rather return to the standard, completely blank workbook and worksheet appearance, just delete those two templates.

To make broader changes, that you can use optionally, instead of every time, you can create another template—a generic document that can be used over and over to start some of your new Workbooks. Because a template can hold formatting and text, it’s a great base for a Workbook that you redo regularly (such as a monthly report).

Figure 13-10. The Excel Preferences → General pane lets you set what you’d like to see every time you start Excel. You can control the number of sheets in a new workbook, the font and font size, your preferred location to save Excel files, and even have an entire folder full of Excel documents open all at once. (Microsoft appears to be betting on the popularity of bigger and bigger monitors—you can choose font sizes up to 409 points!)

To make a template, create a new workbook or a copy of one that already looks the way you like it. You can select the entire sheet or specific sections of it, apply formats (as described in this chapter), and even include text (column headings you’ll always need, for example). When you finish formatting the sheet, choose File → Save As. In the Save dialog box, enter a name for the template in the “Save As” field, and then choose Excel Template (.xltx) from the Format menu. Excel gives your file an .xltx extension and switches the Where pop-up menu to “My Templates.” Click Save.

Back in Excel, close the template workbook.

Thereafter, whenever you’d like to open a copy of the template, choose File → Project Gallery. Click the My Templates category, and double-click your template.

Ordinarily, Excel automatically slides a number to the right end of its cell, and text to the left end of its cell. That is, it right-justifies numbers, and left-justifies text. (Number formatting may override these settings.)

But the Formatting Palette gives you far more control over how the text in a cell is placed. In the Text Alignment section of the palette (Figure 13-9, bottom), you’ll find enough controls to make even a hard-core typographer happy:

Every now and then, a single cell isn’t wide enough to hold the text you want placed inside—the title of a spreadsheet, perhaps, or some other heading. For example, the title may span several columns, but you’d rather not widen a column just to accommodate the title.

The answer is to merge cells into a single megacell. This function removes the borders between cells, allowing whatever you put in the cell to luxuriate in the new space. You can merge cells across rows, across columns, or both.

To merge two or more cells, select the cells you want to merge, verify that the Text Alignment portion of the Formatting Palette is open, and then turn on the Merge Cells checkbox, shown in Figure 13-11.

To unmerge merged cells, select the cells and turn off the Merge Cells checkbox; the missing cell walls return. Note, however, that although the combined space returns to its original status as independent cells, any data discarded during the merge process doesn’t return.

You can also merge and unmerge cells by using the Format Cells dialog box. To do this, select the cells to merge, then press ⌘ -1 (or choose Format → Cells or Control-click the cells and choose Format Cells from the contextual menu). In the Format Cells dialog box, click the Alignment tab, and then turn on (or turn off) the Merge cells item in the Text control section.

Figure 13-11. Because Excel treats merged cells as one big cell, you can align the contents of that cell any way you’d like; you don’t have to stick to the grid system imposed by a sheet’s cells. One typical use for this is centering a title over a series of columns. Without using merged cells, centering doesn’t do the job at all. When you merge those cells together and apply center alignment, the title is happily centered over the table.

To insert a picture, use the Insert → Picture submenu, which presents five options. Here’s a summary:

The Insert menu works splendidly, but it’s a little slow and stodgy. In Excel 2008 you can add shapes, clipart, symbols, and photos with a quick click on the Object Palette. To access them, click the Object Palette button in the Toolbox. The palette is divided into four sections—an objects section and a graphics section. The object section includes:

Choose Insert → Movie to open the Insert Movie dialog box with which you can locate any QuickTime movie on your Mac to make part of your worksheet. Select it, and then click Choose. The movie appears with its upper-left corner in the selected cell. You can then resize and reposition it, and then double-click the cinematic masterpiece to play it (see Converting object style). You can insert a sound file in exactly the same way—it lands on your spreadsheet as a loudspeaker icon.

Choose Insert → Text Box; Excel transforms your cursor into a letter A with a crosshair icon. Drag it diagonally anywhere on your spreadsheet to create a text box. When you release the mouse button, the insertion point begins blinking inside, awaiting your text entry. Move your cursor toward any of the text box’s edges until it takes on a four-arrow shape—then you can drag and reposition the box. Surprisingly, unlike text boxes in Word, Excel text boxes feature the green rotating handle sprouting from their top. Drag it to rotate the box.

Excel text boxes are ready for text editing with one click. If you want to change the look of the text box itself—its fill color, line, shadow, and so on—you have the entire Formatting Palette at your disposal (see The Formatting Palette).

If you don’t want to take the time to format the appearance of a text box one element at a time, try the Formatting Palette’s Quick Styles and Effects pane. Click one of the six effects tabs, and then click one of the style thumbnails to apply it to your text box. You can combine the effects to quickly create various 2-D and 3-D effects using shadows, reflections, glows, and so on (see Shadow Tab).