The F4 key in your keyboard is usually used to lock the values in cells in Excel. But sometimes the key doesn’t work for some reason. In this article, we will tell you why the F4 key is not working sometimes in absolute cell reference in Excel and how to fix that.
Following is a free Excel file based on Absolute Cell References. You can download the file to practice.
What is Absolute Cell Reference in Excel?
Absolute cell reference in Excel is used when we want to lock the position of selected cells in any formula so that its value will be fixed and won’t be changed whenever the cells are being copied to other cells or sheets.
This can be done by simply putting the dollar ($) sign before the cell references. For example, if you want to lock the value of Cell B2, then it will look like $B$2 with absolute cell reference.
What Does F4 Do in Absolute Referencing in Excel?
F4 is one of the most-used keyboard shortcuts in Excel. One of its common uses is to lock references to make a cell reference absolute. The F4 key allows adding the dollar sign ($) with a single keystroke. The referencing changes depending on how many times the F4 key is being pressed.
Even though you can type the dollar sign manually, if you have so many formulas then using this shortcut key can really save you a lot of time.
3 Solutions on F4 Not Working in Absolute Cell Reference in Excel
In this section, you will learn 3 reasons why the F4 key is not working in absolute cell reference and quick solutions on how to fix that in Excel.
1. Activate Edit Mode to Make F4 Work in Absolute Cell Referencing
If you just select a cell and press F4 then it won’t change the cell to an absolute cell reference. You have to make sure that you are in “Edit mode”, meaning you must place the pointer of your mouse inside the reference of the formula before you hit the shortcut key.
To enter the “Edit mode”:
- First, place the mouse pointer on the cell and press F2 from your keyboard or double click the cell. It will turn the cell into “Edit mode”.
- Then use the left and right arrow keys of the keyboard to move through the formula and press F4 whenever you need it.
As a result, it will insert the dollar sign ($) to make the cell reference absolute.
- Relative and Absolute Cell Address in the Spreadsheet
- Excel VBA: Get Cell Value from Another Workbook without Opening
- How to Use Cell Value as Worksheet Name in Formula Reference in Excel
- Excel VBA: Insert Formula with Relative Reference (All Possible Ways)
- Mixed Cell Reference in Excel (4 Examples)
2. Press Fn + F4 Together to Create Absolute Cell Reference in Excel
This solution is the most accepted solution in fixing the F4 key problem in absolute cell referencing.
There are some keyboards where the F4 key controls the system’s specific performances such as adjusting the volume or screen brightness etc. In that case, you have to press the Fn (Function) key or F Lock (Function Lock) key on your keyboard, and hold it down (the key varies from system to system) before pressing the F4 key.
It will work just fine.
3. Alternative of F4 for MAC User for Absolute Cell Referencing in Excel
If you are a MAC user then pressing F4 won’t change the cell reference. Using the F4 key to lock cell reference only works on Windows.
To toggle between absolute and relative references in MAC, you have to use the shortcut ⌘ + T.
To conclude, this article showed you 3 reasons and solutions on how to fix F4 not working in absolute cell reference in Excel with the VBA macro. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.
- Excel VBA: R1C1 Formula with Variable (3 Examples)
- How to Reference Text in Another Cell in Excel (14 Ways)
- How to Find and Replace Cell Reference in Excel Formula
- Excel VBA: Cell Reference in Another Sheet (4 Methods)
- How to Use Variable Row Number as Cell Reference in Excel
- [Fixed!] Relative Cell Reference Not Working in Excel