To make a cell reference stay fixed when you copy a formula in Excel for Mac, add dollar signs to its column and row—for example, change F1 to $F$1. While editing a formula, select the reference and press Command+T to cycle through reference types; you can also type the dollar signs yourself. Use an absolute reference when every copied formula should point to the same cell, such as a tax rate or constant.
These steps apply to Excel for Microsoft 365 on Mac. The same dollar-sign notation works in other desktop versions of Excel, although shortcut behavior can vary with your version and keyboard settings. You will need a workbook with a formula to edit, and it helps to have a small example in which you can see what changes when you copy the formula.
What You’ll Need
- Excel for Mac and a workbook with data or a formula you can edit.
- A keyboard, if you want to use Command+T. You can enter dollar signs manually if the shortcut is unavailable or inconvenient.
- A clear idea of which part of the reference must stay fixed: the column, the row, or both.
Step-by-Step
-
Set up a formula that uses a cell reference. For a practical example, imagine that column B contains item prices and cell F1 contains a discount rate of 8%. You want each row to calculate the discount using the same rate in F1. The example assumes a price of 12.50 in B2 and 8% in F1. In C2, enter
=B2*$F$1. The result is 1.00, because 12.50 multiplied by 8% equals 1.00. B2 is a relative reference; $F$1 is an absolute reference. -
Open the formula for editing. Select the cell containing the formula, such as C2, then double-click it or click in the formula bar. You can also select the cell and press Control+U on a Mac to edit the active cell in some Excel configurations; using the formula bar is a reliable alternative if that shortcut does not work. Make sure you are editing the formula, not just selecting the cell.
-
Select the reference you want to lock. In the formula, place the insertion point within F1. If you are using the mouse, select the F1 reference characters in the formula bar or formula being edited. Do not select the multiplication sign or the other reference by mistake. The shortcut changes the reference that contains the insertion point or is selected.
-
Cycle the reference type with Command+T. Press Command+T (⌘+T) to cycle the selected reference through its available forms. For a reference such as F1, the sequence is F1, $F$1, F$1, and $F1, then back to F1. Stop when you see $F$1 to lock both the column and the row. If the shortcut is intercepted by your Mac or does not cycle the reference in your Excel version, type the dollar signs directly.
-
Confirm the formula and its result. Press Return to finish editing. The formula should still calculate the same result in C2; adding dollar signs changes how a reference behaves when copied, not the arithmetic performed in the current cell. If Excel displays a formula error, check that the formula still has its equals sign, multiplication operator, and closing reference.
-
Copy the formula to other rows. Select C2 and drag the small fill handle at the lower-right corner of the cell down the rows you need, or copy and paste the formula. The relative reference B2 changes to B3, B4, and so on, while $F$1 stays fixed. If the next price is in B3, the copied formula uses B3 with the rate still coming from F1.
-
Check a copied formula directly. Select a filled cell such as C3 and inspect the formula bar. Confirm that the price reference changed to B3 and the fixed reference remains $F$1. Check at least one calculated result against a hand calculation. This quick check catches a common problem: locking the price cell by mistake instead of locking the rate cell.
-
Use a mixed reference when only one part should stay fixed. A reference can lock its column or its row without locking both. In a formula copied across columns, a fixed row such as F$1 can keep pointing to row 1 while the column changes. In a formula copied down rows, $F1 can keep pointing to column F while the row changes. Select the reference and cycle with Command+T until the dollar sign appears in the required position, or type it into the reference manually.
Excel calls these reference styles relative, absolute, and mixed. In A1-style notation, A1 has no fixed part; $A$1 fixes the column and row; A$1 fixes only the row; and $A1 fixes only the column. The dollar signs do not refer to currency. They tell Excel how to adjust a reference as a formula is copied or filled.
Common Mistakes
-
Locking the wrong cell. If the constant is in F1, lock F1—not the changing price cell. In the example, B2 should remain relative so it can become B3 as you fill down.
-
Using a relative reference for a fixed input. If you enter F1 without dollar signs and copy the formula down, Excel changes it to F2, F3, and later rows. Those cells may be empty or contain unrelated values, producing incorrect results without an obvious error message.
-
Locking both parts when you need a mixed reference. $F$1 will never change in either direction. That is correct for one shared input, but not for a table where formulas should move across columns while keeping the same header row.
-
Expecting the dollar sign to change the displayed number. It only controls reference movement. To show a value as currency, change the cell’s number format; that is a separate setting.
-
Pressing the shortcut without selecting a reference. Command+T needs to be used while editing a formula with the relevant reference active. If Excel is not in formula-edit mode, the keystroke may trigger a different command or do nothing useful.
Tips
-
Choose the reference style based on how you will copy. For formulas copied down, ask whether the row should change. For formulas copied across, ask whether the column should change. A reference can be fixed in one direction and flexible in the other.
-
Type the dollar signs when that is faster. You do not have to use a shortcut. While editing a formula, change F1 to $F$1, F$1, or $F1 as needed. This is especially useful if your Mac keyboard or Excel settings make function or command shortcuts awkward.
-
Use a named range for a frequently reused input. A descriptive name such as DiscountRate can make a workbook easier to read than a cell address. You can define a name using Formulas > Define Name in Excel for Mac. Review the name’s scope and the cells it refers to before relying on it in formulas.
-
Fill a whole range consistently. If you have a long list, enter and check the first formula before filling down. You can also select the formula cell and the cells beneath it, then use Home > Fill > Down. Inspect a few formulas near the top, middle, and end of the range.
-
Keep your source input easy to find. Put shared rates or assumptions in clearly labeled cells and consider applying a distinct fill color. A visible label next to F1 makes it easier for someone reviewing the workbook to understand why the formula keeps referring to that location.
Troubleshooting
-
Command+T does not change the reference. First confirm that the cell is being edited and that the cursor is inside the reference. Try selecting the reference characters, then press Command+T again. If your Mac uses a different shortcut mapping or Excel version, enter the dollar signs manually. Avoid assuming that the Windows F4 shortcut behaves identically on a Mac; function keys may also be assigned to macOS controls.
-
The formula changes to the wrong reference style. The shortcut cycles through all four forms, so one press may not produce the style you want. Keep pressing it until the dollar signs appear in the right places, or edit the reference directly. For example, a fixed column and changing row must look like $F1, not F$1.
-
Copied formulas show zeros, blanks, or errors. Select one of the affected cells and read its formula in the formula bar. Check whether the supposedly fixed address moved, and confirm that the relative reference points to the intended row. Also check whether the fixed input cell contains a number stored as text, is blank, or contains an unexpected value.
-
The formula appears as text instead of calculating. Check whether the cell is formatted as Text or whether an apostrophe was entered before the equals sign. Change the cell format to General, edit the formula, and press Return. If Excel is displaying formulas throughout the sheet, turn off formula display from the Formulas tab or use Control+` if that shortcut is available on your Mac keyboard.
-
The number is correct in one row but wrong farther down. Check whether the formula was filled through the intended rows and whether any cells in the input column are blank or contain text. Then inspect the formula references in a distant row. An absolute reference prevents movement; it does not verify that the chosen fixed cell contains the correct input.
Frequently Asked Questions
What does an absolute reference do in Excel?
An absolute reference keeps both the column and row fixed when you copy or fill a formula. Dollar signs show the locked parts: $F$1 always points to column F, row 1.
What is the shortcut for an absolute reference in Excel for Mac?
While editing a formula, select or place the cursor in the cell reference and press Command+T to cycle its style. If it does not work in your setup, type the dollar signs directly.
Is F4 the absolute-reference shortcut on a Mac?
F4 is commonly used for this task in Excel for Windows. On Mac, Command+T is the usual reference-cycle shortcut, though keyboard settings and Excel versions can affect shortcuts. Manual dollar-sign editing works regardless.
What is the difference between $A$1, A$1, and $A1?
$A$1 locks both the column and row. A$1 locks only row 1, while $A1 locks only column A. These mixed references are useful when formulas need to move in one direction but remain fixed in the other.
Do I need to lock a reference before copying a formula?
Only if part of the reference must keep pointing to the same row or column. If every reference should move with the formula, leave it relative. If it should never move, make it absolute before copying.
Can I use absolute references in Excel for the web too?
Yes. Dollar-sign reference notation works in Excel for the web as well. Shortcuts can differ by browser and operating system, so typing the dollar signs directly is a dependable option there, too.
Related
- Excel Power Query: Extract Text from a String
- Extract Email Addresses From Text in Excel 365 Easily
- Remove Spaces in an Excel Column: Step-by-Step
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Group Rows at Multiple Levels: Steps and TipsYes. In Excel for Microsoft 365 on Windows, you can group rows in nested levels: select a set…
- How Text to Columns Works in ExcelExcel’s Text to Columns tool splits the contents of one column into separate columns, using either characters such…
- Excel Goal Seek: How It Works and When to Use ItIn Excel, Goal Seek is a What-If Analysis tool—not a worksheet function—that finds the input value needed to…
- Make a Fillable Form in Word: A Step-by-Step GuideTo make a fillable form in Microsoft Word, turn on the Developer tab, add content controls such as…
- Data Validation Plan in Excel: Build Reliable ChecksA data validation plan is a clear set of rules for what information a worksheet will accept, how…
- Microsoft Word: How to Add a Header and Page NumberIn Microsoft Word for Microsoft 365 on Windows, choose Insert > Header, select a layout, and type your…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.