How to Create Formulas in Excel: A Complete Beginner's Guide
Excel formulas are the engine behind every spreadsheet. Whether you're adding up expenses, calculating averages, or building complex financial models, formulas are what separate a static table from a genuinely useful tool. Here's exactly how they work — and what shapes how well they work for you.
What Is an Excel Formula?
A formula in Excel is an instruction that tells the program to perform a calculation or operation on data in your spreadsheet. Every formula starts with an equals sign (=). Without it, Excel treats your entry as plain text.
A basic formula looks like this:
That tells Excel: take the value in cell A1, add the value in B1, and display the result.
Formulas can reference individual cells, ranges of cells, fixed numbers, or a combination of all three.
The Anatomy of an Excel Formula
Understanding the building blocks helps you write formulas confidently:
| Component | What It Does | Example |
|---|---|---|
| = (equals sign) | Signals the start of a formula | = |
| Cell reference | Points to a specific cell's value | A1, B3 |
| Operator | Defines the math operation | +, -, *, / |
| Function | A built-in shortcut for common calculations | SUM, AVERAGE, IF |
| Arguments | The inputs a function needs | (A1:A10) |
| Constants | Fixed numbers typed directly | 100, 0.05 |
A more advanced formula might combine several of these:
This adds all values from A1 to A10, then multiplies the result by 0.9.
How to Enter a Formula in Excel 📝
- Click the cell where you want the result to appear.
- Type an equals sign (=) to start.
- Enter your formula — either type cell references manually or click the cells you want to include.
- Press Enter to confirm. The result appears in the cell; the formula appears in the formula bar at the top.
To edit a formula later, click the cell and modify it in the formula bar, or press F2 to enter edit mode directly in the cell.
Using Excel's Built-In Functions
Functions are pre-built formulas for common tasks. Instead of writing =A1+A2+A3+A4+A5, you write:
The colon (:) means "from A1 through A5" — a range.
Some of the most widely used functions include:
| Function | Purpose | Example |
|---|---|---|
| SUM | Adds values | =SUM(B2:B20) |
| AVERAGE | Calculates the mean | =AVERAGE(C2:C10) |
| COUNT | Counts cells with numbers | =COUNT(D2:D50) |
| MAX / MIN | Returns highest or lowest value | =MAX(A1:A10) |
| IF | Returns one value or another based on a condition | =IF(A1>100,"Yes","No") |
| VLOOKUP | Searches a table for a matching value | =VLOOKUP(E2,A:B,2,FALSE) |
Excel also offers AutoComplete — start typing a function name and Excel suggests matches. This is useful when you know roughly what you need but can't remember the exact syntax.
Relative vs. Absolute Cell References
This distinction catches many users off guard. By default, cell references are relative — when you copy a formula to another cell, the references shift automatically.
For example, if =A1+B1 is in cell C1 and you copy it to C2, it becomes =A2+B2.
When you need a reference to stay fixed — say, a tax rate in cell F1 that every formula should always point to — you use an absolute reference by adding dollar signs:
Now when you copy this formula down, the $F$1 part never changes. You can also lock just the row (F$1) or just the column ($F1) depending on how your data is laid out.
Common Formula Errors and What They Mean
Excel displays error codes when something goes wrong:
| Error | Typical Cause |
|---|---|
| #DIV/0! | Formula is dividing by zero or an empty cell |
| #VALUE! | Wrong data type (e.g., text where a number is expected) |
| #REF! | A referenced cell has been deleted |
| #NAME? | Excel doesn't recognize a function or range name |
| #N/A | A lookup function can't find a match |
Understanding these codes speeds up troubleshooting significantly. 🔍
Factors That Affect How You'll Build Formulas
Knowing the mechanics is one thing — applying them effectively depends on several variables specific to your situation:
- Excel version: Features like XLOOKUP, IFS, and dynamic array functions (such as FILTER and UNIQUE) are only available in Excel 2019, Microsoft 365, and later. Older versions require workarounds.
- Operating system: Excel for Mac and Excel for Windows share most functionality, but some keyboard shortcuts and add-in behaviors differ.
- Spreadsheet complexity: A simple budget tracker uses basic arithmetic and SUM. A financial model or data analysis project may require nested IFs, array formulas, or Power Query.
- Data structure: Whether your data is in a flat table, a named range, or an Excel Table (formatted with Ctrl+T) affects which formulas and references work most cleanly.
- Skill level: Absolute beginners benefit most from mastering SUM, AVERAGE, and IF first before moving into VLOOKUP or nested formulas.
Nested Formulas and Combining Functions
Formulas can live inside other formulas. A nested formula passes the result of one function as an argument to another:
This calculates the average first, then uses the IF function to return a label based on the result. Nesting is powerful but can become difficult to read — Excel's Evaluate Formula tool (under Formulas > Formula Auditing) lets you step through complex formulas one calculation at a time.
One Thing Worth Noting Before You Build
The formulas covered here work across most versions of Excel, but the best approach for any specific task — whether that's a simple SUM or a multi-layered lookup — depends on how your data is organized, what version of Excel you're running, and what you actually need the spreadsheet to do. Two people using the same function can end up with very different results based on those details alone.