Copying Formulas

As we probably all know, you can use the fill-handle (small black box that appears when you hover your mouse over the lower right corner of a cell or range) to drag (and therefore, copy) a formula down the rest of the range. Now that is fine when you have just a few cells, but if the range has any significant length, “dragging” can be, well, a drag!

A better solution is to hover your mouse over the handle (you will see it turn into a crosshair) and Double-click it. Bamm! You have copied your formula down the entire range. It doesn’t matter whether the range is 3 rows or 30,000, the results are for all practical purposes, instantaneous!

Now, let’s say we have something just a bit more complex. It is important to be aware of Absolute and Relative (I know, let’s keep our relatives out of this…) references. For instance, if you want to lock into a column, put a $ sign in front of the column (e.g. $B2). If you want to lock into a row, put a $ sign in front of the row (e.g. B$2).

Make a worksheet similar to the illustration below and try constructing the following simple concatenation formula in cell B2:   =$A2&" Sales in "&B$1

Then grab the handle on cell B2 and drag it through cell E5 and watch what happens…

Is that cool or what?!?! Go on, give it a try!