The Excel spreadsheet offers a multitude of simple and complex formulas for performing calculations. Like a calculator, Excel uses formulas during operations in order to calculate and process data. Discover our list of Excel formulas.
What is an Excel formula?¶
Once the appropriate Excel formula has been found, the spreadsheet can be used like an intelligent calculator. Indeed, an Excel formula is nothing other than a mathematical equation with formula signs, numbers and letters. The use of calculation operators allows you, among other things, to make additions or multiplications using Excel formulas. By associating cell references or constants with the desired calculation operators, you can calculate individual cell values as well as complex cell ranges.
Excel with Microsoft 365 for Business and IONOS!
Use Excel to organize your data and save time – included in all Microsoft 365 packages!
Office Online
OneDrive with 1TB
24/7 support
What is the difference between a formula and a function in Excel?¶
Just like formulas, Excel functions are a valuable tool for calculations. And even though the two are similar and used in combination, there are a clear difference between Excel formulas and functions :
- formula : A formula always begins with an equation sign followed by cell references or cell addresses. These are linked by calculation operators such as “+” to add or “-” to subtract.
- function : a function consists of a formula but is not a formula. Functions are automated workflows, algorithms and programs such as “SUM” or “ROUND”. A function uses a fixed, predefined combination of formulas to produce a specific result. Functions allow you to calculate complex cell values and can be part of a formula.
To highlight the difference between a formula and a function, we will for example compare the addition of cells A1 to A6 with a formula and a function:
| Add using an Excel formula | Add using a function |
|---|---|
| =A1+A2+A3+A4+A5+A6 | =SUM(A1:A6) |
The commonality between Excel formula and Excel function is that both can create a link between cells and the values contained in the cells.
List of Excel formulas you absolutely need to know¶
Below we give you a list of essential Excel formulas to know how to use.
| Calculation operator | Excel formula example |
|---|---|
| Addition | =A1+A2 |
| Substraction | =A1-A2 |
| Multiplication | =A1*A2 |
| Division | =A1/A2 |
| Power | =A1^2 |
| Select and use a range of cells | =A1:A10 |
How does an Excel formula work?
To perform calculation operations, Excel formulas use different calculation algorithms that are based on cell references, constants and related calculation operators. Each formula you enter into an Excel cell of your choice must begin with the equation sign “=”. After the equation sign, enter cell references and operators. For example, by entering the formula “=A1+A2” in Excel, cells A1 and A2 and their contents are added and the result is displayed in the command line.
Note that, in Excel formulas, the classic rule of precedence of operations applies with the period before the comma. So, if you want to use multiple operators in a formula, multiplication and division always take precedence over addition and subtraction.
What is the structure of an Excel formula?¶
Each Excel formula starts with the equation sign “=”, followed by cell references, i.e. cell addresses which you need for the calculation. You can also calculate using constants, that is, fixed, unchanging cell values. Cell references in turn bind to calculation operators.
An Excel formula can contain the following elements, individually or all at once and in chronological order:
- the equation sign “=”
- a function such as “SUM”
- cell references (e.g. A2 or B3)
- constants (moving or copying a formula to get constant text or number values in a cell like “3”)
- operators like “*” or “+”
Below we give you two examples that explain the structure of an Excel formula:
- example 1: you want to add cells A1 to A4 using an Excel formula. This is what the formula looks like: “=A1+A2+A3+A4”
- example 2: you want to divide the numerical values of cells A1 and A2, the formula is: “=A1/A2”
What are the calculation operators for Excel formulas?¶
The main ones types of calculations available in the form of Excel formulas are normally old memories from math classes. Depending on the desired calculation or analysis of cells and values, you can choose from the following calculation operators:
- addition: “+”
- substraction : ” – “
- multiplication: “*”
- division: “/”
- power: “^”
HiDrive online storage
With HiDrive, your files are securely backed up in the Cloud based in Europe and can be accessed from any device!
24/7 support
Shared access
Ultra-secure
How to use Excel formulas?
We show you below a list of some Excel formulas and how to use, process and display them.
Create an Excel formula¶
To enter an Excel formula that refers to cell values, follow these steps:
- Click on the cell of your choice or on a cell adjacent to the cell values.
- Now enter the equation sign “=”.
- Directly select the cells for your formula. You can also enter the address of the cells after the equation sign.
- Now type the desired operator like “+” or “-”.
- Now select the next cell or enter the cell address after the operator.
- Press the Enter key so that the result for the selected cells is displayed.
Process an Excel formula¶
To modify a created Excel formula after the fact, two options are available to you:
- option 1: click on the cells affected by the formula and modify it directly in the cell.
- **option 2:**as soon as you enter an Excel formula in a cell of your choice, it is displayed at the top in the formula bar.
View an Excel formula¶
Do you want to display a formula used in the datasheet later? Proceed as follows:
- Click at the top in the menu bar on the “ Formulas “.
- Click on ” Show formulas “.
- The formula used is now displayed in the chosen cell.
Use an Excel formula with built-in function¶
When creating an Excel formula, you have the option of integrating a function. To do this, perform the following steps:
- Select the cell you want for the formula.
- After the equation sign, enter the desired function (for example: “SUM”).
- Now enter an opening parenthesis as usual for functions and select the range of cells you need for your calculation.
- To end the function, enter a closing parenthesis.
- The result is displayed by pressing the enter key.
Delete an Excel formula and keep the result¶
To remove a formula from the datasheet and keep or add only values, follow these steps:
- Click the cell with the corresponding formula. Copy cell contents using Excel shortcut [STRG] + [C].
- Select the cell you want to insert a value without a formula. Then go to the “ Welcome ”, then on the little arrow in the “Paste” space.
- In the submenu “ Paste values “, select the “Values” option so you can insert the value or result without the formula.
The advantage of deleting formulas is that you pass a datasheet or workbook to a third party, with all the results readable, without the formulas altering the reading.
Microsoft 365 for businesses with IONOS!
The latest version of your favorite Office apps on all devices.
Free installation assistance!
Exchange up to 50 GB
OneDrive with 1TB
24/7 support
