How To Use The OFFSET Function in Excel

The OFFSET function is a powerful tool that can be used for many purposes. In this blog post, we will discuss what the OFFSET function is and how to use it. We will also provide some common examples of how you can use it in your own work. Stay tuned because we are going to show you everything you need to know about the OFFSET function in Excel!

What Does OFFSET Do?

The OFFSET function returns a cell or range of cells that is a specified number of rows and columns from a cell or range of cells. OFFSET can be used in many different ways, such as to create dynamic ranges, return values based on criteria (e.g., if the value in column A equals X), and more.

Where To Find It?

Option 1:

The OFFSET function can be found in the Formulas tab of Excel under Lookup & Reference

Option 2:

You can use Formula Builder to walk you through the formula step by step.

Option 3:

You can manually type the formula into any cell.

The Formula

=OFFSET (reference, rows, cols, [height], [width])

• Reference – This is the cell or group of cells that you want to offset from
• Rows – This is the number of rows to move from the reference cell (or from the top left cell of the range)
• Columns – This is the number of columns to move from the reference cell (or from the top left cell of the range)
• Height (optional) – This tells Excel the height of the range. If you don’t enter an argument, it will match the height of the reference.
• Width (optional) – This tells Excel the width of the range. If you don’t enter an argument, it will match the width of the reference.

Let’s Take a Look at Some Real-World Examples

Excel Workbook

You can follow along with our YouTube walkthrough and examples using this Excel workbook:

YouTube Walkthrough

Example 1 – Beginner

In the first example, we will use OFFSET to select values in a 9 cell grid. In the grid below, lettered columns are combined with numbered rows to populate a grid.

For the first task, we want to select B1 from the grid, highlighted in blue. Our formula should be:

=OFFSET(B8, 1, 2)

• Reference = B8 which is the top left cell of the grid
• Row = 1 which will have Excel count down one row from the reference (row 8 to row 9)
• Column = 2 which will have Excel count over 2 rows from the reference (column B to column D)
• Height and width are left blank as we only want to return one cell

Your turn: Use the workbook and the OFFSET function to select cell C3 and finish the cell highlighted in green

Example 2 – Intermediate

In the second example, we will look at a real-life scenario. Let’s say you are working on a sales forecast. You want to use historical data as your baseline and add inflation and other adjustments. We can use the OFFSET function to pull the adjusted baseline from 2021 as the new baseline for 2022.

Our formula should be:

=OFFSET(G8, 2, -4)

• Reference = G8 which is the current period we want to forecast
• Row = 2 which will have Excel count down 2 rows from Baseline to Adjusted
• Column = -4 which will have Excel count backwards over 4 rows from the reference (column G to column C). Remember, a positive number moves forward or down while a negative number moves backwards or up
• Height and width are left blank as we only want to return one cell

Your Turn: Use the workbook and the OFFSET function to finish the table by populating the OFFSET function in the green cells

Example 3 – Advanced

In the third example, we will look at another real-life scenario. Let’s say you are working on stocking inventory. You want to keep 4 weeks of inventory on hand, but need to keep track of how many units are moving in a 4-week period. We can use the OFFSET function to sum the prior four-week period on a rolling basis. And yes, we could also just use SUM for this, but have to learn somehow

Our formula should be:

=SUM(OFFSET(G10,-1,-3, 1, 4))

• SUM Formula is being used with OFFSET as the cell reference. OFFSET can be used with any formula that expects a reference
• Reference = G10 which is the current period we want to forecast
• Row = -1 which will have Excel count up 1 row to the weekly sales. Remember, a positive number moves forward or down while a negative number moves backwards or up
• Column = -3 which will have Excel count backwards over 3 rows to the period 4 weeks ago
• Height = 1 which will pull a range one row high
• Width = 4 which will pull a range four columns wide

Your Turn: Use the workbook and the OFFSET function to finish the table by populating the OFFSET function in the green cells

Tips and Tricks

It’s Ok To Be Negative

You can use either positive or negative numbers to move the offset up, down, left, or right. This applies to the row, column, height, and width arguments.

Better Together

OFFSET works in tandem with any other function that expects to receive a reference. It pairs especially well with IF Functions.

Volatility

OFFSET is a volatile function that can cause performance issues in large excel workbooks. A volatile function is one that recalculates every time there is a change in the workbook.

If You Get a #REF Error

This usually means the range returned by the function is invalid. Most often, your range went off the worksheet. Rethink your column and row references to stay within bounds.

If You Get a #VALUE! Error

One of your arguments was non-numeric. Since offset is moving a certain number of rows or columns, row, column, height, and width all need to be numbers.

Let’s Recap

The OFFSET function is a versatile tool that you can utilize for a variety of goals. In this blog post, we discussed what the OFFSET function is and how to utilize it. We also provided some typical examples of how you might utilize it in your own work. Be on the lookout for more articles on Excel and financial wizardry.

Have any questions? Are there other topics you would like us to cover? Leave a comment below and let us know! Make sure to subscribe to our Newsletter to receive exclusive financial news right to your inbox.