Software

9 shortcuts for working more efficiently with Excel expressions

Typing entire expressions from the keyboard is tedious. Instead, use these 9 shortcuts to enter and work with expressions more efficiently.

istock-845705578-excel-woman.jpg
Image: Wavebreakmedia, Getty Images/iStockphoto

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.

SEE: Windows 10 power tips: Secret shortcuts to your favorite settings (Tech Pro Research)

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

excelformulashortcuta.jpg
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. To learn more about this powerful and flexible feature, read Unleash the power of Excel's AutoSum tool.

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.

SEE: Manage Active Directory with these 11 PowerShell scripts (Tech Pro Research)

To 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

excelformulashortcutb.jpg
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

excelformulashortcutc.jpg
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

excelformulashortcutd.jpg
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

excelformulashortcute.jpg
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

excelformulashortcutf.jpg
Calculate pieces of an expression in the formula bar.

8. Forget the closing parenthesis?

When entering an expression or function, you don't need to type the closing parenthesis. When you press Enter, Excel will add it for you.

9. F3

Entering a defined name in an expression is easier than entering the same range reference, and they're self-documenting. Thanks to F3, they're also easy to include. While entering an expression from the keyboard, you can press F3 at any time to access a list of defined names. Simply select one to include it in your expression—it's much easier than typing and you eliminate the possibility of typos.

Send me your question about Office

I answer readers' questions when I can, but there's no guarantee. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.

See also

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox