Databases Reference
In-Depth Information
TABLE 4.1
Proposed Voltage Levels for New Cars
Volts (V)
Current (mA)
Resistance (k)
12
0.01
1200
18
0.01
1800
24
0.01
2400
30
0.01
3000
36
0.01
3600
42
0.01
4200
48
0.01
4800
apply formatting to a cell in which an invalid entry has been made. Utilizing the conditional
formatting feature, Excel can dynamically change the formatting of cells based upon their
contents. For example, if a cell has a value greater than a specified maximum, or is changed to
a value greater than a specified maximum, the background of the cell can be made yellow (or any
color for that matter), and an outline can be placed around the cell. This type of functionality
obviously has a great deal of utility in the real world.
To show how to use the conditional formatting available in Excel, the Worksheet shown in
Table 4.1 will be utilized as an example. This table shows proposed voltage, current, and resistance
levels for future automobile electrical systems. Here, it is assumed that the current level will remain
constant and the system (battery) voltage can be increased. Table 4.1 shows how each 6-V increase
in system voltage will affect the resistance or load that can be driven.
Suppose, this table is contained within a Worksheet and it is desired to highlight the row with
the current electrical scheme utilized by most new cars, which is 12-V. This can be accomplished
by selecting from the menu F
o
rmat->Con
d
itional Formatting, which will bring up the GUI shown
in Figure 4.11.
To highlight the rows that contain a system voltage of 12, the following must be done. First,
in the drop-down box, the user must choose between “Formula Is” and “Cell Value Is.” In this
instance, it is necessary to utilize “Formula Is” option because the cells in a row must be colored
based on the value present in another cell (in this case col
A
, row
n
), not just the value within their
own cell. The following formula will accomplish this:
=
IF($A2
=
12,TRUE,FALSE)
=
TRUE
(4.7)
In this formula, the column is fixed (denoted by the $ in the cell reference $A2). When this formula
is painted across the other cells, the row number will change but the column number (
A
) will remain
FIGURE 4.11
Setting up conditional formatting.
