Cell referencing MCQs Quiz | Class 9
This quiz is for Class IX students of Computer Applications (Code 165), focusing on Unit 3: Office Tools (Spreadsheets). It specifically covers the topic of Cell referencing MCQs Quiz | Class 9, including relative references and the basics of referencing cells. Attempt all questions, submit your answers to see your score, and download the PDF answer sheet for future reference.
Understanding Cell Referencing in Spreadsheets
Cell referencing is a fundamental concept in spreadsheet applications like Microsoft Excel or LibreOffice Calc. It refers to how a formula identifies a cell or a range of cells on a worksheet. Understanding different types of cell references is crucial for creating dynamic and efficient spreadsheets, especially when copying formulas.
Types of Cell References
There are three main types of cell references you need to know:
1. Relative Reference
This is the default type of cell reference. When you copy a formula with relative references to another cell, the references automatically adjust to their new location. For example, if you have the formula =A1+B1 in cell C1 and you copy it to cell C2, the formula will automatically change to =A2+B2.
- Example: A1, B10, C5
- Use Case: Ideal for calculations that need to be repeated across rows or columns, like summing up values for each row.
2. Absolute Reference
An absolute reference is a cell address that remains fixed when a formula is copied. It does not change regardless of where you move or copy the formula. You can make a reference absolute by placing a dollar sign ($) before the column letter and the row number.
- Example: $A$1
- Use Case: Used when you want a formula to always refer to a specific cell, such as a constant value like a tax rate or a commission percentage. For instance, in the formula
=B2*$A$1, the reference to cell A1 will not change when copied.
3. Mixed Reference
A mixed reference has either an absolute column and a relative row, or a relative column and an absolute row. This type of reference is useful when you want to lock either the column or the row but not both.
- Absolute Column, Relative Row (e.g., $A1): The column (A) is fixed, but the row (1) will change when the formula is copied down.
- Relative Column, Absolute Row (e.g., A$1): The column (A) will change when copied across, but the row (1) is fixed.
- Use Case: Creating multiplication tables or financial models where you need to reference specific headers in a row or column.
Comparison of Reference Types
| Reference Type | Example | Behavior When Copied | Keyboard Shortcut (F4 key) |
|---|---|---|---|
| Relative | A1 | Both column and row adjust. | Press F4 once (from absolute) |
| Absolute | $A$1 | Neither column nor row adjusts. | Press F4 once (from relative) |
| Mixed (Row Absolute) | A$1 | Row is fixed, column adjusts. | Press F4 twice (from relative) |
| Mixed (Column Absolute) | $A1 | Column is fixed, row adjusts. | Press F4 three times (from relative) |
Quick Revision Points
- Default Reference: Relative (e.g., A1).
- Lock Everything: Absolute (e.g., $A$1).
- Lock Column Only: Mixed (e.g., $A1).
- Lock Row Only: Mixed (e.g., A$1).
- Shortcut Key: The F4 key on your keyboard is used to cycle through the four reference types (A1 -> $A$1 -> A$1 -> $A1 -> A1).
Practice Questions
Test your understanding with these extra questions:
- If the formula
=B$5*C6in cell D6 is copied to cell E7, what will the new formula be? - What is the main purpose of using an absolute cell reference?
- Which symbol is used to denote an absolute or mixed reference?
- Describe a scenario where a mixed reference like
$B2would be useful. - How do you change a cell reference from relative to absolute using a keyboard shortcut?
