Enter Excel Formulas Easily with These Useful Tips

Posted by: Avantix Learning Team | Updated April 10, 2021

Applies to: Microsoft® Excel® 2010, 2013, 2016, 2019 and 365 (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

Do you want to learn more about Excel? Check out our virtual classroom or live classroom Excel courses >

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.

More resources

How to Replace Blank Cells in Excel with a Value from a Cell Above

How to Use Flash Fill in Excel (4 Ways)

How to Quickly Delete Blank Rows in Excel (5 Ways)

How to Convert Text to Numbers in Excel (5 Ways)

How to Convert Pounds to Kilograms in Excel (lb to kg)

Related courses

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 courses are delivered in virtual classroom format or at our downtown Toronto location at 18 King Street East, Suite 1400, Toronto, Ontario, Canada (some in-person classroom courses may also be delivered at an alternate downtown Toronto location). Contact us at info@avantixlearning.ca if you'd like to arrange custom instructor-led virtual classroom or onsite training on a date that's convenient for you.

Copyright 2024 Avantix® Learning

You may also like

How to Replace Zeros (0) with Blanks in Excel

How to Replace Zeros (0) with Blanks in Excel

There are several strategies to replace zero values (0) with blanks in Excel. If you want to replace zero values in cells with blanks, you can use the Replace command or write a formula to return blanks. However, if you simply want to display blanks instead of zeros, you have two formatting options – create a custom number format or a conditional format.

What is Power Query in Excel?

What is Power Query in Excel?

Power Query in Excel is a powerful data transformation tool that allows you to import data from many different sources and then extract, clean, and transform the data. You will then be able to load the data into Excel or Power BI and perform further data analysis. With Power Query (also known as Get & Transform), you can set up a query once and then refresh it when new data is added. Power Query can import and clean millions of rows of data.

How to Freeze Rows in Excel (One or Multiple Rows)

How to Freeze Rows in Excel (One or Multiple Rows)

You can freeze one or more rows in an Excel worksheet using the Freeze Panes command. If you freeze rows containing headings, the headings will appear when you scroll down. You can freeze columns as well so when you scroll to the right columns will be frozen.

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 |18 King Street East, Suite 1400, Toronto, Ontario, Canada M5C 1C4 | Contact us at info@avantixlearning.ca

Summary
8 Excel Tips and Tricks for Entering Formulas
Article Name
8 Excel Tips and Tricks for Entering Formulas
Description
Check out these 8 Excel tips and tricks for entering formulas. You'll save a lot of time and even help reduce errors in your formulas.
Author
Publisher Name
Avantix Learning

Pin It on Pinterest

Share This