Definition and Use of Formula in Excel Spreadsheets

Formula Examples in Excel

Formulas in Excel are used to perform calculations or other actions on data entered into the formula and/or stored in program files.

They can 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 the formula is entered, you need only change the amounts to be calculated. You don’t have to keep entering “plus this" or “minus that” as you do with a regular calculator.

Note: The information in this article applies to Excel versions 2019, 2016, and 2013.

Formulas Start With the Equal Sign

Formulas begin with an equal ( = ) sign and, for the most part, are entered into to the worksheet cell(s) where you want the results or answer to appear.

For example, if the formula =5 + 4 - 6 is entered into cell A1, the value 3 appears in that location. When cell A1 is selected, the formula displays in the formula bar above the worksheet.

Formula Breakdown

A formula can also contain any or all of the following:

Values

Values in formulas are not just restricted to numbers but can also include:

  • Dates
  • Text: Words surrounded by quotation marks ( " ")
  • Boolean values: TRUE or FALSE only

Formula Constants

A constant is a value that does not change and is not 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, which changes infrequently.

Cell References in Formulas

Cell references, such as A1 or H34, indicate the location of data in a worksheet. Rather than enter data directly into a formula, it is better to enter the data into worksheet cells and then enter the cell references to the location of the data into the formula.

The advantages of this are that:

  • If you later change your data, the formula automatically updates to show the new result.
  • In certain instances, using cell references makes it possible to copy formulas from one location to another in a worksheet.

To simplify entering multiple contiguous cell references into a formula, they can be entered as a range that just indicates the start and end points. For example, the references A1, A2, A3 can be written as the range A1:A3.

To simplify things even further, frequently used ranges can be given a name that can be entered into formulas.

Functions: Built-in Formulas

Excel also contains a number of built-in formulas called functions.

Functions make it easier to carry out:

Formula Operators

An arithmetic or mathematical operator is the symbol or sign that represents an arithmetic operation in an Excel formula.

Operators specify the type of calculation being carried out by the formula.

Types of operators

The different types of calculation operators that can be used in formulas include:

  • Arithmetic: Used for basic math, such as addition and subtraction.
  • Comparison
  • Text concatenation

Arithmetic Operators

Some of the arithmetic operators, such as the ones for addition and subtraction, are the same as those used in hand-written formulas. Arithmetic operators for multiplication, division, and exponents are different.

The arithmetic operators are:

  • Subtraction: Minus sign (-)
  • Addition: Plus sign ( + )
  • Division: Forward slash ( / )
  • Multiplication: Asterisk ( * )
  • Exponentiation: Caret ( ^ )

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.

Comparison Operators

A comparison operator carries out a comparison between two values in the formula and the result of that comparison is either TRUE or FALSE.

There are six comparison operators:

  • Equals ( = 
  • Less than ( < 
  • Less than or equal to ( < = 
  • Greater than ( > 
  • Greater than or equal to ( > = 
  • Not equal to ( < > )

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

Concatenation Operator

Concatenation means to join things together and the concatenation operator is the ampersand (&). It can be used for joining multiple ranges of data in a formula.

An example of this would be 

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

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