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
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: