Fill Down

What I mean by "Fill Down" is to take the contents in one cell and quickly fill the rest of the column below that cell with the same value. You are many examples of when it is useful to do this, but copying a formula is certainly one of them.

Lets say you have to folling table of data, and have just written a formula in cell D2:


The first thing you could do is copy the cell (Ctrl + c), select the range you want to copy the formula into and paste it in (Ctrl + v).

But what if your column of data is very large? You don't want to have to select the whole range by hand. What you can do is this: put the curser on the cell D2. Move your mouse pointer so that it is over the bottom right corner of the cell. A smaller black "+" will appear. Now double click the left mouse button. Excel will automatically fill every cell in column D, down to the point where there is a break in the data in column C.

This is fine if there are no breaks in the data in colunm C, but what if you have the following situation?

Here, the table of data goes down to row 13, but there is no entry in column C on rows 7 and 11. If we use the double-click method above, only D2 to D6 will be filled.

We can get round this by using the following method:

1. Select the range you want to fill the formula over. You must start your selection with the cell in which you have your formula to be copied. This is an important point - you will see why in a minute.
   
   
2. Press the F2 button on your keyboard. This has the effect of opening the "active" cell for editing. You should see the cursor in the cell.
3. Now, press the Ctrl + Enter buttons at the same time. Whatever was in that first cell is now in all of your cells.
   

You may be wondering "If that just copies the formula, then I'll just have C2*B2 in all my cells. Not very useful." Not true. Excel automatically changes the contents of your formula as it goes down the page. The references to the cells C2 and B2 that are contained in cell D2 are known as "relative" references, in that they are relative to cell D2. If you look in cell D13 you will see the formula C13*B13.

[More on "relative" and "absolute" cell references to come...]