Elegant Excel Formatting Classy Columns, Robust Rows, and Nifty Numbers Helen Bradley
Thu 4/18/02 -- The most important feature of your Microsoft Excel worksheets should be their accuracy -- but there's no law that says they can't look nice, as well. Happily, Excel is so jam-packed with formatting features that it's easy to create great-looking, client-impressing spreadsheets -- as long as you avoid the AutoFormat tool. Word 2002's slightly more elegant AutoFormat lets you create and save your own custom table formats, but Excel's is still stuck firmly in the Dark Ages.
But while we wait for Microsoft to give that function a makeover, here are some ways to create worksheets that'll outshine your colleagues' or competitors'. Those poor souls are probably faithfully using AutoFormat.
Analyze before you format. Before you go wild with Excel's formatting tools, take a moment to look critically at your worksheet -- specifically, to ask yourself how a user is likely to view the data therein.
Will he or she look up a name or product number in column A, then follow the data across the row? Or will readers scan vertically, comparing the budget amounts in one column with the actual expenditures in the next? Maybe your on-screen worksheet tries to mimic the layout of a paper document such as an invoice. When you know how people will use your worksheet, you can format it appropriately.
Reading across a row. When users will be looking up entries in columns and following each entry's data horizontally, provide a visual guide to help them scan across the sheet without losing their place: format rows in different colors.
There are a couple of ways to do this. The hands-on method is to select the row, then format its cells by clicking the Fill Color button on the Formatting toolbar -- then repeating with another color for the row below, or leaving the latter plain white. Finally, select the first two rows by clicking their row letters, click the Format Painter button, and select the remaining rows in the worksheet to format them.
Formatting using a formula. If the above sounds like too much work, you can write a formula to format rows for you. To do this, select the cells to format and choose Format/Conditional Formatting. From the Condition 1 list choose Formula Is, and in the formula area, type =mod(row(),2)=0 to format each even-numbered row. Now choose a format using the Format button and click OK -- or, to apply a different format to each odd-numbered row, choose Add, repeat the process using the formula =mod(row(),2)=1, and click OK.
The formula simply takes the row number (returned by the row() function) and calculates its modulus, the number left over when the number is divided by two -- i.e., either 0 or 1. If the remainder is 0, the row is even-numbered and hence formatted one way; if it's 1, the row is odd-numbered and either left unformatted or, if you specified a second test and format, presented differently.
Counterparts for columns. As you'd expect, you can use either of the above methods to format data in columns as well as rows. If you're using the Conditional Formatting option, type column() in place of row() in the formula.
Formatting numbers with text. When you need to include a unit of measure along with a numeric value, you run into the problem of combining text and numbers in a cell. If you enter 25 lb in a cell, for instance, you won't be able to do anything such as multiply it by 2, because Excel treats the cell as text instead of a number. To get the number looking the way you want, but still be able to use it for calculations, create a custom format.
Select the cell, choose Format/Cells/Number, and click the Custom option; in the Type: area, type 0.00 "lb" and click OK. You can use the same process to add other text before or after any number in a worksheet. The 0.00 part of the format formats all the numbers to two decimal places -- use 0.0 for one decimal place, or 0 to round displayed numbers to whole numbers (none of these formats alters the underlying value, which remains the same).
Leave the leading zeros. If you want to enter numbers with a leading zero -- say, part number 01425 in your company's catalog -- you'll know that by default Excel zaps leading zeros from the worksheet. To force them to appear, use the process in the previous type to create a custom number format which reads 00000; all numbers will then be padded out to display at least five digits, and leading zeros will appear for numbers with four or fewer digits.
Copying formatting. When you have a cell, range, or whole worksheet formatted just as you like, you can copy its format to another cell, range, or worksheet using Excel's copy and paste options. Select the cells whose formatting you like; click Copy; select the cells to format in that way; choose Edit/Paste Special, the Formats option button, and click OK. You'll also find the Format Painter button can copy multiple formats from one area of a worksheet and paint them onto another (see the first tip above).
