Anatomy of Excel formatting: Part 4

In
this final part in the Formatting Excel articles, you will learn how to format
the overall printed spreadsheet. You will also learn to automatically apply
formatting to cells in your spreadsheet based on their contents. For example,
if you enter a negative number into a cell, you can format that cell with a
dark border so that it is more pronounced.

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

Spreadsheet page formatting

In the
previous articles, I’ve provided you with information about
formatting your spreadsheet to make it look better and provide clearer
information to your audience. However, you can do even more to the overall
page to provide your audience with other important information, including the
exact date and time a spreadsheet was printed, and also automatically adds
column and row headings to every page in a multi-page spreadsheet.

Just
like any other Microsoft office
program, Excel lets you change the way your spreadsheet acts when it prints. The
usual suspects, including changing the page margins and the orientation
(landscape vs. portrait), are present, but Excel also provides other page
formatting options. For example, you can ask Excel to center your spreadsheet
on the printed page—horizontally, vertically, or both. You can also combine
multiple pages onto a single page by telling Excel to automatically fit any
number of pages to a single sheet. This can result in unreadable information
since Excel shrinks the size of the text, but can be useful.

All of
these settings are accessible from the menu at File | Page Setup, shown in Figure A.

Figure A

Page setup

Many
of these settings are self-explanatory. I’ll go over many of them, but
especially the ones that are less obvious.

Page orientation

This
one is easy and the Page Setup dialog box even shows you how your page will
print. Choose Portrait for a normal
print. Choose Landscape to print your
page sideways, which, for a spreadsheet, is often useful in helping you get
more columns on a single page.

Scaling

Sometimes,
you put together a spreadsheet that is just
a little bigger than a single page. In these cases, you might not want to
waste paper, or inconvenience your reader, by printing your work on multiple
pages. This is where Excel’s scaling feature comes in handy. Scaling
just means that your printed sheet will be rendered a little larger or smaller
by a certain factor, before it is printed. For example, if you scale
your work to 50% of normal size, it will print in half the space and the
information will be half as large.

Excel
provides two scaling options: Adjust and Fit. When you choose Adjust, you
change the width and height of your work by the specified percentage. If you
choose an adjustment percentage less than 100, your spreadsheet becomes
smaller, but more of it fits on each page; choosing a factor over 100% results
in larger text, and less information on the page.

Fit
provides an absolute way to make sure your work prints on a specific number of
pages. For example, if you have a column that is moving off the page to the
right, but you want your work to fit across on one page, tell Excel that you
want the work to fit to 1 page wide and leave the “tall” field blank.
This will let Excel print all of your columns across each page, but use as many
pages as necessary to get the information printed.

Paper size

This
is another of the mostly self-explanatory choices. This is where you get to
pick your paper size—letter, legal, 11×17, envelopes,
etc. Make sure the option you pick is supported by your printer.

First page number

By
default, Excel puts “Auto” into this option, meaning that, if you put
page numbers on your printed page (explained later), the
page number will start at “1”. If you want to start page numbering
with a different number, enter that number into this field.

The Margins tab

The
Margins tab has quite a few options (shown in Figure B), but is very straightforward to use.

Figure B

You can, surprisingly enough, change your page margins on this tab.

Your
page margin selections define how much space you want around the outside edges
of the page. New Excel worksheets have margins of 1″ at the top and bottom
of the page, with margins of ?” on the edges.

The
Header and Footer options on this page define the distance between the header
and the top of the page. Make sure that this distance is smaller than your top
and bottom margins. If you don’t, you run the risk of your header overlapping
your printed data. See Figure C for
an example of what could happen when you use a header and the top margin and
header margins are the same.

Figure C

Notice that the page heading overwrites some of the spreadsheet text.

See Figure D for some examples of what
happens when you change different margins.

Figure D

Margins

As you adjust your margins the area available for printable
text changes. In
the middle example, the top margin is 2″, while in the last example, the
left and right margins are each 1.5″.

Adding headers and footers

Spreadsheet
data, especially things like financial forecasts and inventory number, are
notorious for changing very quickly. Nothing is more frustrating than picking
up two similar, undated spreadsheets and trying to figure out which one has the
latest information. An easy way to alleviate your users of this frustration is
to print a header or footer on each page listing the date and time the work was
printed. Add or change your own headers and footers by going to File | Page
Setup | Header/Footer.

Figure E

Dialog header

Change
your header and footer to match your needs. I’ve included a sample custom
header and footer in this example. There are two other samples here that give
you a glimpse of Excel’s default headers and footers.

Changing
your headers and footers is easy, especially if you just want one of the
defaults provided by Excel. Among these defaults are:

• The page number
• The worksheet number or name
• The file name of the workbook
• The current date
• The name person who created the work
• Any combination of the above

As an
example of how the headers and footers work, take a look at Figure F, which is a printed version of
the sample inventory spreadsheet with the header and footer I used in the top
picture in Figure E.

Figure F

Header printed

The
date and time in the footer will always be the exact date and time that the
work was printed.

You
don’t have to use Excel’s built-in options, though. You can build any header
and footer you like for your own work by clicking the “Custom Header”
or “Custom Footer” button. (Figure
G)I’ll go over the Footer option here, but be aware that the procedure for
creating a custom header is the same.

Figure G

Just about anything you want can live in your custom footer.

The
custom footer dialog box has three sections, allowing you to place footer
information in the lower left corner, the lower right corner, and right in the
center of the bottom of your work. If you like, you can simply click one of the
areas and start typing; whatever you enter will appear on the printed sheet.

To
make it easy for you to add elements such as page numbers and the current date
and time, use the buttons in the center of the window. Table A below outlines the function of each button.

Table A

Note
that, when you use these items, Excel does not provide a caption. For example,
if you use the &[Tab] option, Excel will print
just “Sheet1”, not “Sheet:
Sheet1”. If you want a heading to appear, you need to type it in.

Add row and column headings to every printed page

The
final tab (Figure H) in the Page
Setup dialog box—Sheet—provides you
with a number of more advanced print options.

Figure H

Use the options on the Sheet tab to control what prints on your page.

Again,
I’ll go through each option.

Print area: Sometimes, you only want to print
part of your spreadsheet. Maybe you’re putting together a cost estimate for a
customer, for example, and you don’t want him to see your markup figures. Excel
makes censoring this information simple—just don’t include it on the printed
page. In the Print Area box, type in the cell range you want to print. You don’t
have to manually enter the information, though. You can also click the icon to
the right-hand side of the box and use the mouse to select the cells you want
to print. In the example I’ve been using in this series, if I limit the print
area to A1 to C4, I get the image shown in Figure
I. Note that all of the information outside this range is now missing. Also
look at Figure J, which provides you
with a look at a worksheet that has a print area assigned. Note the dashed line
at the edge of the print area.

Figure I

The print area is limited to cells A1 to C4. I still have the header printing, too.

Figure J

The dashed lines give you a visual cue that you have a print area assigned.

Print titles: Often, you have column and/or row
headings that make it easier for your audience to keep track of what they’re
reading. How often have you looked at a multi-page table of information and had
to flip back to the first page to find out what the information in one of the
columns means? Excel makes it really easy for you to include column (and row)
headings on all of your pages, without having to manually enter them at the
right places.

If,
for example, you want to repeat the column headings shown in the shot in Figure I, you need to repeat the first
row of the spreadsheet on every page. Again, you can either enter information
manually, or use the icon at the right of the titles choices to use the mouse
to choose a column or row. The format is similar to a cell reference, but only
uses one component of the cell address.

For
example, if you want to repeat the first row on every page, in the “Rows
to repeat at top” box, you would enter “$1:$1”, which is an
absolute row reference without a column reference. You could include multiple
rows at the top of each page, if you like (i.e. “$1:$4 would print the
first four rows at the top of every printed page). Column titles work the same
way. If you want to include the first column of your work on every page, into
the “Columns to repeat at left” box, enter “$A:$A”.

Figure K shows you what happens when you
tell Excel to include a row at the top of each page.

Figure K

Column headings

Note
that the column headings on these two pages are identical, but I did not have
to type them in manually.

Gridlines: If you check this box, Excel will
print all of the borders for every cell. This can be useful when you need your
reader to be able to easily follow information both across and up and down the
page.

Figure L

Gridlines put a whole lot of boxes and lines on your page!

“Black
and white” and “Draft quality”: These two options define how you
want your sheet to look on the printer and I can’t really provide examples for
you to see. Choose the Black and White option if you never plan to print your
work in color, and the Draft option to print your work with reduced quality,
which will make it print faster and might save some of your printer toner. Don’t
use draft for your final product, though!

Row and column headings: I showed you how to include
repeated rows and columns on each page, but this is a little different in that
it prints the Excel row and column
headings using just numbers and letters. Take a look at the sample in Figure M.

Figure M

Row and column headings

This
can be useful if you’re working on a spreadsheet collaboratively and need to be
able to quickly identify cells in your discussions.

Page order: Changes the direction that Excel
prints your work. See Figure N for
an example since, in this case, a picture really is worth a whole lot of words.

Figure N

Page order

When
you change the page order, bear in mind that your page numbers maybe change,
too. If you print “Down, then over” page 1 will be in the upper left
and page 2 immediately below. For “Over, then down” printing, page 1
is still in the upper left, but page 2 is to the right.

Conditional formatting

I saved
this topic for last. It hearkens back to cell formatting, but is a unique, and
sometimes incredibly useful, way to format the contents of your spreadsheet. It
solves something that can be a problem: The need to manually format individual
cells that meet certain criteria. For example, suppose in a budget spreadsheet
you could have Excel in some way highlight a budget line that is within 5% of
the annual limit, giving you an easy visual cue that you need to do something. This
feature is called Conditional Formatting, and is a fairly recent addition to
Excel.

The
best way to show you how useful conditional formatting can be is to show you an
example. In Figure O, I’ve provided
an excerpt from a much larger budget spreadsheet. Suppose that the real
spreadsheet is thousands of lines for a large organization.

Figure O

This is just a part of an imaginary budget spreadsheet with thousands of entries

Now,
suppose that, as the overall budget manager, you want to get a quick visual
look at potential problem areas for the rest of the budget year. As such, you
would like visual cues for the following:

• A budget line that is within 5% of being depleted. (Make
  text bold orange)
• A budget line that is depleted, but within 20%. (Make
  text bold red)
• A budget line that is depleted, and over by more than
  20%. (Make text bold white with red background)

On the
sample spreadsheet, this is easy since there are only a few lines, but, keep in
mind, for much larger sheets, scanning the Balance column could result in
missing a key number.

To accomplish this goal, go to Format | Conditional
Formatting, which opens the dialog box shown in Figure P.

Figure P

The Conditional Formatting dialog box

In
this dialog box are a number of options that allow you to format your cells
based on their contents. Basically, this gives you dynamic formatting rather
than the static formatting we’ve discussed to this point. At the top of the
window, note the text “Condition 1”. You can have up to three
conditions that apply different formats, depending on the cell contents. In
this example, we’ll have the three different conditions I indicated earlier.

The
first pull down box has two options:

• Cell Value Is: Evaluate a cell based on its
  contents. For example, you can tell Excel that you want only cells between
  two numbers. You can use formulas to determine these thresholds. For
  example, you can have a condition that says “For any cell between 0
  and 5% of the contents of cell B4”. I’ll demonstrate this in a bit.
• Formulas Is: You can also use a formula to
  dictate the condition. For example, you could have a condition that says “If
  the formula in the cell is ‘=NOW()’, make the
  result boldface.”

If you
select the Cell Value Is option, the next drop down asks for the condition to
meet. About any condition you would need is present, as shown in Figure Q.

Figure Q

These are the conditions provided by Excel for Conditional Formatting.

If you
choose an option that needs two numbers, such as “between”, two boxes
are made available in the window. For other options requiring only a single
number, only a single box is made available.

Into
the box (or boxes), enter the conditions you want to meet. For example, you
might say that you want the cell value to be between 0 and 100. You can also
use cell references in these boxes. For example, you could say that you want a
value to be between A1 and D4. If A1 has 50 in the cell,
and D4 has 100, that would make your condition “between 50 and 100”.

After
you choose your condition, choose the formatting you want to apply when the
condition is true. For example, you might make the text a different size or
color, or make it bold or italics, or you might put a border around the cell,
or add shading. Click the Format button to make your formatting selections. This
button opens up a limited version of the Format Cells dialog box that has been
discussed in this series.

Figure R

You’ve seen a similar Format Cells window quite often in this series.

If you
want to add another condition, click the Add button. You’ll see this in action
soon. To remove a condition, click the Delete button. When you’re done applying
conditions and formatting, click OK.

Now,
for the example at hand; the conditions are shown in Figure S.

Figure S

Conditional

This
example uses three conditions to achieve its goals. Note that this screenshot
was taken when the active cell was D4 from the sheet shown earlier in Figure O, which is why it references
cell B4. The same formatting is applied to all of the cells in column D, but
the values reference the right cells. For example, in cell D9, the conditions
reference cell B9.

• Condition 1: Basically says that, if the cell value is
  between 0 and 5% of the original budget amount, the cell text should be
  changed to orange.
• Condition 2: Says that, if the cell value is between
  negative 20% of the original value and 0 (up to 20% over budget) the text
  should be made red.
• Condition 3: Says that, if the cell value is more than
  20% over budget, the text should be made white and the cell background
  should be red.

After
setting up these three conditions, I used the “Format Painter” tool
to copy the formatting to the other cells in column D. To use the Format
Painter, click the cell with the formatting you want to copy (in this case, I
applied the conditional formatting rule to cell D4, so I clicked that cell),
then click the Format Painter button (it’s the button with the icon of the
paintbrush). Next, select the rest of the cells to which you want to apply this
formatting (in this example, I selected cells D5 to D13). The resulting
spreadsheet is shown in Figure T.

Figure T

Note that three cells now very much stand out and are quickly identifiable.

Now,
as you change values in your spreadsheet, the formatting in the balance column
will change automatically as conditions change. For example, suppose you update
your budget after doing more spending. Let’s assume that you spent another
$2,500 on overtime, $100 more in paper, $1,700 more on supplies, and $2,000
more on maintenance. To get the result shown in Figure U, I didn’t have to make any formatting changes at all. I
just changed the figured in the YTD spent column. Since the Balance column has
formulas that calculate the contents of each cell, the conditional formatting
changed automatically based on the new information.

Figure U

Note the changes to the formatting in the Balance column.

More to come

This
concludes the Excel formatting portion of this series. Coming soon in the Excel
series are articles on graphing, pivot tables, and much, much more!

Daily Tech Insider Newsletter

Stay up to date on the latest in technology with Daily Tech Insider. We bring you news on industry-leading companies, products, and people, as well as highlighted articles, downloads, and top resources. You’ll receive primers on hot tech topics that will help you stay ahead of the game.

Delivered Weekdays

Sign up today