Podcast: Play in new window | Download

Subscribe: Spotify | TuneIn | RSS

The impetus for this episode is a new Google Sheet (and Excel) tip I just shared on Instagram and TikTok (I never thought I’d join these platforms to start posting tips but alas, this is how people learn these days). After I learned how to be productive the PC version of Excel, I opened my Macbook and realized all my favorite shortcuts didn’t carry over. Back in the day (whatever that means), you had a ThinkPad at work where you did your “serious work” and your personal Mac was for doing the “personal stuff.” I found the PC equivalent shortcuts for the Mac and was able to be dangerous again in Excel and Google Sheets on the Mac. But there was one group of shortcuts I couldn’t quite duplicate until I changed one little setting on my Mac.

Function keys on the Macbook

By default, the function keys on your Macbook do things on your Mac OS like increase brightness (F2), see/search Mac apps (F4), or decrease volume (F11).

If you’re coming from using Excel on the PC, you know that these function keys are coveted tools in being faster in Excel (and Google Sheets). The most useless key is probably F1 because it brings up the help menu, and you might hit it by accident when you’re debugging a formula and alternating between pressing F2 and ESC. Excel users will go as far as popping out the F1 key from their keyboard so they don’t accidentally hit it and having to wait a few seconds for the help menu to open only to close it right away:

Change the default behavior of the functions keys for Mac Excel

To “unlock” the power of the function keys on your Mac so that they do what you expect them to do (like on a PC), follow these steps (this is for MacOS Big Sure v11.4):

1. Click the Apple icon in top-left and open system preferences

2. Click on “Keyboard”

3. Click on the “Use F1, F2, etc.” checkbox

With this simple setting checked off, you can now use the function keys like you’re used to using them on the PC. The downside is (of course everything has a tradeoff) is if you want to increase the brightness on your Mac, you need to press the FN key PLUS the F2 key. With these function keys enabled, some of these common operations become available to you (using Google Sheets as an example):

Enter “edit” mode in a cell formula (F2)

By tapping F2 you can go into the cell and start editing the formula. Once you’re in the formula you can use the arrow keys to move around and when you’re done editing the formula, just hit ESC. In the gif below, I’m just alternating between hitting F2 and ESC to get into the formulas in B2 and C4:

Alternate between locking and unlocking cell references (F4)

One of my favorite shortcuts when working on a big hairy formula is locking and unlocking the cell reference without having to manually type in the dollar sign in front of the column or row. The manual way of doing this is using your left and right arrows to move to the different cell references and typing in the dollar sign (or deleting them one by one):

With the F4 key, you can move your cursor to the cell reference and cycle between locking the row, the column, both the row & column, or nothing at all and keeping the reference as relative:

I tried to show the power of the F4 key in a pithy way in my first Instagram post. Let me know if this is interesting…or not:

Go to special cells (F5)

This shortcut only applies to Excel, but it allows you to bring up the “Go to” menu. This is helpful if you want to quickly go to a named reference or an Excel table. I like to use it for formatting purposes by going to blank cells and formatting them a certain way. This beats having to manually select each empty cell and pressing CTRL (PC) or COMMAND (Mac) as you click the empty cells:

Hopefully this one little trick for “unlocking” the function keys on your Mac can save you some time from clicking around in Excel. More importantly, I hope it shows that Excel or Google Sheets on the Mac are just as powerful as the PC and you should feel confident you can get your real work done on your Mac.

2021 Excel Tables class on Skillshare

This week I launched my 2nd advanced Excel course on Excel tables called Mastering Excel Tables: How to Make and Use Them Like a Pro. As I mention in the intro video, Excel tables are a relatively under-utilized feature in Excel but can really speed up your dashboard creation, make your formulas less error prone, and make it easier for your colleagues to understand how you built your model. In less then 40 minutes, you can learn all the essentials of Excel tables as well as some advanced features for building a robust data-capturing system.

Other Podcasts & Blog Posts

In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

• The Pomp Podcast ep. #563: Kevin O’Leary Buys Bitcoin And Starts Yield Farming