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.

Click here to see how the formula works visually & interactively 

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.

This is what I am going to show you in this tutorial below:

DOWNLOAD EXCEL WORKBOOK

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

1st argumenmt

 

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)

2nd argument

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,

2nd argument less 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,

4th argument

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))

5th argument

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

offset answer

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 🙂

offset answer 2

Offset - Last Value in a Column

HELPFUL RESOURCE:

728x90-2

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

VLOOKUP Multiple Columns What does it do?Searches for a value in the first column of a table array and returns the sum of values in the same row from other columns (to the right) in the table array.Formula breakdown:{=SUM(VLOOKUP(lookup_value, table_array, {col_index_num1,col_index_num2}, ))}...
SUMIFS Function: Introduction The SUMIFS function allows you to Sum multiple criteria.For example, you can select one Sales Rep from a list of Sales Reps and select one Region from a list of Regions and return the Sum of those arguments from a Sales list.  See how easy it is...DOWNLOAD WORKBOOK...
How To Use INDEX-MATCH Formula The VLOOKUP formula searches for a value in the first column of an array and returns a value to the right of that array.How about if you wanted to return a value to the left hand side of that array?Well, this is where the INDEX-MATCH formula comes in and gives you a helpi...
Free Excel Formulas & Functions Webinar Train... In this popular Free Excel Formulas & Functions Webinar Training which goes for 60 minutes you will learn the MUST KNOW Excel Formulas to ADVANCE your Excel skills!You will learn the following Excel Formulas & Functions: IF, SUMIF, VLOOKUP, INDEX/MATCH + MORE...!P...