In an
earlier part in this series, you learned how to boldface and italicize text, and
how to apply different styles, such as currency or date/time, to the text in
your spreadsheet. With those features, you’ve just touched the tip of the
iceberg in unlocking Excel’s text-manipulation potential. In this document, you’ll
learn how to handle more significant text alterations, including aligning text
in different parts of the cells, changing the way that text runs on the page,
and wrapping multiple lines of text into a single cell.

The formatting series

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
  • Resize rows
  • Resize columns

Part 3

  • Text formatting
  • Justify cell contents
  • Change the direction of the text in your spreadsheet
  • Word wrap text

Part 4

  • Automatically format cells based on their contents
  • Change the margins for your printed page
  • Add a header and footer to your printer spreadsheet

Justifying cell contents

In
Microsoft Word, in different situations, you’ve probably had to center text on
your page, or align text with the right hand margin of your document. You can
do this kind of activity in Excel, too. Consider the spreadsheet shown in Figure A.

Figure A
Not bad, but could use a little TLC.

The
spreadsheet shown in Figure A isn’t
really all that bad, but there are some unappealing visual elements that
detract from an otherwise decent chart. For example, in Column A, the Year
label is aligned at the left-hand side of the column, but the data is aligned
to the right-hand side. While this isn’t all that significant, if you’re using
an Excel spreadsheet to provide backup data for what you consider to be a
critical funding request, you need to do everything you can to put your best
foot forward, and that means making sure, in addition to providing accurate
data, you provide information in a way that is easy on the reader.

This
is where Excel’s ability to justify text in a cell comes into play. Excel
provides a couple ways to justify text.

Justify text using the Formatting toolbar

Like
most things formatting-related, Excel’s Formatting toolbar also comes in handy
to quickly justify text.

Figure B
These three buttons unlock Excel’s text justification potential.

Respectively,
these three buttons left align, center, and right align text in your cells. To
use the buttons, select the range of cells whose text justification you would
like to change, and then click one of these buttons. In the example shown in Figure A, I’d like to center the text
in cells A3 to A9. To do so, I selected these cells, and then clicked the Center button on the formatting toolbar.
I’d also like to right-justify the “Total” headings so that they are
a little closer to their values. Again, I just selected these two cells, and
then clicked the Align Right button
on the Formatting toolbar. The result is shown in Figure C.

Figure C
These aren’t huge changes, but do make the table a little prettier.

Justify text using Format | Cells

By going
to Format | Cells | Alignment, you can also use the three justification options
I talked about in the previous section, or you can select from a couple of
additional options. To use one of the justification options I talked about on
the Formatting toolbar, under “Text alignment”, go to the Horizontal
option and click the down arrow at the right-hand side of the drop down box. Figure D shows you an example detailing
most of the horizontal justification options available in Format Cells.

Figure D
An example showing some of the different justification options at your
disposal.

Note
that, in Format Cells, the left and
right justification options have the word “indent” after them. This
just means that you can apply an indentation to any text in the cell, just as
you would if you wanted to indent a paragraph in Microsoft Word. Note that, in
the example, I provided samples of both left- and right-handed indentation of
two spaces. In the left-handed example, the means that the text starts two
spaces indented from the left side of the cell. In the right-handed example,
the text is indented from the right-hand side of the cell. Also note that, in
the Fill example, I only typed the
word “Text” once into the cell. By using the Fill justification method, I filled
the cell with that word. If you have text that already fills up more than half
of the cell (maybe your cell reads “This text already fills up the cell”,
for example), then the Fill justification
reverts to Left justification since
there’s not enough room to copy the text.

For
reference, here is a look at the Format Cells dialog box. (Figure E)

Figure E
The Format Cells dialog window with the Alignment tab selected.

Justify text vertically

In
Excel, you can have a row that contains multiple lines, or, for some reason,
you might want to add height to your rows and align text both horizontally (as
we’ve discussed) and vertically. You
can tell Excel that you want to align text at the top of a cell, for example. See
Figure F for an example. To
vertically justify text, you first need to select the range of cells whose
vertical justification you would like to adjust.

Figure F
In this example, I’ve justified text in both directions.

You,
too, can accomplish this feat from Format | Cells. In the “Text alignment”
section, click the down arrow to the right of the “Vertical” drop
down selection box and choose your vertical justification.

Figure G
Choose your vertical justification selection.

You
get the idea about justification.

Word wrap text inside cells

One
challenge many Excel aficionados face is being able to fit everything across a
page, particularly for worksheets that need a lot of data to be presented. This
becomes even more of a challenge when you have comment columns that need a lot
of space. One way that Excel helps to alleviate this problem is by providing
the ability to wrap long lines of text in a single cell. So, instead of needing
to make a comment column as wide as the widest comment, you can wrap the text
and save a little horizontal real estate. An example of the problem is shown in
Figure H, with a potential solution
shown in Figure I.

Figure H
The text in this column is cut off. For the sake or argument, assume that
the cell can’t be much wider than it is now.

Figure I
With the text wrapped, you can read it all, but it doesn’t take up as much
space across the page.

To configure a cell, or range of cells, with text wrapping,
go to Format | Cells | Alignment. Under the “Text control” section, check the box
next to “Wrap text” option, and click OK. To see where the option
resides, take a look back at Figure E.
You might need to make minor adjustments to the column width to get things just
right. See the previous article in this series to learn how to do that.

Change the text direction

I’ve
talked quite a bit about the appeal of your spreadsheet. You can even add
somewhat unique touches to your spreadsheet that might make it stand out a
little and, again, provide yourself with a little extra space. In some tables,
you might have a bunch of columns for which you just need Yes/No answers to
certain questions. In the case of the sample sheet you’ve been seeing in this
document, you might want to know what kinds of features a particular vehicle
has. For example, you might add a column to track whether or not a car has
leather seats. The information in this column won’t take relatively much space,
but the headers will quickly eat up the width of the printed page. See Figure J for an example.

Figure J
Note how much space is required just to track the options on these
vehicles.

Excel
lets you format that heading text into a vertical format so it takes up less
space and looks a tad nicer. To change the direction of text
in a cell or range of cells, go to Format | Cells | Alignment. Using the
Orientation section, you can have your text flow in a number of different
directions. See Figure K for some
samples.

Figure K
You can choose either straight up and down, or can choose to rotate text
anywhere between -90 degrees and 90 degrees.

From
the Orientation section of the Format
Cells dialog box, choose your desired rotation and then click OK.

Figure L
Choose an orientation� any orientation.

For
the example I showed in Figure K, I
chose to rotate the text 75 degrees. This puts the text almost on its side, but still gives it a little bit of an angle. Why
did I choose this angle when I could have just gone all the way to a 90 degree
rotation and saved a little more space? For one reason: I wanted you to see
what happens to borders you apply to cells with rotated text. Look at Figure M.

Figure M
Note the borders to the left and right sides of each option heading
(columns F through J)

In Figure M, the borders in the heading
row (row 3), columns F through J are angled to match
the orientation specified for each text cell. If Excel didn’t automatically
make this adjustment, you’d end up with lines through your headings! Instead,
Excel gracefully and automatically makes the angle adjustment for you when you
change the direction of the text in a cell.

Taking control

Excel
provides you with a plethora of text formatting options, from boldface, to
currency format, to justification to orientation. Through creative combinations
of each of these features, you can make your Excel data look just about any way
you want.