Microsoft Excel is more than just rows and columns — it’s a powerful tool that can streamline data analysis, automate tasks, and support decision-making. Whether you're in admin, finance, MIS, HR, or operations, Excel skills are non-negotiable in today’s data-driven work environment.
But what really makes Excel stand out are its formulas — the logic-driven engines that make spreadsheets dynamic and insightful.
In this blog, we’ll break down 10 powerful Excel formulas that every professional should know, along with examples and practical uses.
1. VLOOKUP() – Find Data Quickly
Formula:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Use:
VLOOKUP is used to search for a value in the first column of a table and return a value in the same row from a different column.
Example:
You want to find the department name for employee ID 105.
=VLOOKUP(105, A2:C100, 3, FALSE)
Real-life Use:
Matching employee names with IDs, retrieving product prices, or getting vendor details from a master sheet.
2. INDEX() + MATCH() – Better Than VLOOKUP
Formula:
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
Use:
A more flexible alternative to VLOOKUP, especially when the lookup value is not in the first column.
Example:
=INDEX(C2:C100, MATCH("Amit", A2:A100, 0))
Real-life Use:
Look up names, addresses, or prices where VLOOKUP fails.
3. IF() – Conditional Logic
Formula:
=IF(logical_test, value_if_true, value_if_false)
Use:
IF checks a condition and returns one value if TRUE and another if FALSE.
Example:
=IF(B2>100, "Approved", "Pending")
Real-life Use:
Approvals, result status, attendance checks, bonus eligibility, etc.
4. SUMIF() – Conditional Sum
Formula:
=SUMIF(range, criteria, [sum_range])
Use:
Adds values based on a condition.
Example:
=SUMIF(A2:A100, "Delhi", B2:B100)
(Sums values in column B where column A is "Delhi")
Real-life Use:
Calculate total sales by region, expenses by department, or attendance by category.
5. COUNTIF() – Count with Criteria
Formula:
=COUNTIF(range, criteria)
Use:
Counts how many times a specific value appears.
Example:
=COUNTIF(A2:A100, "Late")
Real-life Use:
Track how many employees were late, how many leave requests are "Approved", etc.
6. TEXT() – Format Numbers/Dates
Formula:
=TEXT(value, format_text)
Use:
Converts a number or date to a specific format.
Example:
=TEXT(TODAY(), "dd-mmm-yyyy")
Outputs today’s date like: 09-May-2025
Real-life Use:
Generate readable reports, export uniform date formats, or label sheets.
7. CONCATENATE() / TEXTJOIN() – Merge Text
Formula:
=CONCATENATE(A2, " ", B2) or
=TEXTJOIN(" ", TRUE, A2, B2, C2)
Use:
Joins multiple cells into one text string.
Example:
Combine first name and last name into full name.
Real-life Use:
Creating IDs, merging address lines, or generating email IDs.
8. LEFT(), RIGHT(), MID() – Text Extraction
Formula Examples:
-
=LEFT(A2, 5)– gets first 5 characters -
=RIGHT(A2, 3)– gets last 3 characters -
=MID(A2, 2, 4)– gets 4 characters starting from position 2
Real-life Use:
Extracting codes, initials, last 4 digits of phone numbers, etc.
9. NOW() & TODAY() – Auto Timestamps
Formula:
-
=TODAY()– Current date -
=NOW()– Current date and time
Real-life Use:
Auto-date generation in reports, logs, and daily MIS updates.
10. IFERROR() – Clean Up Errors
Formula:
=IFERROR(formula, value_if_error)
Use:
Displays a user-friendly message instead of an error.
Example:
=IFERROR(VLOOKUP(A2, Table1, 2, FALSE), "Not Found")
Real-life Use:
Keep dashboards clean and readable even if formulas break.