<<

. 56
( 62 .)



>>

[White]
u

[Blue]
u

[Green]
u

[Red]
u

[Yellow]
u


In the zero format above, you can see that we do not have
to put in a number format. Instead, we can directly enter a text
string line ˜˜n/a.™™ It is also possible to combine text strings
and formats. Here, we continue our experimental formatting by
saying that we want the positive number to appear as one
decimal place and with the label ˜˜ months™™ (note the space at
the beginning of the label):
#,##0.0 “ months”_);[Red](0.000);[Blue]“n/a”
With this formatting, this is what Excel will show for the
following entries:

Enter thisEEE And it will appear asEEE In this color
1234 1,234 months Black
(1234.000) Red
À1234
0 n/a Blue


With custom formats, however, you are limited to changing
only the numeric format of the cell and the color of the font.
You cannot have the custom format change anything else. If
you do want these additional controls, see the following section
on conditional formatting.




TLFeBOOK
Chapter 18
308




I have combined the formats together in this way just to
illustrate the variations possible. With these possibilities in mind,
you can now set custom formats in your models that will high-
light the information in the best way possible.

CONDITIONAL FORMATTING
Conditional formatting is a more advanced form of formatting.
It allows you to change many more of the attributes of a cell and
can be a useful alternative to using just Excel™s custom formats
you can change:
The color/bold/italic of the font
u

The border settings for the cell
u

The background (pattern) color of the cell
u




Defining Conditional Formats
Start by putting your cursor on the cell you want to format. Then
click on Format > Conditional Formatting to see the dialog box
shown in Figure 18-1.
This is the setting for Condition 1. There are three con-
ditional formats that you can apply in a cell, and you can set the
other two by pressing on the Add >> button.
For any conditional format, you can choose to set it by one
of two methods:
1. By the ˜˜Cell Value Is™™ method. In this setting, the
conditional format will be driven by the current cell
contents, which you can set using the condition operator
FIGURE 18“1




TLFeBOOK
Bells and Whistles 309




(e.g., ˜˜between,™™ ˜˜greater than,™™ ˜˜less than or equal to,™™
etc.) that is part of the dialog box. Depending on this
choice, the dialog box will then show you inputs for one
or two values.
2. By the ˜˜Formula Is™™ method. In this setting, the format will
be determined by a formula that can refer to a cell or cells
outside the current cell. (The formula can also refer to the
current cell.) You will then have to define the condition
that will trigger the conditional format in the formula. If
the condition or set of conditions gets very complicated,
you should make use of the other two settings available.
Be careful when you write the formula in the ˜˜Formula Is™™
method. You should define it the way you write a formula in
a cell. In other words, make sure you begin the formula with the
equal (¼) sign. Otherwise, the formula will be regarded by Excel
as a text string, much as any formula written without the
beginning equal sign becomes a text string when entered in a
cell. If you have written a conditional formula and it does not
seem to be working, go back to this dialog box and make sure
that it has not been entered as a text string.
Once you have defined the trigger for the conditional for-
mats, click on the Format button to define the various attributes
of the cell. If the starting cell has the plain, regular formatting,
and you want it to change many of the attributes (e.g., to
a cell with a bold, italic font, with borders and a different
pattern background), it may be easier to ˜˜flip™™ how the condi-
tional format works. In other words, make the starting cell
carry the fancy attributes and have the conditional format be
the ˜˜plain, regular™™ attributes. Of course, you have to make
sure that the logic of the conditional format is working in the
correct way.
To delete conditional formats, click on the Delete. . . button
and you can delete any or all of the formats.


Finding Cells That Have Conditional Formats
Be careful about changing your worksheets after you define con-
ditional formats. The formatted cells can stay in their original




TLFeBOOK
Chapter 18
310




locations, even when new rows and/or columns have been
inserted. A good way to check the location of cells that have
conditional formats in them (which can remain apparently
undifferentiated on the screen) is to use the F5 (Go to) key
and then to click on the Special button on that dialog box. On
the Special form, click on the Conditional Formats. This will
cause any cells on the screen with conditional formats to be
highlighted.


HIDING ROWS FOR PRINTING
The model we have been building is simple, but I hope that it
will form the basis of more complex models that you will be
building yourself. Once you have the basic model working, it
will be easy to add more rows to capture more types of accounts.
In fact, it is likely that you will want to develop a model that will
include many types of accounts, not all of which will be used in
any one modeling run. This will result in many printouts in
which there will be some lines that contain nothing but zeroes.
The model will continue to work in terms of its calculations, of
course, but as a matter of visual polish, you may want to have
those rows somehow hidden, whether for viewing on the screen
or for printing.


Hiding Rows with the Group Command
There is a way of hiding rows easily, using the Data > Group and
Outline > Group command, but this involves first highlighting
the row or rows you want and then going through this sequence.
A margin automatically appears on the left edge of the screen,
which shows you the rows that you can hide. By clicking on the
Minus button, you can hide the rows bracketed by the grouping
line. The Minus button changes to a Plus button after you do
this; clicking on this unhides the rows again. Alternately, you can
click on the small numbers buttons at the top of the margin
space. This hides and unhides according to the levels.
While this method gives a way of quickly hiding and
unhiding rows, the disadvantage is that you will have to do
this for each company that you are modeling in your model.




TLFeBOOK
Bells and Whistles 311




Different companies will be filling in different lines in the model,
so you will have to go through the grouping sequence again and
again for this to work properly.


Hiding Rows with Formulas
Here is another way of hiding rows with zeros, and we can make
this work automatically for any set of numbers that you have in
the model. This is the Autofilter command, which can be invoked
through the Data > Filter > Autofilter sequence. The Autofilter
feature is a way of filtering out (read: hiding) rows. It can be
used for columns, too, but for our case, we need it only to
hide rows.
The basic idea for the Autofilter feature is that it will show
only those rows that contain the desired marker in a particular
column. In our case, we will insert a new column at the extreme
left (a new column A), which will contain the letter ˜˜y™™ (for yes)
for the rows we want to print. By activating the Autofilter feature
on this column, Excel will show only those rows with the letter
˜˜y™™, automatically hiding the other rows that do not have this
marker.
But, of course, we want to avoid actually typing in the letter
˜˜y™™ for each row we want to print. This would be too tedious
and violates the principle of letting Excel do most of the work
for us. The solution is to write a formula in column A that tests
that row for numbers other than zero and returns a ˜˜y™™ if the
row meets this criterion. So these are the steps we have to do:

Insert a New Column A
Highlight the whole column of column A. Then use the Excel
command to insert a new column: Insert > Column.

Adjust the Width of Column A
Adjust the column width to 3 by Format > Column > Width.

Write the Formula
This is going to be a long formula and, in essence, what we want
is a way to identify the rows that the Autofilter command can act




TLFeBOOK
Chapter 18
312




on. The Autofilter command can be set to show only those rows
that contain a certain content. For our formula we will simply set
the Autofilter to do the following:
Show the row if column A shows the letter y.
Hide the row if column A shows the letter n.
Thus the task of the formula we want to write is that it
should return a ˜˜y™™ if the row contains numbers other than
zeros. Likewise, it should return an ˜˜n™™ if the row contains
only zeros. (The Autofilter command will work on these letters
whether they are in uppercase or lowercase.)
Let™s assume that row 6 is the row that we want to test.
The columns we want to test are C to H. So let™s start by just
writing the simplest possible formula. You will see that we will
go through several versions of the basic formula, depending on
how ˜˜smart™™ we want the test to be:

¼IF(SUM(C6:H6),“y”,“n”)

The SUM can work, but we run the risk of having a row that
has positive numbers and negative numbers the sum of which
net to exactly zero (a row for deferred tax buildups and reversals
nets out to zero), in which case the formula will return an ˜˜n™™
when it should be a ˜˜y.™™ Let™s use the MAX function:

¼IF(MAX(C6:H6)<>0,“y”,“n”)

This is an interesting formula because it will work if the
numbers on the line are positive or negative or both. However,

<<

. 56
( 62 .)



>>