Overview of Formulas
Formulas perform calculations in Excel. They always begin with the equal ( = ) sign, which is where you want the answer or results to appear. Formulas are great for working out “what if” scenarios that compare calculations based on changing data. Once you enter the formula, change only the amounts you need to calculate. You don’t have to keep entering “plus this” or “minus that,” as you would with a regular calculator. Formulas can contain values, constants, cell references, functions, and operators.
Values
In an Excel spreadsheet, values can be text, dates, numbers, or Boolean data. The type of value depends on the data to which it’s referring.
Constants
A constant is a value that doesn’t change and isn’t calculated. Although constants can be well-known ones such as Pi ( Π ), the ratio of a circle’s circumference to its diameter, they can also be any value, such as a tax rate or a specific date, that changes infrequently.
Cell References
Cell references, such as A1 or H34, indicate the location of data in a worksheet. A cell reference consists of the column letter and row number that intersect at the cell’s location. When listing a cell reference, the column letter always appears first, such as A1, F26, or W345. You’d enter multiple contiguous cell references into a formula as a range, which indicates only the start and endpoints. For example, the references A1, A2, A3 can be written as the range A1:A3.
Functions
Excel also contains a number of built-in formulas called functions. Functions make it easier to carry out commonly performed tasks. For example, easily add columns or rows of numbers with the SUM function. Or, use the VLOOKUP function to find specific information.
Operators
Operators are symbols or signs used in a formula to define the relationship between two or more cell references or values. For example, the plus sign (+) is an arithmetic operator used in formulas such as =A2+A3. Other arithmetic operators include the minus sign (-1) for subtracting, the forward-slash (/) for dividing, and the asterisk (*) for multiplication. In addition to arithmetic operators, comparison operators carry out a comparison between two values in the formula. The result of that comparison is either TRUE or FALSE. Comparison operators include the equal sign (=), less than ( < ), less than or equal to ( < = ) , greater than ( > ) , greater than or equal to ( > = ), and not equal to ( < > ). Finally, the ampersand (&) is a concatenation operator, joining data or multiple ranges of data in a formula. Here’s an example: {=INDEX(D6:F11, MATCH (D3 & E3, D6:D11 & E6:E11, 0), 3)} The concatenation operator is used to combine multiple data ranges in a lookup formula using Excel’s INDEX and MATCH functions.
How to Create a Simple Formula
Here’s how to create a formula that refers to values in other cells.
