F4 No Longer Changes Cell References

by Allen Wyatt
(last updated September 6, 2014)

31

Hazel recently bought a new computer that uses Windows 8.1. She has found that she can no longer use F4 as a method of toggling a cell reference through the absolute/relative settings. Instead, F4 opens a sidebar about projecting to a second monitor. As Hazel only has one monitor, but lots of requirements to set cell references to and from absolute, this is driving her crazy. She would like F4 to work as it traditionally did in Excel.

Testing on a Windows 8.1 system indicates that the F4 key still cycles through absolute/relative versions of references. There is a caveat, however: You need to make sure that you are in "edit mode" when it comes to using F4 with formulas. In other words, select the cell you want, press F2 to enter edit mode, use the left and right arrow keys to move through the formula, and press F4 whenever you want to cycle a reference through various permutations of absolute and relative notation.

If the F4 key still doesn't work as you expect, then it is a good bet that you are using a laptop computer or a super-duper, multi-function keyboard with your desktop system. On these types of keyboards the function keys typically do special things, such as adjust your volume, adjust screen brightness, etc. To get to the normal operation of a particular function key, look for a key labeled something like FN (short for "function"). Hold down that key as you press the F4 key, and it should work as you expect.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13235) applies to Microsoft Excel 2007, 2010, and 2013.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Inserting the Current Month

Need to add the name of the current month to your document? Word includes a field that can make the addition easy, and it ...

Discover More

Getting a Count of Unique Names

When you've got a column full of names, you may want to get a count of how many of those names are unique. You can make quick ...

Discover More

Locking a Worksheet Automatically

The protection features built into Excel can help you to make sure that only things are changeable that you want changed. ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Can't Access the Registry

Many Windows applications rely on information stored in the Registry. If that information cannot be accessed, the application ...

Discover More

Odd Arrow Key Behavior

Press the up or down arrow keys, and you expect Excel to change which cell is selected. If this doesn't occur on your ...

Discover More

Changing the Color Used to Denote Selected Cells

When entering data into a range of cells, the cell in which you are working appears in a different color than the other cells ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 8Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 - 4?

2017-06-24 09:07:54

Diane

Very helpful...Thanks!!!


2017-06-02 15:36:11

sydney scelza

You are a magician - thank you! F4 was my go to key for "repeat last action" action and it drove me nuts that I couldn't use it. Now I can!


2017-04-24 09:51:31

AaronC

Thanks so much; this has been driving me crazy since i recently got a new work computer (Dell Inspiron)


2017-04-05 09:37:39

Paul

Thank you been driving me mad since I got this windows 10 PC


2017-03-23 17:13:27

Jarod Rollins

My keyboard has a function lock that was on, I pressed the function lock key that looks like an F in a square box followed by the word lock, and then F4 worked for changing cell references


2017-03-16 05:10:24

Sandy

Thank you so much. Longer time problem solved


2017-02-21 10:44:51

Liisa

Adolfo Gonzalez, that did the trick for me! Thanks! That explains why it used to work and then quit. I didn't know what that key did.


2017-02-20 20:16:12

Hippy Powers

Hi Wyatt, I ran into this in class the other day. Apparently you can also use the Windows button instead of FN. However, the point is, MS has ruined a perfectly normal shortcut that has been with us for about two decades. Who the hell decides these things??? Certainly not us. Bill


2017-01-18 10:56:02

Mallablue

Nice one. Pressing the FN and F4 key simultaneously solved the trick. :-)


2016-10-03 08:49:15

David

I kept searching after my last post (03 Oct 07:12) and found a solution.
The setting is not Windows related, it is PC related. Need to update BIOS.
Found instruction here, https://www.youtube.com/watch?v=ssg0TlGCtu4


2016-10-03 07:12:56

David

Just bought a new HP running Windows 10 and Office 2016. I have the same problem as the original article. I press f4 to repeat last action or cycle through absolute cell refs, yet all I get is the projector/dual screen option. The same with F2 - instead of editing, my screen dims.
By holding the Fn key before pressing the keys, returns to the expected functionality, which is a part solution. BUT I DON'T WANT TO HAVE TO HOLD THE FN BUTTON DOWN EVERY TIME. I use these functions all the time. I'd rather hold down Fn if I want to switch display (like almost never!). Anyone have any idea? I've searched extensively and this is the closest I've come, without solution.
Thanks...


2016-07-29 03:46:15

David Smith

Professor Brian Revell and Charley thank you for your suggestion for those using MS Excel 2016 with MS Windows 10 operating system and MS Microsoft ergonomic keyboard.


2016-07-08 06:51:34

Marjatta

Hi,

I do indeed have a Toshiba Laptop with Windows 8.1.

If I try to use F2 first, it indeed allows (only) to readjust brightness.

Using FN&F4 removes the original formula (in this case with absolute references to another page in the same workbook) and replaces it with a relative ref. formula on the same page, actually to the cell next to right of the original...

And if I try to do again in an other cell, the same procedure affects nothing whatsoever. Weird, isn't it?

So I am stuck with my original problem!

Marjatta


2016-07-07 15:23:34

Michael

You can hold the windows key down and then press the F4 key as you did before and it will lock your formula...


2016-06-23 10:06:34

Sananda sarkar

Thanks..I was not able to use f4 keys in excel...but now i can by pressing fn keys.Thanks again sir.


2016-04-30 00:25:32

Roman Buchanan

My laptop still lacks the ability to utilize F4 even whilst holding the FN key, nor does it possess this valuable FLock everyone seems fond of. Any other suggestions?


2016-04-26 17:22:46

Adolfo Gonzalez

Thanks. I have a Dvorak keyboard and it's been puzzling me for a very long time. On my keyboard the special key that you suggested as "FN" is labeled "f LOCK" with the "f" inside a box. It unlocked the special function keys and returned them to normal use.

Thanks again!


2016-03-01 12:41:18

Owaiz

Thanks have to use FN now for freeze always....


2016-02-09 05:06:21

Crystal

Thank you. This solved the problem I was having with excel. I'm taking an excel class and I could not figure out what I was doing wrong. But now I know I just needed to activate the Function Key.


2015-11-24 11:12:18

Professor Brian Revell

I am running Office 16 under Windows 10. Whilst the F4 key worked perfectly under windows 8.1 on my Samsung laptop, it does not do so now.

nor does holding the Fn key down with F4 or F2 enter edit mode.

However, by initally pressing th Fn Lock key, the F4 produces the absolute cell referencing in the formula.


2015-07-20 04:58:55

Duncan

@Carolyn
While I like Willy's tip (which I didn't know), if you want more control - for instance because you've got a lot of references and only some are absolute, you can press F4 after entering each reference and only that reference will be affected. This has always been my habit as I build up a formula.


2015-07-18 06:35:08

Willy Vanhaelen

@Carolyn
Simply highlight the entire formula before pressing F4. You can also add the $ signs manually.


2015-07-17 11:31:57

Carolyn

This is great, except when I have a calculation of two cells, and I want both set as absolute cell reference.

For example, A1*B1. When I set absolute cell reference, it only sets B1, so the formula is A1*$B$1. If I copy that cell to, say, I10, it changes the A1 reference to G10, and I get zero since there is nothing in G10.

Any suggestions to set all components of the calculation to absolute cells?


2015-03-02 16:54:19

sandra

I don't have a FN on my computer. im using a gateway computer with windows 8.1


2015-02-28 13:12:02

Jim Conlon

I have a problem with the F keys, F5 in particular. This was my edit key in Windows Vista. Now I have Windows 8.1 where the F keys do not have the same function as in Vista. The Fn function does work in different circumstances. However, it does not let me edit a cell in excel like I used to do. I have to retype the contents of the cell in its entirety. That is a pain.


2015-02-17 03:04:12

sumit

Thanks for sharing your knowledge, the FN plus F4 key did the trick for me. Couldn't find this anywhere. Cheers


2015-01-11 10:09:13

Gadi Bizinyan

Good article. I have Dell Inspiron 3542 and the Escape key has a symbol of a lock with the letters "Fn" within. This is toggling the usage of the Fn key with the functionality of the F buttons.


2014-09-10 10:28:17

Charley

The key for enabling the function keys may also be labled "F Lock" (Microsoft ergonomic keyboard).


2014-09-08 05:43:58

Duncan

Many laptops which use the Fn key as indicated to trigger alternative meanings for the F-keys also have an option somewhere which toggles the behaviour when the Fn key *isn't pressed* between "use the F-key meaning" and "use the computeer control meaning".

You can use that to set you preferred default for the keys and this may be Hazel's long-term answer.

Unfortunately, where this option is found probably varies depending on your hardware manufacturer. And it may be a BIOS setting without a user-friendly switch within Windows itself.


2014-09-07 19:18:53

Dan

I have a similar thing when using my partner's Mac. A lot of my usual shortcuts are completely different and it makes work just that little bit more tedious.


2014-09-06 05:06:50

Lisa

Thanks I found this very helpful.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.