Every Excel workbook eventually comes down to one question: can you tell the program what to compute and trust it to update the answer when numbers change? That is what a formula does. You type an equals sign, point at some cells, and Excel returns a live result that refreshes the moment your data moves. Once that habit clicks, the spreadsheet stops feeling like a glorified calculator and starts working like a tiny decision engine on your screen.

This guide walks through the whole process from scratch. We'll start with the simplest possible formula, layer in cell references, then graduate to functions like SUM, AVERAGE, and IF. By the end you will know how to write a formula by hand, how to use AutoSum for speed, and how to copy a formula across hundreds of rows without breaking it. None of this needs VBA, macros, or any setup beyond a blank sheet.

If you have opened Excel before but never built anything yourself, you are exactly the reader we wrote this for. Beginners stumble in the same three places: forgetting the equals sign, mixing absolute and relative references, and pasting a working formula into a row where the cell math suddenly shifts. We'll hit each one in plain language. No jargon dump.

The Anatomy Of An Excel Formula

A formula in Excel always starts with =. That single character tells Excel: stop treating the cell like text, start treating it like math. After the equals sign you write an expression. The expression can be a calculation, a function call, a logical test, a text join, or any combination of those.

Type =5+3 in a cell, press Enter, and the cell shows 8. The formula bar still shows =5+3, but the cell displays the result. That two-layer view, formula above, value below, is the single most important thing to understand about Excel. The cell is not what you typed. It is what you typed, evaluated.

You'll also see operators. Plus, minus, asterisk for multiply, forward slash for divide. Parentheses control the order. =(2+3)*4 returns 20, while =2+3*4 returns 14 because multiplication runs before addition. Excel follows the same precedence as a math textbook.

Equals Sign Comes First, Always

Forget the equals sign and Excel treats your input as plain text. 5+3 typed without an equals sign just sits there as the string "5+3". This trips up almost every beginner once. After you make the mistake twice, the habit sticks.

Your First Formula: A Simple Sum

Open a fresh worksheet. In cell A1 type 20. In A2 type 15. In A3, type =A1+A2 and press Enter. A3 now shows 35. Change A1 to 100 and watch A3 jump to 115 instantly. That live recalculation is the magic.

Notice what you wrote. You did not type =20+15. You typed =A1+A2. That means you referenced the cells, not the numbers. Cell references are the whole point. They let you build a workbook once and update the inputs forever.

You can also click cells instead of typing references. Start a formula with =, click A1, type +, click A2, press Enter. Excel writes =A1+A2 for you. Most experienced users mix both methods, typing when it's faster and clicking when ranges get wide.

The Five Basic Operators

You already know plus and minus. The others are * for multiply, / for divide, and ^ for exponents. So =2^10 returns 1024 and =A1/A2 returns the quotient. Multiplying in Excel uses the asterisk because the letter x would be ambiguous with cell coordinates.

Relative vs Absolute References

Here's where formulas get powerful and confusing at the same time. When you copy =A1+A2 from C3 down to C4, Excel does not paste the literal text. It shifts the references one row, giving you =A2+A3. That is a relative reference. The cell coordinates move with the formula.

Most of the time you want this. If column C calculates totals from columns A and B, you write the formula once in C2 and drag it down. Each row picks up its own A and B values. Beautiful.

But sometimes you need one part of the formula to stay put. Say cell E1 holds a tax rate of 0.08 and you want to multiply every row by it. If you write =A2*E1 in B2 and drag down, B3 becomes =A3*E2. Wrong cell. The tax rate vanished.

Fix it with a dollar sign. =A2*$E$1 locks E1 in place. Drag the formula down and every row multiplies by E1, exactly what you wanted. Read more in our deep dive on absolute references.

Quick Toggle: The F4 Key

Click on a reference inside the formula bar, hit F4, and Excel cycles through the four lock states: A1, $A$1, A$1, $A1. Press it until you see the lock pattern you want. Once muscle memory kicks in, you'll never type dollar signs by hand again.

Functions: Formulas With Names

So far we've used arithmetic. Excel ships with over 450 built-in functions that handle everything from summing a column to looking up values across sheets. A function is just a named operation that takes arguments in parentheses. Syntax: =NAME(arg1, arg2, ...).

The SUM function is the most common. Instead of writing =A1+A2+A3+A4+A5 you write =SUM(A1:A5). Same result. Cleaner. And it handles ranges with thousands of cells just as easily. Our guide on the SUM formula covers every variant.

Other essentials: AVERAGE for means, MIN and MAX for extremes, COUNT for numeric tallies, and COUNTA for any non-empty cell. Logical decisions use IF. Lookups use VLOOKUP or the newer XLOOKUP. Each one takes inputs, runs its logic, returns a value.

Functions can nest. =ROUND(AVERAGE(A1:A10), 2) averages ten cells, then rounds the result to two decimals. Inner function runs first, outer wraps it. You can stack five or six deep without breaking anything, although readability dies after about three layers.

AutoSum: The Speed Trick

Select a cell directly below a column of numbers. Press Alt and = together. Excel guesses you want SUM, highlights the range above, and writes the formula for you. Press Enter to accept. That keystroke alone saves hours over a working week.

AutoSum also lives on the Home tab and Formulas tab as a Sigma button. Click the dropdown arrow next to it and you can swap to AVERAGE, COUNT, MAX, or MIN with one click. Beginners often miss this menu and write formulas by hand for weeks before discovering it.

One catch: AutoSum guesses the range. If your column has a blank cell in the middle, AutoSum might stop there. Always glance at the highlighted range before pressing Enter. If it's wrong, drag the corners or type the correct range manually.

AutoSum Across Rows And Columns

You can also select a whole block of cells, including an empty row at the bottom and column at the right, then press Alt-equals. Excel fills row totals, column totals, and the grand total all at once. Two seconds, no formulas typed.

Copying Formulas Without Breaking Them

Once a formula works in one cell, you'll want to repeat it down a column or across a row. There are three ways and they all behave slightly differently.

The fill handle is the small green square at the bottom-right of a selected cell. Hover until your cursor turns to a plus, then drag down. Excel copies the formula and adjusts relative references row by row. Double-click that handle and Excel auto-fills down to the last row of adjacent data, which is enormously handy on long sheets.

Copy-paste works the same way for formulas. Ctrl+C on the source, select target range, Ctrl+V. References shift unless they are absolute. If you only want the result, not the formula, use Paste Special and choose Values.

The third option is fill-down keyboard shortcut: select source and target together, press Ctrl+D. Same result, no mouse. More copy techniques in our dedicated guide.

Common Beginner Mistakes

The most frequent issue we see is the #REF! error. You wrote a formula that referenced cell A5, then deleted column A. The reference no longer points anywhere, so Excel throws #REF!. Undo, or restore the column, or update the formula. Errors are not random; they tell you what broke.

#VALUE! usually means you tried math on text. =A1+B1 where B1 contains the word "five" returns the error. Use =A1+VALUE(B1) if the text is actually a numeric string, or fix the source data. The full breakdown of VALUE errors covers every cause.

#DIV/0! appears when you divide by zero or by an empty cell. Wrap the formula in IFERROR: =IFERROR(A1/B1, 0). If B1 is empty, the formula returns 0 instead of erroring out. Saves a lot of cleanup on dashboards.

Circular references happen when a formula refers back to its own cell. Excel will warn you the first time. Track them down with the Formulas tab and the circular reference finder. Until you fix the loop, calculations stay frozen.

Real-World Formula Patterns

Let's walk through a few patterns you'll hit constantly in practice. Each one combines what we covered above into something useful enough to drop into an actual workbook this afternoon.

Pattern 1: Running Total Down A Column

Say column A has daily sales numbers and you want a running total in column B. In B2 type =A2. In B3 type =B2+A3 and drag it down. Each row adds the current day's sales to the prior running total. Simple, but it's the backbone of cumulative charts, year-to-date reports, and waterfall analysis.

Pattern 2: Percentage Of Total

Sum the column first in a cell, say A20. Then in B2 write =A2/$A$20. The dollar signs lock A20 in place. Drag the formula down, format as percentage, and every row shows its share of the grand total. Beginners forget the lock and end up dividing each row by a moving target, which silently produces nonsense.

Pattern 3: Conditional Counts

You want to count how many cells in a column equal "approved". Use =COUNTIF(A:A, "approved"). The first argument is the range. The second is the criterion. COUNTIF and its sibling SUMIF together cover a huge slice of business reporting needs. Quotes around text criteria. No quotes around numbers.

Pattern 4: Pull A Value From Another Sheet

Cross-sheet references use the sheet name plus an exclamation point. =Sheet2!B5 grabs the value from B5 on Sheet2. If your sheet name has a space, wrap it in single quotes: ='Sales Data'!B5. This is how you build dashboards that pull from raw data tabs without copying anything.

Pattern 5: Conditional Result With IF

The IF function takes three arguments: a test, a value if true, and a value if false. =IF(A2>100, "high", "low") labels rows above 100 as high and the rest as low. Nest IFs for more buckets, although once you're past three levels deep, switch to the cleaner IFS function or build a small lookup table.

Pattern 6: Combine Text From Multiple Cells

Need to glue a first name and last name together? Use =A2&" "&B2 with the ampersand operator, or call =CONCAT(A2," ",B2) for the function version. Both produce "Jane Smith" from "Jane" and "Smith". The space in quotes is what keeps the names from running together.

Each of these six patterns is short. Once you've typed them in your own workbook two or three times, they become reflex. Combine a couple, like SUMIF inside a percentage formula, and you've already built logic most office workers would call advanced. The fastest way to lock in any of them is to retype the formula from memory after seeing it work once.

Muscle memory beats reference cards every single time you sit down to actually build something. Aim for one pattern a day and within a week your workbook fluency will look completely different. Bookmark the patterns you struggle with, run a small drill, and watch each one click into place.

From Formula To Function Library

The path from "I can add two cells" to "I built a real model" is shorter than it looks. Once you've nailed equals-sign, cell references, and copy-paste, every new function follows the same pattern: name, parentheses, arguments, result. You don't memorize 450 functions. You memorize the dozen you actually use, and look up the rest when needed.

Start with SUM, AVERAGE, COUNT, MIN, MAX, IF, and VLOOKUP. Add COUNTIF, SUMIF, and CONCATENATE next. That ten-function toolkit covers maybe 90% of real workplace spreadsheets. Anything more exotic, like INDEX/MATCH combinations or array formulas with SUMPRODUCT, comes later as the problem demands.

Hands-on practice beats reading every time. Try our Excel practice tests to sharpen what you learn, then move to the formulas reference when you want to expand your library. Build a budget, build an invoice, build a tracker. The formula muscle grows fastest when you have a real reason to use it.

One last tip: turn on Show Formulas (Ctrl + grave accent) when you're debugging a sheet. It swaps every cell's display from result to formula so you can see what each one is doing at a glance. Toggle it back off when you're done. That single shortcut has saved more spreadsheet headaches than any single function.