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

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...
SUMIF Function: Introduction The SUMIF function is used widely amongst spreadsheet users as it is a simple Excel function.  It allows you to Sum the values in a range that meet a criteria that you specify.So if you want to Sum a range of sales values that are above $3,000 then this is the best Excel func...
VLOOKUP Example: Vlookup with a Drop Down List What does it do?Searches for a 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, )What it means:=VLOOKUP(thi...
SUMIF Function: One Criteria & Sum Range The SUMIF function can also be used to apply the criteria to one range and sum the corresponding values in a different range.So if you have a list of Sales Reps in one list and their Sales in another list and want to Sum the sales of only one of the Sales Reps, then the SUMIF...