How to Create a Formula in Excel: A Complete Beginner's Guide

Excel formulas are the engine behind almost everything useful the application does. Whether you're adding up a column of numbers, calculating a percentage, or pulling data from another sheet, formulas are how you tell Excel to do something rather than just store something.

What Is a Formula in Excel?

A formula is an instruction you type into a cell that tells Excel to calculate a result. Every formula starts with an equals sign (=). Without it, Excel treats your input as plain text or a number — not a calculation.

For example:

=5+3 returns 8
=A1+B1 adds the values in cells A1 and B1
=SUM(A1:A10) adds all values from A1 through A10

The equals sign is non-negotiable. It's how Excel knows a formula is coming.

The Anatomy of an Excel Formula

Understanding the structure helps you build formulas confidently instead of guessing.

Component	What It Does	Example
`=`	Signals the start of a formula	`=`
Function name	A built-in operation	`SUM`, `IF`, `AVERAGE`
Arguments	The inputs the function uses	`A1:A10`, `"Yes"`, `B2>100`
Operators	Math or comparison symbols	`+`, `-`, `*`, `/`, `>`, `=`
Cell references	Points to another cell's value	`A1`, `$B$2`, `C3:C20`

Not every formula uses all of these. =A1+A2 uses only cell references and an operator. =SUM(A1:A10) uses a function with a range argument.

How to Enter a Formula Step by Step

Click the cell where you want the result to appear
Type = to begin the formula
Enter your expression — either a direct calculation or a function
Press Enter to confirm

Excel calculates immediately and displays the result in the cell. To see the formula itself, click the cell and look at the formula bar at the top of the screen.

Using Basic Arithmetic Operators

For straightforward math, you don't need a function at all:

Addition:=A1+B1
Subtraction:=A1-B1
Multiplication:=A1*B1
Division:=A1/B1
Percentage of a value:=A1*0.15 (15% of A1)

Excel follows standard order of operations — multiplication and division before addition and subtraction. Use parentheses to control the order: =(A1+B1)*C1 adds first, then multiplies.

Working With Built-In Functions 📊

Functions are pre-built formulas that handle more complex tasks. You call them by name, then pass in arguments inside parentheses.

Commonly used functions:

Function	What It Does	Example
`SUM`	Adds a range of numbers	`=SUM(B2:B20)`
`AVERAGE`	Calculates the mean	`=AVERAGE(C2:C10)`
`COUNT`	Counts cells with numbers	`=COUNT(A1:A50)`
`MAX` / `MIN`	Returns the highest or lowest value	`=MAX(D2:D30)`
`IF`	Returns one value if true, another if false	`=IF(A1>100,"High","Low")`
`VLOOKUP`	Finds a value in a table	`=VLOOKUP(E2,A2:C50,2,FALSE)`
`COUNTA`	Counts non-empty cells	`=COUNTA(A1:A100)`

When you start typing a function name, Excel's AutoComplete will suggest matching functions — useful for discovering options you didn't know existed.

Cell References: Relative vs. Absolute

This is where many beginners hit a wall, so it's worth being clear. ⚠️

Relative references (like A1) adjust automatically when you copy a formula to another cell. If =A1+B1 is in row 2 and you copy it to row 3, it becomes =A2+B2. That's usually what you want.

Absolute references (like $A$1) stay fixed no matter where you copy the formula. You lock them by adding dollar signs. Use this when a formula always needs to reference one specific cell — like a tax rate or a fixed multiplier stored in a single location.

Mixed references ($A1 or A$1) lock either the column or the row, but not both. These are useful in more advanced spreadsheet layouts.

How Variables in Your Spreadsheet Affect Formula Choice

The right formula depends on several factors that vary by situation:

Data layout — whether your data runs in rows or columns changes which functions work cleanly
Data type — text, numbers, dates, and logical values each behave differently in formulas
Spreadsheet version — Excel 365 includes newer functions like XLOOKUP, FILTER, and LET that aren't available in older versions like Excel 2016 or 2019
Formula complexity — a simple SUM works fine for totals, but nested IF statements or array formulas require more care and are easier to break
Collaboration needs — if others will edit your sheet, overly complex formulas can create maintenance problems

Error Messages You'll Encounter

Excel is specific about what went wrong when a formula fails:

Error	Common Cause
`#DIV/0!`	Dividing by zero or an empty cell
`#VALUE!`	Wrong data type in an argument
`#REF!`	A cell reference points to a deleted cell
`#NAME?`	Function name is misspelled
`#N/A`	A lookup couldn't find a match

Seeing an error isn't a sign something is broken beyond repair — it's Excel telling you exactly where to look.

The Range of Complexity Across Users 💡

Someone summing a weekly expense list needs almost nothing beyond =SUM() and basic arithmetic. A financial analyst building a dynamic model might combine INDEX, MATCH, IF, and array logic in a single formula. A data analyst working with large datasets may lean on XLOOKUP or Power Query entirely.

The building blocks covered here are the same across all those scenarios. What differs is how deeply they're combined, how the data is structured, and what version of Excel is in play. Your starting point — and how far you need to go — is entirely shaped by what your spreadsheet actually needs to do.