Appendix F: Using Equations and the Fill Tool in Excel
Often you will use your data and a formula to calculate a quantity. This calculation might be repeated several times using the same formula with each set of data points. This tutorial shows you how to use the Fill tool in Excel to calculate the average velocity for six different values of distance and time.1
Enter the six values of time and distance in the first two columns, as shown in Fig. 1.
Figure 1: Entering your data
2
The average velocity is defined as the distance divided by the time it takes an object to travel that distance. To calculate the average velocity in Excel, select cell C2. Type =B2/A2 and press ENTER. Notice that this will highlight cells A2 and B2, perform the division, and put the answer in the box you selected (see Fig. 2 and Fig 3).
Figure 2: Typing in the formula
Figure 3: Calculating the value
3
Once you have calculated the value in the first box, you can use the Fill tool to copy the formula to boxes just below it. To do this, click the first box. A small black box is displayed in the bottom right corner of the cell. Click this small black box and drag it down as far as you need to copy the formula (see Fig. 4 and Fig. 5).
Figure 4: Dragging the formula
Figure 5: Copied formula
If you click cell C4 you will notice that the average velocity in cell C4 is calculated using
B4/A4 = 0.769.
Similarly, C5 is calculated using values in B5 and A5 and so on. So for each of the cells in column C where the formula has been copied, the corresponding values of columns A and B have been used.