Microsoft Excel is one of the most powerful tools in the business world, and at its core, formulas are what make it so effective. Whether you’re tracking expenses, analyzing sales data, or building complex dashboards, formulas help you automate calculations and save hours of work.
In this complete guide, we’ll cover everything you need to know about Excel formulas – from basic arithmetic to advanced functions – with clear explanations and examples.
1️⃣ What Are Excel Formulas?
An Excel formula is an expression that performs calculations using cell values, operators, and functions. Every formula in Excel begins with an equals sign (=).
Example:
= A1 + B1
This formula adds the values in cell A1 and cell B1.
2️⃣ Basic Excel Formula Syntax
Here’s how a formula is structured:
= FunctionName(argument1, argument2, …)
🔹 Equals sign (=) – Tells Excel you’re entering a formula.
🔹 Function Name – The specific calculation you want to perform.
🔹 Arguments – The inputs for the formula, inside parentheses.
📌 Example:
=SUM(A1:A10)
This adds up all values from A1 to A10.
3️⃣ Basic Excel Formulas for Beginners 🏁
If you’re just starting, these are the essential formulas to know:
A. Addition ➕
= A1 + B1
B. Subtraction ➖
= A1 - B1
C. Multiplication ✖️
= A1 * B1
D. Division ➗
= A1 / B1
E. SUM (Add a Range) 📊
=SUM(A1:A10)
F. AVERAGE (Find Mean) ⚖️
=AVERAGE(A1:A10)
4️⃣ Commonly Used Excel Functions 📌
1. IF Function (Logic) 🤔
The IF function checks a condition and returns one value if it’s true, another if it’s false.
Syntax:
=IF(condition, value_if_true, value_if_false)
Example:
=IF(A1>50, "Pass", "Fail")
If A1 is greater than 50, return “Pass,” otherwise “Fail.”
2. VLOOKUP (Vertical Lookup) 🔍
Searches for a value in the first column and returns a value from another column.
Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Example:
=VLOOKUP(101, A2:C10, 2, FALSE)
Looks for 101 in column A and returns the value from column B.
3. HLOOKUP (Horizontal Lookup) 📏
Similar to VLOOKUP, but searches in the first row.
Syntax:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
4. INDEX & MATCH Combo 🔗
More flexible than VLOOKUP.
Example:
=INDEX(B2:B10, MATCH(105, A2:A10, 0))
Finds 105 in column A and returns corresponding value from column B.
5. CONCATENATE / CONCAT (Merge Text) 📝
=CONCAT(A1, " ", B1)
Combines first name and last name.
6. LEFT, RIGHT, MID (Extract Text) ✂️
- LEFT:
=LEFT(A1, 4)→ First 4 characters. - RIGHT:
=RIGHT(A1, 3)→ Last 3 characters. - MID:
=MID(A1, 3, 5)→ Extracts 5 characters starting from position 3.
7. LEN (Count Characters) 🔢
=LEN(A1)
8. TRIM (Remove Extra Spaces) ✂️
=TRIM(A1)
9. PROPER, UPPER, LOWER (Text Case) 🔠
- UPPER:
=UPPER(A1)→ ALL CAPS - LOWER:
=LOWER(A1)→ all lowercase - PROPER:
=PROPER(A1)→ First Letter Caps
5️⃣ Date & Time Formulas ⏳
TODAY() – Current Date 📅
=TODAY()
NOW() – Current Date & Time ⏰
=NOW()
DATEDIF – Difference Between Dates 📏
=DATEDIF(A1, B1, "d")
Returns days between two dates.
6️⃣ Financial Formulas 💰
PMT – Loan Payment Calculation
=PMT(rate, nper, pv)
FV – Future Value of Investment
=FV(rate, nper, pmt, [pv], [type])
7️⃣ Error Handling Formulas 🚫
IFERROR – Handle Errors
=IFERROR(A1/B1, "Error")
If an error occurs, return “Error.”
8️⃣ Lookup & Reference Formulas 🔎
- CHOOSE: Picks a value from a list.
=CHOOSE(2, "Apple", "Banana", "Cherry") → Banana
- HYPERLINK: Creates a clickable link.
=HYPERLINK("https://vijayreddy.in", "Visit Site")
9️⃣ Dynamic Array Formulas (Excel 365 / 2021) ⚡
- FILTER: Filters data based on criteria.
=FILTER(A2:B10, B2:B10>50)
- UNIQUE: Returns unique values.
=UNIQUE(A2:A10)
- SORT: Sorts a range.
=SORT(A2:A10, 1, TRUE)
🔟 Tips for Working with Excel Formulas 🚀
- Use absolute references ($A$1) to lock cell positions.
- Break complex formulas into smaller parts for troubleshooting.
- Use named ranges for clarity.
- Learn keyboard shortcuts to speed up work.
- Keep formulas simple for better performance.
📊 Example: Sales Analysis Formula Workflow
Imagine you have this data:
| Product | Units Sold | Price |
|---|---|---|
| A | 50 | 20 |
| B | 30 | 15 |
| C | 40 | 25 |
Step 1: Calculate revenue.
= B2 * C2
Step 2: Total revenue.
=SUM(D2:D4)
Step 3: Average price.
=AVERAGE(C2:C4)
Final Thoughts 💡
Excel formulas are more than just math – they’re a productivity powerhouse. By mastering them, you can work faster, make fewer mistakes, and create powerful data-driven reports.
📌 Key Takeaway: Start with basics like SUM and IF, then move on to advanced combinations like INDEX + MATCH and dynamic arrays for next-level efficiency.
