Skip to content
Tutorials Center
My learning

Topics

Lesson 93 of 93 ยท Practice and Review

Excel Cheat Sheet

A one-page Excel quick reference: formulas by task, a lookup chooser, text and date patterns, error meanings and shortcuts, with links to the full lessons.

A cheat sheet is not a way to learn Excel; it is a way to stop searching for the thing you learned last month. This page puts the formulas, patterns and shortcuts you reach for most on one scrollable page, grouped by the task you are trying to finish. Each section links to the lesson that explains the details and the traps.

Excel cannot run on this site, so everything here is read-only reference text. Two small live stand-ins in JavaScript, labelled as such, let you check the date and text patterns, which are the ones people most often mistype. Functions marked 365 need Excel for Microsoft 365 or Excel 2021 and later; older versions show #NAME?.

Find it by task

Totals SUM, SUMIFS, COUNTIFS.
Lookups XLOOKUP, INDEX, MATCH.
Text LEFT, TEXTSPLIT, TRIM.
Dates EOMONTH, NETWORKDAYS.

Scan the section, copy the pattern, adapt the ranges.

1 / 2

Totals, counts and averages

Task Formula
Sum a range =SUM(B2:B100)
Average, median, most common =AVERAGE(B2:B100), =MEDIAN(B2:B100), =MODE.SNGL(B2:B100)
Smallest, largest =MIN(B2:B100), =MAX(B2:B100)
nth largest or smallest =LARGE(B2:B100,3), =SMALL(B2:B100,3)
Count numbers / non-empty / empty =COUNT(B2:B100), =COUNTA(B2:B100), =COUNTBLANK(B2:B100)
Sum if one condition =SUMIF(A2:A100,"North",B2:B100)
Sum with several conditions =SUMIFS(B2:B100,A2:A100,"North",C2:C100,">=100")
Count with conditions =COUNTIFS(A2:A100,"North",C2:C100,"<>")
Average if =AVERAGEIFS(B2:B100,A2:A100,"North")
Weighted average =SUMPRODUCT(B2:B10,C2:C10)/SUM(C2:C10)
Rank =RANK.EQ(B2,$B$2:$B$100,0)
Percent of total =B2/SUM($B$2:$B$100)
Percent change =(C2-B2)/B2
Round to 2 decimals =ROUND(B2,2) (ROUNDUP, ROUNDDOWN, MROUND for other rules)

Criteria text used by SUMIF, COUNTIF and friends: "North" is equal to; ">100", "<=50" compare; "<>North" not equal; "<>" is not empty; "A*" starts with A; "*ing" ends with ing; "?at" is three characters ending in at; ">"&D1 uses a cell. Add ~ before * or ? to match the character itself. See conditional aggregates, SUM and statistics basics and SUMPRODUCT.

Logic

Task Formula
Simple choice =IF(B2>=50,"Pass","Fail")
Both conditions =IF(AND(B2>=50,C2="Yes"),"OK","No")
Either condition =IF(OR(B2="A",B2="B"),"Good","Check")
Many cases in order =IFS(B2>=90,"A",B2>=80,"B",TRUE,"C") (365)
One value against exact cases =SWITCH(A2,"N","North","S","South","Other") (365)
Pick by number =CHOOSE(2,"a","b","c") returns b
Fallback for any error =IFERROR(A2/B2,0)
Fallback for #N/A only =IFNA(XLOOKUP(...),"Missing")
Test for blank, number, text =ISBLANK(A2), =ISNUMBER(A2), =ISTEXT(A2)
Nested IF is getting long Use IFS, SWITCH or a lookup table

See IF, AND, OR and NOT and IFS and SWITCH.

Lookup chooser

You need to... Use Pattern
Find a value, any direction, Excel 2021 or 365 XLOOKUP =XLOOKUP(key,keys,results,"Not found")
Same thing in any version INDEX + MATCH =INDEX(results,MATCH(key,keys,0))
Quick lookup to the right in an old file VLOOKUP =VLOOKUP(key,table,3,FALSE)
Look across a row HLOOKUP or XLOOKUP =HLOOKUP(key,table,2,FALSE)
Band or bracket (tax, grade) Approximate match =XLOOKUP(score,limits,grades,,-1) or =VLOOKUP(score,bands,2,TRUE)
Two-way table (row and column) INDEX + two MATCH =INDEX(grid,MATCH(r,rows,0),MATCH(c,cols,0))
Several criteria, numeric result SUMIFS =SUMIFS(result,k1,a,k2,b)
Several matches returned FILTER (365) =FILTER(table,col="North","None")
Position of a value MATCH or XMATCH =MATCH(key,keys,0)
Shift a reference by rows and columns OFFSET (volatile), INDEX prefer INDEX

Rules of thumb: always ask for an exact match unless you are building bands; give VLOOKUP the table with the key in the first column; and compare data types, because the text "101" never equals the number 101. See VLOOKUP, INDEX and MATCH, XLOOKUP and lookups with multiple criteria.

Text patterns

Task Formula
First / last / middle characters =LEFT(A2,3), =RIGHT(A2,4), =MID(A2,5,2)
Length =LEN(A2)
Position of text =FIND("@",A2) (case-sensitive), =SEARCH("@",A2) (case-insensitive, wildcards)
Clean spaces =TRIM(A2); also =CLEAN(A2) for non-printing characters
Change case =UPPER(A2), =LOWER(A2), =PROPER(A2)
Replace by content / position =SUBSTITUTE(A2,"-",""), =REPLACE(A2,1,3,"XXX")
Join =A2&" "&B2, =TEXTJOIN(", ",TRUE,A2:A6), =CONCAT(A2:B2)
Split (365) =TEXTSPLIT(A2,","), =TEXTBEFORE(A2,"@"), =TEXTAFTER(A2,"@")
Pad with zeros =TEXT(A2,"00000")
Show a number as a date or money =TEXT(A2,"yyyy-mm-dd"), =TEXT(A2,"#,##0.00")
Text to number =VALUE(A2) or =--A2
Repeat text =REPT("*",5)

See text functions, joining and splitting text and TEXT, VALUE and number-as-text problems.

Date patterns

Dates are serial numbers (day 1 is 1 January 1900) and times are fractions of a day, so date arithmetic is ordinary arithmetic. See dates and times.

Task Formula
Today / now =TODAY(), =NOW() (both volatile)
Build a date =DATE(2025,3,14)
Year, month, day =YEAR(A2), =MONTH(A2), =DAY(A2)
Day of week =WEEKDAY(A2) (Sunday is 1), =WEEKDAY(A2,2) (Monday is 1), =TEXT(A2,"dddd")
Days between =B2-A2 (format as a number)
Complete years, months =DATEDIF(A2,B2,"Y"), =DATEDIF(A2,B2,"M")
Same day, N months later =EDATE(A2,3)
Last day of month =EOMONTH(A2,0)
First day of month =EOMONTH(A2,-1)+1
Working days between =NETWORKDAYS(A2,B2,Holidays)
Date N working days ahead =WORKDAY(A2,10,Holidays)
Add 90 minutes =A2+TIME(1,30,0)

The stand-in below reproduces several of these in JavaScript so you can see the logic. It handles modern dates only and ignores the 1900 leap-year quirk that Excel keeps for compatibility.

JS Date patterns from the sheet

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Dynamic array functions (365)

Task Formula
Sort a list =SORT(A2:A20), descending =SORT(A2:A20,1,-1)
Sort a table by a column =SORTBY(A2:C20,C2:C20,-1)
Filter rows =FILTER(A2:C20,C2:C20>100,"No rows")
Distinct values =UNIQUE(A2:A20)
Numbers 1 to 12 =SEQUENCE(12)
Whole spilled result =SUM(F2#)
Name a value inside a formula =LET(total,SUM(B2:B9),B2/total)
First / last rows =TAKE(A2:C20,3), =TAKE(A2:C20,-3)
Stack tables =VSTACK(A2:B5,A10:B13), =HSTACK(A2:A5,C2:C5)

A #SPILL! error means something blocks the cells the result needs. See dynamic arrays and spilling, FILTER, SORT, SORTBY and UNIQUE and LET and LAMBDA.

Text pattern stand-in

Pattern mistakes usually come from counting characters wrong. This stand-in mirrors a few of the functions above on one e-mail address and one product code.

JS Text patterns on sample data

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Error meanings

Error Usual cause First thing to check
#DIV/0! Dividing by zero or an empty cell The denominator
#N/A A lookup found no match Spelling, spaces, text versus number
#VALUE! Wrong type of argument, such as text in arithmetic Hidden spaces, numbers stored as text
#REF! A referenced cell or sheet was deleted The cell the formula pointed at
#NAME? Misspelled function or name, or a function your version lacks Spelling and Excel version
#NUM! Impossible number, such as a negative square root, or a result too large The inputs and iteration settings
#NULL! A space used between two ranges that do not intersect Missing : or ,
#SPILL! Something blocks a dynamic array result Cells below and to the right
#CALC! An array calculation failed, such as an empty array The FILTER or LAMBDA inputs
##### The column is too narrow, or a date/time is negative Widen the column

A green triangle in a cell corner marks a formula Excel considers suspicious; select the cell and open the warning menu to see why. See formula errors and auditing formulas.

Shortcuts that save the most time

Shortcut Action
Ctrl + arrow Jump to the edge of data
Ctrl + Shift + arrow Select to the edge of data
Ctrl + Space / Shift + Space Select column / row
F2 Edit the cell
F4 Cycle $ anchors in a formula; repeat last action elsewhere
Ctrl + Enter Fill the entry into all selected cells
Ctrl + D / Ctrl + R Fill down / right
Alt + = AutoSum
Ctrl + T Create a table
Ctrl + Shift + L Toggle filters
Ctrl + 1 Format Cells
Ctrl + ; Insert today's date as a value
Ctrl + Alt + V Paste Special
Ctrl + G Go To, then Special for blanks, formulas, errors
Ctrl + ` Show formulas

More detail in keyboard shortcuts and productivity tricks.

Good habits in one list

  • Keep inputs, calculations and outputs in separate, labelled areas.
  • Put constants such as tax rates in cells or names, never inside formulas.
  • Use Tables for lists so ranges grow with the data.
  • Prefer exact-match lookups; use IFNA rather than blanket IFERROR.
  • Avoid whole-column references inside many heavy formulas, and avoid volatile functions (NOW, TODAY, OFFSET, INDIRECT, RAND) unless you need them. See performance and best practices.
  • Test with one row you can check by hand.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which formula gives the last day of the month for the date in A2?

    EOMONTH with 0 months returns the last day of the same month. EDATE keeps the same day number one month later.
  2. 2. You need a lookup in a workbook that must also open in Excel 2016. What is the safest choice?

    INDEX and MATCH exist in every version. XLOOKUP, FILTER and TEXTSPLIT need Excel 2021 or Microsoft 365.
  3. 3. A formula shows #SPILL!. What is the most likely reason?

    A dynamic array formula needs a clear area to spill into. Clear the blocking cells and the formula works.
  4. 4. Which criteria text in COUNTIF counts cells that are not empty?

    "<>" means not equal to nothing, so it counts every non-empty cell. "*" counts only text, and ">0" counts only positive numbers.

Summary

  • Search this page by task: totals, logic, lookups, text, dates, dynamic arrays, errors, shortcuts.
  • Use SUMIFS and COUNTIFS with criteria text like ">100", "<>" and "A*".
  • Choose XLOOKUP when your version has it and INDEX with MATCH when it must work everywhere; ask for exact matches.
  • Dates are serial numbers, so EDATE, EOMONTH and NETWORKDAYS do the calendar work for you.
  • Learn the error names: each one points to a specific kind of mistake.
  • Return to the track overview whenever you want a full lesson on any topic here.