Excel Formulas

🔹 1. Introduction to Formulas

• What is a formula in Excel?

• Formula bar and how to enter a formula

• Using = to begin all formulas

• Understanding cell references (A1, B2, etc.)

• Order of operations (BODMAS in Excel)

________________________________________

🔹 2. Cell References

• Relative Reference (e.g., =A1+B1)

• Absolute Reference (e.g., =$A$1+$B$1)

• Mixed Reference (e.g., =A$1+$B2)

• Use of $ symbol in locking cells

________________________________________

🔹 3. Mathematical Functions

• SUM() – Add numbers

• SUBTOTAL(), PRODUCT(), POWER()

• ROUND(), ROUNDUP(), ROUNDDOWN()

• MOD() – Remainder after division

• ABS() – Absolute value

• RAND(), RANDBETWEEN()

________________________________________

🔹 4. Text Functions

• CONCAT() / TEXTJOIN() – Combine strings

• LEFT(), RIGHT(), MID()

• LEN() – Character count

• TRIM() – Remove extra spaces

• UPPER(), LOWER(), PROPER()

• FIND(), SEARCH(), REPLACE(), SUBSTITUTE()

________________________________________

🔹 5. Logical Functions

• IF() – Conditional statement

• AND(), OR(), NOT()

• Nested IF() formulas

• IFS() – Multiple conditions without nesting

• IFERROR(), IFNA()

________________________________________

🔹 6. Lookup & Reference Functions

• VLOOKUP() – Vertical lookup

• HLOOKUP() – Horizontal lookup

• XLOOKUP() – Advanced lookup (newer Excel)

• INDEX() + MATCH() – Powerful combination

• LOOKUP() – Basic alternative

________________________________________

🔹 7. Date & Time Functions

• TODAY(), NOW()

• DATE(), TIME()

• DAY(), MONTH(), YEAR()

• HOUR(), MINUTE(), SECOND()

• DATEDIF() – Difference between dates

• NETWORKDAYS(), WORKDAY()

________________________________________

🔹 8. Count & Statistical Functions

• COUNT(), COUNTA(), COUNTBLANK(), COUNTIF(), COUNTIFS()

• AVERAGE(), AVERAGEIF(), AVERAGEIFS()

• MAX(), MIN(), MEDIAN(), MODE()

• STDEV(), VAR()

________________________________________

🔹 9. Financial Functions

• PMT() – Loan payment calculation

• FV(), PV() – Future and present value

• NPV(), IRR(), RATE()

________________________________________

🔹 10. Array Formulas & Dynamic Arrays

• What is an array formula?

• Using {} (curly brackets) in legacy array formulas

• UNIQUE(), SORT(), FILTER() – Dynamic array functions

• SEQUENCE(), TRANSPOSE(), SINGLE()

________________________________________

🔹 11. Error Handling Functions

• ISERROR(), ISNUMBER(), ISBLANK()

• IFERROR() – Custom message for error

• ERROR.TYPE()

________________________________________

🔹 12. Useful Utilities with Formulas

• Conditional Formatting using formulas

• Data Validation using formulas

• Creating dynamic charts using formulas

• Formulas in Pivot Tables and Slicers

________________________________________

🔹 13. Practice Scenarios

• Marksheet creation with grade system using IF

• Attendance system using COUNTIF

• Expense calculator using SUMIF

• Age calculator using DATEDIF

• Employee database with VLOOKUP