R4 Excel reference

Look up the functions and commands for the task you are working on.

Use Table 1 to find the functions or commands for your current task.

Table 1: Functions and commands used in the Excel analysis workflow.

Task	Functions or commands
Structure records	Insert Table; headers; sort and filter; Freeze Panes
Calculate	Arithmetic operators; relative, absolute, mixed, and structured references
Apply rules	`IF`; `IFS`; `AND`; `OR`; data validation; conditional formatting
Summarize	`SUM`; `AVERAGE`; `COUNT`; `COUNTA`; `COUNTBLANK`; `MIN`; `MAX`; `ROWS`; `SUMIFS`; `COUNTIFS`; `AVERAGEIFS`
Retrieve related fields	`XLOOKUP` with exact matching; `VLOOKUP` with `FALSE` when reading existing work
Clean or separate fields	`TRIM`; `CLEAN`; `LOWER`; `UPPER`; `LEFT`; `RIGHT`; `MID`; `LEN`; `TEXTBEFORE`; `TEXTAFTER`; `TEXTSPLIT`; `INT`; `MOD`
Return a changing list	`FILTER`; `SORT`; `UNIQUE`; spill-range operator (`#`)
Inspect a formula	Show Formulas; Trace Precedents; compare with a manual calculation
Import and repeat processing	Power Query Text/CSV and JSON connectors; data types; Merge Queries; Refresh All
Report	PivotTable fields and Value Field Settings; PivotChart; number formats; chart titles and units

R4.1 Mixed references

A mixed reference fixes either its column or its row. Use Table 2 when copying a formula across rows and columns.

Table 2: Copy behavior of the 2 mixed-reference forms.

Reference	Copied one row down and one column right	Fixed part
`$G2`	`$G3`	column `G`
`G$2`	`H$2`	row `2`

Select a reference inside a formula and press F4 repeatedly to cycle through relative, absolute, and mixed forms.

Use the tutorial that matches the task in the workbook. These Microsoft Support pages provide written steps, and several include an official video:

Check the source type, exact-match rule, included population, unit, and expected result whenever a formula or summary gives an unexpected value.