Excel’s OFFSET function lets you create a reference that is—quite literally—offsetfrom a starting point. It allows references to adjust dynamically to changes in your spreadsheet’s data and structure.

The OFFSET Syntax

Before we look at how it works in practice, let’s run through the syntax. The OFFSET function has five arguments:

where

An Excel spreadsheet containing an OFFSET formula and result, with arrows indicating how the result is obtained.

ValuesB,C,D, andEcan be either numerical or cell references.

Using a negative value inBwould create an upward offset, and a negative value inCwould create a left offset.

A table of data in Excel, and three colored report areas on the right-hand-side of the data table.

A Basic Example of OFFSET

To exemplify how OFFSET works, I’ll use a very basic, non-practical example. Typing

into cell A6 would use cell A1 as the starting point (A), move down 2 rows (B), move across 3 columns (C), and return a series of values 2 cells high (D) and 3 cells wide (E):

The OFFSET function being used in Excel to extract data from a single cell.

If I had not typed valuesDandEin my formula, the result would have defaulted to referencing cell D3 (Deer) alone. This is because Excel assumes the result is the same size as the reference if you don’t specify the height and width.

Based on this principle, you can also define the size of the result within valueA. For example, typing

The VLOOKUP function in Excel being used to return an employee’s name from their ID.

would return a result that is three cells high, as this is the size of the starting pointA.

Using OFFSET in the Real World

Now, I’ll show you how OFFSET can be used in real-world examples.

Real-world Example 1

Here, I’ve got ten employees, and the number of units they’ve sold over three weeks. I’m going to use OFFSET to create three data reports.

In the first data report (purple), I want Excel to tell me the number of units an employee has sold when I enter their ID and a week number. To achieve this, in cell H4, I’ll type

The COUNTIF function in Excel being used to count the number of cells in row 1 containing the word WEEK.

whereB1is the starting point,H2tells Excel how many rows down it needs to offset from the starting point (in other words, the employee ID), andH3tells Excel how many columns across to offset from the starting point (in other words, the week number). Notice how I’ve used cell references here, because this means the data the OFFSET function pulls from my table can be dynamic.

I’m starting with cell B1 as the first input because I want the first week to be the first cell to the right of that starting point. So, any time you use the OFFSET function, think carefully about where your starting point should be.

The SUM and OFFSET functions in Excel being used together to pull data from a table.

After typing the above formula into cell H4, I’ll type6into the ID box (cell H2), and2into the week box (cell H3). This will tell me how many units Ollie sold in week 2, because the OFFSET function is pulling the data from the cell six rows down and two columns across from my starting point, cell B1.

I can now change the values in cells H2 and H3 at any point to pull any individual figure from the table.

The AVERAGE and OFFSET functions in Excel being used together to pull data from a table.

Next, I want to create an employee report in the blue table. When I enter an employee ID, Excel will automatically tell me the employee’s name (using the VLOOKUP function), the total number of weeks of data we have (using the COUNTIF function), and the total and average units sold by that employee (using SUM and AVERAGE with OFFSET).

First, I will type an employee ID into cell H7 to get us started. Let’s go for employee ID3, which is Jason. Then, I’ll use theVLOOKUP functionto return Jason’s name in cell H8:

The SUM and OFFSET functions being used together to pull data from a table in Excel.

Now, I’ll use theCOUNTIF functionwith a wildcard to tell Excel to count how many cells in row 1 contain the word “Week” followed by a number:

I can now use this information to work out Jason’s total and average units sold. First, in cell H10, I’ll type

The AVERAGE and OFFSET functions being used together in Excel to pull data from a table.

whereB1is my starting point,H7is the number of rows to offset downwards (employee ID), the first1is the number of columns to offset to the right, the second1is the height of the result, andH9is the width of the result (the total number of weeks). However, because OFFSET is embedded within theSUM function, the result will only be a single cell in size.

I can copy and paste the same formula into cell H11, but change SUM toAVERAGE, since I’m using the same references.

A table in Excel containing sales and profits totals, and a table on the right where data will be pulled to.

I can now change the value in cell H7 to generate a report for any other employee in my data table. What’s more, if I were to add another week to my data, cell H9 would automatically change to 4, and the subsequent calculations in cells H10 and H11 would also update.

Finally, I want to create a weekly report in the orange table. This will require me to input a week number, and Excel will then generate the total and average units sold for that week. I’ll start by typing1into cell H14, so Excel will produce data from week 1. Then, in cell H15, I’ll type

whereB1is the starting point, the first1is the number of rows to offset downwards,H14is the number of columns to offset to the right (week number),10is the height of the result, and the second1is the width of the result. However, because OFFSET is embedded within the SUM function, the result will only be a single cell in size.

Finally, I can copy and paste that formula into cell H15, and change SUM to AVERAGE.

Real-world Example 2

In this example, I’ll show you how to use OFFSET witha formatted table, and what it looks like when used to return a result in more than one cell.

Here, I have current sales and profits so far this year in a formatted table, and I want my report table on the right to display the last three months' profit totals individually, as well as the average and total profit for the same period.

First, I need Excel to work out how many full months have elapsed so far, so that when I use OFFSET, I can use this figure to tell Excel how far from the starting point I need to offset. To do this, in cell F1, I’ll type

which willdisplay the current month number. In this case, it’s November, month 11.

We can now use this month number to display the profits from the past three months. In cell F3, I’ll type

and click cell A1, my starting point. This will automatically add the formatted table’s month column header into the formula:

Now, I’ll add a comma, and continue my OFFSET formula. The next stage is to tell Excel how many rows to offset downwards. This will be the current month (cell F1) minus three:

Finally, I’ll tell Excel that we need to offset two columns to the right, and the result will be three rows high and one column wide.

Notice how cells F4 and F5 contain data, even though the formula is only in F3. This is known as a spilled array, because the data has spilled over the active cell.

Now, when the month changes to 12 (December), Excel will automatically move this result down one cell, because the value in cell F1 will have increased by one.

I can then complete my table by typing

into cell F7, and

into cell F8.

The octothorp (#) symbol is Excel’s way of calculating a spilled array.

Things to Remember

There are two important things to remember when using OFFSET:

Personally, I use the OFFSET function to record and analyze my favorite football team’s data. After all, Excelisn’t just for accountants—you can use it at home alongside your hobbies!