Formulas

Formulas are expressions that perform calculations, manipulate data, or analyze information within a Microsoft Excel spreadsheet. They are used to automate tasks, perform complex calculations, and make data analysis more efficient.

Entering a formula

If you enter the equal sign (=) at the beginning, Excel knows that it has to treat this entry as a formula and not as standard text or any other value.

You can insert a formula into a cell in two ways: manually from the keyboard and through references to other cells.

You can also start by entering a formula using the plus (+) sign. Microsoft left this option for people that previously worked with the Lotus spreadsheet.

TIP

If you start a formula with a plus, it will be automatically changed to the equal sign (=).

Example 1:

Look at the following example. There are two cells with values: B2 = 2 and B3 = 4. Enter the new formula to cell D2 to sum up those two numbers. To enter this formula manually, type =B2+B3, and press Enter. It means: adding the values from cells: B2 and B3 and keeping the result in cell D2.

Example 2:

Create a formula in cell D3 that will subtract the value in cell B2 from the value in cell B3.

  1. Click cell D3 and type “=”,
  2. Click cell B3, then type “-“,
  3. Click cell B2 and press Enter.

When you work with Excel, instead of typing the value directly, refer to other cells by clicking them. It will make it easier to edit those values and the formulas will be automatically updated.

Editing a formula

After you enter some data in the cell, you can always go back and edit that value. There are a few ways you can do it:

  1. If you double-click the cell that contains data, the cursor will appear in the very place you clicked. Now you can enter additional data into the cell.
  2. Press F2. This will put the cursor at the end of the cell contents,
  3. Select the cell you want to edit and click the Formula Bar . This will allow you to edit the cell contents directly from there.

When you finish editing data and you want to confirm changes- press Enter. If you want to cancel the changes that you have just made– press the ESC button.

If any of your formulas return an error and you don’t want to change it now, but you’d rather keep the formula in a cell, delete “=” from the beginning of the formula and Excel will start treating it as a text, so you can come back later and correct it.

TIP

Formula errors

If you make a syntax mistake while entering a formula, Excel will notify you immediately. It will be so stubborn that it won’t accept the entry until you correct it.

There are quite a few different errors that you can encounter in Excel:

#DIV/0!

The #DIV/0! error appears when you want to divide a number by 0. Excel will display it also when you try to divide the number by a blank cell.

#VALUE!

This error tells you that one of the formula arguments is of an incorrect type. As an example, let’s take the formula, which contains division. Both numerator and denominator must be numbers. Even if one of them is of a different type, for example- text, then Excel will return the error.

#NAME?

The #NAME? error can occur when you did a typo in the function name or forgot to take the text in quotation marks. In the following example, instead of using the SUM function, I entered the SUMS, which resulted in the #NAME? error.

#N/A

This error occurs when the value is not available for a function or formula.

#NULL!

One example when this type of error can occur is when you want to add cell values, but instead of typing the arithmetic sign, you type space.

For example: =A1+B1 C1.

#NUM!

This error usually appears when the value in a cell is too small or too large and exceeds the range of numbers in which Excel operates. In the following example, the number 1234 was raised to the power of 100, which greatly exceeds the maximum size of a number that Excel can handle.

#REF!

A cell reference is not valid. This error can occur when a formula refers to a cell of the non-existent sheet. Suppose that you have 3 sheets in the workbook: Sheet1, Sheet2, and Sheet3. In this situation a formula =Sheet5! B2 will display this error because it can’t refer to that particular cell.

#####

This is actually not an error. Excel fills a cell with “hashes” to let you know that the cell is too narrow and the result won’t fit in. If you widen the column then the result will be displayed correctly.

Circular references

This error tells you that the formula refers to itself or to the value on which it depends, directly or indirectly.

Suppose you want to enter the formula =3+A1 into a cell A1. After you do this, Excel will display information that the formula you have entered contains a circular reference and may return incorrect values.

Excel 365 Update

Dynamic Arrays: Introduced in 2021, this functionality allows formulas to automatically populate results across multiple cells, eliminating the need for manual copying of formulas. This can significantly improve efficiency, especially when dealing with large datasets or complex calculations. https://support.microsoft.com/en-gb/office/dynamic-array-formulas-in-non-dynamic-aware-excel-696e164e-306b-4282-ae9d-aa88f5502fa2

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.