Mixed references ($A1, A$1) in Excel - Step-by-Step Guide

Introduction

Mixed references in Excel let you fix either the column or the row when copying formulas. This helps keep part of the formula constant while allowing the other part to change, saving time and avoiding errors.

When you want to copy a formula across columns but keep the row fixed, like applying a tax rate from a single row to many columns.

When you want to copy a formula down rows but keep the column fixed, such as multiplying each row's value by a fixed price in one column.

When creating multiplication tables where one axis stays constant and the other changes.

When referencing a header row or column that should not move as you copy formulas.

When you want to mix fixed and relative parts in a formula to adapt to different data layouts.

Steps

Step 1: Select the cell where you want to enter the formula

- Excel worksheet

The cell is active and ready for input

Step 2: Type an equal sign (=) to start the formula

- Formula bar or selected cell

Formula input mode is activated

Step 3: Click the cell you want to reference or type its address

- Worksheet

Cell reference appears in the formula

Step 4: Press F4 key once or twice to toggle mixed reference styles

- Formula bar with cell reference selected

The reference changes to mixed style like $A1 or A$1

💡 Press F4 repeatedly to cycle through absolute, mixed, and relative references

Step 5: Complete the formula and press Enter

- Formula bar or cell

Formula calculates and shows result in the cell

Step 6: Copy the formula to other cells by dragging the fill handle

- Bottom-right corner of the formula cell

Formula copies with mixed references adjusting correctly

Before vs After

Before

Formula in cell B2 is =A1 and copying it to C2 changes it to =B1

After

Formula in cell B2 is =$A1 and copying it to C2 changes it to =$A1 (column fixed, row relative)

Settings Reference

Reference style toggle

📍 Formula bar when editing a formula

To fix either the column or row or both in a cell reference

Default: Relative (A1)

Common Mistakes

Using $A$1 when only $A1 or A$1 is needed

This fixes both column and row, so the reference never changes when copied

Use mixed references like $A1 or A$1 to fix only column or row as needed

Not pressing F4 to toggle reference styles and typing $ manually

Manually typing $ can cause errors or inconsistent references

Use F4 key to toggle reference styles quickly and accurately

Summary

Mixed references fix either the column or the row in a cell reference.

Use F4 key to toggle between relative, mixed, and absolute references.

Mixed references help formulas adapt correctly when copied across cells.