One of the most basic and the most used features of the Excel spreadsheet is its ability to do calculations. The two main methods you can do calculations in Excel are formulas and functions. In Excel, the words ‘formulas’ and ‘functions’ are frequently used interchangeably but they are two different things.

A formula is an expression that makes calculations on direct values or values in a cell or values in a cell range. A function is a predefined formula (that already exists in Excel) that performs an operation on values in your worksheet. A Formulas is generally composed of an operator and an operand while a function is made up of a function name and an argument.

This article explains how to write, edit, and copy formulas using constant and operator, cell references, and pointing methods.

Excel Formula Basics

In Excel, Formulas can short and simple or long and complex. A formula in Excel always returns a result, even when that result is an error.

Elements of Formulas

A formula always starts with an equal sign (=) and can contain any or all of these elements:

  • Mathematical operators: + for addition and * for multiplication, etc.
  • Values: Constants are numeric values, dates, or text values that you enter directly into a formula
  • Cell reference: Individual cells or range of cells
  • Function: Predefined worksheet functions such as SUM, PRODUCT, etc.

Calculation Operators

Basically there are four types of operators in Excel: arithmetic, comparison, text concatenation, and reference.

Arithmetic Operators

The following arithmetic operators helps you perform various arithmetic calculation in Excel.

  • Addition: + (plus sign)
  • Subtraction: - (minus sign)
  • Multiplication: * (asterisk)
  • Division: / (forward slash)
  • Percent: % (percent sign)
  • Exponentiation: ^ (caret)

Comparison Operators

Comparison operators (logical operators) are used for comparing two values (numeric or text). When you compare two values using any of the following operators, the output would be a logical value either TRUE or FALSE.

Excel has the following logical or comparison operators:

  • Equal to: =  
  • Greater than: >
  • Less than: <  
  • Greater than or equal to: >=
  • Less than or equal to: <=
  • Not equal to: <>

Text Operator

There’s is only one text operator available in the Excel formula for concatenating one or more text strings.

  • Join or Concatenate text strings: &  

Reference Operators

The reference operator is used for combining a range of cells to do calculations.

  • Range operator : – It makes 1 reference to all of the cells between 2 specified cell references, including those 2 references.
  • Union operator , – It joins multiple range references into one reference.

Cell References

There are two types cell references:

  • Relative reference: It is a basic cell reference and it is based on the relative location of the cell. It changes and adjusts when a formula is copied to another cell
    → Example: B1
  • Absolute references: It is a locked reference that doesn’t change or adjust when copied. It is designed by adding a dollar $ symbol before the column and row.
    → Example: $B$1

Create a Simple Formula with Constants and Operators

Now, let us learn how to create a simple Excel formula using constants and operators. 

Select a cell where you want the result. This is where you will type your formula. 

The first step to creating an Excel formula is to type an equal (=) sign. This will let Excel know that you are going to enter a formula. Without the (=) sign, Excel will take it as a string of text or numbers.

Then, Enter the formula. For example, if you want to add 23 and 5, type the first constant (23), then the operator (+), and then the second constant (5). To finish the formula, press ‘Enter’.

Edit a Formula

When you select a cell, Excel displays the value or formula of the selected cell in the formula bar which is located right above the column letters.

To edit the formula, select cell C1, which contains the formula, and click on the formula bar. Then, edit the formula and press ‘Enter’. You can directly edit the formula in the cell by double-clicking on it. We’ll try subtraction this time in the below example.

Creating a Formula With Cell References

You are prone to make mistakes when you’re entering a long value in a formula. To avoid mistakes you can refer to the cells containing values, instead of manually typing those values into your formula.

In Excel, each cell has its own address (cell reference) that is specified by a column letter and a row number. The first part of the address is the column letter (A, B, C, etc.) which is shown along the top of the spreadsheet, while the row numbers (1, 2, 3, etc.) are shown along the left side.

If you select a cell, you can find its cell reference in the Name Box next to the formula bar (See below).

Now, select the cell where you want to output the result and type the formula. Type (=) sign and the cell address that contains the first value in the equation, followed by the operator, followed by the cell address that contains the second value, and so on. Press ‘Enter’ to Finish the formula.

Also, as you type each cell reference in the formula, that cell becomes highlighted as shown above. 

You can add as many cell references and operators as you want in a formula.

Creating a Formula in Excel Using the Point and Click Method

The point and click method is actually another method you can use to refer to the cells, containing the values you want to include in your formula. This is similar to the cell reference method; instead of manually tying the cell addresses, you can use your pointer to select cells to include in your formula. But you still have to type (=) sign and operators manually. 

This is the most accurate and fastest method for creating formulas because it eliminates the risk of making a mistake in writing a cell reference address.

For example, we want to compare two values in cells A5 and B4 and output the result in cell D3.

To do that, first, select the cell (D3) where you want the result and type in the equal sign (=). After that, click the first cell (A5) to be included in the formula. Next, type the operator (> for comparison), then use your pointer to select B4 to insert the second cell reference into the formula. Finally, press ‘Enter’.

As you select each cell reference, that cell becomes highlighted as shown above. 

Since it’s a comparison formula, If the cell A5 is greater than B4, then it returns ‘TRUE’ as the result.

Operator Precedence

Excel has a default operator order in which it carries out the calculations. If you use multiple operators in a formula, Excel performs the arithmetic calculations in a certain order.

The Order of Operations in a basic Excel formula is shown below, in descending order.

  • () (Parenthesis)
  • ^ (Exponents)
  • / (Division) or * (Multiplication)
  • + (Addition) or -(Subtraction)

If you write a formula that has multiple operators with the same precedence, then Excel calculates the operators from left to right.

Now, let’s create a complex formula to test the operator precedence in Excel. 

Select the cell where you want your answer and type (=) sign and then enter the formula as shown below.

First, Excel calculates the part inside parentheses (B1+B2). Then, it multiplies the result by the value of cell A1 and then finally it divides that result by the value in cell B3.

That’s how you can create formulas in Microsoft Excel.