Excel Yourself: Formula tips, part 1

Excel Yourself

Here are a few small tips that can speed up and simplify the creation of formulas in Excel.

Here are a few small tips that can speed up and simplify the creation of formulas in Excel.

Formula tip 1 - the plus key

You can start all formulas with the + symbol.

Why do it? Well, the plus key is a large key on the far right-hand side of the keyboard. It’s easy to hit quickly. It’s also above a large Enter key, also easy to hit.

The = key, by comparison, is a normal-sized key in the middle of the keyboard and not so easy to hit.

I tend to live on the right-hand side of the keyboard as it has many of the keys I need to build formulas. Moving your hands around the keyboard slows you down; if your hand stays near the numeric keypad you can work faster.

When you finish the formula, Excel puts the = sign in.

Formula tip 2 - use Formula AutoComplete (Excel 2007 and later versions)

AutoComplete is the term for the feature where Excel shows you function names as you are typing.
See Figure 1.

This is a placeholder image

This is a placeholder image

As well as showing you the possible functions, it also has a description on the right-hand side of the selected function. If the function is highlighted, press the Tab key to enter it.  You can also double click the function name to

enter it or use the up and down arrow keys to select and then press the Tab key to enter it.

This saves you typing and it automatically inserts the first bracket of the function.

If you use range names they will are also be listed (with a different icon on the left), which helps reduce typing errors when using range names.

Formula tip 3 - the final bracket

If you are creating a single function formula you don’t need to enter the final bracket. Excel will do it for you. Just press Enter to finish the function and Excel will complete the formula. This assumes you have entered all the required arguments for the function. If you haven’t, Excel will tell you.

For simple function formula, combine this with Tip 2 and you don’t ever need to enter brackets.

If your formula is more complex, Excel will show you what it is going to do and asks you if the brackets it is putting in are correct; you can accept or reject it and fix it yourself.

In part 2 of this article we will look at ways to get those $ signs right in your formula references.

Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services.

How-to articles, sample spreadsheets and more

Excel yourself

Combine annual and monthly data in Excel with these simple steps

Making data relationships work

Easy steps to make data Relationships work in Excel

Excel Yourself

Easy tips for matching salary to job class in Excel