What does it do?

It returns a reference to a range, from a starting point to a specified number of rows, columns, height and width of cells

Formula breakdown:

=OFFSET(reference, rows, columns, [height], [width])

What it means:

=OFFSET(start in this cell, go up/down a number of rows, go left/right a number of columns, height of range, width of range)

The OFFSET function in Excel is one of the Lookup functions and is great if you want to reference a range of cells and use that reference to do a calculation.

When we have a large data set and add new rows/columns of data on a daily basis, we sometimes want to capture that new data inÂ our analysis automatically without having to amend theÂ data range.

One example is when we are using a Pivot Table and our data source is not in an Excel Table. Â We want the new data to be added in the Pivot Table’s Data Source automatically without having to Change the Data Source each time manually.

This is how it is done below (go to the bottom of the page if you want to see the animated gif tutorial):

STEP 1: We need to enter the OffsetÂ function

## =OFFSET

STEP 2: 1st Offset argument – Where do we want to to start our reference?

This is at the start of the data source (i.e. the top left hand corner)

Make sure to add an absolute reference to the 1st argument by pressing F4

## =OFFSET(\$A\$10

STEP 3: 2nd Offset argument – How many Rows down do we want to go?

As we want the referenced range toÂ start at cell A10, we simply enter 0

## =OFFSET(\$A\$10,Â 0,

STEP 4: 3rd Offset argument – How many Columns to the right/left do we want to move?

We do not want to move to any Columns, so we simply enter 0

## =OFFSET(\$A\$10,Â 0,0,

STEP 5: 4th Offset argument – How High do we want our referenced data to be?

This is where the COUNTA function is used to count how many transactions we have in our data verticallyÂ and returnÂ the [height]Â of our data.

So as we are starting at cell A10, we are going to captureÂ 13 cells downwards ( COUNTA(A10:A1048576) ) i.e.Â The rangeÂ up to the last active vertical cell of our data.

NB: It is always a good idea to enter an ending range that is more than your last data cell or up till the last row number i.e. 1,048,756. Â That way as your new data get entered, we will be sure to capture it!

Make sure to add an absolute reference to the 4thÂ argument by pressing F4

## =OFFSET(\$A\$10,Â 0,0,COUNTA(\$A\$10:\$A\$1048576

STEP 6: 5th Offset argument – How WideÂ do we want our referenced data to be?

This is where the COUNTA function is used to count how many transactions we have in our data horizontallyÂ and returnÂ the [width]Â of our data.

So as we are starting at cell A10, we are going to capture 3Â cells to the right( COUNTA(\$A\$10:\$XFC\$10) ) i.e.Â The rangeÂ up to the last active horizontalÂ cell of our data.

NB: It is always a good idea to enter an ending range that is more than your last data cell or up till the last columnÂ number i.e. XFC. Â That way as your new data get entered, we will be sure to capture it!

Make sure to add an absolute reference to the 4thÂ argument by pressing F4

## =OFFSET(\$A\$10,Â 0,0,COUNTA(\$A\$10:\$A\$1048576,),COUNTA(\$A\$10:\$XFC\$10))

As you can see from the image below, the OFFSET function captures the following data range:

STEP 7:Â Now that we have our OFFSET function, we need to create a Named Range for it so we can use it as our data sourceÂ in Step 9.

To do this we need to select the whole OFFSET function and Copy it

…and go to Formulas > Name Manager > New:

STEP 8:Â In the New Name dialogue box we need to enter a custom name with no spaces e.g. Data_RangeÂ in theÂ Name:Â area and paste the OFFSET function from Step 7 in the Refers to: area and press OK

STEP 9:Â We need to insert a Pivot Table by going to Insert > Pivot Table

…and in the Create PivotTable dialogue box we need to manually enter the Named Range from Step 8 i.e.Â Data_RangeÂ into the Table/Range area, choose a New/Existing Worksheet and press OK:

STEP 10:Â In the Pivot Table, you will need to put the SALES field in the VALUESÂ area, the YEAR field in the COLUMNSÂ area and the MONTH field in the ROW area:

STEP 11: In your data source you can start adding new data in the empty rows:

STEP 12: To reflect this new data in the Pivot Table, all you need to do is Right Click inside the Pivot Table and choose Refresh:

The new data will now be shown in the Pivot Table:

STEP 13: To check that ourÂ Named Range called Data_Range captures the new data entered, we need to click in the Pivot Table and go to PivotTable Tools >Analyze/Options > Change Data Source:

So we can confirm that our Named Range Data_Range captures any new data entered in our data source!

This is a great trick but if you use an Excel Table as your data source, you avoid creating this OFFSET function within a Named Range. Â Excel Tables rock but Excel purists still love playing around with formulas, so this trick is for you ðŸ™‚