Essential formulas

Ten or twelve formulas cover most of the work an analyst does in Excel. Learn these properly and the rest of the function list becomes easy to pick up.

The workhorses

Formula	Use
=SUM(B2:B100)	Total
=AVERAGE(B2:B100)	Mean
=IF(C2>=50,"Pass","Fail")	One condition
=COUNTIFS(A:A,"Delhi",C:C,">50000")	Count rows matching several conditions
=SUMIFS(C:C,A:A,"Pune")	Sum with conditions
=TRIM(PROPER(A2))	Clean names
=TEXT(D2,"dd-mmm-yyyy")	Format a date as text
=ROUND(E2,2)	Round to two decimals

Conditional aggregation is the real skill

SUMIFS and COUNTIFS take the range to sum first, then pairs of criteria range and criteria. AVERAGEIFS and MAXIFS follow the same pattern.

=SUMIFS($E$2:$E$5000, $B$2:$B$5000, "Pune", $A$2:$A$5000, ">="&DATE(2026,4,1))
=COUNTIFS($B$2:$B$5000, "Delhi", $E$2:$E$5000, ">50000")
=AVERAGEIFS($E$2:$E$5000, $C$2:$C$5000, "Saree")

Notice ">="&DATE(2026,4,1): the operator is text, joined with & to a real value. Typing ">=01-04-2026" works only by luck, and depends on the machine's date settings.

Absolute vs relative references

E2 shifts when you copy the formula; $E$2 never moves; $E2 locks the column only; E$2 locks the row. Press F4 to cycle through them. Lock your lookup ranges and criteria cells, keep the row reference relative.

=SUMIFS(Sales!$E:$E, Sales!$B:$B, $A2, Sales!$C:$C, B$1)

That single formula, copied across a small grid of cities and product categories, builds a summary table.

Nested conditions without the pain

=IF(E2>=100000,"Platinum",IF(E2>=50000,"Gold",IF(E2>=20000,"Silver","Bronze")))
=IFS(E2>=100000,"Platinum", E2>=50000,"Gold", E2>=20000,"Silver", TRUE,"Bronze")

IFS (Excel 2019/365) reads far better than four nested IFs. Combine conditions with AND and OR: =IF(AND(B2="Delhi", E2>50000), "Priority", "Normal").

Text and date functions you will actually use

=TRIM(A2)                         strip extra spaces
=UPPER(LEFT(B2,2))                first two letters of a state code
=TEXTJOIN(", ",TRUE,A2:A5)        combine a list, skipping blanks
=LEFT(F2,2)&"-"&MID(F2,3,10)      rebuild a GSTIN display format
=EOMONTH(D2,0)                    last day of that month
=EDATE(D2,1)                      same day next month
=DATEDIF(D2,TODAY(),"m")          complete months since invoice date
=NETWORKDAYS(D2,E2)               working days between two dates

Catch errors before your manager does

=IFERROR(VLOOKUP(A2, Products, 3, FALSE), "Code not found")
=IFERROR(E2/D2, 0)

Wrap only the part that can fail, and return something meaningful. A sheet full of #N/A and #DIV/0! breaks every chart and pivot built on it.

Common mistakes

• Copying a formula down without locking the lookup range with $.
• Summing a column of numbers stored as text; the total quietly comes out as zero.
• Hard-coding values such as 0.18 inside twenty formulas instead of referencing one GST rate cell.
• Using IFERROR to hide a genuine mistake in the data rather than fixing it.