Filling Down

Often, we will use formulas to generate a table of values, as for a function. We use filling down to keep from having to enter the formula in each cell individually. It's very similar to the the Fill Series technique used to fill in a sequence of numbers in a column.

Creating a Table of Values

Let's create a table of values for the function f(x) = x2 - 2 x + 1. First, we fill in the x values in a table using the Fill Series technique:

 
A
B
1
x
y
2
0
 
3
0.5
 
4
1
 
5
1.5
 
6
2
 

In this case, we choose to use x values from 0 to 2 with a step value of 0.5.

Next, we enter the formula for y in the first cell in the y column, which is cell B2. Since the x value is right next to us in cell A2, we use the formula =A2^2-2*A2+1, using A2 for x.

Now we copy down in one of two ways:

Edit-> Fill -> Down
  1. Click on the first cell with the formula (in this case, cell B2), and drag to the bottom of the column to select the entire y column. (In our case, this would be down to cell B6.)
  2. Under the Edit menu, go to the Fill submenu, and select "Down."
Dragging down
  1. Click to select the first cell with the formula (in this case, cell B2).
  2. Click on the fill handle in the lower right corner of the selection, and drag this down to the end of the column. The formula will automatically be copied down.

In both cases, if you go back and inspect the formulas in column B, you will find that the formula has changed as it was copied down. Cell B3 for example, has the formula =A2^2-2*A2+1. This is because we have used a relative reference. Relative references tell Excel what the address of a cell is relative to the current cell, so that you can copy a formula to another cell and have it behave in the same way. See Relative and Absolute References for more information on this.

Return to Excel Guide