Spreadsheets properly

Relative and absolute references

Also called $A$1, mixed reference, cell reference, relative reference, absolute reference

What

A relative reference (A2) shifts when you copy a formula; an absolute one ($A$2) stays fixed; a mixed one ($A2 or A$2) fixes only the column or row.

Why

Copying formulas down thousands of rows is how spreadsheets scale. Wrong anchoring silently produces wrong numbers.

=B2*$F$1        tax rate fixed in F1
=B2/SUM(B$2:B$100)   share of column total

Structured tables

Also called Excel table, Ctrl+T, structured references

What

A range turned into a named table, whose formulas refer to columns by name and which grows automatically when you add rows.

Why

Formulas, pivot tables and charts built on a table pick up new data without editing ranges, which removes a classic source of stale reports.

=SUM(Sales[Amount])
=[@Quantity]*[@UnitPrice]

SUMIFS and COUNTIFS

Also called AVERAGEIFS, conditional sum

What

Functions that sum, count or average only the rows that meet one or more conditions.

Why

They answer most quick business questions, such as sales by region for a month, without a pivot table.

=SUMIFS(Sales[Amount], Sales[Region], "North",
       Sales[Date], ">="&DATE(2026,1,1))
=COUNTIFS(Sales[Status], "Refunded")

XLOOKUP

Also called VLOOKUP, INDEX MATCH, lookup

What

Looks up a value in one column and returns the matching value from another, with a fallback when nothing is found. It works in current Excel and Google Sheets.

Why

Bringing a column from one table into another (a customer region, a product price) is one of the most common tasks in analysis.

=XLOOKUP([@CustomerID], Customers[ID],
        Customers[Region], "Not found")

Text and date cleaning functions

Also called TRIM, TEXT, DATEVALUE, SPLIT, PROPER, SUBSTITUTE

What

Functions that fix the usual mess: extra spaces, mixed case, text that should be numbers or dates, and codes that need splitting.

Why

Dirty text breaks lookups, counts and pivot tables. Cleaning in helper columns keeps the original data intact and the steps visible.

=TRIM(PROPER(A2))            clean a name
=DATEVALUE(B2)               text to date
=TEXT(C2, "yyyy-mm")         month label

FILTER, UNIQUE and SORT

Also called dynamic arrays, spill range

What

Functions that return a whole range of results that spill into neighbouring cells: a filtered list, the distinct values or a sorted copy.

Why

They replace many manual copy and paste steps with formulas that update when the data changes.

=UNIQUE(Sales[Region])
=SORT(FILTER(Sales, Sales[Amount]>1000), 3, -1)

Pivot tables

Also called PivotTable, pivot

What

A tool that summarises a table by dragging fields into rows, columns, values and filters, such as total sales by region and month.

Why

They are the fastest way to explore a new dataset and answer "how much, by what" questions, and they teach the group-by thinking SQL and BI tools use.

Example

Rows: product category. Columns: month. Values: sum of revenue and count of orders. Filter: country.

Data validation

Also called drop-down list, input rules

What

Rules on cells that limit what can be entered, such as a drop-down of allowed regions or dates within a range.

Why

Stopping bad data at entry is far cheaper than cleaning it later, especially in sheets that several people fill in.

Raw, working and output tabs

What

Keeping untouched source data on one tab, cleaning and calculations on another, and the summary or chart on a third, with a notes tab describing each.

Why

Anyone, including you in three months, can trace a number back to its source and rerun the steps with new data.