10 Excel Formulas Every Office Job Needs

Almost every office job interview in Mumbai asks one question: "Do you know Excel?" Knowing how to type in cells is not enough. Recruiters want people who can calculate, find and summarise data quickly.

Here are the 10 formulas our Advanced Excel students use most often at work. Learn these well and you will handle most day-to-day office tasks with confidence.

1. SUM and AVERAGE

The basics, but used everywhere.

• =SUM(B2:B50) adds all values from B2 to B50.
• =AVERAGE(B2:B50) gives the average.

Tip: Select a column and press Alt + = to insert SUM instantly.

2. IF – make decisions

=IF(C2>=40,"Pass","Fail")

If the marks in C2 are 40 or more, Excel writes Pass, otherwise Fail. IF is used for targets, discounts, attendance, grading and much more.

3. COUNTIF – count with a condition

=COUNTIF(D2:D200,"Mumbai")

Counts how many rows have Mumbai in column D. Very useful for reports like "how many orders are pending" or "how many students are absent".

4. SUMIF and SUMIFS – add with conditions

• =SUMIF(A2:A500,"Kurla",C2:C500) adds the sales in column C only where the area is Kurla.
• =SUMIFS(C2:C500,A2:A500,"Kurla",B2:B500,"Oct") adds sales for Kurla and Oct together.

SUMIFS is one of the most asked formulas in MIS and accounts jobs.

5. VLOOKUP – find a value from another table

=VLOOKUP(F2,A2:C100,3,FALSE)

Looks for the value in F2 in the first column of A2:C100 and returns the matching value from the 3rd column. Always use FALSE at the end for an exact match.

6. XLOOKUP – the modern VLOOKUP

=XLOOKUP(F2,A2:A100,C2:C100,"Not found")

XLOOKUP can look left or right, does an exact match by default and lets you show your own message when nothing is found. It is available in Excel 2021, Excel 2024 and Microsoft 365. If your office uses an older version, stick with VLOOKUP.

7. IFERROR – hide ugly errors

=IFERROR(VLOOKUP(F2,A2:C100,3,FALSE),"Not found")

Instead of showing #N/A, your report shows a clean message. Wrap it around any formula that might fail.

8. TRIM – clean extra spaces

=TRIM(A2)

Data copied from software or websites often has extra spaces, which breaks VLOOKUP and COUNTIF. TRIM removes them.

9. TEXT – format dates and numbers

• =TEXT(A2,"dd-mmm-yyyy") turns a date into 12-Oct-2026.
• =TEXT(B2,"#,##0") shows 125000 as 125,000.

Handy when you join text and numbers in one cell.

10. TEXTJOIN – combine text

=TEXTJOIN(" ",TRUE,A2,B2)

Joins first name and last name with a space. It works in Excel 2019 and newer. In older versions use =A2&" "&B2.

Bonus: Pivot Table

Not a formula, but the fastest way to turn thousands of rows into a summary report. Select your data → Insert → PivotTable, then drag fields into Rows and Values. Most MIS roles expect you to know this.

How to practise

1. Download any sample sales or attendance sheet.
2. Try each formula above on it.
3. Build one small report using SUMIFS, XLOOKUP and a Pivot Table.

Want to learn these hands-on with a trainer? Our Advanced Excel course in Kurla West covers all of these formulas plus Pivot Tables, charts and MIS dashboards — with practice on real office-style data.