Chapter 3: Excel Formulas & Functions – Beginner’s Guide 2025

📘 Chapter 3: Working with Formulas & Functions

Excel’s true power lies in its ability to perform calculations and automate data analysis using formulas and functions. Whether you’re summing values, finding averages, or applying conditions, mastering formulas is essential for anyone working with data.

In this chapter, you’ll learn how to write formulas, use cell references, apply basic mathematical functions, and fix common formula errors.

🔣 What is a Formula in Excel?

A formula in Excel is an expression that calculates the value of a cell. All formulas begin with an equal sign (=). For example:

=A1 + B1 adds the values of cells A1 and B1.

🔗 Types of Cell References

• Relative Reference: A1 – changes when copied to another cell
• Absolute Reference: $A$1 – stays constant when copied
• Mixed Reference: $A1 or A$1 – only part remains fixed

🧮 Basic Arithmetic Operators

• + – Addition
• - – Subtraction
• * – Multiplication
• / – Division
• ^ – Exponent (Power)

Example: =B2*10% calculates 10% of the value in B2

🧠 Order of Operations (BODMAS)

Excel follows the BODMAS rule – Brackets, Orders, Division, Multiplication, Addition, and Subtraction.

Example: =(A1 + B1) * C1

🛠️ Commonly Used Functions

• SUM(range) – Adds numbers in a range
• AVERAGE(range) – Returns the mean
• MAX(range) – Finds the highest value
• MIN(range) – Finds the lowest value
• COUNT(range) – Counts numbers in a range
• COUNTA(range) – Counts non-blank cells
• ROUND(value, num_digits) – Rounds to desired decimal

💡 Tips While Using Functions

• Use = to start all formulas
• Use Ctrl + ` to view all formulas in the sheet
• Use function suggestions (auto-complete) while typing
• Check the Function Arguments dialog (Shift + F3) for help

❗ Error Handling in Excel

• #DIV/0! – Division by zero
• #VALUE! – Wrong data type in formula
• #NAME? – Misspelled function
• =IFERROR(A1/B1, "Error") – Handles errors gracefully

🎯 Real-Life Examples

• =SUM(B2:B10) – Add total marks of students
• =AVERAGE(C2:C6) – Calculate average sales
• =MAX(E2:E100) – Find top performer
• =ROUND(F2, 2) – Round value to 2 decimal places

📝 Practice Task

1. Create columns: Product, Price, Quantity
2. Calculate Total = Price * Quantity
3. Use SUM() to get the grand total
4. Find the highest quantity using MAX()
5. Use IFERROR() to catch divide errors