Stop pressing F4 on every cell reference, and stop copied formulas from breaking their references. Paste your formulas below and lock them all once.
Text inside quotes is never changed. Nothing is uploaded; formulas are processed in your browser only.
How to use the bulk formula reference locker
- Paste your formulas into the box, one per line.
- Choose the conversion: Absolute $A$1 (lock row and column), Lock row only A$1, Lock column only $A1, or Remove $ (all relative).
- Optional: in “Only lock these cells”, list just the cells you want locked, separated by commas (for example, B1, C3). Leave it blank to convert every reference. Then copy the converted formulas back into Excel or Google Sheets.
Worked examples
| Original formula | Setting | Converted formula |
|---|---|---|
| =A2*B1+SUM(C3:C5) | Make Absolute | =$A$2*$B$1+SUM($C$3:$C$5) |
| =A2*B1 | Only B1 locked | =A2*$B$1 |
| =A2 | Lock row | =A$2 |
| =A2 | Lock column | =$A2 |
What each mode means
$A$1 locks both the row and the column. The reference never moves when you copy the formula anywhere.
$A1 locks the column only. The row still shifts when you copy down.
A$1 locks the row only. The column still shifts when you copy across.
Mixed references matter when you copy across rows or columns. They keep one part fixed while the other part follows the copy.
Lock the column when every column should point at the same lookup column. Lock the row when every row should point at the same header row.
When in doubt, use Absolute $A$1. A fully locked reference is safe to copy anywhere.
Pick the mode that matches your copy direction before you paste.
Common mistakes
- Pressing F4 for every reference by hand. Paste the whole list here instead and convert them all at once.
- Locking text inside quotes. A cell address inside quotes is just a text label, not a reference. It is never changed.
- Locking every reference when only one needs locking. Use the “Only lock these cells” box to pin just the ones you need.
- Forgetting that a range like C3:C5 locks each cell separately. Make Absolute turns it into $C$3:$C$5.
FAQ
How do I make all references absolute in a formula?
Paste your formulas into the box and choose Absolute $A$1. Then copy the converted formulas back. Every A1-style reference in every formula is locked at once.
What is the difference between $A$1, $A1 and A$1?
$A$1 locks both row and column. $A1 locks the column only, so the row still shifts when you copy down. A$1 locks the row only, so the column still shifts when you copy across.
How do I lock references in Google Sheets without pressing F4 for each one?
Paste your formulas into this tool and pick the lock mode. Then copy the converted formulas back into Sheets. It works on whole lists at once.
Can I lock only some references and leave others relative?
Yes. Type the cells you want locked in the “Only lock these cells” box, separated by commas (for example, B1, C3). Only those are converted. The rest stay relative.
Will the converter change text inside quotes or structured table references?
No. Text inside quotes is never changed. Structured table references such as Table1[Column] are left alone. Only plain A1-style cell references are rewritten.
Try the bulk formula reference locker above on your own formulas. It takes seconds and beats fixing broken references by hand.