Page 141 - Lean six sigma demystified

120 Lean Six Sigma DemystifieD

The destination column is a bit more chal-
lenging. Data Validation will let you specify a
list of values, but travel destinations might be
too varied. Excel, however, will autocomplete a
cell after a few characters, so we could enter the
ten most common destinations in C2:C11
(Fig. 4-16). Then, when an agent starts to type
in that column, the destination will appear (e.g.,
San Francisco). If all trips originate from a com-
FIGURE 4-16 • Simplify data entry using mon destination (e.g., Denver), we could enter
autofill. routes as well.
Then, use Format-Row-Hide to hide rows 2–11 (Fig. 4-17). With rows 2–11
hidden, agents will be prompted with destinations and routes when they begin
to type. Now let’s look at another way to do this using a list.

FIGURE 4-17 • Hiding rows.

Time Sheet Example

Imagine an HR staffer trying to get valid time sheets using Excel. The hours are
rounded to the nearest half hour. But employees keep putting in values like
4:30 instead of 4.5. Here’s how easy it can be to solve that problem with data
validation.
First, in an empty column, enter 0 in the first cell (I2) and then a formula
(=I3+0.5) in the next cell and copy/paste the formula down to get 24 hours
(Fig. 4-18)