Sign in with Microsoft
Sign in or create an account.
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

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.

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        

Note: This does not apply to Excel for the web.

To switch between relative, absolute, and mixed references:.

  1. Select the cell that contains the formula.

  2. In the formula bar Button image, select the reference that you want to change.

  3. 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.

Detect errors in formulas

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!