Ravindra BagaleCourses & study guides मराठी Track your progress

Relative vs Absolute vs Mixed Cell Reference in Excel (F4 Key) with GST Example

By Last updated:

You write one formula, drag it down, and the first row is right but the rest show 0, #VALUE! or a number with nine zeros. Nothing is wrong with Excel. The formula is doing exactly what references do when they are copied. This chapter explains the three kinds of cell reference (relative, absolute and mixed) with one example everyone understands: adding GST to a price list. By the end you will write a single formula that fills a whole grid of prices and tax rates.

What you'll learn in this chapter

  • What a relative reference does when you drag a formula
  • Why a "rate in one cell" breaks when you drag without $
  • The three errors you will see: 0, #VALUE! and wrong large numbers
  • Absolute reference: $C$1 and the F4 key
  • A rate typed as 18 instead of 18%
  • Mixed reference: $B5 and C$4
  • One formula for a whole price × rate grid

Practice file: excel-cell-references-gst-practice.xlsx has a sheet for each section (Relative, GST, Rate without % sign, Mixed grid) and a Your turn sheet.

1. Relative reference: the default

Every reference you type, such as A5, is relative. Excel does not remember "cell A5"; it remembers "the cell two columns to my left, in my row".

Try it:

  1. Put 10, 20, 30 … 80 in A5:A12 and 20, 40, 60 … 160 in B5:B12.
  2. In C5 type =A5+B5 and press Enter: 30.
  3. Drag the fill handle down to C12.
  4. Click C6: the formula is now =A6+B6. C7 has =A7+B7, and so on. C12 shows 240.

When you drag down, the row number goes up by one in every row. When you drag right, the column letter moves by one. That is exactly what you want for "add the two cells in my row".

Classroom line

When we hold a cell and drag it down, the row number keeps increasing automatically.

A relative reference moves with the cell A B C 4 A value B value A + B 5 10 20 30 6 20 40 60 7 30 60 90 8 40 80 120 9 50 100 150 10 60 120 180 11 70 140 210 12 80 160 240 =A5+B5 =A6+B6 =A7+B7 =A12+B12 Dragging down row numbers go up by one: A5 → A6 → A7 … Dragging right moves the column letter instead.

Figure 1. A relative reference moves with the cell. C5 adds A5 and B5; dragged down, C6 adds A6 and B6.

2. The problem: one rate for every row

Now a price list. Column A has products, column B their prices, and the GST rate sits in one cell, C1 (we use 18% as an example; the rate is in its own cell precisely because rates can change).

Product Price
Laptop 1,00,000
Camera 60,000
Tablet 30,000
Mobile 25,000
Smartwatch 20,000
Earphones 10,000
Speaker 8,000

In C5 you type =B5*C1 and get 18,000 for the laptop. Correct. Now drag it down:

Cell Formula after dragging Result Why
C5 =B5*C1 18,000 correct
C6 =B6*C2 0 C2 is empty, and an empty cell counts as 0
C7 =B7*C3 0 C3 is empty too
C8 =B8*C4 #VALUE! C4 holds the header text "GST"
C9 =B9*C5 36,00,00,000 multiplies by the laptop's GST, not by 18%

The price part (B5, B6, B7 …) should move. The rate part (C1) should not, but it moved too: C1 became C2, C3, C4 …

Classroom line

A #VALUE! error means the data type has gone wrong.

#VALUE! appears because the formula tried to multiply a number by the text "GST". The large wrong numbers are worse: they look like real answers. Always click two or three cells after dragging and read their formulas.

Without $: the rate cell slides down too A B C 1 GST rate 18% 2 3 4 Product Price GST 5 Laptop 1,00,000 18,000 6 Camera 60,000 0 7 Tablet 30,000 0 8 Mobile 25,000 #VALUE! 9 Smartwatch 20,000 36,00,00,000 10 Earphones 10,000 0 11 Speaker 8,000 0 Cell Formula after dragging Why C5 =B5*C1 correct C6 =B6*C2 C2 is empty C7 =B7*C3 C3 is empty C8 =B8*C4 C4 is the text "GST" C9 =B9*C5 uses the laptop's GST Only the price should move. The rate in C1 must stay put.

Figure 2. Without $, the rate cell slides down with the formula: 0, 0, #VALUE! and then huge wrong numbers.

3. Absolute reference: lock it with $

A $ in front of the column letter locks the column; a $ in front of the row number locks the row. $C$1 locks both, so it stays C1 wherever you copy the formula. This is an absolute reference.

How:

  1. In C5 type =B5*$C$1 and press Enter: 18,000.
  2. In D5 type =B5+C5 for the total: 1,18,000.
  3. Select C5:D5 and drag down to row 11.
  4. Click C9: it shows =B9*$C$1. B moved, C1 stayed.

Results: GST 18,000; 10,800; 5,400; 4,500; 3,600; 1,800; 1,440. Totals 1,18,000; 70,800; 35,400; 29,500; 23,600; 11,800; 9,440.

Now change C1 to 12%: the laptop's GST becomes 12,000 and its total 1,12,000. Change it to 28%: 28,000 and 1,28,000. One cell controls the whole sheet.

Classroom line

When one number has to be used in every row, we use an absolute reference.

With $C$1 every row uses the same rate cell A B C D 1 GST rate 18% 2 3 4 Product Price GST Total 5 Laptop 1,00,000 18,000 1,18,000 6 Camera 60,000 10,800 70,800 7 Tablet 30,000 5,400 35,400 8 Mobile 25,000 4,500 29,500 9 Smartwatch 20,000 3,600 23,600 10 Earphones 10,000 1,800 11,800 11 Speaker 8,000 1,440 9,440 C5: =B5*$C$1 C9: =B9*$C$1 B moves C1 stays Change C1 and everything updates 12% → laptop GST 12,000, total 1,12,000 28% → laptop GST 28,000, total 1,28,000 One cell controls the whole sheet.

Figure 3. With $C$1 every row multiplies its own price by the same rate cell. Change C1 and every GST and total updates.

4. The F4 key

Typing $ by hand works, but F4 is faster. Click inside the reference in the formula (or just after it) and press F4. Each press changes it:

  1. C1 → $C$1 (absolute)
  2. → C$1 (row locked)
  3. → $C1 (column locked)
  4. → C1 (relative again)

On many laptops the F keys control volume and brightness, so press Fn+F4. On a Mac, use Cmd+T. A reference has at most one $ before the letter and one before the number; if you ever see $$, Excel will refuse the formula, so remove the extra sign.

Ravindra Bagale's Tip

After you drag any formula, click the last cell and read its formula in the Formula Bar. If the fixed cell has drifted (C1 became C11), you forgot the $. Checking the last cell takes two seconds and catches the mistake before anyone sees your report.

5. A rate typed as 18, not 18%

When you type 18%, Excel stores 0.18, so =B5*$C$1 already gives 18,000. You do not need to divide by 100.

Classroom line

There is no need to divide 18 by 100.

If the rate cell holds plain 18 (no % sign), divide in the formula instead:

=B5*$C$1/100

The laptop gives 18,000 again. Without the $ (=B5*C1/100 dragged down) you get the same 0, #VALUE! and wrong numbers as before, because the problem is the moving reference, not the % sign. Choose one style for the sheet and label the cell ("GST rate, %") so the next person knows which one you used.

6. Mixed reference: one formula for a whole grid

Task: show the tax at five example rates (5%, 10%, 12%, 18%, 28%) for all seven products. Rates go across row 4 (C4:G4), prices down column B (B5:B11). We want one formula in C5 that can be copied to the whole grid C5:G11.

Classroom line

My aim now: I write one formula, and it should work for every cell.

Think about what must move and what must stay:

  1. The price is always in column B, but its row changes as you go down. Lock the column only: $B5.
  2. The rate is always in row 4, but its column changes as you go right. Lock the row only: C$4.

=$B5*C$4

Type it in C5 (laptop at 5% = 5,000), drag right to G5, then drag C5:G5 down to row 11. G11 now holds =$B11*G$4: the speaker at 28% = 2,240. This is a mixed reference: half locked, half free.

Classroom line

Don't give a dollar to what will change; give a dollar to what won't change.

One formula for the whole grid: =$B5*C$4 A B C D E F G 4 Product Price 5% 10% 12% 18% 28% 5 Laptop 1,00,000 5,000 10,000 12,000 18,000 28,000 6 Camera 60,000 3,000 6,000 7,200 10,800 16,800 7 Tablet 30,000 1,500 3,000 3,600 5,400 8,400 8 Mobile 25,000 1,250 2,500 3,000 4,500 7,000 9 Smartwatch 20,000 1,000 2,000 2,400 3,600 5,600 10 Earphones 10,000 500 1,000 1,200 1,800 2,800 11 Speaker 8,000 400 800 960 1,440 2,240 $B5 → column B locked, row free (moves down) C$4 → row 4 locked, column free (moves right) G11 holds =$B11*G$4: the speaker at 28% = 2,240 Blue cells: the price column and the rate row that every formula reads.

Figure 4. One formula, =$B5*C$4, fills the whole grid. $B keeps every row on the price column; $4 keeps every column on the rate row.

7. Why the other two versions fail

Formula in C5 Copy right Copy down
=$B$5*C4 works (D5 = 10,000) wrong: C6 becomes =$B$5*C5 = 50,00,00,000
=B5*$C$4 wrong: D5 becomes =C5*$C$4 = 250 works (C6 = 3,000)
=$B5*C$4 works works

Locking everything in one direction is the most common mistake. Ask two questions for every reference: "when I copy down, should the row move?" and "when I copy right, should the column move?" Put a $ in front of each part that must not move.

Only the mixed reference works both ways =$B$5*C4 down breaks B C D 4 Price 5% 10% 5 1,00,000 5,000 10,000 6 60,000 50,00,00,000 1,00,00,00,000 =B5*$C$4 right breaks B C D 4 Price 5% 10% 5 1,00,000 5,000 250 6 60,000 3,000 150 =$B5*C$4 both work B C D 4 Price 5% 10% 5 1,00,000 5,000 10,000 6 60,000 3,000 6,000 Copy down: C6 = $B$5*C5 multiplies the laptop price by the 5,000 above it. Copy right: D5 = C5*$C$4 multiplies the 5,000 on its left by 5%. Ask for every reference: should the row move when I copy down? Should the column move when I copy right?

Figure 5. Three versions of the same formula. Only the mixed one gives correct answers in both directions.

Ravindra Bagale's Tip

Not sure which $ you need? Write the formula for the first cell, copy it one cell right and one cell down, and read both formulas. If the price or the rate has slipped in either one, add the $ to that part. Two test copies are faster than fixing a whole grid.

8. The whole chapter on one page

  1. Relative (A5): moves when copied. The default.
  2. Absolute ($C$1): never moves. Use it for one value every row needs: a rate, a target, maximum marks.
  3. Mixed ($B5 or C$4): one part locked, one part free. Use it for grids.
  4. F4 (Fn+F4 on many laptops, Cmd+T on a Mac) cycles through the four types.
  5. 0, #VALUE! or huge numbers after dragging = a reference that should have been locked.
  6. 18% in a cell is already 0.18. Plain 18 needs /100 in the formula.
  7. Test a formula by copying it one cell right and one cell down.

9. Try at home

Try at home

Task 1: GST

  1. Make the price list from section 2 with the rate in C1.
  2. Write =B5*$C$1 and =B5+C5, drag down, and change C1 to 5%, 12% and 28%.

Task 2: break it on purpose

  1. In a new column write =B5*C1 (no $) and drag down. Find the 0, the #VALUE! and the first huge number.

Task 3: grid

  1. On the Your turn sheet fill C5:G11 with one formula.
  2. Check: Tablet at 12% = 3,600; Mobile at 18% = 4,500; Camera at 28% = 16,800.

Task 4: discount table

  1. Discounts 5%, 10%, 15% across row 4, the same prices down column B. Write one formula for the price after discount. Hint: =$B5*(1-C$4).

Recap

In short

  1. Relative references move; that is why dragging works.
  2. A value every row needs must be locked with $.
  3. $C$1 = absolute, $B5 and C$4 = mixed.
  4. F4 (Fn+F4, Cmd+T on a Mac) adds the $ signs for you.
  5. One mixed formula can fill a whole grid.
  6. After dragging, read the formula in the last cell.

Quick revision cards

Quick revision

Revise this chapter in two minutes. Save the cards on your phone or download them as PNG files. All names and numbers are made up for practice.

Revision card: GST with relative and absolute references. Example data table with GST on row 5: ₹216; Copied down to row 7: ₹450; Mixed references: 2 mixed types.
Card 1 of 2: GST with relative and absolute references · Download PNG
Revision card: Mixed references for a grid, and GST typed as 18. Example data table with One formula for the whole grid: 200; Same formula copied to D6: 600; GST typed as 18, not 18%: ₹18.
Card 2 of 2: Mixed references for a grid, and GST typed as 18 · Download PNG

More cards for every chapter: Ravi Sir's Cards.

Loading practice tasks...

  1. Total invoices (Easy): Add up Invoices in D2:D11.
  2. Average amount (Easy): Average of E2:E11, rounded to 2 decimals.
  3. Rows in Admin (Easy): How many rows have Department "Admin"?
  4. Largest invoices (Easy): The largest value in D2:D11.
  5. Invoices for Admin (Medium): Total of D2:D11 where B is "Admin".
  6. Admin in Berlin (Medium): Total of D2:D11 where B is "Admin" and C is "Berlin".
  7. Best in Admin (Medium): Largest E2:E11 value where B is "Admin".
  8. Value of row 2 (Medium): D2 times E2.
  9. Look up Neha Gupta (Medium): The Invoices for "Neha Gupta" (use a lookup on column A).
  10. Total value (Hard): Sum of D times E for all rows.
  11. Share of Admin (Hard): Share of D2:D11 that is "Admin", rounded to 3 decimals.
  12. Rank of row 2 (Hard): Rank of D2 among D2:D11 (largest = 1).
  13. Above average? (Hard): "Above" if E2 is above the average of E2:E11, else "Below".

See all practice tasks in the Task Hub →