All You Need to Know About

# OFFSET Formula in Excel

The OFFSET Function in Excel has a lot of unique uses. It may not seem natural at first on how to use this but I will show you a lot of creative ways to maximize the power of the Excel OFFSET Formula!

Here are the top things on what you can do with the Excel Offset Formula:

## Excel OFFSET Formula Introduced

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.

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.

## Sum the Last 7 Transactions with the Offset Function

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. Â For example, if we have daily sales going down one row per day, then we can Sum the last X transactions.

STEP 1: We need to enter the Sum function as we are going to Sum the last 7 transactions
(We can also add the Average function if we want to show the Average of the last 7 transactions):

## =SUM

STEP 2: We need to enter the Offset function so we can reference the range that we want to Sum:

## =SUM(OFFSET

STEP 3: 1st Offset argument – Where do we want to to start our reference?
This is at the start of the Daily Sales list:

## =SUM(OFFSET(D11

STEP 4: 2nd Offset argument – How many Rows down do we want to go?
This is where the COUNTA function is used to count how many transactions we have in total and go down to the last cell in our list.
So as we are starting at cell D11, we are going to go down 11 cells ( COUNTA(D11:D100) ) and end up afterÂ the last cell with a value.
NB: It is always a good idea to enter an ending range that is more than your last data cell. Â That way as your new data get entered, we will be sure to capture it!

## =SUM(OFFSET(D11, COUNTA(D11:D100)

STEP 5:Â How many of the last sales transactions do we want to Sum? Â 7
So we need to take away 7 from the COUNTA formula which means that our Offset function will go upÂ by 7 cells:

## =SUM(OFFSET(D11, COUNTA(D11:D100)-7,

STEP 6: 3rd Offset argument – How many Columns to the right/left do we want to move?
We do not want to move to any Columns, so we simply enter 0

## =SUM(OFFSET(D11, COUNTA(D11:D100)-7,0,

STEP 7: 4th Offset argument – How High do we want our referenced data to be?
Since we want to Sum 7 transactions, then we need the [height] to be 7 cells high:

## =SUM(OFFSET(D11, COUNTA(D11:D100)-7,0,7,

STEP 8: 5th Offset argument – How WideÂ do we want our referenced data to be?

We want to reference 1 Column only, so we need to enter 1 for the [width]

## =SUM(OFFSET(D11, COUNTA(D11:D100)-7,0,7,1))

As we can see the Sum of the last 7 transactions from our example is \$28,472.

If we add more transactions at the bottom of our Sales list then it it automatically return us the last 7 transactions, without the need to update the formula :)

## Return the Last Value in a Column with the Offset Function

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.
Sometimes we have a list that we enter our daily sales into and want to showÂ the last transactionalÂ value entered. Â This can be used to highlightÂ the latest sales value in aÂ Dashboard or daily metrics report.

STEP 1: We need to enter the OffsetÂ function

## =OFFSET

STEP 2: 1st Offset argument – Where do we want to to start our reference?
This is at the start of the Daily Sales list:

## =OFFSET(D11

STEP 3: 2nd Offset argument – How many Rows down do we want to go?
This is where the COUNT function is used to count how many transactions we have in our listÂ and go down by that amount.
So as we are starting at cell D11, we are going to go down 11 cells ( COUNTA(D11:D1000) ) and we end up afterÂ the last cell with a value.
NB: It is always a good idea to enter an ending range that is more than your last data cell. Â That way as your new data get entered, we will be sure to capture it!

## =OFFSET(D11, COUNT(D11:D1000)

STEP 4:Â How many of the last sales transactions do we want to Sum?Â Â 1
So we need to take away 1Â from the COUNT formula which means that our Offset function will go upÂ by 1Â cell:

## =OFFSET(D11, COUNT(D11:D1000)-1,

STEP 5: 3rd Offset argument – How many Columns to the right/left do we want to move?
We do not want to move to any Columns, so we simply enter 0

## =OFFSET(D11, COUNT(D11:D1000)-1,0,

STEP 6: 4th Offset argument – How High do we want our referenced data to be?
Since we want to show the lastÂ transaction, then we need the [height] to be 1Â cells high:

## =OFFSET(D11, COUNT(D11:D1000)-1,0,1,

STEP 7: 5th Offset argument – How WideÂ do we want our referenced data to be?

We want to reference 1 Column only, so we need to enter 1 for the [width]

## =OFFSET(D11, COUNT(D11:D1000)-1,0,1,1))

As we can see the Sum of the last transaction from our example is \$6,810.

If we add more transactions at the bottom of our Sales list then it it automatically return us the last transaction, without the need to update the formula :)

Latest Tutorials

## How to use OFFSET Function in Excel

September 25, 2023

April 1, 2021

March 16, 2021

May 4, 2016

March 7, 2016