Absolute Cell Reference

An absolute cell reference is a cell reference that remains constant, even when a formula is copied or filled into other cells. It uses dollar signs before the column and row (e.g. $A$1).

When to use: When you want one value (like a VAT rate or exchange rate) to stay fixed in every calculation.

Example:

=B2 * $E$1

Here, the reference to cell B2 changes as you copy down, but $E$1 remains fixed.

See also: Relative Cell Reference

Conditional Formatting

Conditional formatting automatically changes the appearance of a cell based on rules you define. It helps highlight trends, anomalies, or important information at a glance.

Common uses:

• Highlight overdue dates in red
• Show top 10 performers in bold green
• Apply colour scales to compare values

Example rule: Highlight sales below £500 using Home > Conditional Formatting > Less Than.

Pro tip: Use a formula like =AND($A2="UK",$D2>1000) for advanced custom rules.

PivotTable

A PivotTable is a powerful Excel feature that summarises, analyses, and reorganises large amounts of data without complex formulas. You can group data, calculate sums/averages, and drill into detail instantly.

When to use: To analyse large datasets quickly, compare regions or categories, or produce management reports.

Example workflow:

1. Select your data table.
2. Go to Insert > PivotTable.
3. Drag “Region” into Rows and “Sales” into Values.

Result: A summary of sales by region with automatic totals.

See also: Pivot Chart, Fields

VLOOKUP

VLOOKUP is a lookup function that searches for a value in the first column of a table and returns data from another column in the same row. It’s widely used but limited (only searches left to right).

Syntax:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Example:

=VLOOKUP(102, A2:D20, 3, FALSE)

This searches for product ID 102 in the first column of the table and returns the value from column 3.

See also: XLOOKUP, INDEX, MATCH