Even basic formulas require absolute and relative references, so one of the most useful formula shortcuts is the toggle for references. For example, to complete this multiplication table, we need to multiply the left column by the top row. This means we have to lock both the column and row references to make sure they don’t change. Use F4 on Windows and Command + T on a Mac to toggle through the 4 options for absolute and relative references. To temporarily display all formulas in a worksheet instead of their results, use the shortcut Control + `. Use the same shortcut again to turn off formulas. Whenever you need to enter the same formula in multiple cells, you have several options. You can double-click the fill handle after you’ve entered the first formula. You can enter the first formula and the use Control D to fill down. You can select all cells and use Control Enter instead of just Enter. Finally, if you convert the table to a proper Excel Table with Control + T, Excel will automatically copy the formula down the table for you. This worksheet uses the PROPER function to lowercase each name, and two more complicated formulas to extract the first and last name. To get rid of the formulas and leave only the result. Copy the formulas, then use Paste Special > Values to overwrite. Then you can remove the formulas and data you don’t need. You can also sometimes avoid formulas altogether by using Paste Special with a math operation. For example, to increase these prices by 10%, in place, I can use Paste Special with Values and Multiply. You can evaluate parts of the formula using F9. Just select a valid expression or argument, and use F9 in Windows and fn + F9 on a Mac. This is an extremely useful tool to understand and troubleshoot more complex formulas. You can use the function screen tip window to navigate and more precisely select arguments. When you’re working with a long formula that takes up many lines in the formula bar, you can expand the bar with Control +Shift + U. Use the same shortcut again to collapse. To enter any array formula, use Control + Shift + Enter. When you do this correctly, you’ll see curly braces on either side of the formula. Make sure you use Control + Shift + Enter again when you edit the formula. Finally in larger worksheets when you have automatic calculation turned off for performance reasons, you manually trigger calculation with F9 in Windows or Function F9 on a Mac. If you want to calculate only the active sheet, just add shift. To force calculation of all open worksheets, use control + Alt + F9.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
