Begin creating formulas and using built-in functions to perform calculations and solve problems.
Important: The calculated results of formulas, as well as some Excel worksheet functions, may be slightly different between a Windows PC with x86 or x86-64 architecture and a Windows RT PC with ARM architecture. More information about these differences.
Important: This article discusses the similar XVERWEIS and SVERWEIS problems. Try using the new XVERWEIS function, an improved version of SVERWEIS that works in any direction and returns exact matches by default, making it easier and more convenient to use than the previous function.
Creating a formula that refers to values in other cells
Select a cell.
Type the equal sign (=).
Hint: Formulas in Excel always start with an equal sign.
Select a cell or enter its address in the selected cell.
Enter an operator. Example: The minus sign (-) for subtraction.
Select the next cell or enter its address in the selected cell.
Press the ENTER key. The result of the calculation is displayed in the cell with the formula.
See formula
When you enter a formula in a cell, it is also entered in the Edit bar displayed.
To view a formula, select a cell and the formula will be displayed in the edit bar.
Entering a formula that contains a built-in function
Select an empty cell.
Enter the equal sign (=) and then a function. Example:"=SUM" to retrieve the total metabolic rate.
Enter an opening parenthesis"(" a.
Select the cell range and enter a closing parenthesis")" an.
Press the ENTER key to display the result.
Download the formula tutorial workbook
We have put together a workbook for getting started with formulas that you can download. If you are new to Excel, but even if you have some experience, this tour will walk you through the most commonly used formulas in Excel. With real-world examples and helpful visuals, you’ll soon be using SUM, NUMBER, AVERAGE, and REFERENCE like a pro.
Formulas in detail
You can go through the individual sections below for more information on specific form elements.
A formula can also contain any or all of the following elements: Functions, References, Operators and Constants.
Components of a formula
1. Functions: The PI() function returns the value Pi: 3.142. back.
2. ReferencesA2 returns the value in cell A2.
3. constants: Numeric or text values entered directly into a formula, z. B. 2.
4. OperatorsThe caret sign (^) is used to raise a number to the power and the asterisk (*) indicates a multiplication.
A constant is a value that is not calculated, it always remains the same. The date 09.10.2008, the number 210 or the text "Quarterly profits" are examples of constants. An expression or a value resulting from an expression is not a constant. If you use constants in a formula instead of references to cells (example: =30+70+110), the result will only change if you change the formula. In general, it is best to enter constants in individual cells where they can be easily changed if needed, and then use references to those cells in formulas.
A reference identifies a cell or range of cells in a worksheet and tells Excel where the values or data to be used in a formula are located. Using references, you can use data from different parts of a worksheet in a single formula or use the value of a cell in different formulas. You can define references to cells in other worksheets of the same workbook or to other workbooks. References to cells in other workbooks are also called links or external references.
A1 reference type
By default, Excel uses the A1 reference format, which refers to columns of letters (A through XFD, for a total of 16.384 columns) and to rows with numbers (1 to 1.048.576) refers. These letters and numbers are called row and column headers. To refer to a cell, enter the column letter followed by the row number. For example, B2 refers to the cell at the intersection of column B and row 2.
Create a reference to
Use
The cell in column A and row 10
The cell range in column A between rows 10 and 20
The cell range that spans columns B through E in row 15
All cells in row 5
All cells in rows 5 to 10
All cells in column H
All cells in columns H to J
The cell range in columns A through E and rows 10 through 20
reference to a cell or range of cells on another worksheet in the same workbook
In the following example, the AVERAGE function is used to find the average value for the range B1:B10 in the worksheet "Marketing" calculated in the same workbook.
1. Refers to the worksheet "Marketing
2. Refers to the cell range B1 to B10
3. The exclamation mark (!) separates the worksheet reference from the cell range reference
Hint: If the worksheet being referenced contains spaces or numbers, you must place single quotation marks (‘) before and after the worksheet name, z. B. =’123’!A1 or =’Sales January!A1.
The difference between absolute, relative and mixed references
Relative references A relative cell reference in a formula (z. B. A1) is based on the relative position of the cell containing the formula and the cell to which the reference is made. If the position of the cell with the formula changes, the reference is changed as well. When you copy or fill the formula across rows or vertically along columns, the reference is automatically adjusted. By default, new formulas use relative references. If you use z. B. copy or fill a relative reference from cell B2 for cell B3, it will be automatically changed from =A1 to =A2.
Copied formula with relative reference
Absolute references An absolute cell reference in a formula (z. B. $A$1) always refers to a specific cell position. If the position of the cell with the formula changes, the absolute reference remains the same. If you copy or fill the formula across rows or vertically along columns, then the absolute reference is not adjusted. By default, new formulas use relative references; you may need to change these to absolute references. If you delete z. B. copy or fill an absolute reference from cell B2 for cell B3, the reference remains the same in both cells: =$A$1.
Copied formula with absolute reference
Mixed references A mixed reference has either an absolute column and a relative row or an absolute row and a relative column. An absolute column reference has the form $A1, $B1, and so on. An absolute row reference has the form A$1, B$1, etc. If the position of the cell containing the formula changes, the relative reference is changed, the absolute reference does not change. If you copy or fill the formula across rows or columns, the relative reference automatically adjusts; the absolute reference does not adjust. If you use z. B. copy or fill a mixed reference from cell A2 to B3, it will be adjusted from =A$1 to =B$1.
Copied formula with mixed reference
Simple references to multiple worksheets If you want to analyze data in the same cell or range of cells on multiple worksheets within a workbook, use a 3D reference. A 3D reference includes the cell or range reference preceded by a range of worksheet names. Excel uses all worksheets stored between the start and end names of the reference. For example, =SUM(sheet2:sheet13!B5) adds all values contained in cell B5 on all worksheets between and including Sheet2 and Sheet13.
You can use 3D references to reference cells in other worksheets, to set names, and to create formulas using the following functions: SUM, AVERAGE, AVERAGEA, NUMBER, NUMBER2, MAX, MAXA, MIN, MINA, PRODUCT, STDABW.N, STDABW.S, STABWA, STDABWNA, VAR.P, VAR.S, VARIANZA and VARIANZENA.
3D references cannot be used in array formulas.
3D references cannot be used together with the intersection operator (a single space) or in formulas where the Implicit Intersection is used.
What happens when you move, copy, paste or delete worksheets The following examples illustrate the effects of moving, copying, pasting, or deleting worksheets that are referenced in a 3D reference. In all examples, the formula =SUM(Table2:Table6!A2:A5) used to add cells A2 to A5 in worksheets 2 to 6.
Paste or Copy When you insert or copy worksheets between Table2 and Table6 (the two outer sheets in this example), Excel includes all values that are in cells A2 through A5 of the added sheets in the calculation.
Delete If you delete worksheets between Table2 and Table6, Excel removes their values from the calculation.
Move If you move worksheets from the range Table2 to Table6 to a location that is outside this range of sheets, Excel removes their values from the calculation.
Move an endpoint If you move Table2 or Table6 to a different location in the same workbook, Excel adjusts the calculation to fit the newly created table range.
Delete an endpoint delete Table2 or Table6, Excel adjusts the calculation to fit the newly created table range.
You can also use a reference type where both rows and columns are numbered in the worksheet. These Z1S1 references are especially useful for calculating row and column positions in macros. For Z1S1 references, Excel gives the position of a cell with the letter "Z" followed by the row number and the letter "S" followed by the column number to.
A relative reference to the cell located in the same column two rows above the active cell
A relative reference to the cell located two rows below and two columns to the right of the active cell
An absolute reference to the cell located in the second row and in the second column
A relative reference to the entire line above the active cell
An absolute reference to the current line
When recording a macro, Excel records some commands using Z1S1 references. For example, if you want to perform a command, such as clicking on the button AutoSum, record to insert a formula that adds a cell range, Excel records the formula with Z1S1 references rather than A1 references.
You can turn the R1C1 reference style on or off by selecting the checkbox R1C1 reference style under the section Working with formulas in the category Formulas of the dialog box Options activate or deactivate. To display this dialog box, click on the tab File.
Do you need further help?
You can always ask an expert in the Excel Tech Community or get support in the Answers Community.