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

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

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...
Jump To A Cell Reference Within An Excel Formula When writing, editing or auditing Excel formulas you will come across a scenario where you want to view and access the referenced cells within a formula argument. This is helpful if you want to check how the formula works or to make any changes to the formula. There is ...
INDIRECT Function Using Sheet References What does it do? Returns a reference to a cell, or a range of cells of a sheet. Formula breakdown: =INDIRECT(ref_text, ) What it means: =INDIRECT(Return the referenced range, Omit if the reference is an A1 style or enter FALSE if it is a R1C1 style) The ...
VLOOKUP Multiple Columns What does it do? Searches for a value in the first column of a table array and returns the sum of values in the same row from other columns (to the right) in the table array. Formula breakdown: {=SUM(VLOOKUP(lookup_value, table_array, {col_index_num1,col_index_num2}, ))}...