Enter Excel Formulas Easily with These Useful Tips

Posted by: Avantix Learning Team | Updated December 29, 2017

Applies to: Microsoft® Excel® 2010, 2013 and 2016 (Windows)

There are all kinds of tips, tricks and shortcuts you can use when entering formulas in Excel. Below are some common as well as little known tips for entering formulas quickly and easily in Excel.

Recommended article: Microsoft Excel Quick Tip – Expand the Formula Bar

1. Use AutoComplete and Tab to enter functions

When you enter an equal sign (=) and then start to type a function name, Excel will begin matching the text you enter against the list of available functions using AutoComplete. A list of matching functions will appear in a menu below the cell. The list of available functions will decrease with each letter you type.

You can double-click a function or use the down arrow key to select a function and then press Enter. After the function has been entered, press Tab to add the opening parentheses or bracket.

2. Let Excel add the closing parentheses or bracket

When you’re entering a simple formula, you don’t need to type the final parentheses or bracket. For example, if you enter the following formula and then press Enter, Excel will add the closing parentheses or bracket automatically (and will capitalize the cell references):

=AVERAGE(b1:b20

Note that this won’t work if your formula contains more than one set of parentheses or brackets.

3. Copy formulas down by double-clicking the Fill handle

It’s common to copy formulas down from the first cell in a column to the last cell. Often, users will drag the Fill handle (the rectangle in the lower right corner of the cell) down to the last cell. However, if the formula is in a column where the column to the left contains a full set of data, simply double-click the Fill handle to copy the formula down to the last cell in the column.

4. Quickly change relative cell references to absolute references

The default type of cell reference in Excel is relative (if a formula is copied, it will change relative to the location it is copied). For example, A1 is a relative reference. If you don’t want a cell reference in a formula to change when you copy it, you can make it absolute by adding dollar signs ($) in front of the column and row number. For example, $A$1 is an absolute reference and will not change when copied. To quickly change the selected cells in a formula you are editing to absolute references, press F4.

5. Copy a formula using Drag and Drop and keep references from changing

If you copy and paste a formula to a new location, normally all relative cell addresses (such as B1) will change relative to the location the formula is copied. One option is to change the relative references to absolute references. Alternatively, if you don’t want the formula using relative cell addresses to change, you can change the formula to text temporarily and then copy using the Drag and Drop pointer to keep cell addresses unchanged.

To change a formula to text temporarily, in the cell or Formula Bar, enter an apostrophe (‘) at the beginning of the formula and press Enter. To use drag and drop to copy, position the cursor on the border of the cell with the formula (the pointer will change to four arrows which is the Drag and Drop Pointer), press Control and the left mouse button and drag the border of the cell containing the formula to a new location, then release Control and the mouse button. Change the formula back to normal by removing the apostrophe (‘) and pressing Enter.

6. Use the formula tip pop-up to select arguments

Whenever you’re entering a formula that contains an Excel function, you can use the formula tip pop-up to select arguments.

Start by entering the formula with the function. A pop-up appears below the Formula Bar or below the cell. To select arguments easily, click in the function in the Formula Bar or cell whose argument you want to select. Excel will then display a hint pop-up for that function that shows all arguments. In the pop-up, click the argument you want to select. Excel will select the entire argument, even if it contains other functions or formulas. Replace the argument with the appropriate value(s), cell(s) or range of cells.

7. Quickly insert function argument placeholders

When you enter a function in a formula, Excel will present tips about each argument as you add commas. However, there’s a little known trick you can use to have Excel add placeholders for all the function arguments at once.

Once you’ve started to enter a formula and you type or select a valid Excel function, press Control + Shift + A. Excel will enter all of the arguments required for the function. You can then double-click each argument and change to the appropriate value(s), cells(s) or range of cells.

For example, if you type:

=vlookup

and then press Control + Shift + A, Excel will insert:

=vlookup(lookup_value,table_array,col_index_num,range_lookup)

Replace each argument with the appropriate value(s), cell(s) or range of cells.

Excel Formula Bar with function arguments.

8. Move the formula tip pop-up out of the way

When you’re entering a formula, the formula tip pop-up may block your view of cells you may want to see on your worksheet. You can move this pop-up out of the way by positioning the mouse over the edge of the pop-up until the cursor changes to four arrows. You can then click and drag the pop-up to a new location.

These are a few great ways to enter Excel formulas more efficiently.

Subscribe to get more articles like this one

Did you find this article helpful? If you would like to receive new articles, join our email list.

Recommended Microsoft Excel training

Microsoft Excel: Intermediate / Advanced

Microsoft Excel: Data Analysis with Functions, Dashboards and What-If Analysis Tools

Microsoft Excel: Introduction to Visual Basic for Applications (VBA)

VIEW ALL COURSES >

Our instructor-led classroom training courses are delivered at our downtown Toronto location at 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada (some courses may also be delivered at an alternate downtown Toronto location). Contact us if you’d like to arrange custom training at your office on a date that’s convenient for you.

To request this page in an alternate format, contact us.

Copyright 2018 Avantix® Learning Inc.

You may also like

How to Freeze Row and Column Headings in Microsoft Excel

As many Excel worksheets can become quite large, it can be useful to freeze row and column headings so that they are locked in place when you scroll through your worksheet. In Excel, you can freeze both row headings and column headings or just one.

10 Excel Tips for Working with the Subtotal Feature

You can insert subtotals in Microsoft Excel data sets or lists using the Subtotal feature. Check out these 10 great tips which include showing and hiding subtotals, formatting subtotals, copying only subtotals and grand totals and more …

Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners.

Avantix Learning | 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada M5E 1W7 | info@avantixlearning.ca

Pin It on Pinterest

Share This