Microsoft Office Tutorials and References
In Depth Information
Using the Format Cells Dialog Box
decimal places to the highlighted values. At the same time, Excel
automatically widens columns B, C, D, and E just enough to display all this new
formatting. In versions of Excel earlier than Excel 2003, you had to widen these
columns yourself, and instead of the perfectly aligned numbers, you were
confronted with columns of #######s in cell ranges E3:E10 and B10:D10. Such
pound signs (where nicely formatted dollar totals should be) serve as
overflow indicators, declaring that whatever formatting you added to the value in
that cell has added so much to the value’s display that Excel can no longer
display it within the current column width.
Fortunately, Excel eliminates the format overflow indicators when you’re
formatting the values in your cells by automatically widening the columns. The
only time you’ll ever run across these dreaded #######s in your cells is when
you take it upon yourself to narrow a worksheet column manually (see the
section “Calibrating Columns,” later in this chapter) to the extent that Excel
can no longer display all the characters in its cells with formatted values.
Currying your cells with the Comma Style
The Comma Style format offers a good alternative to the Currency format. Like
Currency, the Comma Style format inserts commas in larger numbers to
separate thousands, hundred thousands, millions, and . . . well, you get the idea.
This format also displays two decimal places and puts negative values in
parentheses. What it doesn’t display is dollar signs. This makes it perfect
for formatting tables where it’s obvious that you’re dealing with dollars and
cents or for larger values that have nothing to do with money.
The Comma Style format also works well for the bulk of the values in the
sample first-quarter sales worksheet. Check out Figure 3-10 to see this table
after I format the cells containing the monthly sales for all the Mother Goose
Enterprises with the Comma Style format. To do this, select the cell range
B3:D9 and click the Comma Style button — the one with the comma icon ( ) — ,
in the Number group on the Home tab.
Note how, in Figure 3-10, the Comma Style format takes care of the earlier
decimal alignment problem in the quarterly sales figures. Moreover, Comma
Style–formatted monthly sales figures align perfectly with the Currency
format–styled monthly totals in row 10. If you look closely (you may need a
magnifying glass for this one), you see that these formatted values no longer
abut the right edges of their cells; they’ve moved slightly to the left. The gap
on the right between the last digit and the cell border accommodates the
right parenthesis in negative values, ensuring that they, too, align precisely
on the decimal point.