ExamFinch.com My Study

Study guide

Relative, Absolute, and Mixed References

Practice this exam

Microsoft Excel Microsoft 365 Apps

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

About the MO-210 Exam Performance-Based Exam Strategy

2 Import Data & Navigate Workbooks

Import Data from Text Files Import Data from Online Sources Search and Navigate to Named Cells, Ranges, Elements Insert and Remove Hyperlinks

3 Format Worksheets, Views & Collaboration

Page Setup, Headers, Footers, Row Height & Column Width Quick Access Toolbar, Views, Freeze Panes, Properties, Display Formulas Print Area, Export Formats, Print Settings, Inspect, Comments & Notes

4 Manipulate Data in Worksheets

Paste Special and Auto Fill Insert and Delete Rows, Columns, and Cells Generate Numeric Data with RANDBETWEEN and SEQUENCE

5 Format Cells and Ranges

Merge, Alignment, Orientation, Indentation, Wrap Text Number Formats, Format Painter, Format Cells Dialog Cell Styles, Clear Formatting, Group Worksheets

6 Named Ranges & Visual Summaries

Define and Reference Named Ranges Insert and Manage Sparklines Apply and Remove Conditional Formatting

7 Create and Format Tables

Create Excel Tables from Cell Ranges Apply Table Styles and Convert Tables to Ranges Add or Remove Table Rows and Columns

8 Table Options, Filter & Sort

Configure Table Style Options Insert and Configure Total Rows Filter Table Records Sort Data by Multiple Columns

9 Formula References

Relative, Absolute, and Mixed References Structured References in Tables

10 Calculate and Transform Data

SUM, AVERAGE, MIN, and MAX COUNT, COUNTA, and COUNTBLANK Conditional Operations with IF SORT and UNIQUE Functions

11 Format and Modify Text

LEFT, RIGHT, and MID UPPER, LOWER, LEN, CONCAT, and TEXTJOIN

12 Create Charts

Create Charts from Data Create Chart Sheets

13 Modify and Format Charts

Add Data Series and Switch Rows/Columns Add and Modify Chart Elements Chart Layouts, Styles, and Alternative Text

Relative, Absolute, and Mixed References

Key Takeaways Explain This Topic AI

Relative references (A1) adjust when you copy or fill; absolute references ($A$1) stay locked to one cell

Mixed references lock only the column ($A1) or only the row (A$1)—use them for rate tables and cross-tab formulas

Press F4 (or Fn+F4 on some Mac keyboards) while the cursor is on a reference to cycle A1 → $A$1 → A$1 → $A1

MO-210 graders check formula text after fill: a missing dollar sign often scores wrong even if one cell looks correct

Lock input cells such as tax rates, commission rates, and constants before you Auto Fill across rows or columns

Last updated: August 2026

Why reference types matter on MO-210

Domain 4 of Exam MO-210 (Perform Operations by Using Formulas and Functions) includes the skill Insert references . At Associate level that means you can build formulas that use relative , absolute , and mixed cell references correctly, then copy or fill them so every destination cell calculates the right value. Graders do not only look at one finished number—they inspect formula text after Auto Fill, so a missing $ is a frequent score loss even when the first row looks perfect.

Task wording often sounds like "In cell C2, multiply quantity by the unit price in B2, then fill down" , "Calculate sales tax using the rate in cell H1 for every row" , or "Create a multiplication table that locks the row headers and column headers appropriately." Those map to choosing B2 vs $H$1 vs $B2 / B$2 , and using F4 to toggle the dollar signs quickly.

Relative references (the default)

A relative reference is written without dollar signs: A1 , B2 , C10:C20 . Excel stores the offset from the formula cell, not a fixed address. When you copy or fill the formula, that offset moves with it.

Worked example: line totals

  • A (Item) B (Qty) C (Price) D (Line Total)
  • 1 Header Qty Price Line Total
  • 2 Pens 10 1.50 =B2*C2
  • 3 Pads 5 3.00 fill from D2
  • 4 Clips 20 0.25 fill from D2

Enter =B2*C2 in D2. Drag the fill handle down to D4. Excel adjusts to =B3*C3 and =B4*C4 . That is correct relative behavior: each row multiplies its own quantity and price.

If you had written =$B$2*$C$2 by mistake and filled down, every row would repeat the Pens calculation. Relative references are the right default whenever the formula should track the same shape of cells as it moves.

Absolute references ($A$1)

An absolute reference locks both column and row with dollar signs: $H$1 , $Tax!$B$2 . No matter where you copy the formula, Excel keeps pointing at that same cell.

  • Worked example: shared tax rate
  • Cell H1 holds 0.08 (8% sales tax). Column D should compute tax for each line total in column C: C2 = 100
  • C3 = 250
  • C4 = 75
  • H1 = 0.08

In D2 enter =C2*$H$1 . Fill down. Results:

  • D2: =C2*$H$1 → 8
  • D3: =C3*$H$1 → 20
  • D4: =C4*$H$1 → 6

C2 stays relative so each row uses its own total; $H$1 stays absolute so every row reads the single rate cell. On MO-210, if you leave H1 relative ( =C2*H1 ) and fill down, D3 becomes =C3*H2 —often blank or wrong—and you lose the task.

Exam habit: Any constant the project stores once (tax rate, discount, commission, hourly rate, conversion factor) usually needs $ on both column and row before you fill.

Mixed references ($A1 and A$1)

A mixed reference locks only one axis:

  • Syntax Locks Adjusts
  • $A1 Column A Row number when filled vertically
  • A$1 Row 1 Column letter when filled horizontally

Mixed references shine when you build grids: prices across the top, quantities down the side, or lookup-style multiplication tables.

Worked example: price × quantity grid

Row 1 has unit prices in B1:D1 . Column A has quantities in A2:A5 . You need each cell in B2:D5 to multiply the quantity from column A by the price from row 1.

In B2 enter: =$A2*B$1

$A2 keeps column A locked when you fill right (always use quantity from column A), but the row can change when you fill down .

B$1 keeps row 1 locked when you fill down (always use the header price), but the column can change when you fill right .

Fill B2 across to D2, then fill that row down to row 5. Sample formulas after fill:

  • B C D
  • 2 =$A2*B$1 =$A2*C$1 =$A2*D$1
  • 3 =$A3*B$1 =$A3*C$1 =$A3*D$1

If you used fully relative =A2*B1 and filled right, B2's neighbor becomes =B2*C1 —wrong. If you used fully absolute =$A$2*$B$1 everywhere, every cell repeats one product. Mixed references are the Associate-level tool for this pattern.

Toggling with F4

While editing a formula, place the cursor on (or select) a reference and press F4 . Excel cycles:

  • A1 (relative)
  • $A$1 (absolute)
  • A$1 (mixed — row locked)
  • $A1 (mixed — column locked)
  • back to A1

On many Mac keyboards the cycle is Fn+F4 or Command+T , depending on Excel settings and the keyboard. Learn the cycle on your lab machine before exam day. F4 is faster and safer than typing dollar signs by hand under time pressure.

You can also insert references by clicking cells while building a formula; Excel inserts relative references by default. Select the reference in the formula bar and press F4 until the locks match the task.

Fill, copy, and paste behavior

Fill handle / Auto Fill: relative parts shift; absolute parts stay; mixed parts shift only on the unlocked axis.

Copy and Paste: same adjustment rules as fill when pasting formulas.

Cut and Paste: Excel usually keeps the same reference addresses (it does not re-offset the way copy does). Know the difference if a task says cut versus copy.

Paste Values: replaces formulas with numbers—avoid unless the task asks for values only.

Display formulas with Formulas → Show Formulas (or Ctrl+ ) to audit $` signs across a filled block before you move to the next project step.

Common MO-210 traps

Filling a rate formula without locking the rate cell ( H1 instead of $H$1 ).

Locking everything ( $B$2*$C$2 ) when rows should stay relative.

Using $A$2 in a grid when $A2 or A$2 was required—one wrong lock breaks the entire fill.

Typing dollar signs on the wrong character ( $A1$ is invalid).

Checking only the first calculated cell and never verifying formulas two or three rows down.

Confusing named ranges (Domain 2) with absolute references—both can "lock" a value, but the task wording tells you which tool to use.

Practice sequence for the exam

Build =B2*C2 , fill down, confirm relative adjustment.

Add a rate in G1 , rewrite as =B2*$G$1 , fill down, open Show Formulas and verify $G$1 on every row.

Build a small 3×3 product grid with =$A2*B$1 , fill across and down, spot-check corner cells.

Start with a relative reference and press F4 through a full cycle so muscle memory is automatic.

Master relative, absolute, and mixed locks plus F4, and you cover the classic cell-reference half of Domain 4's Insert references objective.