Which type of cell reference in MS-Excel contains a formula which does not change when the formula is copied?

Appeared in: AIIMS Raipur NO - 2019 (Shift-1)

Explanation

An absolute cell reference in a formula always refers to a cell in a specific, fixed location.
When a formula containing an absolute reference is copied to other cells, the reference remains constant and does not change.
This type of reference is denoted by placing a dollar sign ($) before both the column letter and the row number (e.g., $A$1).
It is essential when a formula needs to consistently use a single value, such as a tax rate or a fixed price, across multiple calculations.

Option B: This is a general, descriptive term for how Excel refers to data in cells. It is not a specific type of reference behavior like absolute, relative, or mixed.
Option C: A mixed reference is only partially fixed. Either the column is locked (e.g., $A1) or the row is locked (e.g., A$1), but not both. Therefore, it will change when copied.
Option D: A relative reference is the default type in Excel and is designed to change relative to the new location when the formula is copied.

Clinical Relevance

Nursing practice connection: This is primarily an exam-oriented knowledge point with limited direct bedside application, so retain Cell Reference Types in MS-Excel as background academic context rather than a clinical decision trigger.
Using absolute references is crucial for calculations involving a constant value, such as a tax rate, discount percentage, or a specific lookup value in a table.
For example, to calculate sales tax for a list of items, you would use an absolute reference for the cell containing the tax rate (e.g., =B2*$E$1), so the rate cell doesn't change when you drag the formula down the column.
What if? - If you used a relative reference (e.g., =B2*E1) for the tax rate, copying the formula down would result in incorrect calculations because the formula would start referencing cells below the tax rate cell (E2, E3, etc.), which are likely empty or contain wrong data.

How to Approach the Question

Step 1: Identify the key phrase in the question: 'does not change when the formula is copied'.
Step 2: Recall the three main types of cell references in Excel: Relative, Absolute, and Mixed.
Step 3: Analyze the behavior of each reference type upon being copied.
Step 4: A Relative reference (e.g., A1) changes. A Mixed reference (e.g., $A1 or A$1) changes partially. An Absolute reference (e.g., $A$1) does not change.
Step 5: Match the requirement 'does not change' with the correct reference type, which is Absolute.
Step 6: Eliminate 'Data reference' as it is a general category name, not a specific type of reference behavior.

Concept Tested & Keywords

QebL81q0aC_VQA_rSh9drl

Practise the full AIIMS Raipur NO - 2019 (Shift-1)

Attempt every question from this paper in a timed mock, then review the full solution for each one.