← Functions

Use OFFSET Function for Dynamic Referencing

How to use Excel's OFFSET function to create dynamic chart ranges and cell references that expand automatically as data grows.

Excel has a powerful function called OFFSET that allows dynamic referencing without VBA modules or macros. For example, a chart can automatically expand to include new monthly data as it is added, with no manual intervention.

Syntax

OFFSET(reference, rows, cols, [height], [width])

  • reference — the starting cell or range (required)
  • rows — number of rows to move up or down from the reference (required)
  • cols — number of columns to move left or right from the reference (required)
  • height — number of rows to include in the returned range (optional)
  • width — number of columns to include in the returned range (optional)

The height and width arguments are the keys to dynamic referencing — they define the scope of values to include.

Example: Dynamic Chart Range

If you have monthly revenue data in cells B4:M4 and only 5 months of data so far:

=OFFSET(Chart!$B$4, 0, 0, 1, 5)

  • reference = Chart!$B$4 — first data cell (January)
  • rows = 0 — same row
  • cols = 0 — same column
  • height = 1 — one row of data
  • width = 5 — include 5 months (January to May)

When data extends to 8 months, change the width to 8:

=OFFSET(Chart!$B$4, 0, 0, 1, 8)

To make this fully automatic, replace the width constant with a COUNT function:

=OFFSET(Chart!$B$4, 0, 0, 1, COUNT(Chart!$B$4:$M$4))

COUNT returns the number of cells with data, so the range expands automatically as new months are added.

Connecting OFFSET to a Chart

  1. Go to Formulas → Name Manager and click New
  2. Enter a name such as ChartDataRange
  3. Paste the OFFSET formula into the Refers to: field and click OK
  4. Insert a chart, then right-click it and choose Select Data
  5. Edit the series and set Series Values to use the named range

Using OFFSET in a Cell

If you type an OFFSET formula directly into a cell, you will get a #VALUE! error because OFFSET returns a range, not a single value. Wrap it in a function that accepts a range:

=SUM(OFFSET(Chart!$B$4, 0, 0, 1, 8))

=AVERAGE(OFFSET(Chart!$B$4, 0, 0, 1, 8))

This flexibility makes OFFSET useful for dynamic YTD totals and averages that update based on a user-selected month.