Formulas transform Excel from a static grid into a living calculator that updates automatically as your data changes. Type numbers into cells and they just sit there. Write formulas that reference those cells and suddenly you have a system that responds intelligently to any update. This responsiveness is why Excel dominates spreadsheet work it’s not just storage, it’s active calculation.
Every formula starts with an equals sign. This tells Excel you’re entering a calculation, not plain text or numbers. Without the equals sign, Excel treats whatever you type as text. Type 2+2 into a cell and Excel shows exactly that: the text “2+2”. Type =2+2 and Excel calculates it, displaying 4. That equals sign is the trigger for Excel’s calculation engine.
Formulas can be as simple as adding two numbers or as complex as nested functions with multiple conditions. This article covers the basics: arithmetic operators, how to reference cells, and simple calculations. These building blocks underpin everything more advanced you’ll learn later. Get comfortable with basic formulas and you’ve unlocked Excel’s true power.
The Five Basic Arithmetic Operators
Excel uses symbols for math operations, and they’re mostly what you’d expect. The plus sign (+) adds, the minus sign or hyphen (-) subtracts, the asterisk (*) multiplies, the forward slash (/) divides, and the caret (^) raises to a power. These five operators handle the vast majority of calculation needs.
Addition is straightforward: =5+3 equals 8. In practice, you rarely add literal numbers in formulas. Instead, you add cell references, which we’ll cover shortly. Subtraction works identically: =10-4 equals 6. These operations are so basic they feel obvious, but building confidence with simple examples is important before moving to cell references and complexity.
Multiplication uses the asterisk, not an x. This avoids confusion with the letter x. So =45 equals 20. If you need to calculate a price per unit times quantity, you’d use the multiplication operator: =A2B2 where A2 might hold price and B2 might hold quantity. Division uses the forward slash: =20/4 equals 5. This calculates ratios, averages (manually), per-unit amounts, and percentages.
The power operator raises a number to an exponent. =2^3 means 2 to the third power, or 222, which equals 8. This operator is less common in everyday work but essential for compound interest calculations, exponential growth models, and scientific formulas. Most beginners don’t need it immediately but it’s good to know it exists.
These operators can combine in one formula: =10+5-3 equals 12. Excel follows standard order of operations multiplication and division before addition and subtraction. So =10+52 doesn’t equal 30 (doing left to right); it equals 20 (52=10, then 10+10=20). Understanding this order prevents calculation errors and leads to the next concept: parentheses.
Using Parentheses to Control Calculation Order
Parentheses force Excel to calculate what’s inside them first, overriding standard order of operations. If you want (10+5)2, you need parentheses. Without them, =10+52 does the multiplication first. With them, =(10+5)*2 adds first (getting 15), then multiplies by 2 (getting 30).
Parentheses are critical for complex formulas. Imagine calculating the total cost of items where cost equals base price plus tax percentage. The formula is price * (1 + tax). If tax is 8%, that’s =A2*(1+0.08) or =A21.08. Without the parentheses, you’d write =A21+0.08, which multiplies A2 by 1 (getting A2), then adds 0.08 – completely wrong.
You can nest parentheses inside each other for multi-step calculations. Excel evaluates from the innermost parentheses outward. Something like =((10+5)*2)-3 would first add 10+5 (getting 15), then multiply by 2 (getting 30), then subtract 3 (getting 27). This nesting lets you build complex calculations while keeping the logic clear.
When writing formulas with parentheses, count carefully to ensure they balance. Every opening parenthesis needs a closing one. Excel helps by color-coding matching pairs as you type, and it alerts you if parentheses don’t match when you press Enter. Still, it’s a common mistake to miss a close parenthesis in a long formula, so check deliberately.
Parentheses also improve readability. Even if order of operations would give the correct answer without them, adding parentheses makes your intent explicit. =A1+B1/C1 is technically clear, but =(A1+(B1/C1)) or =((A1+B1)/C1) removes all ambiguity about what you meant. For formulas others will read or you’ll revisit later, extra parentheses are documentation.
Referencing Cells in Formulas
The real power of formulas comes from referencing cells instead of hardcoding numbers. When you write =A1+A2, Excel looks at what’s currently in cells A1 and A2, adds those values, and displays the result. Change what’s in A1 or A2 and the formula result updates instantly. This makes spreadsheets dynamic.
Cell references use column letters and row numbers. A1 is column A, row 1. B5 is column B, row 5. AZ200 is column AZ, row 200. Excel highlights cells when you click them while writing a formula, making it easy to build references without typing. Start typing =, then click the cells you want to include. Excel inserts the references automatically.
You can mix cell references and operators freely. =A1+B1-C1 adds the values in A1 and B1, then subtracts C1’s value. =(A1+A2)*B1 adds A1 and A2, then multiplies the sum by B1’s value. =A1/B1 divides A1 by B1. Each formula creates a connection between cells, establishing relationships that persist as data changes.
References aren’t limited to cells on the same row or column. =A1+Z50 is perfectly valid even though those cells are far apart. =B2*D8/F3 combines three different cells in one calculation. The spatial relationship doesn’t matter; you’re referencing values by their address.
One key behavior: if you copy a formula to another cell, Excel adjusts the references automatically. This is called relative referencing. If B1 contains =A12 and you copy B1 to B2, Excel changes the formula to =A22. It maintains the relationship (“multiply the cell to my left by 2”) rather than the specific address. We’ll return to this concept because it’s crucial for efficiently building spreadsheets.
Creating Your First Simple Formulas
Let’s walk through creating a few basic formulas to solidify these concepts. Imagine you’re tracking weekly sales. In cell A2 you type Monday’s sales: 150. In A3, Tuesday: 200. In A4, Wednesday: 175. Now in A5, you want the total for these three days. Click cell A5 and type =A2+A3+A4, then press Enter. Excel displays 525, the sum of those three values. Look at the formula bar at the top of the screen while A5 is selected – you’ll see the formula =A2+A3+A4 displayed there, while the cell shows 525. The formula bar always shows the actual formula, while the cell shows the calculated result. Understanding this difference prevents confusion.
Now change Monday’s sales in A2 to 175. The moment you press Enter, cell A5 updates to 550 without you touching it. That’s the formula responding to changed input. This live calculation is what makes formulas indispensable. You build the relationships once, then your data stays consistent as inputs change.
For another example, calculate the average sales per day. You could use a function (AVERAGE), but with basic operators you can do it manually. In A6, type =(A2+A3+A4)/3. This adds the three days and divides by 3. The result is 183.33. Again, if you change any of the daily values, the average recalculates instantly.
Try a percentage calculation. If A7 contains planned sales of 500 and A8 contains actual sales of 550, what percentage of plan did you achieve? In A9, type =A8/A7. Excel shows 1.1 (or maybe 1.10 depending on formatting). To express this as a percentage, either format A9 as percentage (which multiplies by 100 and adds the percent sign automatically), or adjust the formula to =A8/A7*100, which displays 110.
These simple examples demonstrate the core formula workflow: select a cell, type =, build the calculation using operators and cell references, press Enter. The cell displays the result, the formula bar shows the formula, and Excel recalculates automatically if inputs change. This pattern remains consistent as formulas grow more complex.
The Difference Between Formulas and Values
A cell can contain either a value or a formula, not both. When you type 100 into a cell, that cell contains the value 100. When you type =50+50, that cell contains a formula that evaluates to 100, but it doesn’t contain the value 100. Excel stores the formula and displays the result. This distinction matters when copying, referencing, or troubleshooting.
If you copy a cell containing a formula and paste it elsewhere with standard paste (Ctrl+V), you copy the formula, not the displayed value. The formula gets pasted and recalculates based on the new location’s context. If you specifically need to copy the result as a static value, you use Paste Special > Values (accessible by right-clicking after copying, or Ctrl+Alt+V). This pastes only the calculated number, not the formula that produced it.
Referencing a cell in another formula uses whatever that cell contains. If A1 has the value 50, and B1 has the formula =A1*2, then B1 displays 100. If you write =B1+10, Excel takes B1’s result (100) and adds 10, giving 110. You’re building a chain: value in A1, formula in B1 references A1, formula in C1 references B1. This chaining is common and powerful, letting you break complex
calculations into logical steps.
You can see whether a cell contains a formula or a value by selecting it and looking at the formula bar. Values appear as-is. Formulas start with =. Sometimes you’ll see a cell displaying a number but the formula bar shows a calculation, confirming it’s a formula-generated result. This transparency is one of Excel’s strengths you can always audit how a number was calculated. Understanding this formula/value distinction helps when troubleshooting. If a cell shows an unexpected number, select it and check the formula bar. You might discover it’s pulling from a different cell than you thought, or using an operator you didn’t intend. The formula bar is your debugging tool for understanding what Excel is actually calculating.
Common Formula Errors and How to Fix Them
Forgetting the equals sign is the most common beginner mistake. Type 5+3 without the = and Excel thinks you’re entering text. It displays exactly what you typed instead of calculating. If your formula doesn’t calculate, first check that it starts with =. Mismatched parentheses create errors. If you open three parentheses but only close two, Excel shows an error when you press Enter and may try to guess how to fix it. Carefully count your parentheses. Keeping formulas formatted on paper or in a text editor first can help spot these issues before entering them into Excel.
Division by zero is impossible mathematically, and Excel tells you so with a #DIV/0! error. This happens when the denominator of a division is zero or a blank cell. If A1 is empty and you write =10/A1, you get #DIV/0!. The solution is ensuring the denominator contains a non-zero value, or using error-handling techniques (IF statements or IFERROR function) to gracefully handle division by zero cases.
Referencing a deleted cell causes a #REF! error. If your formula says =A1+B1 and you delete column B, the formula becomes =A1+#REF! because B1 no longer exists. Excel can’t calculate it. You’ll need to fix the formula to reference an existing cell or undo the deletion.
Text in formulas causes problems if you’re not careful. =A1+A2 works fine if both cells contain numbers. If A1 contains the text “Hello”, Excel can’t add text to a number and shows #VALUE! error. Make sure cells referenced in math formulas actually contain numbers. Sometimes numbers stored as text cause this – they look like numbers but Excel treats them as text. Converting them
to proper numbers fixes the error.
Circular references happen when a formula refers to its own cell, directly or indirectly. If A1 contains =A1+1, Excel can’t calculate it because A1 depends on A1’s current value, which depends on A1, infinitely. Excel usually detects and warns about circular references. Fix them by breaking the circular dependency – typically moving part of the calculation to another cell.
Copying Formulas: Relative References Explained
When you copy a formula to another cell, Excel automatically adjusts the cell references. This is called relative referencing and it’s one of Excel’s most powerful features. If cell B2 contains =A22 (meaning “multiply the cell to my left by 2”), copying B2 down to B3 changes the formula to =A32. Excel keeps the relative relationship (cell to the left), not the absolute address (A2). This lets you write one formula and replicate it across hundreds of cells instantly. Imagine a spreadsheet with product names in column A, prices in column B, and quantities in column C. In column D, you want total cost (price * quantity). Write the formula once in D2 as =B2C2, then copy D2 down to D100. Every row gets the correct formula with references adjusted: D3 becomes =B3C3, D4 becomes =B4*C4, and so on.
To copy formulas down or across, use the fill handle – the small square at the bottom-right corner of the selected cell. Click and drag it, and Excel copies the formula, adjusting references automatically. Alternatively, copy the cell (Ctrl+C), select the range where you want it pasted, and paste (Ctrl+V). Both methods apply relative referencing.
Relative referencing is why formulas are so much more powerful than typing values. One formula serves an entire column or row. Update the original formula and you can quickly re-copy it to update all dependent cells. This efficiency scales: a 10-row spreadsheet and a 10,000-row spreadsheet use the same single formula technique.
Understanding relative references also helps troubleshoot. If you copy a formula and it gives wrong results, check whether the references adjusted as you intended. Maybe you needed an absolute reference (discussed in more advanced topics) to keep one part of the formula fixed. For now, know that standard copy-paste of formulas shifts all references relatively.
When to Use Formulas vs Functions
Formulas and functions are related but distinct. A formula is any calculation that starts with =. Functions are pre-built formulas that Excel provides, like SUM, AVERAGE, and IF. You use functions within formulas. So =SUM(A1:A10) is a formula that uses the SUM function.
For simple arithmetic, basic operators often suffice. =A1+A2+A3 is perfectly fine for three values. For longer ranges, functions are better: =SUM(A1:A10) is much easier than =A1+A2+A3+A4+A5+A6+A7+A8+A9+A10. Functions handle tedious repetition and offer capabilities operators alone can’t match.
Functions come later in your learning path. For basic tasks, operators cover your needs. Addition, subtraction, multiplication, division, and powers handle pricing calculations, percentage changes, totals, averages (manually), and ratios. Once you master these operators and cell references, functions are a natural next step that builds on this foundation.
Think of operators as the grammar of Excel formulas. You need to understand operator syntax and precedence before functions make sense. Functions are vocabulary – useful shortcuts that leverage the grammar. Learn grammar first, then expand vocabulary. This article focuses on grammar. Your next step is exploring functions like SUM, AVERAGE, and COUNT, which are
covered in dedicated function tutorials on this site.
Ready to graduate from basic formulas? Check out our guide to essential Excel functions to learn SUM, AVERAGE, COUNT, and more. Dive into absolute and relative cell references for better control when copying formulas. Or apply these formula basics immediately by following our tutorial on creating a simple budget in Excel, where formulas bring your financial data to life.
