Microsoft Excel is a powerful spreadsheet program that is used by a lot of people in their daily lives. When working with this tool, you may have found there are many ways to perform the same action. Some of these methods help you to perform a task faster than others. Such methods can significantly improve the productivity of your work. In this article, we are going to share with you some Excel tips and tricks to help you save time and improve the productivity of your work.
You may also interested in 10 Microsoft Excel Tips to Save Time and Work Faster
Note: The examples and demos in this post is created with Microsoft Excel 365.
Table of Contents
1. Use the tab to autocomplete functions
In Excel, you don’t need to type the complete function.
When you start typing the function you will see, a list of candidate functions appearing in a dropdown list below. At this time, you can use the up(↑) and down (↓) arrows to select the correct function. Then click the Tab key to auto-complete the function.
This will save your time in two ways. One is you don’t need to spend time typing the complete function. And the other is, that you do not need to move your hand to the mouse or touchpad to select, click and insert the function.
You also don’t need to remember all the functions as Excel can suggest the candidate function when you type a few letters of it.
2. No need to type functions in uppercase, Excel converts it
All the functions you see in an Excel file are in UPPERCASE. So, some users try to type all the functions in uppercase which is not actually required. Trying to type functions in the upper case will need time.
You can type function name in any case and Excel automatically convert them to uppercase. You can see this in the above video.
3. No need to 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 with 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 the 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 formulas in the sheet (Ctrl+’)
Sometimes, you may need to find cells with formulas in Excel. You can easily display all the formulas in Excel by pressing the Ctrl+` (Control + backquote, also called the grave accent key). This will show you all the formulas in the sheet instead of their resulting values.
You can also, go to the Formulas tab and click the “Show Formulas” button to show all the formulas in Excel.
8. Trace Precedents and Dependents
When you are working with a 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 affects 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 the “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 the 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 the 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 it 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) and 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.
You may also read, Excel Named Range – Make Your Work Easier!
12. Make the formula more readable with the named range
The complex Excels formulas you are building include references to various cells and cell ranges, in different sheets. Finally, it becomes difficult to read and understand.
With Excel named range, you can assign names to cells and cell ranges, then you can use them in the formulas. You can improve the readability of the complex formulas by including named ranges instead of 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 the Name Box located left of the Formula Bar.
You can make the name available throughout the workbook or only on the current sheet. However, if you use the name box, Excel assigns the scope as “workbook” by default.
You may also read, Excel Named Range – Make Your Work Easier!
13. Apply names to an existing formula
You can add a 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 in 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.
The referred cells (the formula) are changed as you copy it from one cell to another in relative reference. In absolute reference, the referred cells (the formula) do not change when you copy it from 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 that easy. In Microsoft Excel, you evaluate a part of the equation to check whether it outputs the intended result. This will be very helpful when you want to debug a complex equation.
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 the formula step by step
F9 evaluates 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 its 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 the function until you get the final result.
18. Use AutoSum (Alt+=) to enter the 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 the 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 readability.
To break the formula into lines; place the cursor in the location where you want to break it. Then press the 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 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.
In Microsoft Excel, you will find several different methods to perform the same task. Some of these methods can save your time over the other.
In this post, I have listed 20 Excel tips and tricks that can save your time when working with Excel formulas. Some of these Excel tips will help you to write formulas easily and faster while others will help you to debug and reduce the errors in your formulas.