Understanding Excel Formulas: The Foundation of Excel

Beginner18 min readUpdated: July 2024

1. What Are Excel Formulas?

Excel formulas are expressions that calculate values in your spreadsheet. They're the heart of Excel's functionality, allowing you to:

  • Perform mathematical calculations
  • Analyze and manipulate data
  • Automate repetitive tasks
  • Create dynamic relationships between cells

Key Rule: All Formulas Start with =

Every formula in Excel must begin with an equals sign (=). This tells Excel that you want to calculate something, not just display text.

Formula vs. Function

  • Formula: Any expression that starts with = (e.g., =A1+B1)
  • Function: Pre-built formulas with names (e.g., =SUM(A1:A10))

2. Creating Your First Formula

Basic Steps

  1. Click on a cell where you want the result to appear
  2. Type = to start your formula
  3. Enter your calculation
    • Numbers: =5+3
    • Cell references: =A1+B1
    • Mix of both: =A1+10
  4. Press Enter to calculate

Simple Examples

=10+5

Result: 15 (simple addition)

=100*0.15

Result: 15 (calculating 15% of 100)

=A1+A2+A3

Adds values from three cells

3. Excel Operators

Operators are symbols that tell Excel what type of calculation to perform.

Arithmetic Operators

OperatorNameExampleResult
+Addition=10+515
-Subtraction=10-55
*Multiplication=10*550
/Division=10/52
^Exponentiation=10^2100
%Percentage=50%0.5

Comparison Operators

= Equal to
<> Not equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to

4. Understanding Cell References

Cell references allow formulas to use values from other cells, making your spreadsheets dynamic and powerful.

Types of References

Single Cell Reference

=A1

References one specific cell

Range Reference

=SUM(A1:A10)

References multiple cells (A1 through A10)

Multiple Ranges

=SUM(A1:A10,C1:C10)

References non-adjacent ranges

Pro Tip: Click to Select

Instead of typing cell references, click on cells while creating formulas. Excel will automatically insert the correct reference!

5. Order of Operations (PEMDAS)

Excel follows the standard mathematical order of operations, often remembered as PEMDAS:

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

Examples

=10+5*2

Result: 20 (multiplication happens first: 5*2=10, then 10+10=20)

=(10+5)*2

Result: 30 (parentheses first: 10+5=15, then 15*2=30)

=2^3*4+1

Result: 33 (exponent first: 2^3=8, then 8*4=32, then 32+1=33)

Remember: When in doubt, use parentheses!

They make your formulas clearer and ensure correct calculation order.

Try It: Practice Order of Operations

Enter formulas in the yellow cells to see how order of operations affects results:

Loading simulator...

6. Common Formula Errors

Understanding error messages helps you quickly fix formula problems:

#DIV/0!

Division by zero

=10/0

#VALUE!

Wrong type of argument or operand

=10+"text"

#REF!

Invalid cell reference

Deleted cell referenced

#NAME?

Excel doesn't recognize text in formula

=SUMM(A1:A10)

#N/A

Value not available

VLOOKUP can't find match

#NUM!

Invalid numeric value

=SQRT(-1)

7. Formula Best Practices

✓

Keep formulas simple

Break complex calculations into multiple cells

✓

Use cell references instead of hard-coded numbers

Makes updates easier and formulas more flexible

✓

Document complex formulas

Add comments or use descriptive cell labels

✓

Test your formulas

Use known values to verify results

✓

Use parentheses for clarity

Even when not strictly necessary

Practice Exercise: Build a Simple Budget

Create a personal budget using formulas:

  1. Create headers:
    • A1: "Income"
    • A3: "Expenses"
    • A10: "Total Expenses"
    • A11: "Remaining"
  2. Enter income: B1: 3000
  3. Enter expenses:
    • A4: "Rent", B4: 1000
    • A5: "Food", B5: 400
    • A6: "Transport", B6: 200
    • A7: "Utilities", B7: 150
    • A8: "Other", B8: 300
  4. Create formulas:
    • B10: =B4+B5+B6+B7+B8 (or =SUM(B4:B8))
    • B11: =B1-B10

Try changing any expense amount - watch how the totals update automatically!

Quick Reference Card

Essential Formula Tips

  • 📌 All formulas start with =
  • 📌 Click cells to add references
  • 📌 Press F2 to edit a formula
  • 📌 Press Esc to cancel editing
  • 📌 Use Tab or Enter to confirm
  • 📌 Double-click a cell to see/edit its formula

Ready for the Next Step?

Continue your Excel journey with: Excel Data Entry Best Practices

Next Tutorial
Previous
Excel Keyboard Shortcuts Guide
3 of 25
Next
Excel Data Entry Best Practices