Navigating calculations with OFFSET and ranges

Advanced Excel Functions

Agata Bak-Geerinck

Product Owner Data, Telenet

Navigating Excel cells

white spacing

Example of absolute referencing in Excel

white spacing

Absolute referencing:

white spacing

A1, C1 for individual cells
A1:C1 for ranges

white spacing

Example of relative referencing in Excel

white spacing

Relative referencing:

white spacing

x rows down, up
y columns right, left

When to use relative referencing?

white spacing

Some practical applications:

New rows / columns are added to the dataset
Anchoring data relative to a (dynamically) selected data point
Reformatting nested data inputs into proper tables

Example of an Excel table with one extra row and one extra column

Example of a dynamic filter in Excel

Example of a nested table and its transformation

OFFSET( ) in practice

Syntax:

Syntax of Offset function in Excel

A set of cells with arrows pointing down and right from one towards another cell, to illustrate OFFSET use in Excel

An example of OFFSET function based on the above set of cells in Excel

OFFSET( ) in practice

Syntax:

Syntax of Offset function in Excel

A set of cells with arrows pointing up and left from one towards another cell, to illustrate OFFSET use in Excel

An example of OFFSET function based on the above set of cells in Excel

Wrapping OFFSET( ) in other functions

Examples:

Excel syntax for SUM and OFFSET function

A table with two rows and two columns to illustrate a summed array

white spacing

An example of SUM and OFFSET function in Excel

Going dynamic!

An example of a dynamic selection tool in Excel

Going dynamic!

An example of a dynamic selection tool in Excel

OFFSET () and MATCH () combination

Sales of Week 2 (cell E2) = OFFSET (A5, 1 , MATCH($B$1, Weeks, 0))

Going dynamic... with charts!

white spacing

Gif presenting the table and chart dynamic combination

OFFSET () and MATCH () combination with charts
Visualizing dynamically selected data

Let's practice!

Advanced Excel Functions