Switch Excel Cell References: F4 and Reference Types

To switch a formula reference between relative, absolute, and mixed types in Excel for Microsoft 365 on Windows, select the reference while editing the formula and press F4. Each press cycles through the four forms: A1, $A$1, A$1, and $A1. If you mean changing the address style from A1 to R1C1, use Excel’s formula settings instead.

What “switch cell references” can mean

People use “switch references” to describe two different Excel tasks. The common one is changing whether a formula’s row or column adjusts when the formula is copied. That is the job of relative, absolute, and mixed references, and F4 is the quick way to cycle through them.

The other task is changing how Excel displays cell addresses. In A1 style, a cell is identified by a column letter and row number, such as B3. In R1C1 style, rows and columns are both numbered. Changing that setting alters the notation Excel shows, not the underlying data or the calculation’s purpose. The two tasks are related to references, but the steps are different.

Use F4 to change a reference type

To change just one reference inside a formula on Windows, click the formula cell and press F2 to edit it, or click in the formula bar. Put the insertion point in or beside the reference you want to change, then press F4. Check the formula before pressing Enter: Excel changes the selected reference, not necessarily every reference in the formula.

Select the formula cell and enter edit mode.
Move the cursor into the reference you want to alter, such as B2.
Press F4 repeatedly until the dollar-sign pattern you need appears.
Press Enter to accept the formula, then test it by copying if appropriate.

The cycle is A1 → $A$1 → A$1 → $A1 → A1. The first press makes both the column and row absolute; later presses remove one dollar sign at a time. If F4 is not cycling the reference, confirm that Excel is editing a formula and that the cursor is positioned in the address. Outside formula editing, F4 can repeat the last action instead. On some laptop keyboards you may need to hold Fn while pressing F4, depending on the keyboard’s function-key setting.

What the four reference forms do

A plain reference such as A1 is relative. When copied, Excel adjusts both its column and row according to the new location. A dollar sign fixes the part immediately after it: the dollar sign before a column letter fixes the column, and the one before a row number fixes the row.

A1: relative column and relative row. Copying one column right and one row down changes it to B2.
$A$1: absolute column and absolute row. It remains A1 when copied in any direction.
A$1: relative column and absolute row. Copying right changes the column; copying down keeps row 1.
$A1: absolute column and relative row. Copying down changes the row; copying right keeps column A.

For example, with units in A2 and price in B2, and with A2 equal to 4 and B2 equal to 7, the formula in C2 =A2*B2 returns 28. Copying it to C3 makes Excel adjust the addresses to A3 and B3. That is useful when each row has its own inputs.

Absolute references are useful when a calculation repeatedly uses one fixed input. With a tax rate of 8% in F1 and a sale amount of 100 in B2, the formula in C2 =$F$1*B2 returns 8. Copying it down keeps the rate anchored at F1 but lets the sale amount move to B3, B4, and so on. If the formula instead used F1 without dollar signs, copying down would shift the rate reference and could produce incorrect results.

Mixed references are especially useful in tables copied both across and down. Suppose A2 contains 10, B1 contains 2, and the formula in B2 is =$A2*B$1. The result is 20. When copied one column right and one row down, the reference becomes =$A3*C$1: column A stays fixed for the row input, while row 1 stays fixed for the column input. The formula can therefore calculate a grid of combinations without rewriting each cell.

Copying formulas and checking the result

Before choosing a reference type, decide what should move when the formula is copied. If both inputs should come from the same relative position on each row, use a relative reference. If a formula must always point to one settings cell, make that reference absolute. If only the row or only the column should stay fixed, use a mixed reference.

A useful check is to copy a formula one cell in the direction you expect to fill it, then inspect the adjusted addresses. You can also select the destination cell and look at the formula bar. A formula that calculates correctly in its original cell can still be wrong after copying if a reference moved when it should have stayed fixed, or stayed fixed when it should have moved.

F4 changes the reference notation in the formula; it does not move cells, change the values stored in them, or immediately guarantee a different result. For example, changing A1 to $A$1 in a formula located where no copying has occurred may leave its current displayed answer unchanged. The difference becomes important when the formula is filled or copied elsewhere.

Switch between A1 and R1C1 notation

To change the address style on Windows, open File > Options > Formulas. Under Working with formulas, select or clear R1C1 reference style, then choose OK. In A1 style, columns use letters and rows use numbers. In R1C1 style, both use numbers, so the cell at row 3, column 2 is displayed as R3C2.

R1C1 can make patterns in formulas easier to see because references can be described in relation to the formula’s current row or column. It is sometimes useful when inspecting repeated formulas or working with code and generated spreadsheets. Most everyday worksheets use A1 style, and switching styles can surprise users who are accustomed to column letters. The setting changes how Excel presents references across the application; it is not a command for converting one formula from relative to absolute.

When R1C1 style is enabled, existing formulas are displayed using that style, but their calculation relationships are retained. Turn the option off to return to A1 notation. If a colleague says the column headings have changed from letters to numbers, this option is a likely explanation.

Excel on Mac and Excel for the web

In Excel for Mac, select the reference while editing a formula and press Command+T to cycle reference types in current versions. Keyboard mappings can vary with the Mac keyboard and Excel release; if that shortcut is intercepted or does not work, type the dollar signs directly into the reference. Mac laptops may also require the Fn key for function-key shortcuts.

To change notation on a Mac, look in Excel > Settings (called Preferences in some releases) > General for Use R1C1 reference style. In Excel for the web, function keys and shortcuts can depend on the browser and keyboard. If F4 does not cycle a reference, edit the formula and add or remove the dollar signs manually; the A1 and R1C1 setting may not be available in the same place as in the desktop app.

Common problems and fixes

F4 repeats an action. The formula is probably not in edit mode, or the cursor is not in a reference. Press F2, place the cursor in the address, and try again.
The wrong reference changed. A formula may contain several addresses. Position the cursor in the specific one you want to switch, and inspect the full formula before confirming.
The result did not change. Reference types mainly affect how formulas behave when copied. The current calculation may legitimately return the same value after a reference becomes absolute.
The formula shows #REF! after editing. This error means the formula contains an invalid reference, often because a referenced cell or range was deleted. Undo if possible, or restore a valid address.
Numbers appear instead of column letters. Check whether R1C1 reference style is enabled; it is a display setting, not a broken workbook.

A dollar sign does not lock a worksheet or protect a cell. In a cross-sheet reference, the cell address can be absolute while the sheet name remains a separate part of the reference. For instance, when the sheet is named Rates, ='Rates'!$B$2 refers to that fixed cell. Protecting a worksheet, preventing edits, and controlling whether a formula reference moves are separate Excel features.

When to type the dollar signs yourself

F4 is fast when editing one reference at a time, but typing the desired form directly can be clearer for a long or complicated formula. You can enter $A$1, A$1, or $A1 as needed. This is also a reliable fallback in Excel for the web or when a laptop’s function keys are configured for brightness, volume, or other system controls.

For formulas with many references, change one address at a time and review the formula carefully. Avoid replacing every occurrence of a cell address blindly: the formula may intentionally use a mix of fixed and moving references. If you are unsure, test the formula in a copy of the worksheet and fill it across or down a small sample before applying it to a large range.

Frequently Asked Questions

How do I switch a cell reference from relative to absolute? Edit the formula, place the cursor in the reference, and press F4 on Windows until both the column and row have dollar signs, as in $A$1. You can also type the dollar signs directly.

What does pressing F4 do in an Excel formula? When a cell reference is selected during formula editing, F4 cycles through relative, absolute, and mixed reference forms. Outside that context, F4 may repeat the previous action instead.

How do I lock only a row or only a column? Use A$1 to keep row 1 fixed while allowing the column to change, or $A1 to keep column A fixed while allowing the row to change. F4 cycles through both mixed forms as well as the fully relative and absolute forms.

How do I change Excel from A1 to R1C1? On Windows, go to File > Options > Formulas and enable R1C1 reference style. On Mac, look under Excel > Settings or Preferences > General. This changes address notation, not whether a reference is fixed when copied.

Does F4 work in Excel for Mac? In current Mac versions, Command+T cycles reference types while editing a formula. If it does not work on your setup, use the dollar signs directly; keyboard behavior can depend on the Mac keyboard and version.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.