The Most Useful Excel Functions

Math and aggregation: SUM, AVERAGE, SUMIF

SUM adds a range of numbers — =SUM(A1:A10). AVERAGE returns the arithmetic mean of the same range.

SUMIF adds only the values that match a condition: =SUMIF(A:A, ">100", B:B) sums B where A is greater than 100. SUMIFS extends this to multiple conditions.

Logic: IF and IFS

IF returns one value when a test is true and another when it's false: =IF(A1>100, "High", "Low").

Nest IFs or use IFS for multiple branches: =IFS(A1>=90, "A", A1>=80, "B", A1>=70, "C", TRUE, "F").

Lookups: VLOOKUP, XLOOKUP, INDEX/MATCH

VLOOKUP searches the first column of a table and returns a value from another column. It's still everywhere in legacy workbooks.

XLOOKUP is the modern replacement — flexible direction, exact match by default, and a built-in not-found argument: =XLOOKUP(A2, B:B, C:C, "Not found").

INDEX/MATCH is the classic combo that worked before XLOOKUP and still has the best performance on huge ranges.

Counting: COUNTIF and COUNTIFS

COUNTIF counts cells that meet one condition: =COUNTIF(A:A, ">100"). COUNTIFS extends to multiple criteria across multiple columns.

Text: CONCAT and TEXTJOIN

CONCAT joins strings end-to-end: =CONCAT(A1, " ", B1).

TEXTJOIN is far more useful for lists — pass a delimiter and let it skip empty cells: =TEXTJOIN(", ", TRUE, A1:A20).

Where to go next

Once these are second nature, look at LET (cleaner formulas), LAMBDA (reusable functions), FILTER and SORT (dynamic arrays), and the Power Query editor for repeat data cleaning.