This article was orginally published on September 9, 2005. A downloadable PDF version is also available.

By this
point, you’ve probably learned about the basics in Microsoft Excel, and
you’ve learned how to create some rudimentary formulas. Now, perhaps you’ve
created a useful spreadsheet, complete with formulas that calculate sales of
your inventory. Before you submit it to your boss, wouldn’t it be nice to make
some things stand out? Maybe you’d like to easily turn some values into dollar
figures or have Excel automatically highlight an inventory line for which you’ve
run out of stock. In this How do I…, and in future articles, series, you’ll learn to do these things, and more.

Before you start

Before
formatting your spreadsheet, it probably looks like a bland table of numbers. By
the end of the next three articles in this How do I… series, you’ll be
able to do the following:

Part 1

  • Boldface, italicize and
    underline cell content
  • Change the size and font of
    your text
  • Apply a default Excel style
    (i.e. dollar, percent, etc) to cells
  • Use date and time formatting in
    your spreadsheet
  • Apply shading

Part 2

  • Apply borders
  • Automatically format cells
    based on their contents
  • Resize rows and columns
  • Word wrap text
  • Justify cell contents
  • Change the direction of the
    text in your spreadsheet

Part 3

  • Change the margins for your
    printed page
  • Add a header and footer to your
    printer spreadsheet
  • Use all of the features of the
    Format Cells dialog box

The Formatting toolbar

If you’ve
used other Microsoft programs, you’re probably familiar with the use of the
Formatting toolbar, shown in Figure A.
In Figure B, see examples of what
the various buttons on the Formatting toolbar accomplish. To use one of the
buttons, select the cell, or range of cells, that you wish to format and then
click the desired button on the toolbar. Table
A
below outlines the Excel default styles that are available from the
Formatting toolbar.

Figure A

The Excel Formatting toolbar.

Figure B

An example of the results of some of the buttons on the
Formatting toolbar.

Table A

Task

Keyboard Shortcut

Description

Change
the font

Ctrl +
Shift + F

Click the
drop-down arrow next to the font name to see a list of the fonts available
for you to use in the selected cell or cell range. The keyboard shortcut just
takes you to the font name box. It’s easier to use the mouse.

Change
the size of the font

Ctrl +
Shift + P

Change
the size of the text in the selected cell or range. The keyboard shortcut
just takes you to the font size box. It’s easier to use the mouse.

Boldface
text

Ctrl + B

Boldface
the text in the selected cell or range.

Italicized
text

Ctrl + I

Italicize
the text in the selected cell or range.

Underlined
text

Ctrl + U

Underline
the text in the selected cell or range.

Left-justified text

Line the
text up with the left-hand side of the cell.

Centered
text

Center
the text in the cell.

Right-justified text

Line the
text up with the right-hand side of the cell.

Currency

Ctrl +
Shift + 4

Automatically
right-justifies the number in the cell and adds a dollar sign and two
decimal places. Numbers are automatically right-justified when you put them
into a cell.

Percentage

Ctrl +
Shift + 5

Right-justifies
the number in the cell and adds a percent sign to the end of the number.

Comma

Ctrl +
Shift + 1

Adds a
comma and two decimal places to the number.

Add a
decimal place

Adds a
decimal place to a number. For example, 10,000.05 becomes
10,000.050.

Remove a
decimal place

Removes a
decimal place from a number. For example, 10,000.050 becomes
10,000.05.

Common formatting toolbar options

There are
more options on the Formatting toolbar, some of which will be discussed later
in this article.

Font face, style, and size

If you’ve
ever used a word processor, you probably know all about fonts. When used
appropriately, fonts are a really easy way to make your work look good and
stand out.

There are
two ways to change the font in Excel: (1) use the Formatting toolbar or (2) use
the Format Cells dialog box. We’ll go over both methods here.

Use the formatting toolbar to change the font

The first
two options on the formatting toolbar control the font face and size in your
spreadsheet. By selecting a cell or range of cells and then using these
formatting toolbar options, you can select a different font and/or make the
font bigger or smaller, depending on your needs. (See Figure C)

Figure C

These two options control the font face and size.

To change
the font, select the cells upon which you’d like to foist the new font and
click the down arrow next to the current font name. A complete list of fonts
available on your computer comes up, with each font conveniently displayed in its
respective style, as shown in Figure D.

Figure D

Scroll up and down the list to find the font that suits your needs.

To apply a
new font, just select it from the list. That typestyle is then instantly
applied to your selection. If you don’t like it, press the Undo button on the
Standard toolbar (it’s the button with the rounded arrow pointing to the left)
or press Ctrl + Z on your keyboard. Both accomplish the same goal: undoing your
last action.

The font
size is managed in the same way. Click the down arrow next to the font size and
choose the size you want for your text. Unfortunately, the font size box
options are all the same size, so you’ll need to experiment a bit to find the
size that meets your needs.

In addition
to the font face and size, you can also change the font color using the
Formatting toolbar. At the right-hand side of the toolbar, find the
button with the “A” on it. (See Figure
E
)

Figure E

This button lets you quickly change the color of the font in your
spreadsheet.

Use this
button by first selecting the cell or cells for whose contents you’d like to change
the color and then click the down arrow immediately to the right of the button, as
shown in Figure E. The result will
be similar to the example shown below in Figure
F
.

Figure F

Select a color you like. It’s immediately applied to your selection.

Likewise,
if you want to apply boldface, italics, or underlining to your text, you can use
the corresponding buttons on the Formatting toolbar.

It’s as
easy as that! If you want more control
over your formatting options, you can opt to use the Format Cells dialog box
to make changes to your text.

Change the font with the Format Cells dialog box

The Format
Cells dialog box is your one-stop-shop for text formatting. Accessible from
Format | Cells (or right-click your cell selection and choose Format Cells
from the shortcut menu), click on the Font tab at the top of the dialog box to make
changes to the font in your selection. See Figure
G
below for an example.

Figure G

From this window, you can completely control the look of your text.

The main
drawback to the Format Cells dialog box is that it doesn’t list each font in
that font’s style — meaning that you need to individually select each font to see
what it looks like. As a shortcut, click your mouse on any font in the list and
then use your keyboard’s up and down arrow keys to move through the list. You’ll
save your wrist that way!

In addition
to the font face and size, you can change the font style — bold, italics,
regular—underline style, color — and apply some effects, including
strikethrough, superscript, and subscript, to your text. The Format Cells dialog
box includes a preview window so you can see the final result without having to
constantly go back to your spreadsheet.

Take note
of the Normal Font check box. Suppose you’ve made a bunch of changes
to your spreadsheet and you want to undo them all and just put things back to
the normal style — Arial font, 10 point, etc. Just select this check box and click
OK. Everything in the cell or range of cells you’ve selected will return to
this default style.

Numeric, date, and time formats

You’ve
already learned a little about how to apply basic numeric formatting to your
spreadsheet. To Excel, a date and time are also just numbers — the style you
assign to a cell determines its final look and feel. Why is this? Simply put,
Excel considers a date just another number. In fact, if you look deep, you’ll
find that a date, such as August 21, 2005, looks to Excel to be the number
38,595. This date — August 31, 2005 — happens to be 38,595 days since January 0,
1900.

No, “January
0” is not a typo–it’s Excel’s “day zero.” Some documentation
also indicates that Excel’s day zero is December 30, 1899. Excel also treats
the year 1900 as a leap year, even though it wasn’t. Like “January 0,”
this is by design. Excel acts this way to maintain compatibility with Lotus
123, which did have this bug. Excel
was introduced when Lotus 123 had a huge share of the market and to
make it easy for people to switch to Excel, Microsoft maintained the bug.

Since Excel
treats dates as numbers, you can easily do math on dates. Try this: The formula
“=now()” (those are parentheses) places the
current date and time into the cell. For example, entering “=now()” into a cell while I write this article returns
the following:

8/23/2005 23:49

Now, if I
enter “=now()-1” into a cell, I get the
following:

8/22/2005 23:49

Look
closely and note that this is just one day earlier.

This might
seem like a digression, but the discussion leads us to numeric formatting. Enter
a date into a cell, such as “8/31/2005” and then apply the comma
numeric format to the cell. See the example below in Figure H.

Figure H

Into both cells, I entered 8/31/2005. For cell A2, I applied the comma
numeric format.

To format a
cell as a date, you can just enter a date into the cell, such as “8/31/2005”
or “August 31, 2005”. Excel knows what a date is supposed to look
like and automatically changes the format of the cell to a date. However, if
for some reason, you need to change a cell or range of cells to date format,
do so by going to Format | Cells… (the keyboard
shortcut for this is Ctrl + 1). The Format Cells dialog box opens, which provides
you with a ton of options for changing the format of your cells. The Format
Cells dialog box is shown in Figure I.

Figure I

The Format Cells dialog box provides a way for you to assign any format you
want to a cell range.

You
probably get the idea about by now about how this kind of formatting works in
Excel. Let’s move on to another kind of formatting.

Shading

Use of
shading can create a spreadsheet that draws your reader to a specific
section — perhaps the section with the most important information, totals, and so
on. Consider the sample sales spreadsheet shown in Figure J.

Figure J

In this example, the “total” line is bold, but
it still doesn’t stand out very well.

You’ll
notice that the total line in Figure J
is boldface, but it still lacks something. Take a look at the same information,
but with shading added to make the total line stand out a bit more Figure K.

Figure K

The totals line now stands out from the rest of the sheet.

There are a
couple ways you can add shading to your sheet. First, you can use the
Fill Color button on the formatting toolbar. This is the quickest way to apply
shading to cells in your spreadsheet. Or you can use the Format Cells dialog box,
which requires a couple of additional steps but provides many more options.

Shading from the Formatting toolbar

Figure L

The Fill Color button on the formatting toolbar.

To use the
Fill Color button, make sure that the cell to which you’d like to apply shading is
the active cell in your spreadsheet. Then, click the down arrow next to the
picture of the can of pouring paint. This opens up a color selection window from
which you can select the color you’d like to apply to your spreadsheet. The
color selection window is shown in Figure
M
. As soon as you choose a color, it’s immediately applied to the cell or
range of cells you’ve selected in your worksheet. Keep in mind that just the
background color of the cell is changed. You’ll learn how to change the text
color in the next section.

Figure M

In this figure, the gray color is selected since that is the color
currently in use to shade the cell.

Shading from the Format Cells dialog box

The Format
Cells dialog box provides you with a place from which you can control just about
any formatting aspect of your spreadsheet. You saw the Format Cells dialog box
earlier in this tutorial when you were formatting cells for dates and times. To
shade cells using Format Cells, select the cell or cells you’d like to shade
and go to Format | Cells. Alternatively, you can right-click your cell or
selection and choose Format Cells from the shortcut menu. Both options bring
you to the same place — the Format Cells window. Once this window opens, choose
the Patterns tab (Figure N), which provides cell shading options.

Figure N

The Patterns tab of the Format Cells dialog box provides more options than the
Fill Color button on the Formatting toolbar.

Notice
first that there is a color section, plus a pattern selection drop-down box,
along with a sample box in the right-hand part of the window. Like you can from
the Fill Color button on the Formatting toolbar, you can just select a color and
click OK. However, you can also apply a pattern, such as diagonal lines, to
your spreadsheet. Even more, you can apply patterns of a specific color that
lie on top of whatever color you select as the background color for the cell. See
Figure O for an example.

Figure O

Note the three selections in this window that, when combined, form what you
see in the Sample box.

In Figure O, the background color for the
cell is yellow, and the pattern is a series of red diagonal stripes, which
result in what you see in the Sample box. Using the various combinations, you
can achieve a practically unlimited palette for your spreadsheet.

Once you’ve
made your selections, click the OK button. Your selections are immediately
applied to your spreadsheet.

More formatting

In this
part of the Excel How do I… series, you’ve explored some of the formatting
options available to you in Excel. In part two, you’ll learn more advanced
formatting features. In part three, you’ll learn about overall page formatting.