Microsoft Office Tutorials and References

In Depth Information

**MAX and MIN—Recording Highs and Lows**

Figure 3–24.
Copying allowed, here: Dragging to copy Alice’s formula gives me the averages for all the

students

Then click anywhere to turn off the blue selection color. Here, the fill handle—that same device we

earlier put to the task of producing series of data—e.g., 1,3,5,7, days of the week, etc.—is used to
copy

formulas
. Just click on the first (which at the moment is the
only
formula, and drag the fill handle as far
)

as you need to go—either down a column or across a row. The copy procedure collaborates with relative

referencing to install the proper cell references in each cell, and what this means is that if you need to

copy a formula to many cells, you only need to actually write the formula
once
the formula that will
—

serve as the model to be copied to all other cells.

And once you learn how that capability works, there’s an even easier and cooler means to carry out

this kind of copying task. Once you write that first, model formula, click back on that cell (in this case

Alice’s average in I10), and
double-click
the fill handle. All the other student cells running down the I

column receive the formula, without you needing to drag the fill handle. The double-click
automatically

copies the original formula down
all
the cells that also have data in the immediately adjoining column to

its left or right.

To summarize this tip—if you need to copy a formula down a column—and this only works when

you copy down a column, not across a row—click on the cell storing the original formula and then

double-click its fill handle. As long as there are data in the
adjoining
column (either to the left or right;

and that means in our example if the H column were empty this wouldn’t work), the formula copies

down for as many rows as there are data. And this will work as surely for 20,000 rows as it will for 20. I use

this shortcut all time; I
told
you I was lazy.

Now there is one more permutation of this cell-reference copying business that you need to know.

Consider this case:

Suppose I’ve given my students a rather challenging exam, and, after having canvassed the sobering

results, decide to grant them an extra three points in order to curve the scores upward. My simple grade

book looks like Figure 3–25, at the outset: