Learn All Excel Functions for Free — A–Z Function Dictionary, Examples, and Practice Sheet Guide

This guide helps you learn how to use Excel functions for free in one place. It provides reproducible practice steps without downloads, including dynamic arrays and the latest functions.

Quick Fix — 10-Minute Setup for Using Excel Functions for Free

1. Create a Practice sheet, then create a tbSales table (headers: Date, Item, Qty, UnitPrice, Category, Note).
2. In the Amount column (column G), enter =[@Qty]*[@UnitPrice].
3. Enter a lookup value in J2, then enter =XLOOKUP(J2, tbSales[Item], tbSales[UnitPrice], "N/A") in K2.
4. Enter =SUM(FILTER(tbSales[Amount], tbSales[Category]="IT")) in M2.
5. Enter =IFERROR(K2, 0) in N2 to prevent errors.

Excel Function Categories at a Glance

Logical and Information

IF, IFS, SWITCH, AND, OR, NOT, IFERROR, ISBLANK, ISTEXT, ISNUMBER …

Text and Date/Time

TEXTSPLIT, TEXTBEFORE/AFTER, TEXTJOIN, LEFT/RIGHT/MID, LEN, TRIM, DATE, EOMONTH, NETWORKDAYS …

Lookup and Reference

XLOOKUP, XMATCH, INDEX, MATCH (including legacy VLOOKUP/HLOOKUP)

Math, Statistics, and Arrays

SUM/IF/S, AVERAGEIF/S, COUNTIF/S, SUBTOTAL, SORT/BY, UNIQUE, TAKE, DROP, SEQUENCE, SCAN, REDUCE …

Data Wrangling

FILTER, CHOOSECOLS/ROWS, WRAPROWS/COLS …

30 Essential Functions for Work — Examples and Combination Recipes

Formula examples in this article (based on the tbSales table).

=IF([@Amount]>=50, "Large", "Small")
=IFERROR([@Amount]/[@Qty], 0)
=XLOOKUP(J2, tbSales[Item], tbSales[UnitPrice], "N/A")
=INDEX($B$2:$B$6, XMATCH(G2, $A$2:$A$6, -1))
=FILTER(tbSales, tbSales[Category]="IT")
=SORTBY(tbSales, tbSales[Amount], -1)
=UNIQUE(tbSales[Category])
=TAKE(SORTBY(tbSales, tbSales[Amount], -1), 3)
=TEXTSPLIT([@Note], " ")
=EOMONTH([@Date], 0)
=NETWORKDAYS([@Date], TODAY())
=SUMIF(tbSales[Category], "IT", tbSales[Amount])
=COUNTIF(tbSales[Item], "USB")
=SUBTOTAL(9, tbSales[Amount])
=LET(f, FILTER(tbSales[Amount], tbSales[Category]="IT"), SUM(f))
=SCAN(0, tbSales[Amount], LAMBDA(a,v, a+v))

Upgrade to Modern Functions — XLOOKUP, FILTER, LET, and LAMBDA

Move beyond VLOOKUP limitations by handling exact matches, approximate matches, and search direction with XLOOKUP. Build lightweight dashboards with FILTER, UNIQUE, and SORT. Organizing formulas with LET and LAMBDA makes them easier to maintain.

Free Practice Routine (Text-Based Training Without Sample Files)

• Day 1: Split text; Day 2: Lookup and reference; Day 3: Conditional sums; Day 4: Dynamic arrays; Day 5: LET/LAMBDA.
• Check: reproducibility, error handling, and use of table-name references.

Alternative Methods, Notes, and Checklist

• Create a table first and reference columns by name.
• Standardize data types (numbers and dates).
• For approximate lookups, ensure sorting and specify match_mode (-1/1).
• Clear spill conflicts, and apply IFERROR last.

Troubleshooting

Conclusion & References

• Complete Guide to XLOOKUP with Multiple Criteria
• IF Function Multiple Criteria Guide
• Calculate Range Totals with SUMIFS
• Quick Excel Error Fixes
• Organize Tables with TEXTSPLIT

Official documentation: Microsoft Excel Support · Microsoft Learn (Excel)