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:

MOTH 468x90.update.your.skills.animated.chart.learn.online.2

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

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

INDEX-MATCH Maximum Sales The INDEX-MATCH formula cannot only look up values to the left and right of your data but its versatility means that it can also lookup the largest values within an array.Say that you had a list of Sales Reps in one array and their corresponding Sales Values in another array....
Vlookup Approximate Match in Excel What does it do?Searches for an approximate value in the first column of a table array and returns a value in the same row from another column (to the right) in the table array.Formula breakdown:=VLOOKUP(lookup_value, table_array, col_index_num, )Wha...
GETPIVOTDATA Function What does it do?A formula that extracts data stored in a Pivot TableFormula breakdown:=GETPIVOTDATA(data_field, pivot_table, , ,...)What it means:=GETPIVOTDATA(return me this value from the Values Area, any cell within the Pivot Table, ,...) T...
Summarize Data With Dynamic Subtotals What does it do?It returns a Subtotal in a list or databaseFormula breakdown:=SUBTOTAL(function_num, ref1)What it means:=SUBTOTAL(function number 1-11 includes manually-hidden rows & 101-111 excludes them, your list or range of data)***Go to the bottom...