Excel & spreadsheets

Excel turns a blank grid into a live calculation engine. Learn the functions, lookups, and shortcuts that do the heavy lifting — then steal the formulas.

Quick reference

The daily surface on one screen — each formula is expanded in the sections below.

• =SUM(A2:A10) — Add a range — AVERAGE and COUNT take the same shape.
• =IF(B2>1000, "High", "Low") — Branch on a condition; use =IFS(t1, v1, t2, v2) for several cases.
• =SUMIFS(B:B, A:A, "West", C:C, ">0.2") — Sum where criteria match — COUNTIFS/AVERAGEIFS work the same.
• =XLOOKUP(E2, A2:A10, B2:B10, "Not found") — Modern lookup: exact by default, looks in any direction.
• =UNIQUE(A2:A10) · =SORT(B2:B10) · =FILTER(A2:B10, C2:C10>100) — Dynamic arrays: dedupe, sort, and filter — results spill down.
• $A$1 — Absolute reference (locked) vs relative A1 — press F4 to cycle.
• =TEXTJOIN(", ", TRUE, A2:A10) · =TEXTSPLIT(A2, ",") — Join with a delimiter, then split it back apart.
• Insert → PivotTable — Summarize rows by dragging fields into Rows and Values.
• Ctrl+Shift+L · F2 · F4 · Ctrl+T — Filters · edit cell · cycle refs · make a Table.

The essentials

Every formula starts with =. Learn how Excel addresses cells before you write anything else.

1. Cells & ranges

A colon spans a contiguous block.

=A1 + B2
=SUM(A1:A10)

2. References

$ locks the part that follows it.

$A$1   locked row + column
$A1    locked column only
A$1    locked row only
A1     both shift

3. Autofill

Drag the fill handle to extend a pattern.

Jan, Feb, Mar…
1, 2, 3…
Q1, Q2, Q3…

4. Formula basics

Type, commit, edit, cancel.

=SUM(B2:B10)   Enter commits
F2             edits the cell
Esc            cancels

Operators

Arithmetic and comparison operators build the conditions inside every formula.

+  -  *  /  ^         arithmetic
=  >  <  >=  <=  <>    compare

Order of operations

Multiplication binds tighter than addition; parentheses always win.

=(B2 + C2) * D2
=B2 + C2 * D2

KEY: Absolute vs relative. $A$1 is fully locked; A$1 locks the row; $A1 locks the column; plain A1 shifts both when copied or filled. Press F4 to cycle through them.

Key functions

The ten functions that answer most spreadsheet questions.

✓: Tip: COUNT ignores text and blanks; use COUNTA to count any non-empty cell and COUNTBLANK for the empties.

Lookups

Match a value in one place and pull back the value next to it.

⌁: XLOOKUP syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). A match_mode of 0 forces an exact match; -1/1 allow approximate.

XLOOKUP vs VLOOKUP

XLOOKUP can return left of the lookup column, defaults to exact match, and never needs a column index number.

=XLOOKUP("West", A2:A10, B2:B10)
=VLOOKUP("West", A2:C10, 3, FALSE)

INDEX/MATCH, step by step

MATCH finds the row number; INDEX returns the value at that row.

=MATCH(E2, A2:A10, 0)
=INDEX(B2:B10, 3)

Pivot tables & analysis

Summarize thousands of rows into a report without writing a single formula.

1. Select — Click a cell in your data and go to Insert → PivotTable.
2. Choose range — Pick the data range (or a Table) and where the pivot should land.
3. Drag fields — Drop fields into Rows (categories), Columns, Values (numbers), and Filters.
4. Set summary — Values default to Sum; switch to Count or Average via Value Field Settings.
5. Refresh — Press AltF5 when the source data changes.

Charts

Select data → Insert → Chart. AltF1 inserts one instantly.

Conditional formatting

Home → Conditional Formatting to color-code values, bars, and duplicates.

Data validation

Data → Data Validation to restrict input or build dropdown lists.

Slicers

PivotTable Analyze → Insert Slicer for clickable, visual filters.

Text & cleaning

Functions that split, join, trim, and reshape messy strings.

=TEXTBEFORE(A2, ",")

=TEXTAFTER(A2, "@")

=LEFT(A2, FIND(" ", A2)-1)

=TEXTSPLIT(TRIM(A2), ",")

⌁: Cleaning pipeline: chain =TRIM(SUBSTITUTE(A2, CHAR(160), " ")) to nuke non-breaking spaces, then LEFT/MID to split on position.

Shortcuts

Move, select, and edit without ever touching the mouse.

Navigation

• Jump to edge of data — Ctrl→←↑↓
• Go to A1 / last cell — CtrlHomeCtrlEnd
• Next / previous sheet — CtrlPgDnPgUp
• Go To dialog — F5

Selection

• Extend to edge of data — CtrlShift→
• Select current region — CtrlA
• Select row / column — ShiftSpaceCtrlSpace
• Select to last cell — CtrlShiftEnd

Editing

• Edit active cell — F2
• Cycle reference type — F4
• Fill down / right — CtrlDCtrlR
• New line inside cell — AltEnter

Formatting

• Toggle filters — CtrlShiftL
• Format Cells dialog — Ctrl1
• Bold / italic — CtrlBCtrlI
• Currency / percent — CtrlShift$%

Entering data

• Confirm and move down / right — EnterTab
• Fill selection with active cell — CtrlEnter
• Today’s date / current time — Ctrl;CtrlShift:
• Insert a comment / note — ShiftF2

!: On a Mac, swap Ctrl for ⌘ and Alt for ⌥; function keys often need Fn too.

Advanced

Once basics are boring, these techniques scale to real models.

Array formulas

Modern Excel spills results into adjacent cells; reference the whole spill with #.

=SORT(A2:A10)
=UNIQUE(A2:A10)
=FILTER(A2:B10, C2:C10>100)
=A2#

LET

Name intermediate values so a formula reads top-to-bottom and calculates once.

=LET(x, A2:A10,
     y, x*1.2,
     SUM(y))

LAMBDA

Define your own reusable function, with or without a named range.

=LAMBDA(price, price*1.2)(A2)

Named ranges

Formulas → Name Manager to name cells, then reference them by name.

=SUM(West_Sales)

Tables

Press CtrlT to make a Table; structured references use column names.

=[@Sales]*[@Qty]

Power Query

Data → Get Data imports and shapes data repeatably — each recorded step reruns on refresh.

⌁: Formula aggregates (2024): GROUPBY and PIVOTBY roll rows into a summary table with one formula — no pivot table required. =GROUPBY(A2:A100, B2:B100, SUM).

!: Spill errors. Dynamic array formulas fill adjacent cells automatically; if anything is in the way you get #SPILL!. Clear the blocking cells to let the array spill.

Pitfalls

Small behaviors that silently corrupt a workbook.

VLOOKUP can’t look left

It only searches the first column and returns to the right. Use XLOOKUP or INDEX/MATCH to return a column to the left.

Text vs number

"123" is not 123. Numbers stored as text break SUM and sorting. Fix with =VALUE(A2) or Text to Columns.

Forgotten $ locks

Copying =A1*B1 down becomes =A2*B2 — usually right, but a tax rate in B1 should be $B$1.

Volatile functions

NOW(), TODAY(), RAND(), OFFSET(), and INDIRECT() recalculate on every change and slow large workbooks.

VLOOKUP’s hidden approximate mode

Omit the 4th argument and VLOOKUP defaults to TRUE — an approximate match that needs sorted data and silently returns the nearest value. Always pass FALSE for exact.

Dates are serial numbers

Excel stores dates as days since Jan 1, 1900 (1 = that day). Subtract two date cells to get a day count, and check the format when a “number” should be a date.

Floating-point drift

=0.1+0.2 is not exactly 0.3 in binary. Sums and comparisons of decimals can drift — wrap money math in =ROUND(…, 2).

Leading zeros vanish

Typing 00123 stores 123. For ZIP codes, phone numbers, and IDs, set the column to Text before entering, or prefix with an apostrophe '.

!: Merged cells break sorting, filtering, and range references. Prefer “Center Across Selection” (Format Cells → Alignment) for the same look with none of the pain.