A formula in Excel is an entry that calculates a value. Microsoft’s definition is compact: “Formulas are equations that perform calculations on values in your sheet.” Every formula begins with an equal sign; the = tells Excel to calculate what follows instead of storing it as characters. Type =2+3 into a cell and the cell shows 5. Leave the equal sign off, SUM(A1:A10) for instance, and Excel stores the text string SUM(A1:A10) with no calculation behind it. Microsoft’s own troubleshooting page calls the missing equal sign a common mistake.
The formula and its result live in different places on screen. The cell shows the calculated value; the formula itself appears in the formula bar at the top of the window whenever the cell is selected. The last section of this page is a map: when a formula misbehaves, it points you at the guide for that specific failure.
| A | B | |
|---|---|---|
| 1 | Month | Sales |
| 2 | Jan | 1,200 |
| 3 | Feb | 950 |
| 4 | Mar | 1,400 |
| 5 | Total | 3,550 |
The parts of a formula
After the equal sign, a formula can contain any or all of four kinds of parts: functions, references, constants, and operators. One formula shows all four at once:
With the sales figures from the grid above, this returns 187.50: SUM adds B2:B4 to 3,550, times 0.05 is 177.50, plus 10 is 187.50.
- Functions are built-in, named calculations.
SUMis the one in the example;PI()takes no inputs at all and returns the value of pi. A function is a part; the formula is the whole entry, so=B2+B3+B4is a perfectly good formula that uses no function at all. - References point the formula at cells instead of fixed values.
A2returns whatever value is in cell A2;B2:B4refers to the block from B2 through B4. Because the formula reads the cells, the result updates when the cells change (with calculation on its default automatic setting). - Constants are numbers or text values typed directly into the formula, such as the
0.05and the10in the example. They never change unless you edit the formula. - Operators say what to do with the values.
^(caret) raises a number to a power and*(asterisk) multiplies. Excel groups its operators into four families: arithmetic, comparison, text concatenation (the&that joins text), and reference operators.
Each of those parts, along with the wider vocabulary of workbooks, worksheets, cells, and ranges, gets a one-line definition in the Excel definitions glossary.
Order of operations: what Excel calculates first
When a formula combines several operators, Excel does not simply work left to right. It evaluates in a fixed order: reference operators first (the colon in a range like B2:B4, plus the comma and the single space), then negation (the minus in -1), percent, and exponentiation (^), then multiplication and division (* and /), then addition and subtraction (+ and -), then the & that joins text, and comparison operators last (=, <, >, <=, >=, <>). When operators share a precedence level (a formula with both a multiplication and a division, for example), Excel evaluates them left to right.
Microsoft’s example is the shortest demonstration: =5+2*3 returns 11, not 21, because the multiplication runs first and the addition second. Parentheses override the order: =(5+2)*3 forces the addition first and returns 21.
The same trap in a worksheet looks like this:
| A | B | |
|---|---|---|
| 1 | Item | Amount |
| 2 | Subtotal | 200.00 |
| 3 | Shipping | 10.00 |
| 4 | Total with 5% tax | 220.50 |
When in doubt, add parentheses around the part you want calculated first. They cost nothing and they make the intent readable.
The formula bar
The formula bar sits at the top of the Excel window. Per Microsoft’s overview page, “When a formula is entered into a cell, it also appears in the Formula bar.” That is how you read a worksheet: the grid shows results, and to see the formula behind any result, you select the cell and read the bar.
The split matters because the grid alone cannot tell you where a number came from. A cell showing 3,550 might hold a typed constant or a calculation; both look identical in the grid. The formula bar is where you find out which one you are looking at.
Working in the bar:
- Select a cell to see its formula in the bar. You can edit there directly, and finish from either place: Enter completes an entry from the cell or the formula bar, Esc cancels it.
- In Excel for Windows, F2 edits the active cell and puts the insertion point at the end of its contents; if in-cell editing is turned off, F2 moves the insertion point into the formula bar instead.
- In Excel for Windows, Ctrl+Shift+U expands or collapses the formula bar. A formula can run to 8,192 characters; the expanded bar shows more of a long one at once.
Entering and editing a formula
The basic entry procedure is short: select the cell where you want the result, type = followed by the constants, references, and operators of your calculation, and press Enter (Return on a Mac). You can also point instead of typing: type =, select the first cell you want to include, type an operator such as +, then select the next cell, and Excel writes the references for you.
For the most common calculation of all, summing a column or row, you do not need to type anything: select a cell next to the numbers, select AutoSum on the Home tab, and press Enter. Excel proposes the range and writes the SUM formula.
To change an existing formula, select the cell and edit in the formula bar, or press F2 in Excel for Windows to edit in the cell.
When a formula goes wrong
If you looked this up because a formula is misbehaving, match the symptom; each failure mode has its own guide here.
A formula that shows itself, or will not update. A cell displaying =SUM(B2:B4) as text, or a total that ignores edits to its inputs, is the territory of why is my Excel formula not calculating: Text-formatted cells, Show Formulas mode, manual calculation, and stray characters before the equal sign. A close cousin hides on the input side: values that look like numbers but are stored as text, so calculations quietly skip them. Numbers stored as text covers spotting and fixing those.
An error value in the cell. A cell showing #REF!, #VALUE!, #NAME?, or #DIV/0! is calculating, and the calculation is failing; each error names its own cause, and Excel’s error values, explained walks through the vocabulary. The most common special case is a lookup returning #N/A because it cannot find a match, which has its own walkthrough in the VLOOKUP #N/A guide. If you are choosing a lookup in the first place, start with XLOOKUP or the VLOOKUP vs XLOOKUP comparison, and reach for INDEX and MATCH when the lookup column is not where VLOOKUP needs it.
Right formula, wrong cells. A copied formula that returns a plausible but wrong number usually had a relative reference shift when it moved; cell references in Excel covers when to lock a reference with dollar signs and when not to. Formulas that pull values from other tabs have their own syntax and their own failure modes, covered in referencing another sheet. And when a formula has grown into coordinate soup, named ranges replace $B$1 with a readable name.
Loops and spills. A formula that includes its own cell in its inputs, directly or through a chain of other cells, is a circular reference; circular references in Excel covers finding and fixing them. And a single formula that returns a whole set of values places them in the neighbouring cells, which Excel calls spilling; array formulas explains that behaviour and its failure modes.
Building logic, not fixing it. For a formula that chooses between outcomes, start with the IF function. For counting and adding with conditions, COUNTIF and SUMIF. When the arithmetic outgrows them, multiplying matching rows and summing the results, SUMPRODUCT is the tool. Joining text from several cells into one is the job of the ampersand and its function relatives, covered in concatenate in Excel; the reverse, pulling a piece back out of a string with LEFT, RIGHT, MID, and FIND, is extracting text in Excel; and formulas that compute with dates, TODAY, DATEDIF, WEEKDAY and the rest, are collected in Excel date functions.
Numbers and dates that look wrong. A value that displays rounded, or a column that will not add up to its own displayed total, is usually the display, not the data; how to stop Excel from rounding covers the fixes, and the ROUND function covers rounding the values on purpose. A date showing up as a five-digit number, or refusing to change format, is the territory of Excel date formats. And formatting a number or date into a label is the TEXT function’s job.
The short version
- A formula is an equation that calculates a value, and it always starts with
=. Without the equal sign, the entry is text and nothing is calculated. - After the
=, a formula can contain any or all of four parts: functions, references, constants, and operators. For the everyday arithmetic with those operators, how to add, subtract, multiply, and divide shows those four operators in use. - Excel calculates in a fixed operator order (multiplication before addition, among others) and evaluates equal-precedence operators left to right. Parentheses force the order you mean.
- The cell shows the result; the formula bar at the top of the window shows the formula behind it. In Excel for Windows, Ctrl+Shift+U expands the bar for long formulas.
- When a formula misbehaves, the symptom picks the guide: showing as text or not updating, error values, wrong cells after copying.
And when the question is not what a formula is but what changed between two versions of a workbook, compare the two files instead of reading formulas cell by cell.
