These are the top 16 Excel function and formula tips every consultant at any level of experience should know.
Even “experts” can learn new tricks or combine old tricks to work faster and more efficiently.
As you scan these tips keep a “beginner’s mind.” A beginner’s mind helps you see new opportunities and insights.
“In the beginner’s mind there are many possibilities, but in the experts there are few.” Shunryu Suzuki (1905-1971)
Help another Excel user. Pass this on!
Save time when editing and selecting in the formula bar. Use the same shortcut keys for selecting that you use in Microsoft Word or PowerPoint.
In the following table, the term “word” is used to describe a cell reference, function name, text, or another group of characters in the formula bar or cell.
With the mouse,
Select characters Click and drag
Select a “word” in a formula Double-click it
Select “words” in a formula Double-click the first “word”, then drag
With keys, press F2 to edit a cell or click in the formula bar,
Select characters Press Shift+arrow
Select “words” in a formula Press Shift+Ctrl+arrow
Select to beginning Press Shift+Home
Select to end Press Shift+End2. Type functions in lower case to prevent typos
Type functions in lower case. If you type them without misspellings, they will convert to uppercase when you press Enter.
Excel automatically enters the comma after a cell reference in a function if you Ctrl+click on a cell instead of just clicking the cell. Your first click enters the first reference, but follow that with a Ctrl+click on the next reference and you will see the comma automatically inserted.
If you have to interrupt your work, you may want to save your partially completed formula. To do that yype a single quote or apostrophe (‘) before the equal sign to turn the function or formula into text. When you are ready to return and complete it, just delete the apostrophe (‘) and continue working on the function or formula.
Some formulas are complex and difficult to understand, especially if the formula was created months earlier or was created by someone else. Two ways to add documentation to your formulas is to use range names, described later, and to attach a note to the formula.
Attach a text note to a formula with the N function. The N function returns a number for cell values, for example, TRUE is returned as 1. But it returns zero, 0, when used on text so you can add it to the end of a formula without changing a formula’s results.
In the simple example shown the formula is D1*D2. Adding the zero returned by the text inside the N function doesn’t change the result. This is a silly, super simple example. To see more realistic examples, see the Critical to Success article “Document your formulas right in the cell!”
Excel helps you remember the arguments needed inside functions. As you begin typing a function, Excel’s Autocomplete shows a list of the most probable functions under the formula bar. You can continue typing or you can select one of the functions by clicking on it or by pressing the down-arrow to select one, then press Tab.
Once you select a function, Excel displays the arguments used by the function. The current argument appears in bold. Optional arguments appear in square brackets, [ ].
If you are not sure of the function you need, click the Insert Function tool to the left of the formula bar to display the Insert Function dialog box. Use this box to search for categories of functions, such as financial or statistical, and see a description of the function.
To use Insert Function,
1. Position the insertion point in the Formula Bar where you want the function.
2. Click the Insert Function icon to the left of the Formula Bar. The icon looks like Fx.
3. Select a category of function, then select a function from the list to see a description of what it does.
4. Double-click the function you need. The Function Argument dialog box will appear to guide you through using the arguments to complete the function.
5. Click Ok to enter the function and arguments into the formula bar.
When Excel displays a function name and its arguments, as in tip 6 above, it’s easy to replace the argument names in the formula bar with cell references. Select an entire argument by double-clicking on it. Once the argument name is selected, click the cell reference you want to replace the argument name.
If you are using range names for your function arguments, tip 10 shows you how to quickly and accurately enter range names using the F5 key.
Range names are human language synonyms or abbreviations for Excel cell references. Range names make formulas easier to understand, for example, rngInitialPayment is more understandable than $C$54.
They are one of the powerful features all Excel beginners should learn.
Learn the basics in this article, “LQuickly Create Range Names.”
Range names are easier to understand and locate in lists of range names if you use a structured naming convention. Learn one convention for structured range names in this Critical to Success article, “Use a Structured Prefix to Identify Range Name Types.”
Another trick in using range names is in this article, “Create Range Names with Upper and Lower Case, but Type them in Lower Case.”
Even though your worksheet may contain tens or hundreds of range names there is a trick that can help you enter them quickly into functions and formulas.
The Go To dialog box displays lists range names from which you can enter a range name into a function by double-clicking it.
To quickly and accurately enter range names, enter the function to the point where you need the range name, press F5 to display the Go To dialog box. Click the range name you want, then click Ok.
If you have used Autocomplete and tab, as described in tip 6, you will see the function and its argument names. To replace an argument name with a range name, just double-click the argument name, press F5, then double-click the range name to enter.
Range names are great. Once you have created them you can enter them in formulas and Excel automatically understands them. But, if you first create a function or formula and then create the range names used for the same references Excel does not automatically update your formula or function to include the range names.
To apply range names to existing formulas or functions,
1. Select the cells containing formulas and functions you want to use the new range names.
2. On the Formula tab, in the Defined Names group, click Apply Names to display the Apply Names dialog box.
3. Select the names you want to apply and click Ok.
Complex, multi-segment formulas are sometimes mindboggling to create (at least for my mind), especially when they involve multiple nested IF statements. Instead of trying to create and troubleshoot a long complex formula, it is usually easier to build it in segments with each segment in a separate cell. This makes each segment easier to understand and validate.
Once you have the segments validated you have two choices. If you want to make the complex formula easier to understand in the future, leave the segments in separate cells and reference them from a formula that integrates the segments. If you want a long complex, gnarly formula, then copy and paste the segments into the formula bar to create one long formula.
An aide to understanding complex formulas is to attach a note to the formula itself. Tip 5 explains how to attach a note at the end of a formula.
When you create complex functions or formulas it is a good idea to check internal formula segments to make sure they calculate correctly. This is especially true when working with nested IF functions or functions involving logical choices.
To calculate part of a formula, first enter the function or formula, then select the portion to calculate, then press F9. The results for that portion will show within the formula in the formula bar. Press Esc to undo the partial calculation.
For this to work you must select a formula segment that can be calculated as though it was a standalone formula.
See tip 14, “Use the Formula Evaluator to step through complex formulas” for another method of seeing how the internal segments of a formula calculate.
Sometimes you need better tools than tip 13 or tip 12 to evaluate how a complex formula works. You need a tool to see the calculated results of small inner segments and decisions as they grow outward to the final result.
Microsoft Excel has the Evaluate Formula tool for that purpose. Evaluate Formula shows you the calculated result and logical choices of each segment within large formula. For example, the following formula in cell E7 is used in the example that follows. The formula checks if the largest number in the range C7:C11 is greater than 70. If it is greater, then “Limit exceeded” displays. If it is not greater, then “In limits” displays.
=IF(MAX(C7:C11)>70,”Limit exceeded”,”In limits”)
Evaluate Formula only evaluates the formula in one cell at a time. To evaluate a formula,
1. Select the cell containing the formula.
2. On the Formulas tab, in the Formula Auditing group, click the Evaluate Formula tool to display the Evaluate Formula box. The unevaluated formula appears in the Evaluation box. The underlined segment shows what will be evaluated next.
3. Click Evaluate to evaluate the underlined segment. The result of MAX(C7:C11) is 78, so the next underlined segment to be evaluated is 78>70. Click Evaluate to evaluate this segment.
4. The 78>70 segment evaluates to TRUE. The next underlined segment is the entire IF function. Click Evaluate.
6. The IF function evaluates to the TRUE result, “Limit exceeded.”
If the underlined segment of the formula references another formula, you can click the Step In button to “step into” the other formula. To return to the original formula, click Step Out.
Learn more about the Formula Evaluator at the Microsoft support site.
Warning! Converting formulas to values can be dangerous.
One of the costliest worksheet mistakes I ever found was probably the result of someone converting formulas into values. I was hired to rebuild a series of international product pricing worksheets used by accountants in a medical devices company. I started by first auditing the worksheets to make sure I understand how they worked and to validate the formulas.
What I found was that some formulas had been converted into fixed values (numbers). As a result, policy and pricing decisions were being made on revenue and units that were wrong by hundreds of thousands of dollars!!!
Use this, but be careful.
To convert one or more formulas into fixed values, select the cells containing formula and copy them with Home, Copy or with the shortcut key, Ctrl+C.
With the same cells selected, on the Home tab, in the Clipboard group, select Paste, Paste Values, and choose the Paste Value tool.
When you are faced with a completed worksheet it can be time-consuming to search for cells containing formulas. Use this trick to locate cells containing a formula.
1. To select all cells containing formulas in a worksheet, select a single cell. To select cells containing formulas within a range, select the range.
2. Press F5, the Go To key, and click the Special button. Or on the Home tab, in the Editing group, click Find & Select, Go To Special. The Go To Special dialog box displays.
The Go To Special dialog box enables selecting all formulas producing a specific type of result.
3. In the Go To Special dialog box, select the Formulas option, then select the types of formula results you want to locate. Select one or more of the following formula results,
Numbers Formulas resulting in numeric values
Text Formulas resulting in text values
Logicals Formulas resulting in TRUE or FALSE
Errors Formulas resulting in an error value, such as #REF! or #N/A
4. Click Ok
All cells containing the type of formula you are looking for will be selected.
When auditing a worksheet, it is helpful to permanently identify cells containing formulas. To do this, first save the worksheet with a special name different from the original, such as originalname-audit.xlsx. Run the procedure above to select all cells of the type you want, then while the cells are selected, format the selected cells with a background color.