Pinterest Pixel

OFFSET Function Introduced

What does it do? It returns a reference to a range, from a starting point to a... read more

Download Excel Workbook
John Michaloudis
Posted on

Steps To Follow

Overview

OFFSET Function Introduced | MyExcelOnline

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!

See also  Autosum an Array of Data in Excel

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.

 

Want to know how to use the OFFSET function?

*** Watch our video and step by step guide below with free downloadable Excel workbook to practice ***

Watch it on YouTube and give it a thumbs-up!

download the youtube excel practice fileOFFSET.xlsx

 

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.

See also  Evaluate Formulas Step By Step in Excel

OFFSET Function Introduced | MyExcelOnline
Download excel workbookOffset-Intro.xlsx

 

 

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

OFFSET Function Introduced | MyExcelOnline

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!