Excel OFFSET Function for Dynamic Calculations - Explained in Simple Steps

Nov 5, 2016 Excel Advanced Formulas & Features
OFFSET tells Microsoft Excel to “fetch” a cell location (address) from within a data range. Download the Workbook here: http://www.xelplus.com/excel-offset-function-for-dynamic-calculations/

★ My Online Excel Courses ► https://www.xelplus.com/courses/

00:00 How to Use the OFFSET Function in Excel
00:46 OFFSET Formula for Dynamic Calculation for the Last 6 Months
10:41 OFFSET Formula for Dynamic Calculation Based on User Selected Date

★ Get the Official XelPlus MERCH: https://xelplus.creator-spring.com/

Excel OFFSET Function Tutorial Summary:
The offset formula looks like:
=OFFSET(starting point, rows to move, columns to move, height, width)
Here are practical uses of OFFSET:
• Dynamic Calculations: The OFFSET formula can be included in other Excel functions to get dynamic ranges. For example, calculating the average sales of the last 3 months based on a selection
• Flexible Chart Ranges: In conjunction with charts it can be used in the NAME manager to add flexibility to chart data. The OFFSET function behaves like an Excel table where the data range automatically expands and contracts when chart data is updated.
• Dynamic dashboards: It can be used together with Excel’s Form Options to allow for dynamic analysis and reports based on user-selection.
You can tell the OFFSET function to return one cell if you specify height and width as 1. It can return an array of cells when the height and width are greater than 1. Height and/or width CANNOT be zero, because you either need 1 cell or a range of cells. In the illustration above, the height and width are 2.
This video shows how you can use the Excel OFFSET function to calculate the average value for the last 6 months. You will also learn how to use the OFFSET formula to calculate the average value based on the date selected by the user.
(You will see formula combinations such as OFFSET, COUNTA and MATCH functions)
In general Excel OFFSET function is an interesting formula and it’s one that can do so much more than meets the eye. It can used inside many formulas to provide them with flexible ranges instead of fixed ranges. This makes it a perfect candidate for dynamic reports and dashboards.

★ My Online Excel Courses ► https://www.xelplus.com/courses/

✉ Subscribe & get my TOP 10 Excel formulas e-book for free
https://www.xelplus.com/free-ebook/

EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/

GEAR I use: https://www.xelplus.com/resources/#gear

More resources on my Amazon page: https://www.amazon.com/shop/leilagharani

Let’s connect on social:
Instagram: https://www.instagram.com/lgharani
Twitter: https://twitter.com/leilagharani
LinkedIn: https://at.linkedin.com/in/leilagharani

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#MsExcel