OFFSET Function in Excel: Most Useful Built-in-Functions in Excel.

It returns a range given to the no. of Rows and Columns.

The User gives the Row, Column and Reference to a range and wherever the Row and Columns meet, the Offset Formula in Excel will give the Output based on that.

What is an OFFSET Function in Excel?

The Range returned by an OFFSET Function in Excel is a Single Cell. It can even be a Range of Multiple Adjacent Cells.

OFFSET Function in Excel works like VLOOKUP. It gives you the Output through the Reference.

And when it returns the Value, OFFSET Formula specifies the Size which you insert as the number of Rows and Columns.

Offset Function Excel is not mainly used to find Independent Values. You can find Dynamic Range of Values with OFFSET Function.

When you’re working on Dashboards, KPIs and Various Charts, OFFSET Function and OFFSET with Match would be really helpful.

How does an OFFSET Function Work in Excel?

Situation: In the given example, you’ll be able to see the list of Departments such as Sales, Marketing, Finance Respectively. It is Followed by the no. of Employees that were Present in each Department in a Particular Month.

No. of Months: January to May

Problem: You need to find out:

  • The SUM of Last 3 Rows of March with OFFSET Function
  • The Average of last 4 Column of Department ‘Data’ with OFFSET Function

How will you find the Values?

Solution: OFFSET Formula and OFFSET Match will help you get this in minutes

Important: Arguments in OFFSET Formula:

Reference- The starting point Supplied as Cell Reference. It refers to a cell or a range of cells on a worksheet. Use it in a formula so that you can find the values or data that you want the formula to calculate.

rows- The number of rows you need to calculate after applying OFFSET Function, from the point below the reference

cols-  The number of Columns you need to calculate after applying OFFSET Function, from the point i.e. right to the reference

height- The required height of the Rows that is to be returned in the Reference

width- The required width of the Columns that is to be returned in the Reference

🠊 OFFSET Formula to find the SUM of Last 3 Rows:

Enter Sum formula ahead of Offset Formula

The Syntax:

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

  • Type =OFFSET,
    in the reference: Select Department i.e. A1,
    Insert COUNTA with OFFSET before Rows.
    Select the Counta Values i.e. D1 to D6 for
    rows: Type ‘-3’
    cols: Type ‘3’
    Height: Type ‘3’
    Width: Type ‘1’
    The OFFSET Formula will look like “=OFFSET(A1,COUNTA(D1:D6)-3,3,3,1)”

NOTE1: The Column/Row Count for OFFSET Formula starts with 0.

NOTE2: Counta with OFFSET Function will Count the Value that we will add in the Non-Blank Cells.

In our Example Branding and May are the Respective Row and Column that we have left Blank.

We have selected Row Value as 3 as we want the SUM of 3 Row Values
We have selected Column Value as 3 as the Count from Department to March is 0-3

This will give you a #VALUE! ERROR

Notice: Now, this is the mistake which everyone makes.

Wrong Offset Formula

WRONG “=OFFSET(A1,COUNTA(D1:D6)-3,3,3,1)”

“You need to insert a Formula for SUM before OFFSET”.

  • Insert the SUM Formula before Offset Formula “=SUM(OFFSET(A1,COUNTA(D1:D6)-3,3,3,1))”

Now the OFFSET Formula in Excel is going to Look like:Enter Sum formula ahead of Offset Formula

RIGHT: “=SUM(OFFSET(A1,COUNTA(D1:D6)-3,3,3,1))”

This will give you the Value 115.

(115 is the SUM of Last 3 Rows of March)

  • Add in 50 in the last Column of MARCHe. the Employees for Branding in the Month of March.

The SUM instantly changes to 125

As now, The OFFSET Function returns the Changed SUM value which is (40+35+50)

Isn’t it Magical?

This is the benefit of using Counta Function with Offset Function in Excel.

Let us check, how to find:

🠊 OFFSET Function to find The Average of last 4 Columns of the Department ‘DATA’:

Average of last 4 rows

The Syntax:

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

  • Type =AVERAGE, Select OFFSET inside the Bracket,

in the reference: Select Department i.e. A1,

For rows: Type ‘4’. Insert the COUNTA with OFFSET before Columns)

Select the Counta Values i.e. A5 to F5. Type ‘-4’ (as we are counting 4 Columns)

For Height: Type ‘1’

For Width: Type ‘4’

NOTE1: The Column/Row Count for OFFSET Function starts with 0.
NOTE2: Counta with OFFSET will Count the Value that we will add in the Non-Blank Cells.

We have selected Row Value as 4 as the Count from Department to April is 0-4

Add value in the last row

We have selected Column Value as 4 as we need the Average of 4 Columns with Offset Function

Now the OFFSET Formula in Excel is going to Look like:

=AVERAGE(OFFSET(A1,4,COUNTA(A5:F5)-4,1,4))

This will give you the Value 30.5

(30.5 is the Average of last 4 Columns of DATA)

  • Add in 40 in the last Row of DATA e. the Employees for DATA in the Month of May.

The SUM instantly changes to 35.5

As now, The OFFSET Function returns the Changed Average value which is (30+35+37+40) i.e. sum function

The value changes instantly as we have used the Counta Function with Offset Function in Excel.

The syntax of the OFFSET function

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

You can see there are five possible arguments, two of them optional. You can see there are five possible arguments, two of them optional.

reference – This required argument is the cell or range of adjacent cells we wish to offset our result from. If not a single cell, this argument must refer to an adjacent range of cells or it will return the #VALUE error.

rows – This required argument tells Excel the number of rows to move up or down from the ‘reference’ argument value. Positive numbers are downward from the reference. Negative numbers are upward from the reference. OFFSET will return the #REF error if this argument is greater than the number of rows available.

cols – This argument, also required, tells Excel the number of rows to move to the left or right from the reference argument value. Positive numbers are to the right while negative numbers are to the left of the reference value. OFFSET will return the #REF error if this argument is greater than the number of columns available.

[height] – This is an optional argument that allows you to specify the size of the returned range in number of rows. If omitted, the formula returns the default size of a single row.

[width] – This is also an optional argument. It allows you to specify the size of the returned range in number of columns. If omitted, the formula returns the default size of a single column.

Pivot Tables (Complete Guidelines)

INTRODUCTION TO PIVOT TABLES When you have a large amount of data in your worksheet and you can’t analyze or sort the data then Pivot Table helps you to carry out the task easily and more conveniently. Pivot Tables are powerful functions in excel.  We are going to start with what is Pivot Table and...
Data Table with Duplicate Value Power BI DAX Deduplication

Power Bi Dax Deduplication Based On Column

To do deduplication based on column or to eliminate the duplicate data from a data set, Power BI has a weapon called DAX. Data Analysis Expressions (DAX) is a library of functions and operators that can be united to create formulas and expressions in Power BI Desktop. We can deduplicate any data based on a column...

Special Character Symbol List with Shortcodes in Excel

To activate the special character, you need to use an Alt keyboard sequence: Num Lock key must be pressed, to activate the numeric key section of the keyboard (you can find on right top corner side). Press Alt key, and hold. While the Alt key is pressed, type the sequence of numbers (on the numeric keypad) from the Alt...

Pre-Registration Open

Power BI Dax

Related Tutorials

Text To Column in Excel | Split Cells
January 4, 2019
Pivot Tables (Complete Guidelines)
December 27, 2018
Power Bi Dax Deduplication Based On Column
December 17, 2018
Special Character Symbol List with Shortcodes in Excel
December 15, 2018
How to use AVERAGEIFS FUNCTION in Excel
December 10, 2018
How to use AVERAGEIF FUNCTION
December 10, 2018
How to use ABS Function in Excel
December 10, 2018
How to use AND Function in Excel
December 10, 2018
Delete Duplicate in Excel or Remove Duplicate in Excel
November 9, 2018
Excel Formulas PDF
September 6, 2018
How To Lock Cells in Excel | Unprotect Excel
August 13, 2018
4x Faster at Excel
August 6, 2018
Separate Content of One Excel Cells into Separate Columns
August 3, 2018
How to Transpose Excel Columns to Rows | Paste Special Method
July 26, 2018
How to create sparklines in Excel
July 19, 2018
AutoSum in Excel with Shortcut
July 17, 2018
Strikethrough Shortcut in Excel & Word
July 4, 2018
INDIRECT Function with SUM, MAX, MIN & Independent Cell Value
June 29, 2018
Pivot Table Slicers In Excel
June 12, 2018
How to Wrap Text in Excel Automatically and Manually
June 6, 2018
How to Hide/Unhide Column in Excel
June 5, 2018
Highlight row based on cell value
June 4, 2018
Learn how to remove blank cells in Excel
June 3, 2018
How to Group Numbers, Dates & Text in Pivot table in Excel
June 1, 2018
5 Powerful Tricks to Format cells in Excel
May 31, 2018