What are Excel formulas? An ex­pla­na­tion and overview

Excel offers you a range of simple and complex formulas for per­form­ing cal­cu­la­tions. Similar to a pocket cal­cu­la­tor, the spread­sheet program takes on the work of cal­cu­lat­ing and eval­u­at­ing data. It is able to do this via formulas.

What is the de­f­i­n­i­tion of an Excel formula?

When you know the right Excel formulas, the software can be used like an in­tel­li­gent cal­cu­la­tor. At a basic level, an Excel formula is nothing more than a math­e­mat­i­cal equation con­sist­ing of symbols, numbers and letters. By using arith­metic operators, you can use Excel formulas to add or multiply, as well as perform other actions. By combining cell ref­er­ences or constants with arith­metic operators, you can not only calculate in­di­vid­ual cell values but also work with complex cell ranges.

What is the dif­fer­ence between formulas and functions in Excel?

Just like formulas, Excel functions are an important spread­sheet tool. Although the two are similar and are often combined, there is a clear dif­fer­ence between Excel formulas and functions:

• Formulas: A formula always begins with an equals sign, followed by the cell ref­er­ences or cell address. These are linked using certain cal­cu­la­tion operators such as + for adding or - for sub­tract­ing.
• Functions: A function can be made up of formulas but is not a formula itself. Functions are automated op­er­a­tions, al­go­rithms and programs such as ‘SUM’ or ‘ROUND’. Functions use a fixed, pre­de­fined com­bi­na­tion of formulas to deliver a specific result. They are par­tic­u­lar­ly suitable for complex numerical values and can therefore also be part of a formula.

To il­lus­trate the dif­fer­ence between formulas and functions, let’s compare adding cells A1 to A6 as a formula and a function:

One thing Excel formulas and functions have in common is that they both have the ability to create links between cells and the values they contain.

Overview of the most important Excel formulas

Below is a list of the most important formulas that you can use in Excel.

How do Excel formulas work?

Excel formulas use various cal­cu­la­tion al­go­rithms based on cell ref­er­ences, constants and linked cal­cu­la­tion operators to perform arith­metic op­er­a­tions. Each formula that you enter in a selected cell in Excel must begin with an equals sign =. Cell ref­er­ences and operators are entered after the equals sign. For example, you would use the formula =A1+A2 to instruct Excel to add the content of cells A1 and A2 together and display the result in the cell where you have entered the formula.

What is the structure of Excel formulas?

Every Excel formula begins with the equals sign =. This is followed by row ref­er­ences, i.e., the addresses of the rows that are needed for the cal­cu­la­tion. Al­ter­na­tive­ly, you can calculate with constants. These are fixed cell values that don’t change. The in­di­vid­ual row ref­er­ences are again combined with cal­cu­la­tion operators.

An Excel formula can contain the following com­po­nents by them­selves or in full (typically in the order shown below):

• Equals sign =
• a function like SUM
• Cell ref­er­ences (for example, A2 or B3)
• Constants (with moving or copying a formula, constant text or numerical values in cells such as “3”)
• Operators such as * or +

The following examples il­lus­trate the structure of Excel formulas:

• Example 1: If you want to add rows A1 to A4 together using an Excel formula, the formula would look like this: =A1+A2+A3+A4
• Example 2: If you want to divide the numerical values of cells A1 and A2, the formula would be: =A1/A2

What are the arith­metic operators for Excel formulas?

You’re probably already familiar with the basic arith­metic op­er­a­tions that Excel offers for formulas from the math lessons you had in school. The following cal­cu­la­tion operators can be used, depending on the type of cal­cu­la­tion or cell and value eval­u­a­tion:

• Addition: +
• Sub­trac­tion: -
• Mul­ti­pli­ca­tion: *
• Division: /
• Ex­po­nen­ti­a­tion: ^

How to use excel formulas

In the following section, we’ll show you various ways you can use, edit or show Excel formulas.

Create Excel formulas

If you want to enter an Excel formula that relates to values in cells, do the following:

1. Click on any cell or a cell adjacent to the cell value that you want to use in the formula.
2. Now enter the equals sign =.
3. Select the cells for your formula. Al­ter­na­tive­ly, you can enter the address of the cell after the equals sign.
4. Now enter the cal­cu­la­tion operator you want to use, for example, + or -.
5. Now select the next cell or enter the cell address for the cell that you want to place after the operator.
6. Press the Enter key to see the result of the formula.

Edit Excel formulas

If you want to change an Excel formula you have created, you have two options:

• Option 1: Click on the cells with the relevant formula and change it directly in the cell.
• Option 2: When you enter an Excel formula in a selected cell, it also appears at the top in the formula bar.

Show Excel formulas

If you want to show a formula used in the data sheet ret­ro­spec­tive­ly, do the following:

1. Open the Formulas tab at the top of the menu bar.
2. Select the Show formulas option.
3. You will now see the formula used in the selected cell.

Use functions in Excel formulas

When creating an Excel formula, you can also integrate a function. To do this, proceed as follows:

1. Select the cell you want to use for the formula.
2. Enter the relevant function after the equals sign. This could be SUM, for example.
3. Now enter an open bracket as you normally would for functions and select the cell range you need for the cal­cu­la­tion.
4. Close the bracket at the end of the function.
5. You will be shown the result after you press the Enter key.

Delete Excel formula and keep the result

To remove a formula from the data sheet and only keep or insert values, do the following:

1. Click on the cell with the cor­re­spond­ing formula. Copy the content of the cell using the Excel shortcut [CTRL] + [C].
2. Select the cell where you want to insert the value without formulas. Then go to the Start tab and click on the small arrow pointing downward under Paste in the Clipboard menu area.
3. In the Paste values section, select the Values option to paste only the values (i.e., the result without the formula).

The benefit of removing formulas is that you leave the data sheet or workbook with all the results clearly arranged. This way you can pass it on to third parties without any formulas getting in the way.