10 Best Excel Formulas Tips Every Excel User Should Know

Excel spreadsheet is all about formulas and by creating formulas, you can get the quick calculation even if the information changes in the cells related to the formula. And if you are a regular Excel user, then you must be working with a lot of formulas.

However, getting formulas working properly is really very tricky and sometimes a simple problem ends up taking far too long. So today here in this article, I will share some best Excel formulas tips that will save your time while working in Excel.

So, learn the useful Excel formulas tips to become more productive and easily use the complex software

Here have a look…

 

1. Utilize a Table to Enter Formulas Automatically

 

This is the easiest and faster way to enter formulas easily in an Excel file. First, convert the table to an official Excel table and formulas will enter automatically.

Well, this terminology is quite confusing, as any data with more than one column is technically called a “Table” but Excel is having a formal structure known as a Table, and this provides many advantages.

Since as you convert your data to a table, the entire formulas you enter in the first row will be automatically copied simply by dragging down to the full length of the table. This is easy and as saves a lot of time of the user, doing this will also prevent errors.

Not only this, whenever there are any changes in the formulas in a table. Excel will update the entire formulas in the same column.

 

2. Utilize Autocomplete + Tab to Enter Functions

 

In Excel whenever you enter an equal sign and start typing, Excel starts matching the text enter against the list of available functions.

When you type you will see a list of “candidate” functions appear below. And the list will narrow with each letter that you type. And as the function that you need is selected in the list, you can then ask Excel to enter it automatically by pressing tab.

Well, in Windows functions are automatically selected as you type. And on Mac options are offered but not selected. So, in this case, you need to use the arrow key to select the function that you need and press tab or return to have Excel enter the function for you.

This Excel formula tip will save your precious time and also avoid the risk of getting errors.

 

3. Make Use of the Control + Click to Enter Arguments

 

If you don’t like typing commas between the arguments, then Excel will do it for you.  While entering arguments in a function hold down the Control key (in Mac: Command) since as you click each reference, Excel will automatically enter commas.

For instance:  when you enter a formula: = SUM(A1, B10, C6: C10) then by entering =SUM(” then Control-clicking the reference will work with any function where you supply references as arguments.

 

4. Move the “Formula Thing” Out of Your Way

 

In some case when you enter a formula, the formula hint window gets in its own way and blocks your view of other cells you desire to see on the worksheet.

Well, in this case, you can move the hint window out of your way. All you need to mouse over the edge of the window until you see the cursor change, click and drag to a new location.

Now you can continue to enter or edit your formula. However, depending on the structure of your worksheet, there is another way to solve the problem is to edit the formula in the formula bar instead of doing it directly in the cell.

 

5. Toggle Display All Formulas At Once

 

This is the easiest way to see entire formulas at once and also check formulas for regularity. When you edit a cell that contains formulas, Excel automatically displays formula instead of the result.

But sometimes you may see all the formulas in a worksheet at one time.  Since to do this: use the keyboard shortcut for displaying the formulas: Control + ~ (known as a tilde).

This shortcut will rapidly toggle the display all formulas on a worksheet or off.

 

6. Select Entire Formulas in a Worksheet At Once

 

Another easiest way to see the entire formulas in a worksheet is to select them. To do this use the more powerful and hidden features in Excel: Go To > Special (Ctrl + G).

This command will help to select all sorts of interesting things in Excel, as well as blank cells, cells that contain numbers, blank cells, and much more.

One option is the cells that contain formulas, select entire cells that contain formulas on a worksheet, type of Ctrl + G to bring up the Go to the dialog box and click the Special button and choose Formulas. And when you click OK, the entire cells that contain formulas will be selected.

Also Read:

1. 8 Common Excel Error Messages that Irritates You The Most
2. 30 Tips & Tricks to Master Microsoft Excel
3. 11 Vital Tips To Optimize Excel File and Speed-up your Excel

 

7. Employ F4 to Toggle Relative and Absolute References

 

The main key to constructing formulas that can be copied sophisticatedly to new locations and still work correctly is to use the right combination of absolute and relative references.

This will allows you to reuse the existing formulas instead of creating new ones and re-utilizing the same formula will considerably reduce the possibilities of workbook errors by limiting the number of formulas that are required to be checked.

Though converting the references back and forth between relative and complete references can be a pain and typing all the dollar signs is tiresome and highly prone to errors.

Fortunately, there is another shortcut that allows you to quickly toggle through the 4 options presented for each reference: (Windows: F4, Mac: Command + T). Just put the cursor into a reference and utilize the shortcut.

Every time this is utilized, Excel will “rotate” to the next option in this order: fully relative (A1) > fully absolute ($A$1) > Absolute row (A$1) > absolute column ($A1).

 

8. Utilize F9 to Assess Parts of a Formula

 

In Windows, the shortcut F9 (fn + F9 on a Mac) solve parts of formula in real time. This is a brilliant tool for debugging larger formulas, while you require verifying the result of a particular part of the formula in what you are expecting.

For using these tips, edit the formula and choose the expression or function that you need to evaluate (utilize the function tip window for selecting entire arguments).  And as you have a selection, hit F9, you can see that part of the formula are replaced by the value it returns.

Please Note: in Windows, you can undo F9, but this won’t work on Mac.  So to exit formula without making changes make use of the Esc key.

 

9. Utilize Named Ranges like Variables

 

Well, in most of the cases, it is valuable to use named ranges like variables to make formulas more flexible and easier to work with. You can simply refer to the named ranges directly despite adding plenty of complex syntax to your formulas.

For additional benefit, if your named ranges contain text values, you don’t require using any quotation marks around the text while you add them to a formula.

 

10. Enter Same Formula in Multiple Cells At Once

 

While working on Excel we often require entering the same formula into a group of cells.  This can be actually done in one step with the keyboard shortcut Control + Enter.

Just choose the entire cells at the same time, and enter the formula normally for the first cell. Then instead of pressing Enter, hit Ctrl+ Enter. In this way Excel will add the same formula to entire cells in the selection, adjusting the references as required.

While you utilize this approach, there is no need to copy and paste, fill down or utilize the fill handle and you are done in one step.

This same technique can be used to edit multiple formulas at the same time. So select the entire formulas at once, and make the required changes and hit Ctrl + Enter.

Well, these are some of the best Excel formulas tips n tricks that will save your time and also prevent formulas errors in Excel. 

Conclusion:

In this article, I try my best to provide the valuable formulas tips n tricks that will help you to prevent errors as well as save your precious time.

Make use of the given tips and see the Magic, how all your calculations are performed in just a couple of minutes.

Start implementing it now.