Relative and absolute cell references

Hello students, welcome to Kwickprep. You copy a correct formula down a column, and suddenly the answers are wrong. Why does that happen? Today we will learn relative, absolute and mixed cell references, and how to fix this common mistake.

Let us start with two simple terms. A cell is one box where a row and a column meet. A cell reference is the address of that box, like B2. We always write the column letter first, and then the row number. A formula uses these addresses, so when a value changes, the answer updates by itself.

Here is a small canteen bill in a spreadsheet. Cell A2 holds the item name, Samosa. Cell B2 holds the price, fifteen rupees. Cell C2 holds the quantity, four. Cell D2 holds the formula, equals B2 star C2, where the star means multiply, so D2 shows sixty. Row three holds Tea, price ten, quantity three. Row four holds Sandwich, price twenty, quantity two.

A reference like B2, with no dollar sign, is called a relative reference. Relative means it changes when you copy the formula to another cell. If you copy it one row down, B2 becomes B3. If you copy it one column to the right, B2 becomes C2. This is the default type in every spreadsheet.

Relative references are useful most of the time. We drag the formula in D2 down to D4, using the small square at the corner of the cell, called the fill handle. In D3, the formula becomes B3 star C3, so tea gives thirty. In D4, it becomes B4 star C4, so the sandwich gives forty. Each row uses its own price and quantity, which is exactly what we want.

Now the trouble. Suppose the tax rate, five percent, sits in only one cell, E1. In E2, the formula D2 star E1 divided by hundred gives the correct tax of three rupees. But when we copy it down, E1 also moves and becomes E2. Row three now multiplies by E2, which holds three, not the rate. Row four uses E3, so the tax values are wrong.

The fix is an absolute reference. We put a dollar sign before the column letter and before the row number, like dollar E dollar one. An absolute reference stays locked, even when you copy the formula anywhere. Use it for a single fixed value, like a tax rate, a target or a grand total. Remember, this dollar sign only means locked, it has nothing to do with money.

Here is the corrected sheet. In E2, the formula is D2 star dollar E dollar one divided by hundred, which gives three. Copied to E3, D2 changes to D3, but dollar E dollar one stays the same, so tea gets one point five. In E4, the sandwich gets a tax of two rupees. Every row now uses the same rate cell.

There is a third type, called a mixed reference, where only one part is locked. A relative reference like B2 locks nothing. An absolute reference like dollar B dollar two locks both the column and the row. Dollar B two locks only the column, so the row can still change. B dollar two locks only the row, so the column can still change. Mixed references are handy in tables like a multiplication chart.

You do not have to type the dollar signs yourself. While editing the formula, click on the reference you want to change. In Microsoft Excel, press F4 to switch its type. Each press moves through B2, dollar B dollar two, B dollar two, dollar B two, and back to B2. In LibreOffice Calc, the same shortcut is Shift+F4.

Let us test your understanding with an exam style question. Cell C1 has the formula, equals A1 plus dollar B dollar one. You copy C1 and paste it into D3. Pause the video and predict the formula in D3. The answer is equals B3 plus dollar B dollar one. The copy moved one column right and two rows down, so A1 became B3, while the absolute part did not move.

Here is a simple guide for choosing. For totals that follow each row, like price times quantity, use relative references. For one fixed value used by every row, like a rate, use an absolute reference. To lock only a heading row, use a mixed reference like B dollar one. To lock only a first column, use a mixed reference like dollar A two.

Let us revise what we learned today. A cell reference is the address of a cell, like B2. A relative reference changes when you copy the formula. An absolute reference, with two dollar signs, stays fixed when copied. A mixed reference locks only the column or only the row. And F4 in Excel, or Shift+F4 in Calc, switches between the types. Practise with your own bill sheet and watch the formulas as you copy them.