What Are Formulas in Excel and How Do I Use Them?

Types of formulas, how they're used, and why they're helpful

In Microsoft Excel, formulas perform calculations or other actions on data. Formulas range from basic mathematical operations, such as addition and subtraction, to complex engineering and statistical calculations. 

Formulas are great for working out "what if" scenarios that compare calculations based on changing data. Once you enter the formula, change only the amounts you need to calculate. You don't have to keep entering "plus this" or "minus that," as you would with a regular calculator.

Here's a look at the basics of Microsoft Excel formulas.

The information in this article applies to Excel versions 2019, 2016, and 2013, as well as Excel for Microsoft 365 and Excel for Mac.

Overview of Formulas

Formulas perform calculations in Excel. They always begin with the equal ( = ) sign, which is where you want the answer or results to appear.

Formulas can contain values, constants, cell references, functions, and operators. Here's a brief look at these components and what they mean.

Values

In an Excel spreadsheet, values can be text, dates, numbers, or Boolean data. The type of value depends on the data to which it's referring.

Constants

A constant is a value that doesn't change and isn't calculated. Although constants can be well-known ones such as Pi ( Π ), the ratio of a circle's circumference to its diameter, they can also be any value, such as a tax rate or a specific date, that changes infrequently.

Cell References

Cell references, such as A1 or H34, indicate the location of data in a worksheet. A cell reference consists of the column letter and row number that intersect at the cell's location. When listing a cell reference, the column letter always appears first, such as A1, F26, or W345.

You'd enter multiple contiguous cell references into a formula as a range, which indicates only the start and endpoints. For example, the references A1, A2, A3 can be written as the range A1:A3.

Give frequently used ranges a name that can be entered into formulas.

Functions

Excel also contains a number of built-in formulas called functions. Functions make it easier to carry out commonly performed tasks. For example, easily add columns or rows of numbers with the SUM function. Or, use the VLOOKUP function to find specific information.

Operators

Operators are symbols or signs used in a formula to define the relationship between two or more cell references or values. For example, the plus sign (+) is an arithmetic operator used in formulas such as =A2+A3. Other arithmetic operators include the minus sign (-1) for subtracting, the forward-slash (/) for dividing, and the asterisk (*) for multiplication.

If more than one operator is used in a formula, there is a specific order of operations that Excel follows in deciding which operation occurs first.

In addition to arithmetic operators, comparison operators carry out a comparison between two values in the formula. The result of that comparison is either TRUE or FALSE. Comparison operators include the equal sign (=), less than ( < ), less than or equal to ( < = ) , greater than ( > ) , greater than or equal to ( > = ), and not equal to ( < > ).

The AND and OR functions are examples of formulas that use comparison operators.

Finally, the ampersand (&) is a concatenation operator, joining data or multiple ranges of data in a formula. Here's an example:

{=INDEX(D6:F11, MATCH (D3 & E3, D6:D11 & E6:E11, 0), 3)}

The concatenation operator is used to combine multiple data ranges in a lookup formula using Excel's INDEX and MATCH functions.

How to Create a Simple Formula

Here's how to create a formula that refers to values in other cells.

  1. Select a cell and type the equal sign (=).

  2. Select a cell or type its address in the selected cell.

    Screenshot of creating a simple Excel formula
  3. Enter an operator. In this example, we're using the minus sign (-).

  4. Select the next cell, or type its address in the selected cell.

    Screenshot of creating simple formula in Excel
  5. Press Enter or Return. You'll see your calculation's result in the cell with the formula.

When you enter a formula into a cell, it also appears in the Formula bar. To see a formula, select a cell, and it will appear in the formula bar.

How to Use a Built-In Function With a Formula

  1. Select an empty cell.

  2. Type an equal sign (=) and then type a function. In this example, we're using  =SUM to see total sales.

  3. Type an opening parenthesis and then select the range of cells. Then type a closing parenthesis.

    Screenshot of a simple Excel formula using the SUM function
  4. Press Enter or Return to see your result.