Courses / Application of ICT (GEN-103) – BS 1st Semester
Week 11 Lab 11 – MS Excel: Relative, Absolute and Mixed Cell Addressing; Paste Special
Week 11 – Communication Modes, Protocols and Networks · 90 min read · 13 MCQs to check yourself
Lab objectives
Understand how references change when formulas are copied; use relative, absolute and mixed addressing; use Paste Special options.
Cell addressing (cell referencing)
When you copy a formula to other cells, Excel adjusts its cell references depending on the type of reference.
1. Relative cell addressing (default)
- Written normally:
A1,B2. - When copied, the reference changes relative to the new position.
- Example: in
D2the formula=B2*C2; copied down toD3it becomes=B3*C3, inD4→=B4*C4. - Used when each row/column needs the same calculation on its own data (totals, row-wise products).
2. Absolute cell addressing
- A dollar sign before both the column letter and the row number fixes the reference:
$A$1. - When copied, the reference does not change.
- Press F4 after typing a reference to toggle:
A1→$A$1→A1→A1→A1. - Used for a constant value stored in one cell: tax rate, exchange rate, bonus %, total marks.
Example – 5% sales tax stored in cell F1:
| A | B | C | |
|---|---|---|---|
| 1 | Item | Price | Tax (rate in F1 = 5%) |
| 2 | Laptop | 120000 | =B2*$F$1 → 6000 |
| 3 | Printer | 30000 | =B3*$F$1 → 1500 |
| 4 | Mouse | 1500 | =B4*$F$1 → 75 |
B2 changed to B3, B4 (relative) while $F$1 stayed fixed (absolute). If you wrote =B2*F1 and copied it, the next row would use F2 (empty) and give 0 – a common mistake.
3. Mixed cell addressing
- Only the column or only the row is fixed:
$A1– column fixed, row changes.A$1– row fixed, column changes.
- Useful for tables like a multiplication table: in
B2type=$A2*B$1and copy across and down.
| Reference | When copied down | When copied right |
|---|---|---|
A1 (relative) |
Row changes | Column changes |
$A$1 (absolute) |
No change | No change |
$A1 (mixed) |
Row changes | Column fixed |
A$1 (mixed) |
Row fixed | Column changes |
Other references: another sheet =Sheet2!B5; named range =B2*TaxRate (Formulas → Define Name).
Paste Special
Paste Special lets you choose what part of the copied cells to paste. Copy (Ctrl + C) → right-click destination → Paste Special (or Ctrl + Alt + V).
| Option | What it pastes |
|---|---|
| All | Everything (same as normal paste) |
| Formulas | Only the formulas (no formatting) |
| Values | Only the results of formulas as fixed numbers – very useful to "freeze" calculated values |
| Formats | Only formatting (fonts, colours, borders, number formats) |
| Comments and Notes | Only comments/notes |
| Validation | Only data-validation rules |
| All except borders | Everything except borders |
| Column widths | Only the column widths |
| Formulas and number formats / Values and number formats | Combinations |
| Operation: Add, Subtract, Multiply, Divide | Combines copied values with destination values (e.g. copy 1.1 and Multiply to increase all prices by 10%) |
| Skip blanks | Does not overwrite destination cells with blank copied cells |
| Transpose | Switches rows into columns and columns into rows |
| Paste Link | Pastes a link to the source (=Sheet1!A1) so it updates automatically |
Quick paste icons also appear under the Paste ▼ button: Paste, Formulas, Formatting, Values (123), Transpose, Picture, Linked Picture.
Lab exercise (submit)
- Create a salary sheet: Basic Pay in column B for 8 employees, House Rent = 45% of basic (rate in cell
H1), Medical = 10% (rate inH2). Use absolute references ($H1,H$2) and copy formulas down; calculate Gross Pay. - Build a 10 × 10 multiplication table using a mixed reference formula.
- Copy the Gross Pay column and use Paste Special → Values in a new column; then change the rate in
H1and observe which column changes. - Use Paste Special → Transpose to convert the employee names column into a row.
- Use Paste Special → Multiply to increase all basic pays by 10%.
Excel: Relative and Absolute Cell References (LearnFree)
Short and long questions
1. Explain cell addressing in Excel with an example of calculating tax using a rate stored in one cell. Also explain Paste Special.
Model answer
Cell addressing decides how references change when formulas are copied. Relative (A1) changes with position; absolute ($A1) never changes; mixed (A1/A$1) fixes column or row; F4 toggles.
Example: prices in B2:B4, tax rate 5% in F1. In C2 enter =B2*$F1 and drag down; C3 becomes = B3*F1, C4 = B4*F$1. B changes (relative) while F1 stays fixed (absolute). Using =B2*F1 would wrongly become =B3*F2 (empty) and return 0.
Paste Special: after copying, Ctrl+Alt+V lets you paste only Values (results), Formulas, Formats, Comments, Validation, Column widths; perform Operations (add/subtract/multiply/divide copied values into the destination); Skip blanks; Transpose (rows ↔ columns); and Paste Link (linked formulas).
2. Differentiate between relative, absolute and mixed cell references with examples.
Model answer
Relative (A1) – changes when the formula is copied (A1 → A2 when copied down). Absolute ($A1) – stays the same when copied; used for constants. Mixed (A1 or A$1) – only the column or only the row is fixed. F4 toggles between them.
3. What is Paste Special? Name any four options.
Model answer
Paste Special (Ctrl+Alt+V) lets you paste only selected parts of copied cells. Options: All, Formulas, Values, Formats, Comments, Column widths, Operations (Add, Subtract, Multiply, Divide), Skip blanks, Transpose, Paste Link.
This lesson ends with a quiz of 13 MCQs. Open the lesson on PrepOasis to take it and save your progress.
Previous: Week 11 Theory – Transmission Modes, Protocols, Topologies and Network Types · Next: Week 12 Theory – Security, Privacy, Malware and Computer Ethics
All lessons in Application of ICT (GEN-103) – BS 1st Semester