Fill handle — number series and copying formulas

Hello students, welcome to Kwickprep. Your teacher asks you to number forty students, one to forty. Will you type forty numbers? No. One small drag can do it. Today we will learn the fill handle, how it makes number series, and how it copies formulas.

The fill handle is a small but powerful tool. It is the tiny square at the bottom right corner of the selected cell or range. When you point the mouse at it, the pointer changes to a thin plus sign. Now drag it down or across, and Calc fills the nearby cells for you.

Here are the steps to use the fill handle. First, type the first value, for example one, in cell A2. Then select that cell by clicking it. Next, point at the fill handle until the pointer becomes a thin plus sign. Now hold the left mouse button and drag down or across. Release the mouse, and the cells are filled.

Calc is smart about what it fills. Start with one and drag, and Calc makes one, two, three, four, adding one each time. Start with Monday, and it continues Tuesday, Wednesday, because Calc knows day names. Start with Jan, and it continues Feb, Mar, April, because it knows month names. Start with Roll 1, and it gives Roll 2 and Roll 3, increasing the number at the end.

A number series is a list of numbers that change by the same step. To set the step, type the first two numbers, select both cells, then drag the fill handle. Type two and four, and you get six, eight, ten, with a step of two. Type five and ten, and you get the five times table. Type one hundred, then ninety, and the series goes down, eighty, seventy, sixty. Type two thousand twenty and two thousand twenty two, and you get every second year.

Sometimes you want the same value in every cell, not a series. Hold the Ctrl key while dragging the fill handle, and Calc copies the same value. For example, one dragged with Ctrl held gives one, one, one. This is useful for repeating the class name, a section like 9 A, or a fixed fee.

For long or exact series, use the Fill Series dialog box. First, select the range, for example A2 colon A41 for forty students. Then open the Sheet menu and choose Fill Cells. Next, choose Fill Series. Set the start value to one and the increment, or step, to one. Click OK, and roll numbers one to forty appear.

Now the second big use of the fill handle, copying formulas. A formula is a calculation that starts with an equals sign. Here, B2 holds Term 1 marks, four hundred fifty. C2 holds Term 2 marks, three hundred eighty. D2 holds the formula equals B2 plus C2, and it shows eight hundred thirty.

Select D2 and drag its fill handle down to D5. Calc does not paste the same formula. In D2 the formula adds row two. In D3 it changes to equals B3 plus C3, adding row three. In D4 it becomes B4 plus C4. And in D5 it becomes B5 plus C5, so every student gets their own total.

Why did the formula change by itself? Cell addresses in a formula move along with the copy. When you drag down one row, every row number goes up by one. When you drag right by one column, every column letter moves one step, like B to C. Such addresses are called relative references, and we study them in detail in cell referencing.

Pause and predict. Cell E2 has the formula equals B2 times two. You drag E2 down to E6. What formula will E6 hold? Answer: E6 is four rows below E2, so it holds equals B6 times two.

Here is a time saver for long lists. Dragging the handle down four hundred rows is slow. Instead, type the first value, or the first two for a series. Then select from that cell down to the last row you want. Now press Ctrl+D, and Calc fills the whole selection at once. This is great when the class has forty rows, or a school has four hundred.

Let us revise what we learned today. The fill handle is the tiny square at the bottom right of the selected cell. Dragging it makes series of numbers, days and months. Type two values first to set your own step. Hold Ctrl while dragging to copy the same value. And when you copy a formula, its cell addresses adjust to each new row. Make a marks sheet today and fill the totals with one drag.