Formula Basics

formula-basics-part

This guide provides an overview of formula basics. Take a closer look at the anatomy of a formula from the ground up.

Formula basics are essential skills to create advanced formulas in Excel.

The Parts of an Excel Formula

All formulas contain one or more built-in functions, and the formula includes the following parts:

  • Equal sign
  • Functions: SUM
  • References: B1:B4 returns all the numbers in a range of cells
  • Operators: The * (asterisk) operator multiplies numbers
  • Constants: numbers or text that you add to the formula, such as 20%
formula-basics-part

How to Insert a formula

Follow these steps to enter a formula:

  1. Select the cell
  2. Select Formula Bar
  3. Type and equal sign (=)
  4. Type the formula, for example, =SUM(B1:B4)
  5. Press Enter to execute the formula
how-to-insert-a-formula-basics

Tip: There are some ways to insert a new formula. Use the Insert Function Button on the Formulas Tab to avoid mistyping words. To do that, Select the cell > Click Formulas Tab > Click Insert Function > Select the function from the list. Finally, click OK.

insert-a-function

How to Edit a formula

Editing formulas is not rocket science. Let’s see the steps:

  1. Select the cell that contains a formula
  2. Click on the Formula bar to change the formula
  3. Press enter

Tip: Excel highlights the given range with a blue border if you are in formula editing mode.

Copy and Paste Formulas

You want to use the entered formula for a different range in the example. Should you type the formula again and again? No, you need only seconds to copy formulas to another location.

Example 1: Adjacent cells

To copy a formula, follow the steps below:

  1. Select the cell that contains a formula
  2. Rest your cursor in the lower-right corner while it turns into a plus sign (+)
  3. Drag the fill handle right
how-to-copy-a-formula

Example 2: Non-adjacent cells

What if you want to copy the formula to another location with non-adjacent cells?

  1. Select the cell that contains a formula
  2. Press Ctrl + C to copy the formula
  3. Right-click on the target cell
  4. Select Paste Formulas
copy-and-paste-formulas

Best practices for using Excel Formulas

  1. Toggle between relative and absolute reference. First, select the cell that contains the formula and use the F2 shortcut to enter the cell edit mode. Then, press F4 to switch between the reference types.
  2. Apply autocomplete formula function. First, type an equal sign (=) and type the first character of the function. Excel shows a dynamic list of built-in functions. Next, select the function that you want to use.
  3. Use Function ScreenTips. Type an equal sign and enter the function name. To add arguments, type the opening parenthesis. A screen tip will appear and show your formula’s corresponding argument.

Useful resources to learn Excel Formulas