Download Excel Workbook
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.
Table of Contents
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!
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 workbookOffset-Intro.xlsx
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.
