9 shortcuts for working more efficiently with Excel expressions
Most
of us spend a lot of time entering expressions—they're the backbone of the
entire workbook. Fortunately, you don't have to be a ninja typist. Excel
provides many shortcuts that we can use to enter and modify expressions more
efficiently. In this article, I'll show you nine of them. You might know some,
but most likely you'll learn something new and useful.
I'm
using Office 365's Excel 2016 (desktop), but most of these will work in earlier
versions, and some of them will work in the browser. There's no demonstration
file; you won't need one.
Terms
Before we start reviewing shortcuts, let's clarify the difference between an expression and a function. Within the context of this article, the shortcuts work with both.
An Excel function is a built-in set of instructions for evaluating values that you specify as literal values or cell references. In relaxed terms, a function is or can be part of an expression. An expression is an equation that uses operators and data to return a result (or value). Many people use expression and formula interchangeably, and in Excel terms, that's fine. In classical terms, the formula is the idea (or syntax), and the expression is the execution.
In short, all functions are expressions; not all expressions are functions. To simplify things, I'll use the term expression, unless function is more appropriate.
1. AutoSum
By far, the easiest way to enter one of Excel's most commonly used functions, such as SUM(), AVERAGE(), MIN(), MAX(), and so on, is to let Excel do it for you. In fact, you're probably already familiar with AutoSum. Using this tool, you can click a blank cell adjacent to a row or column of values to enter a SUM() function that will total those contiguous values.
Figure A shows the results of selecting F15, and then clicking AutoSum in the Editing group (on the Home tab). Or, you can press Alt+=. As you can see, Excel enters a SUM() function that references the contiguous cells above, F3:F14. The feature is smart enough to know that the value in F2 is text and stops looking for values. The only thing left for you to do is press Enter.
Figure A
 |
Use AutoSum to quickly evaluate contiguous values. |
This feature is flexible. It works with rows and columns, and you can change the function from SUM() to another function by choosing another one from the AutoSum dropdown—it's an extra click because SUM() is the default. In addition, you can select specific cells and the adjacent blank cell to evaluate a range of cells rather than including all contiguous cells. You can select several blank cells to enter multiple functions at the same time.
2. Quick absolute referencing
You can't work with Excel until you understand absolute and relative referencing. An absolute reference is denoted by a $ sign before the row or column component; this type or reference refers only to the component specified. Relative referencing is the default and requires no special character. When copying the expression, the relative references update according. Mixed references are a combination of both.
To assign absolute referencing, you precede the component with the $ character: $F$3, $F3, F$3. You can enter these manually of course, but it isn't necessary. If you're working with a complex referencing pattern, you might find it easier to use the F4 key. (The F4 key doesn't work this way in the browser.) Instead of typing $F$3, which is a bit tedious with the shifting back and forth, click the cell or enter the reference, and then press F4 to make both the row and column absolute.
o modify an existing reference, highlight the appropriate component and press F4, as shown in Figure B. You can also cycle through the different absolute and mixed references by pressing F4 multiple times. You might not get the exact results you want, but you'll get close and then you can tweak it by changing only a few components. Experiment a little and see how easy it is.
Figure B
 |
Use F4 to express absolute referencing. |
3. AutoComplete
When entering one of Excel's built-in functions, use AutoComplete to reduce the number of characters you must type. As you enter characters, Excel displays a list of possible completions—a form of AutoComplete, as shown in Figure C. Press Tab to complete the function, based on the selection in the list. For instance, type =aver and press Tab; Excel will complete the function up to and including the opening parenthesis. At this point, you're ready to start entering arguments.
Figure C
 |
Use AutoComplete to quickly complete a function's name. |
4. Argument help
When entering functions, you can use AutoComplete (No. 3) to quickly complete the function's name. If you need help entering the arguments, you can click Insert Function (fx to the left of the formula bar). Or, you can press Ctrl+a. Doing either opens a dialog with the current function and a list of arguments, as shown in Figure D. To access this dialog with an existing function, select the cell and press Shift+F3 to display this dialog.
Figure D
 |
Click Insert Function to access a list of arguments. |
You can enter the arguments manually, but the dialog helps when you're uncertain about the data types, their order, and so on. You won't always need this dialog, but when you do, it's invaluable.
You don't need the dialog to get help though. Enter the function using any method you like up to and including the opening parenthesis. Then, press Ctrl+Shift+a. Excel will display the arguments. You'll have to replace them but doing so is simple. Excel highlights the first argument; simply type the replacement from the keyboard or click a cell or range. Then, double-click the next argument and replace it. Continue until you're done. (Most of these shortcuts don't work in the browser.)
5. Display formulas
By default, Excel displays the results of expressions, not the actual expression. If you want to see the actual expressions instead, press Ctrl+` (that's the backtick character). Doing so provides a quick overview of all your expressions, as shown in Figure E. Press the combination again to show the results—it works as a toggle. (This shortcut doesn't work in the browser.)
Figure E
 |
Display expressions instead of their results. |
6. F2
By default, you must modify expressions in the formula bar, which requires grabbing the mouse, clicking the formula bar, and so on. You might find it easier and more efficient to edit the expression in the cell. To do so, press F2 to access in-cell editing mode. Doing so allows you to use the keyboard to edit the expression in the cell.
7. Troubleshooting
If an expression doesn't return the expected results, you can calculate components in the formula bar by selecting a section and pressing F9. Doing so will return the results of the selected selection in the formula bar alongside the rest of the formula. The expression shown in Figure F is simple on purpose, but you can see the sequence of calculations. (This shortcut doesn't work in the browser.)
Figure F
 |
Calculate pieces of an expression in the formula bar. |
