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):
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 drag and drop 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), press Control and the left mouse button and drag 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.
Recommended Microsoft Excel training
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
It’s easy to lock and unlock cells in Excel to prevent users from changing data or formulas. This involves a two-step process …
Check out these 10 great Excel pivot table keyboard shortcuts. You can use standard shortcuts using the Control key or use Alt to access the Ribbon and create pivot tables in different ways.
You can easily delete blank rows in large Excel data sets in a few steps rather than wasting time manually deleting blank rows one by one.
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 | email@example.com