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.

It is often used when you need to reference a range that is moving or resizing E.g. Daily sales going down one row per day.

Instead of updating your formula each day to include the new row of data, you would simply use an OFFSET function!

Note: The OFFSET function is a volatile function, which means that Excel recalculates the function whenever there is a change in your workbook, which may cause it to slow down, but don’t let this stop you from using its super POWER!

There are many ways were you would use an OFFSET function and here i explain a few scenarios:

  • * Get the sum or average of the last 7 transactions;
  • * Return the last value in a column;
  • * Lookup two values in a table;
  • * Create dynamic ranges that will be used in a chart;
  • * Calculate moving averages.

 

First I will show you how the OFFSET function works in Excel using an interactive workbook.

All you need to do is enter the values for each of the function´s arguments and it will highlight the range for you.  This way you will understand the formula much quicker before we start applying it to real life cases.

DOWNLOAD EXCEL WORKBOOK

Offset - Intro

HELPFUL RESOURCE:

How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel

 

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

INDIRECT Function in Excel What does it do? Returns a reference to a range. The referenced range can be a cell, a range of cells, or a named range. Formula breakdown: =INDIRECT(ref_text, ) What it means: =INDIRECT(Return the referenced range, Omit if the reference is an A1 style or enter FAL...
How To Use INDEX-MATCH Formula   What does it do? Searches the row position of a value/text in one column (using the MATCH function) and returns the value/text in the same row position from another column to the left or right (using the INDEX function) Formula breakdown: =INDEX(array, MATCH...
DATEDIF function: Introduction   What does it do? Calculates the number of Days, Months, or Years between two dates Formula breakdown: =DATEDIF(Start Date,End Date,Interval) What it means: =DATEDIF(starting date, ending date, the unit of measurement) Where INTERVAL is: "m" Months...
MATCH Function Intro   What does it do? It returns the position of an item in a range Formula breakdown: =MATCH(lookup_value, lookup_array, ) What it means: =MATCH(lookup this value, from this list or range of cells, return me the Exact Match) The MATCH function in Ex...