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):
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:
and then press Control + Shift + A, Excel will insert:
Replace each argument with the appropriate value(s), cell(s) or range of cells.
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.
Did you find this article helpful? If you would like to receive new articles, join our email list.
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 firstname.lastname@example.org if you'd like to arrange custom instructor-led virtual classroom or onsite training on a date that's convenient for you.
Copyright 2023 Avantix® Learning
You may also like
You can combine first and last name in Excel in several ways – using the CONCATENATE operator, the CONCATENATE function, the CONCAT function, the TEXTJOIN function or Flash Fill. These functions are often used to combine text in cells but you can also combine text with spaces, commas, dashes or another character. It's common to combine first and last names that appear in two columns into one new column. Some functions are only available in newer versions of Excel but the CONCATENATE operator and function are available in all versions.
You can quickly insert multiple columns in Excel using a drop-down menu, keyboard shortcuts or the Home tab in the Ribbon. The key to inserting multiple columns at once, rather than one by one, is to select the number of columns you want to insert first and then insert columns. Excel will insert the same number of columns you selected.
The XLOOKUP function is a replacement for Excel's traditional VLOOKUP function (as well as HLOOKUP and INDEX / MATCH functions). It has a new set of arguments and is available in Excel 2021 and 365. It allows you to look up a value from an array in a range or table and return one or more results. One of the primary benefits of XLOOKUP is that it can look up from columns to the left in a data set and return a range.
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 email@example.com