Formula tips and tricks

When you start creating formulas, keep the following best practices in mind. It's also good to be aware of common mistakes you can make when you create formulas.

In this article

Best practices for creating formulas

Rules to avoid common mistakes you can make when you create formulas

Best practices for creating formulas

The following table summarizes best practices for entering formulas.

Follow this best practice	By doing the following:
Easily change the type of reference	To switch between relative, absolute, and mixed references:. Select the cell that contains the formula. In the formula bar , select the reference that you want to change. Press F4 to switch between the reference types.
Quickly copy formulas	To quickly enter the same formula into a range of cells, select the range that you want to calculate, type the formula, and then press Ctrl+Enter. For example, if you type =SUM(A1:B1) in range C1:C5, and then press Ctrl+Enter, Excel enters the formula in each cell of the range, using A1 as a relative reference.
Use Formula Autocomplete	To make it easier to create and edit formulas and minimize typing and syntax errors, use Formula Autocomplete. After you type an = (equal sign) and beginning letters (the beginning letters act as a display trigger), Excel displays a dynamic list of valid functions and names below the cell. After you insert the function or name into the formula by using an insert trigger (pressing TAB or double-clicking the item in the list), Excel displays any appropriate arguments. As you fill out the formula, typing a comma can also act as a display trigger — Excel may display additional arguments. You can insert additional functions or names into your formula and, as you type their beginning letters, Excel again displays a dynamic list from which you can choose.
Use Function ScreenTips	If you aren't familiar with the arguments of a function, you can use the function ScreenTip that appears after you type the function name and an opening parenthesis. Click the function name to view the Help topic on the function, or click an argument name to select the corresponding argument in your formula.

Top of Page

Rules to avoid common mistakes you can make when you create formulas

The following table summarizes some rules to avoid common mistakes you can make when entering a formula.

Follow these rules:	By doing the following:
Match all open and close parentheses	Verify that all parentheses you used in the formula are part of a matching pair. When you create a formula, Excel displays parentheses in color as they are entered.
Use a colon to indicate a range	To refer to a range of cells in a formula, use a colon (:) instead of other symbols such as a dash (-) to indicate the first and last cell in a range. For example, enter A1:A5 instead of A1-A5. In formulas, a colon is not used as a mathematical operator, but a dash (or minus sign) is.
Enter all required arguments	To complete a formula successfully, make sure that you enter the right number of required arguments. Arguments that are marked by square brackets in the formula syntax are optional, and you can enter them as needed.
Nest no more than 64 functions	When you nest other functions in the arguments of a formula that uses a function, make sure that you stay within the limit of 64 levels of nested functions for that formula.
Enclose other sheet names in single quotation marks	If you refer to values or cells in another worksheet or workbook in a formula, and the name of that workbook or worksheet contains a non-alphabetical character, enclose the name within single quotation marks (').
Include the path to external workbooks	Verify that each external reference you enter in a formula contains a workbook name and the path to the workbook.
Enter numbers without formatting	Verify that you don't include number formats when you enter numbers in a formula. In formula arguments, dollar signs ($) are used to indicate absolute references, and commas (,) are used as argument separators. Therefore, instead of entering $1,000 in the formula, enter just the number, 1000.

Top of Page