Cell referencing — relative, mixed and absolute referencing

Hello students, welcome to Kwickprep. You copy a GST formula down a column, and every answer below the first one is wrong. Why? The answer is cell referencing. Today we will learn relative, absolute and mixed references, and when to use each one.

A cell reference means using a cell address inside a formula, instead of typing the number. The formula equals B2 plus C2 takes whatever values are in B2 and C2. If you change the value in B2, the answer updates by itself. That is why we use references, not fixed numbers.

There are three types of cell reference. A relative reference is written plainly, like B2, and both its row and column move when copied. An absolute reference has a dollar sign before both parts, like dollar B dollar 2, and nothing moves. A mixed reference has only one dollar sign, like dollar B 2 or B dollar 2, so only one part moves.

A relative reference changes with the position of the formula. This is a stationery bill, with price in column B and quantity in column C. D2 has equals B2 times C2, so pens cost ten times five, fifty rupees. Copy it down, and D3 becomes B3 times C3, so books cost one hundred twenty. D4 becomes B4 times C4, and the bag costs four hundred fifty.

Let us see exactly how they move. Copy a formula down one row, and every row number goes up by one. Copy it right by one column, and every column letter moves one step. The formula keeps the same pattern, such as, multiply the two cells to my left in the same row. Relative is the default type, so a plain address is always relative.

Now the problem from our hook. Some values must stay fixed for every row, like a tax rate. Say cell E1 holds a GST rate of eighteen percent. In E3, equals D3 times E1 gives the correct GST. Copy it down, and E4 becomes D4 times E2, pointing at an empty cell, so it shows zero. E5 becomes D5 times E3, which multiplies by a GST amount, a wrong value.

An absolute reference never changes when copied. The dollar sign locks the column letter or row number that comes right after it. In E3, write equals D3 times dollar E dollar 1. Copy it to E4, and D3 moves to D4, but dollar E dollar 1 stays locked. In E5 as well, the rate cell stays E1, so every GST is correct.

You do not need to type dollar signs by hand. While typing or editing a formula in Calc, click on the cell address. Press Shift plus F4, and Calc changes the reference type each time. It cycles from B2 to dollar B dollar 2, then B dollar 2, then dollar B 2, and back to B2. Note that Excel uses just the F4 key for this.

A mixed reference locks only one part of the address. Dollar B 2 locks column B, but the row number can still move. B dollar 2 locks row two, but the column letter can still move. Dollar B dollar 2 locks both, so it is fully absolute. And plain B2 locks nothing, so it is fully relative.

Mixed references shine in a multiplication table. Row one holds two and three, and column A holds one and two, and B2 has equals dollar A 2 times B dollar 1. Copy it right, and C2 still uses column A but moves to C1, giving three. Copy it down, and B3 still uses row one but moves to A3, giving four. In C3, the formula multiplies two by three and shows six.

Pause and predict. Cell C2 has the formula equals dollar B 2 times D dollar 1. You copy C2 to E5, which is two columns right and three rows down. What formula will E5 hold? Answer: dollar B stays, the row becomes five, D moves to F, and dollar 1 stays. So E5 holds equals dollar B 5 times F dollar 1.

So which reference should you use? When each row needs the same kind of calculation, like a bill total, use relative. When every formula must point at one fixed cell, like a GST rate, use absolute. When one part must stay fixed but the other must move, like a multiplication grid, use mixed.

Let us revise what we learned today. A relative reference like B2 moves both row and column when copied. An absolute reference like dollar B dollar 2 never moves. A mixed reference locks only one part, the column or the row. The dollar sign locks the part that comes right after it. In Calc, press Shift plus F4 to switch between the types. Build a GST bill today and test all three.