Excel has hundreds of functions, but a small group does most of the work in analysis. Learn these well and you will handle the majority of spreadsheet tasks you are given.
Examples refer to a sales table for a fictional stationery distributor, InkWell Supplies, stored as an Excel Table called Sales with columns Date, Region, Rep, Product, Qty and Amount.
Lookups
1. XLOOKUP
=XLOOKUP(A2, Products[Code], Products[Price], "Not found")Looks up a value and returns the matching item, in any direction, with a built-in "not found" result.
2. INDEX + MATCH
=INDEX(Products[Price], MATCH(A2, Products[Code], 0))Works in every Excel version and never breaks when columns are inserted. The 0 means exact match.
3. VLOOKUP
=VLOOKUP(A2, Products, 3, FALSE)Still common in older files. Always use FALSE for exact match.
Conditional totals
4. SUMIFS
=SUMIFS(Sales[Amount], Sales[Region], "North", Sales[Product], "Notebook")5. COUNTIFS
=COUNTIFS(Sales[Rep], "Kavya", Sales[Amount], ">10000")6. AVERAGEIFS
=AVERAGEIFS(Sales[Amount], Sales[Region], "South")Logic
7. IF and IFS
=IF([@Amount]>=10000, "Large", "Regular")
=IFS([@Amount]>=50000, "Key", [@Amount]>=10000, "Large", TRUE, "Regular")8. IFERROR
=IFERROR([@Amount]/[@Qty], 0)Use it to handle expected errors, but do not hide unexpected ones.
9. AND / OR inside IF
=IF(AND([@Region]="West", [@Qty]>100), "Bulk West", "")Text
10. TRIM, PROPER, UPPER
Remove extra spaces and standardise case: =PROPER(TRIM(B2)).
11. LEFT, RIGHT, MID, LEN, FIND
=LEFT(A2, FIND("-", A2) - 1)Extracts the part of a code before the dash.
12. TEXTJOIN and TEXT
=TEXTJOIN(" ", TRUE, B2, C2)
=TEXT([@Date], "mmm-yyyy")Dates
13. EOMONTH, NETWORKDAYS, DATEDIF
=EOMONTH([@Date], 0) last day of the month
=NETWORKDAYS(B2, C2) working days between two dates
=DATEDIF(B2, TODAY(), "m") full months since a dateDynamic arrays (newer Excel)
14. UNIQUE and SORT
=SORT(UNIQUE(Sales[Region]))A clean list of regions that updates itself.
15. FILTER
=FILTER(Sales, Sales[Amount]>50000, "None")Returns all matching rows, like a live filter.
Habits that prevent mistakes
- Convert data ranges to Excel Tables (Ctrl + T) so formulas extend automatically and references are readable.
- Use absolute references (
$A$1) for fixed cells such as a tax rate. - Keep raw data on one sheet and calculations on another.
- Check totals against a pivot table to catch formula errors.
From formulas to SQL
Each of these has a SQL equivalent: lookups are joins, SUMIFS is SUM with CASE or WHERE, IF is CASE WHEN. Read Excel to SQL and pandas and then practise CASE WHEN to carry your Excel thinking into SQL.
Practise what you just read
Reading helps; writing the code yourself is what sticks. Start here: