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)

2. Absolute cell addressing

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

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)

  1. Create a salary sheet: Basic Pay in column B for 8 employees, House Rent = 45% of basic (rate in cell H1), Medical = 10% (rate in H2). Use absolute references ($H1, H$2) and copy formulas down; calculate Gross Pay.
  2. Build a 10 × 10 multiplication table using a mixed reference formula.
  3. Copy the Gross Pay column and use Paste Special → Values in a new column; then change the rate in H1 and observe which column changes.
  4. Use Paste Special → Transpose to convert the employee names column into a row.
  5. 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