The Complete Guide to Writing Excel Formulas from Scratch

If you type 50 10 into a cell, the spreadsheet will simply display the plain text "50 10". But if you type =50 * 10, the equals sign acts as a master trigger that commands the engine to evaluate the expression and return 500.

The Anatomy of a Modern Formula

Every spreadsheet formula consists of four core building blocks:

=FUNCTION(argument1, [argument2], ...)

1. 1The Equals Sign (=): Starts every formula without exception.
2. 2The Function Name: The built-in command telling the engine what calculation to run (SUM, AVERAGE, IF, MAX).
3. 3Parentheses ( ): Enclose the arguments. Every opening parenthesis ( must have a matching closing parenthesis ).
4. 4Arguments: The inputs passed into the function — which can be cell coordinates (B2), range blocks (B2:B10), literal numbers (100), or text in quotes ("Approved").

Core Arithmetic Operators

You can also write direct math formulas using standard keyboard symbols:

• Addition (+): =A2 + B2
• Subtraction (-): =A2 - B2
• Multiplication (): =A2 B2 (use asterisk)
• Division (/): =A2 / B2 (use forward slash)
• Exponentiation (^): =A2 ^ 2 (raise to a power)

The single most important concept in spreadsheet modeling is understanding the difference between relative and absolute cell references.

1. Relative References (The Default)

When you write =B2 C2 in row 2 and drag the formula down to row 3, the spreadsheet naturally shifts the formula to =B3 C3. This is called a relative reference: the formula doesn't remember the exact cells B2 and C2 — it remembers "multiply the cell one column to the left by the cell two columns to the left".

2. Absolute References ($ Signs Lock Cells in Place)

What happens if you have a single Tax Rate (8%) stored in cell $F$1, and you want to calculate tax for 100 different products in column E?

If you write =E2 F1 and drag it down to row 3, the formula shifts to =E3 F2. But cell F2 is empty, so your tax returns 0!

To lock cell F1 permanently so it never moves when dragged, add dollar signs ($):

=E2 * $F$1

• When dragged down to row 3, it becomes: =E3 * $F$1
• When dragged down to row 50, it becomes: =E50 * $F$1

E2 moves freely down the rows, while $F$1 stays firmly anchored in place.

Here are the core formula functions that power over 90% of business spreadsheets:

1. 1SUM & SUMIF (Adding Numbers & Conditional Totals):

=SUM(E2:E10)                     // Adds all numbers in range E2 to E10
=SUMIF(C2:C10, "Electronics", E2:E10) // Adds only rows where category is Electronics

1. 2AVERAGE & AVERAGEIF (Finding Means):

=AVERAGE(D2:D100)               // Computes the average price or score
=AVERAGEIF(B2:B100, ">0", D2:D100) // Averages only positive sales

1. 3COUNT, COUNTA & COUNTIF (Counting Records):

=COUNT(B2:B50)                  // Counts how many cells contain numbers
=COUNTA(A2:A50)                 // Counts all non-empty cells (text or numbers)
=COUNTIF(E2:E50, ">=1000")       // Counts transactions worth $1,000 or more

1. 4MAX & MIN (Finding Extremes):

=MAX(E2:E50)                    // Finds the highest sale or highest score
=MIN(E2:E50)                    // Finds the lowest cost or lowest temperature

1. 5ROUND (Controlling Decimal Precision):

=ROUND(E2 * 0.0825, 2)          // Rounds tax to exactly 2 decimal currency places

Spreadsheets become truly intelligent when you introduce conditional logic. The IF function evaluates a question (like "Is score >= 75?") and returns one result if TRUE, and a different result if FALSE.

1. Basic IF Syntax

=IF(condition, value_if_true, value_if_false)

Example from our Student Gradebook Template:

=IF(D2 >= 75, "Passed", "Need Review")

If student score D2 is 85, the cell displays Passed. If D2 is 60, it automatically displays Need Review.

2. Combining Logic with AND & OR

What if a student must pass both an exam AND have high attendance?

=IF(AND(D2 >= 75, E2 >= 0.80), "Honors", "Standard")

AND requires both conditions to be true.

3. Modern Multi-Tier Conditions with IFS

Instead of writing messy nested IF(IF(IF())) statements, use IFS:

=IFS(D2 >= 90, "A", D2 >= 80, "B", D2 >= 70, "C", TRUE, "F")

IFS checks conditions in order and returns the matching letter grade cleanly.

Spreadsheets follow standard mathematical order of operations (PEMDAS):

1. 1Parentheses ( )
2. 2Exponents ^
3. 3Multiplication * and Division /
4. 4Addition + and Subtraction -

The Importance of Parentheses in Financial Formulas

Look at how our Sales & Revenue Model calculates discounted revenue:

• Cell B2: Quantity (5)
• Cell C2: Unit Price ($1,400)
• Cell D2: Discount Rate (0.10 for 10%)

If you write:

=B2 * C2 * 1 - D2

The engine multiplies 5 1400 1 = 7000, and then subtracts 0.10 to get $6,999.90 — which is completely incorrect!

By adding parentheses around (1 - D2):

=B2 * C2 * (1 - D2)

The engine calculates the discount factor first (1 - 0.10 = 0.90), and then multiplies by price and quantity to yield the correct net revenue of $6,300.00.

When a formula doesn't calculate as expected, our spreadsheet engine returns a specific error code. Here is how to diagnose and resolve the top 4 formula bugs:

1. 1The #DIV/0! Error (Division by Zero):

• Cause: Dividing a number by zero or pointing to an empty cell.
• Fix: Shield the formula using =IF(B2 = 0, 0, A2 / B2) or wrap in =IFERROR(A2 / B2, 0).

1. 2The #VALUE! Error (Wrong Data Type):

• Cause: Trying to perform mathematical arithmetic on text strings (e.g. ="$50" * 2).
• Fix: Strip non-numeric characters using =VALUE(SUBSTITUTE(A2, "$", "")).

1. 3The #NAME? Error (Spelling Typo):

• Cause: Misspelling a function name (e.g. writing =SUMM(A1:A5)) or writing unquoted text inside an IF formula (e.g. =IF(A1=1, Passed, Failed) instead of "Passed").
• Fix: Check function spelling or wrap text values in double quotes.

1. 4Circular Reference Warning:

• Cause: A formula that references its own cell coordinate (e.g. writing =SUM(A1:A5) inside cell A5).
• Fix: Adjust your range to stop before the summary row (e.g. =SUM(A1:A4) in cell A5).