Formula Errors

Fix F4 Not Creating Absolute References in Excel Formulas

Natalie Taylor Oct 9, 2026 869 views

Question details

The user is unable to use the F4 key to create an absolute cell reference inside an INDIRECT formula.

Fix F4 Not Creating Absolute References in Excel Formulas

Product: Excel
Device & OS: not provided
Scenario: Attempting to lock a cell reference (like $D$31) within a complex formula that references another worksheet using quotation marks.
Observed behavior: Pressing the F4 key does not convert the cell to an absolute reference, and manually typing the dollar signs inside the quotation marks results in a formula error.

Before you start

Verify your formula syntax to ensure the cell you want to make absolute is not accidentally enclosed within quotation marks, as Excel treats anything inside quotes as standard text.

Solution 1Recommended

Remove Quotation Marks Around the Cell Reference

Extract the cell reference from the text string so the spreadsheet application recognizes it as a true cell reference, allowing the F4 shortcut to work.

When a cell reference like D31 is placed inside quotation marks (e.g., "!D31"), the application treats it as a static text string rather than a dynamic cell reference. Because it is text, the F4 key cannot modify it, and manually adding dollar signs inside the text string often breaks the formula logic.

Select the formula cell

Click on the cell containing your formula and press F2, or click directly into the Formula Bar to edit the contents.

Separate text from the cell reference

Modify the formula to use an ampersand (&) to join the text string and the cell reference. For example, change =INDIRECT(T5&"!"D31) to =INDIRECT(T5&"!"&D31).

Apply the F4 shortcut

Highlight the D31 part of the formula and press the F4 key. It will now successfully toggle through absolute and relative reference types (e.g., $D$31).

Press Enter to save

Hit Enter on your keyboard to apply the fixed formula. The corrected formula should look like =INDIRECT(T5&"!"&$D$31).

Remove Quotation Marks Around the Cell Reference

Syntax Tip: The ampersand (&) is the concatenation operator. It seamlessly connects a dynamically referenced sheet name, the exclamation mark required for sheet references, and your absolute cell.

Solution 2

Use the Fill Handle to Copy Corrected Formulas

Once your formula uses proper absolute and relative references, use the AutoFill feature to quickly apply it across multiple cells without manual editing.

Powerful Spreadsheet Editor

Create and Manage Complex Formulas with WPS Spreadsheet

WPS Office Spreadsheet provides full support for advanced formulas, including INDIRECT, concatenation, and absolute references. It allows you to use familiar shortcuts like F4 to toggle cell locking effortlessly.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet document.
2. Enter your formula: Click on a cell and type your advanced formula, making sure to separate text and cell references with the & symbol.
3. Toggle references with F4: Highlight any valid cell reference in the formula bar and press F4 to instantly add or remove the $ signs for absolute referencing.

Fully compatible with Microsoft Excel formulas, functions, and formattingFree, lightweight, and fast-performing spreadsheet editorFamiliar user interface ensuring a seamless migrationBuilt-in error checking tools to help troubleshoot formula syntax easily

microsoft office alternative - wps office

Frequently Asked Questions

Why is the F4 shortcut not working on my laptop?

On many modern laptops, the function keys (F1-F12) are mapped to media controls by default. You may need to press Fn + F4 together to toggle absolute references, or enable 'Fn Lock' on your keyboard to use F4 directly.

What does an absolute reference do in a spreadsheet?

An absolute reference (indicated by dollar signs, like $D$31) locks a cell's row and column coordinates. When you copy or drag the formula to other cells, the reference remains fixed on that exact cell instead of shifting relatively.

Why does my INDIRECT formula return a #REF! error?

A #REF! error in an INDIRECT function usually means the text string does not form a valid cell reference. This can happen if the sheet name is misspelled, missing quotation marks around the exclamation point, or if the formula attempts to reference a closed external workbook.

How do I toggle between different reference types?

Highlight the cell reference in the formula bar and repeatedly press F4. It will cycle through absolute ($A$1), mixed row-locked (A$1), mixed column-locked ($A1), and relative (A1) references.

Excel & Data Problems