Microsoft Excel is the most widely used spreadsheet application. We can use it in many ways to achieve the same result. However, there are lots of Excel tips and tricks that can save time and improve productivity.
Using Excel functions is fun, and it improves productivity by reducing the number of steps in your calculations. In this post, I will bring you 20 excel tips and tricks that will save your time and improve the productivity of your work.
1. Use tab to autocomplete functions
When you type the function, Excel list down matching functions with your typing. You can use up and down arrows to select your function and use the tab to insert it. This is a great timesaving Excel tip since you need not type complete function or move your fingers to the touchpad or mouse to click and insert it.
2. Do not type function upper case
It shows Microsoft Excel function names in uppercase. However, you need not type the function in uppercase. You can type in lower 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 over one set of parentheses, add the final parenthesis manually.
Note: In Excel, “Functions” refers to a set of pre-defined formulas available by default with the application.
4. Press “Ctrl” while selecting multiple cells in a function
When including multiple cells that are not adjacent, we require it to add a comma between cell names. Instead of typing the comma, you can 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 helpful when you are working with an unfamiliar function. 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, 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 the “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 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 several 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 it changes the mouse pointer 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 press “V” and this will convert the 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 a 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 several cells in several 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 has not completed the formula. It will give an error if you hit enter if you have not finished writing the formula. To avoid this error message, you can 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 shown in the 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 Formula tab. It will open up the “Evaluate Formula” dialog box. Then you will see that the 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 in the Editing group of the Home tab or 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 several 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.
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 the total row.
Wrapping Up
With these 20 Excel tips and tricks, you can write Excel functions more easily and accurately. Some of them will help breakdown complex formulas and complete them easily. And also these excel tips and tricks will help to save your time and improve workplace productivity.
If you know more tips that can save time with Excel formula, you can share them in the comment section below. You may share this with your friends if you think they might benefit from this article too.
