If you are a Microsoft Excel regular user, think how many formulas you are building a day. You need time and focus to build formulas correctly. This article brings you some good tips that aid you to improve productivity and save time with Excel formula.
Note: This post is created with 2013 version of Microsoft Excel.
1. Use tab to autocomplete functions
When you typing the function, Excel list down matching functions with your typing. You can use up and down arrows to select your formula and use tab to insert it. This is a really time-saving tip since you do not need to type complete function or move your fingers to touchpad or mouse to click and insert it.
2. Do not type function upper case
Microsoft Excel function names are shown in upper case. However, you do not need to type the function in uppercase. You can simply type in lover case and Excel automatically turns them into upper case. As shown in figure-1, when you hit Tab or Enter Excel automatically convert the letters into the upper case.
3. Do not type the final parenthesis of Functions
When entering a function (SUM, VLOOKUP, etc…) you can hit enter without entering the final parenthesis. Excel will add the final parenthesis for you.
Remember, this only works with functions not with formulas. Also if you have more than one set of parenthesis, you should add the final parenthesis manually.
Note: In Excel, “Functions” refers to a set of predefined formulas which are available by default with the application.
4. Press “Ctrl” while selecting multiple cells in a function
When including multiple cells which are not adjacent, it is required to add a comma between cell names. Instead of typing the comma, you can simply select the cell by the mouse while pressing the Ctrl key. The Excel will automatically add a comma between the cell names.
5. Use ToolTips
ToolTips are really helpful when you are working with an unfamiliar function. Simply click the function name of the ToolTips box to access the help menu for that specific function. If you click on an argument name, it will highlight the values assigned to that argument.
This is helpful when you are working with a long formula. To use the partial evaluation (F9 – described below), you can use this technique to select the relevant parameter of the function.
6. Double click fill handle to copy down formulas
It is not efficient to drag the fill handle all the way to the last row when you are working with a large number of rows. You can double-click the fill handle instead. That will copy down the function all the way to the last row. However, if you have blank rows in the middle of your data set, the Excel not going to copy down beyond that row.
7. Display all the formula in the sheet (Ctrl+’)
You can display all the formula in the sheet instead of their resulting values by activating “Show Formula” option in the “Formula Auditing” group in the “FORMULAS” tab. Sometimes you may need this to display or view all the functions in the sheet in order to find the cells contain formulas or some other purpose.
Alternatively, you can use Ctrl+` (Control + backquote) to display the formula.
8. Trace Precedents and Dependents
When you are working with workbook created by someone else or even it an old one of yours, often you need to trace the effect of a specific cell on the other cell of the sheet or vice versa.
You can use Trace Precedents and Trace Dependents options in the Formula Auditing group of the Formula tab to graphically displace the connections.
Trace Precedents: Show arrows that indicate what cell affect the value of the currently selected cell
Trace Dependents: Show arrows that indicate what cells are affected by the value of the currently selected cell
9. Find and select cells containing formulas
You can select all the cells that contain formulas at once from the Formula option in the Find and Select options of the Editing group of the HOME tab (HOME> Find and Select > Formulas).
Alternatively, you can use Ctrl+G or F5 to get the “Go to” dialog box and select “Special” from there. Then select “Formulas” radio button and click OK.
10. Use F2 to start editing the cell in the edit mode
You can go to cell edit mode by double-clicking the cell. However, this is not efficient sometimes, since you need to move your hand away from the keyboard to reach the mouse. In that case, you can press F2 to go to edit mode while the relevant cell is selected. Then you can use arrow keys to move the cursor to get to the required position.
Note: Use Esc to exit from the edit mode
11. Convert formula into value
You might have come across situations where you want to have only the output of the formula remains in the cell instead of the formula. For this, you need to convert your formula into value. Copying the value and paste as a value is the most famous way of doing this. However, Microsoft Excel has a number of ways to do this.
Method 1: If you are a person who uses mouse frequently, select the cell with the formula and move the mouse pointer to the border of the cell, then you will see the mouse pointer is changed to a different icon. Now right click the mouse and drag the cell away and bring back to the original position and release the mouse. Then you will see a menu is open when you release the mouse. then select the “Copy here as values only” option. You can apply this same technique even for a range of consecutive cells or to the entire sheet.
Method 2: Select the cell and press F2 (this will activate the cell). Then press F9 and it will convert the formula into value. Then press Enter.
Method 3: Just copy the value (Ctrl+C) then past it (Ctrl+V). Then you will see a small icon (Ctrl) at the bottom right corner of the pasted cell. If press Ctrl at this time, you will see a list associated with paste options. Then simply press “V” and this will convert formula into its output value.
Method 4: Copy the cell/ cells with the formula. Then place the mouse pointer on the cell where you need to paste the value and right click. You will see the paste as value icon under the paste option and select it.
12. Make formula more readable with the named range
When the workbook is getting bigger, it is difficult to understand and handle. The formula you are writing may be associated with a number of cells in a number of sheets. You can make your functions more readable by assigning names to the variables, rather than using just cell references.
You can assign names from “Name Manager” (Ctrl+F3) available in the “Defined Names” group of the FORMULAS tab. Alternatively, you can easily assign names to cells using Name Box located left to the Formula Bar.
You can make the name available throughout the workbook or only to the current sheet. However, if you use the name box, Excel assigns the scope as “workbook” by default.
13. Apply names to an existing formula
You can add named range to an existing formula. First, you need to give a name to your cell or range of cells that are involved in the function. Next, select the specific cell which contains the formula. Then you can see a small down arrow next to the “Define Name” option in the Defined Names group of the FORMULAStab. Click on that arrow and, select the “Apply Names..” option. It will open up the Apply Names dialog box, then select the names relevant to the function and click OK. Then Excel will apply those names to relevant sections of the function.
14. Save unfinished formulas
By default, Excel does not allow you to leave the cell if have not completed the formula. It will give an error if you hit enter if the function is not finished. To avoid this error message, you can simply remove the equals sign from the beginning of the function and hit enter.
Alternatively, you can put an apostrophe ( ‘ ) at the beginning of the function. This way, you can save anything as it is is in the Excel.
15. Use F4 to switch between absolute and relative reference
The relative reference and absolute reference behave differently when you copy a cell containing a function to another cell. By default, Excel cell reference is relative.
In relative reference, the referred cells (the formula) is changed as you copy it from one cell to another. In absolute reference, the referred cells (the formula) does not change when you copy it to one cell to another.
You can make the cell reference absolute by preceding the column, row or both with a dollar ($) sign. For example, A1 is a relative reference while $A$1 is an absolute reference. However, doing this manually every time is a tedious task.
You can easily toggle between relative and absolute reference with the F4 key while the formula is active. When you press the F4 key multiple times, the formula toggle between four different states as below example.
A1 – Relative row and column
$A$1 – Absolute row and absolute column
A$1 – Absolute row and relative column
$A1 – Absolute column and relative row
16. Evaluate a part of the formula with F9
Building a complicated formula in Excel is not much user-friendly. However, you can evaluate the functions and parts of the formula separately.
First, select the part of the formula you want to examine. Then press F9, and it will show up the relevant result to that section. Then press escape to undo your action and get the formula back.
17. Evaluate formula step by step
F9 evaluate the argument you have selected. However, the “Evaluate Formula” option in the Formula Auditing group of the Formulas tab can do a better job. It debugs a complex formula, evaluating each part of the formula individually. Stepping through the formula part by part can help you verify it’s calculations correctly.
To use this option, first select the cell which contains the function. Then click the “Evaluate Formula” in the Formula Auditing group of the Formulas tab. It will open up the “Evaluate Formula” dialog box. Then you will see that first part of the formula is underlined. Click the Evaluate button, and it will evaluate the underlined part and show the result underlined with the next part to be evaluated. Likewise, you can evaluate function until you get the final result.
18. Use AutoSum (Alt+=) to enter SUM function
Most probably SUM() is the most used function in Microsoft Excel. However many of the users type this function manually, which is really not efficient. Instead, you can use the AutoSum option available int he Editing group of the Home tab or simple use Alt+= keyboard shortcut.
The AutoSum is smart to guess your sum range most of the time and you can easily change the range inside the function if it is wrong.
19. Break long formulas into lines to improve readability
When an Excel formula includes a number of functions and parameters, it is difficult to read or debug. You can break these formulas into lines at convenient locations to improve the readability.
To break the formula into lines; place the cursor in the location where you want to break it. Then press Alt+Enter keyboard shortcut. This will break the section beyond the cursor to a new line without affecting the functioning of the formula.
20. Converting a range of data into a table
Excel tables are smart to automate some parts of your work. You can improve the productivity of your work by using excel tables. Use Ctrl+T to quickly convert the selected range of data into a table.
You may also read 10 Benefits of Converting Your Data into Excel Tables.
In Excel tables, your functions are copied down automatically when you finished the function in the top row. The functions use column names instead of default cell references and it improves the readability. You can pick up a function easily from the total row. The calculation of the total row is limited to the filtered list. Use Ctrl+Shift+T to activate total row.
Thank you for reading all the way to the end!
If you know more tips that can save time with Excel formula, you can share them in the comment section below. Please share this with your friends if you think they might benefit from this article too.