WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » OFFSET Excel Function

OFFSET Excel Function

Offset in Excel

Offset function in excel is a one of the very useful worksheet function in excel which shows the range of the parts from the start point of the cell, this formula has a total of five arguments and all the arguments are mandatory, the method to use this function is as follows, =offset( reference, rows, columns, height, width), height and width refer to the referred reference.

Offset Formula in Excel

Below is the OFFSET Formula in Excel.

OFFSET Formula in Excel

The OFFSET Function in Excel has five arguments, out of which two are optional. Where,

  • reference = This is a required parameter. The reference from which the base is to be offset. It can be a cell or a range of adjacent cells.
  • rows = This is a required parameter. It can be a positive or negative number. It represents the number of rows the upper-left cell would refer to. It uses reference as the base. The rows can be above or below the reference. A positive value means below the reference, and a negative value means above the reference.
  • cols = This is a required parameter. It can be a positive or negative number. It represents the number of columns the upper-left cell would refer to. It uses reference as the base. The columns can be on the left or the right of the reference. A positive value means to the right of the reference, and a negative value means to the left of the reference.
  • Height = This is an optional parameter. The value must be positive. It is the height, in a number of rows, that the reference is to be.
  • Width = This is an optional parameter. The value must be positive. It is the width, in a number of columns, that the reference is to be.

OFFSET in Excel returns a positive numeric value.

How to use the OFFSET Function in Excel?

The said function is a Worksheet (WS) function. As a WS function, it can be entered as a part of the formula in a cell of a worksheet. Refer to the examples given below to understand better.

Let’s look at the examples given below. Each example covers a different use case implemented using the OFFSET function in Excel.

You can download this OFFSET Function Excel Template here – OFFSET Function Excel Template

Example #1 – Find out the 3rd racer from the results.

In this example, cell F4 has an OFFSET formula in excel associated with it.

OFFSET Example 1

So, F4 is a result cell.

OFFSET Example 1-1

The first argument of OFFSET in Excel is a B3, which is a reference. B3 is also the starting cell of the table. The row’s value is 2, and the column value is 1. The row that is 2 pointers below points to row number 5 and the column that is 1 pointer to the right in column C (Name). So, the resultant cell is C5. The value at C5 is nadal.

OFFSET Example 1-2

Example #2 – Value not present in the worksheet.

In this example, cell F5 has an OFFSET formula in excel associated with it.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

OFFSET Example 2

So, F5 is a result cell.

OFFSET Example 2-1

The first argument of OFFSET in Excel is a B3, which is a reference. B3 is also the starting cell of the worksheet. The row’s value is 2, and the column value is 2. The row that is 2 pointers below points to row number 5, and the column that is 2 pointers to the right is column D. So, the resultant cell is D5, but the value at D5 is not present. So, the return value is 0.

OFFSET Example 2-2

Example #3 – Invalid range reference in the worksheet.

In this example, cell F6 has an OFFSET formula in excel associated with it.

OFFSET Example 3

So, F6 is a result cell.

OFFSET Example 3-1

The first argument of OFFSET in Excel is a B3, which is a reference. B3 is also the starting cell of the worksheet. Rows value is -2, and the column value is -2. The row that is -2 pointers above points to row number 0 and the column that is -2. Both the row and the column does not exist in the worksheet. So, the resultant cell F6 contains #REF! The information icon in yellow colour shows Invalid Cell Reference Error.

OFFSET Example 3-2

OFFSET function in Excel can be combined with arithmetic functions in Excel. Let’s go through a few examples to understand the same.

Example #4 – Calculate the sum of values

In this example, cell F3 has an OFFSET formula in excel associated with it.

OFFSET Example 4

So, F3 is a result cell.

OFFSET Example 4-1

The first argument of OFFSET is a C2, which is a reference. C2 is also the starting cell of the worksheet. The row’s value is 0, and the column value is 0. The height is 5, which means 5 rows below the reference, and the width is 1, which means 1 column. The SUM function is applied to the OFFSET. So, here OFFSET Function in Excel would return the sum of all the values in the column ‘C.’ The sum of 98+92+89+88+82 = 449.

Example #5 – Calculate the average of values using OFFSET

In this example, cell F5 has an OFFSET formula in excel associated with it.

OFFSET Example 5

The first argument of OFFSET is a C2, which is a reference. C2 is also the starting cell of the worksheet. The row’s value is 0, and the column value is 0. The height is 1, which means 1 row below the reference, and the width is 1, which means 1 column. AVERAGE function is applied to the OFFSET function in Excel. So, here the OFFSET in Excel would return the average of 2 values in a row of C2, i.e., 98 and 50, which is 74.

OFFSET Example 5-1

Things to Remember

  • If an out-of-range value is provided for the rows or column offset, then the function returns #REF.

Recommended Articles

This has been a guide to OFFSET in Excel. Here we discuss the OFFSET Formula in excel and how to use the OFFSET function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • OFFSET Formula in Excel
  • OFFSET in VBA
  • FIND Function in Excel
  • Bubble Chart in Excel
  • Tally Chart in Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download OFFSET Function Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More