23 Excel formula tips

Hey, Dave here from Exceljet.

So today I want to do some training on efficiency and entering formulas and functions in Excel, so I'm going to take you through more than 20 different tips for working faster with formulas and functions.

And I'll look at things like how to enter formulas and functions faster, how to work with the function arguments using the Function Tip Window or Screen Tip Window. We'll look at how to use named ranges to make your formulas easier to read. We'll look at how to use F9 and evaluate to see what your formulas are doing when you're having trouble and you want to make sure that they're working properly.

We'll also look at some techniques for entering formulas in a table where you want to use the same formula and you need to copy that formula down the table, a few tricks for making that go faster.

Okay so let's go do the training.

Okay so here we are in Excel, and now I'm going to take you through more than 20 tips to help you save time and work faster with Excel formulas.

We cover all these topics in more detail in our course on Excel formulas, so I'm going to move rapid-fire through these tips and trust that you can rewind the video if you need to.

So when you're entering a formula you can use Autocomplete and Tab to enter the formula. Just enter an equals sign and start typing, and then you can use the arrow keys to select the function you need and press Tab.

Notice that you don't need to enter a closing parentheses.

You can also use a feature called the INSERT function. There's a button next to the formula bar, or you can use the shortcut Shift-F3.

And you can use the same feature to get back to the function arguments, either with the button or with the shortcut Shift-F3.

Once you have a function entered in Excel, you can use the function screen tip window to select the different arguments. If you find the Screentip Window is blocking something you want to see, you can just move it anywhere you want.

You can also ask Excel to insert function arguments for you, using the shortcut Control-Shift-A. Then you can select the arguments, and replace them with the values you need.

When you're entering functions that require multiple references on the worksheet you don't need to use a comma, if you hold down the Control key on Windows, or the Command key on the Mac.

There are several ways you can enter formulas in a table and copy them down quickly.

First, you can select all the cells, enter the formula, and then use Control-Enter. Or, you can enter the formula and double-click the fill handle. Or, you can enter the formula, select the formula and the cells below, and the use of the shortcut Fill Down, which is Control-D.

You can also convert a table to an Excel table using Control-T. Once you have a table, and you enter a formula, Excel will automatically copy that formula down.

You can use a shortcut for Autosum to quickly enter some functions for multiple rows, multiple columns, or for an entire table.

When you're working with a complicated formula that has a syntax problem, Excel may not let you enter that formula. As a workaround, you can temporarily convert the formula to text with a single quote, and then come back later when you have more time and fix the problem.

You can quickly see all the formulas in a worksheet using the keyboard shortcut Control + Grave. You can use this shortcut to toggle all formulas on, and all formulas off.

You can also use Go To Special and Formulas. This will select every formula in the worksheet.

On Windows, use the keyboard shortcut F4 to toggle through absolute and relative references, and on the Mac use a shortcut Command-T.

When you want to move formulas without changing relative references, you can use Cut-and-Paste, or, you can use Drag-and-Drop.

When you want to copy formulas that contain relative references, you can temporarily convert them to text. I'll search for the equals sign and replace it with a hash character. 

Then I'll copy and paste the formulas. And then, I'll reverse the search and replace...looking for a hash character, and replacing with the equal sign. Now all formula references are unchanged.

You'll often want to convert formulas to values. A simple way to do this is to use Paste Special with the Values option. Select the formulas, copy, bring up Paste Special, and choose Values. Then you can delete columns that you no longer need.

You can also use Paste Special to convert values in place without any formulas at all. In this case, I'll move all of these dates forward by 14 days, or 2 weeks.

Just copy the value, bring up Paste Special, choose Values, and a math operation. In this case, we want to add. Now all dates are shifted forward by 14 days

I can use the same approach to increase prices by 15%. This time, I want to choose values and the multiply operation.

You can use named ranges to make your formulas easier to read and understand. In this case, I'll name the cell that contains the hourly rate and then update the formulas.

If I undo that, you can also apply names using the apply names feature on the Formulas tab of the Ribbon.

To understand a more complicated function, you can use F9 manually, or you can use the evaluate formula feature. To use F9, work from the inside out.

Here we'll use F9 to evaluate TODAY(). Then, we'll evaluate the YEARFRAC function, and finally, we'll use F9 to see the result of the INT function 

Evaluate formula is on the Formulas tab of the ribbon. Each time you click evaluate, Excel will solve one step of the formula working from the inside out.

Okay, so that's 20 tips to help you work a little faster with Excel formulas. Give these tips a try and save a little bit of time in Excel today.