Copying a formula

Hello students, welcome to Kwickprep. Your class has forty students, and each needs a total of five subjects. Will you type forty formulas? No. Today we will learn how to copy one formula to every row, and why the cell addresses change by themselves.

Let us first learn four words. A cell is one box of the sheet, where a row and a column meet. A cell address is its column letter followed by its row number, like B2. A formula always starts with an equals sign and calculates a value, like equals B2 plus C2. Copying means reusing that one formula in many other cells.

Here is the sheet we will use. Cell A2 holds the student name, Riya. Cell B2 holds her Hindi marks, seventy two. Cell C2 holds her English marks, sixty five. Cell D2 holds the formula equals B2 plus C2, which shows one hundred thirty seven.

The first method uses copy and paste. Click cell D2, which has the formula. Press Ctrl+C, and a moving dotted border appears around the cell. Now select the cells where you want the formula, from D3 down to D41. Press Ctrl+V, and every row now shows its own total.

The second method is faster. The fill handle is the small square at the bottom right corner of the selected cell. When you point at it, the pointer changes into a thin black plus sign. Drag it down over the cells, and the formula is copied as you drag. If the next column has data, just double click the fill handle to fill down to the last row.

The third method is the Fill command. Select D2 together with the empty cells below it. Press Ctrl+D, and the top formula fills all the cells below. Ctrl+R does the same thing towards the right. You can also find Fill on the Home tab, in the Editing group.

Now look closely at the copied formulas. In D2, the formula is B2 plus C2. When copied one row down to D3, it becomes B3 plus C3. In D4, it becomes B4 plus C4, so each student gets the right total.

This happens because of relative references. A normal address like B2 is called a relative reference. The spreadsheet remembers it as a position, like two cells to the left of the formula. So when you copy the formula down, the row numbers change. When you copy it across, the column letters change.

Sometimes this automatic change is a problem. Suppose cell C1 holds one fixed discount rate, zero point zero five, and column B now holds fees. In D2, B2 multiplied by C1 gives the correct discount. Copied down, D3 becomes B3 multiplied by C2, but C2 is not the rate. D4 also points to the wrong cell, so the answers are wrong.

The fix is an absolute reference, which does not change when copied. Write it as dollar C dollar one, and both the column and the row are locked. A dollar before the letter locks only the column. A dollar before the number locks only the row, and these two are called mixed references. In Excel, press F4 while typing the address to add the dollar signs quickly.

Here is the same sheet with the fix. D2 is now B2 multiplied by dollar C dollar one. Copied to D3, B2 changes to B3, but the rate stays C1. In D4, it is still C1, so every discount is correct.

Pause and predict before I show the answer. Cell E5 has the formula equals A5 plus dollar B dollar one. You copy it to F6, which is one row down and one column right. What formula will F6 show? The relative A5 moves to B6, but the locked B1 stays, so it is equals B6 plus dollar B dollar one.

Sometimes you want only the answer, not the formula. A normal paste copies the formula, which may give new results in its new place. Paste Special, Values pastes only the number that was showing. In Excel, Ctrl+Alt+V opens the Paste Special box. This is useful when you send a final result sheet that should not change.

Remember one difference that exams like to test. When you cut a formula with Ctrl+X and paste it, the formula moves. A moved formula keeps exactly the same addresses. Only copying adjusts the relative addresses. After any copy, click two or three cells and check their formulas in the formula bar.

Let us revise what we learned today. You can copy a formula with Ctrl+C and Ctrl+V, the fill handle, or Ctrl+D. Relative references like B2 change to match the new row or column. Absolute references like dollar C dollar one never change. F4 adds the dollar signs, and Paste Special, Values copies only the results. Make a marks sheet of five students and try all three methods.