EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Lookup & Reference Functions in Excel Excel OFFSET Formula
Secondary Sidebar
Excel Functions
  • Lookup and Reference Functions in Excel
    • VLOOKUP Function in EXCEL
    • VLOOKUP True
    • VLOOKUP Error
    • How to Match Data in Excel
    • Excel Match Function
    • Excel Lookup Function
    • ROWS Function in Excel
    • Excel INDEX Function
    • VLOOKUP Table Array
    • Excel OFFSET Formula
    • VLOOKUP For Text
    • IF VLOOKUP Formula in Excel
    • Mixed Reference in Excel
    • CHOOSE Formula in Excel
    • Excel COLUMN to Number
    • Excel Alternatives to VLOOKUP
    • HLOOKUP Examples
    • Excel VLOOKUP From Another Sheet
    • VLOOKUP with Sum
    • Fixing VLOOKUP Errors
    • Excel ROW Function
    • HYPERLINK in Excel
    • Address Excel Function
    • Excel COLUMNS Function
    • Excel REPLACE Function
    • OFFSET Excel Function
    • Excel GETPIVOTDATA Function
    • MATCH Function in Excel
    • VLOOKUP Function in Excel
    • HLOOKUP Function in Excel
    • LOOKUP in Excel
    • CHOOSE Function in Excel
    • TRANSPOSE in Excel
    • COLUMN Function in Excel
    • INDIRECT Function in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Online EXCEL COURSE
  • Online EXCEL ADVANCED Training

Excel OFFSET Formula

By Madhuri ThakurMadhuri Thakur

OFFSET Formula in Excel

Excel OFFSET Formula (Table of Contents)

  • What is OFFSET Formula in Excel?
  • How to Use OFFSET formula in Excel?

What is OFFSET Formula in Excel?

OFFSET Formula in Excel gives a cell or a range of dynamic rectangular cells as an output, a subset of the total given range with a specified number of rows, columns, height, and width.

Syntax of OFFSET Formula in Excel

OFFSET Formula Syntax

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Explanation of OFFSET Function in Excel:

  • reference: The starting point for OFFSET, or we can say a range of cells that can be considered the OFFSET base.
  • rows: The number of rows to OFFSET below or above the base reference.
  • cols: The number of columns to OFFSET to the right or left of the base reference.
  • height: Number of rows in returned reference.
  • width: Number of columns in returned reference.

The height and width are the optional parameters for this function and can be used to determine the size of the reference created.

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,596 ratings)

How to Use OFFSET formula in Excel?

We will now learn how to write the offset formula to get the lookup value. Let’s understand the offset formula with some examples in Excel.

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

Excel OFFSET Formula – Example #1

Suppose we have data as given below.

OFFSET Formula Example 1-1

  • In cell E2, input the following formula:

=OFFSET(A1, 11, 1)

OFFSET Formula Example 1-2

  • Once done with the formula, press Enter and see the output.

press Enter and see the output

What this formula did is it has taken reference from cell A1 (Year) and moved down by 11 rows after A1 (i.e. row no. 12) and then gained the value which is coordinate of the 11th row after A1 and first column (column A = 0 column B = 1 in this case. As we know, we can go both right and left), which is $ 141.27 Million.

Excel OFFSET Formula – Example #2

Now we will see another example of a reference cell for a specific year. Put the following formula in cell E3:

=OFFSET(A1, 11, 0)

OFFSET Formula Example 2-1

  • Once done with the formula, press Enter and see the output.

OFFSET Formula Example 2-2

If you have seen the formula, it is the same as of previous; the only change is in column reference. The column reference 0 (Zero) means the value will be captured from column A for the 11th row after A1 (which is 2010).

Excel OFFSET Formula – Example #3

We will calculate the Sum of the Last N rows using the Excel OFFSET Function in this example. The general formula for summing up the last N numbers is:

=SUM(OFFSET(A1,COUNT(A:A), 0, -N))

To sum up the last N observations from your data, you can use the SUM function along with the OFFSET and COUNT function together. In this example, we will sum up the last 5 data points. Please note that when we say N, it’s a generalization of any integer value. So, you can calculate for the last 3, last 4, last 5, etc., depending on your dataset as well as your requirement.

Let’s have it done for our data set:

  • Input the following formula in cell E2 of the excel file.

=SUM(OFFSET(B1,COUNT(B:B), 0, -5))

OFFSET Formula Example 3-1

  • Click Enter to see the output.

OFFSET Formula Example 3-2

As we have already seen, the Excel OFFSET function outputs rectangular dynamic ranges when given a starting reference.

  • In this case, the starting reference is provided in terms of B1 (as a first argument to the OFFSET function).
  • We used the COUNT function because we need the sum of observations from the last value. COUNT Function helps OFFSET to determine how many observations (rows) are there in column B. It counts all the numeric values in column B (19 is the count).
  • With a count of 19, the OFFSET formula becomes:

=OFFSET(B1, 19, 0, -5)

  • This formula starts Offsetting at B1 with 19 rows and the same column (0). Then height parameter -5 helps this function to extend the range into a backward direction by 5 rows (see the negative sign associated with 5).
  • The dynamic range of the Last five observations is given as an input to the sum function, which yields in summing those from column B.

Excel OFFSET Formula – Example #4

In this example, we will calculate the Median Sales for the Last 3 Years. OFFSET function can be used as an argument to calculate the same.

  • Put the following formula in cell E2:

=MEDIAN(OFFSET(B1,COUNT(B:B), 0, -3))

Example 4-1

  • Press Enter to see the output.

Example 4-2

This formula works on lines similar to the previous one.

  • OFFSET Creates a dynamic range of the last three observations from column B with the help of the COUNT function (=OFFSET(A1, 19, 0, -3)).
  • This can be given as an output to the MEDIAN function. Which then sorts the observations in either ascending or descending order and then selects the middlemost value as you see in output ($ 212.07).

Excel OFFSET Formula – Example #5

Here we will calculate the median year using MEDIAN and OFFSET Function.

  • Use the following formula for the same in cell E3:

=MEDIAN(OFFSET(A1,COUNT(A:A),0,-3))

Example 5-1

  • Click Enter to see the output.

Example 5-2

The same thing has happened here. The formula took up a dynamic range created by the OFFSET function with the help of COUNT. And used it as an argument to a MEDIAN function which then sorted the year values in ascending or descending order and then given output as 2017.

Usage of the OFFSET formula is in creating the dynamic ranges. It’s always better to work on dynamic ranges instead of static ones, as you might be having data that gets added every now and then. For example, Employee Master. Your Employee Master Data will always be getting increased every now and then as some new employees join. Every time using the same formula does not make any sense. Instead of that, create a range that is dynamic using the OFFSET function and see the magic. It will certainly save you time.

Things to Remember

  • OFFSET function only returns a reference to a particular range of cells in Excel. It does not move the cells from their original positions.
  • By default, this function works on the upper part (in terms of rows) and right-hand side (in terms of columns). However, it can be provided with negative arguments to reverse the default functionality. A negative row argument helps offset the data from down to up, and a negative col argument helps offset the data to the left.
  • OFFSET formula may slow down the Excel functionality as it is a formula that gets recalculated every time there is a change in the worksheet.
  • #REF error will be thrown if your range is outside the range of your workbook.
  • Any other function which expects a reference is able to use the OFFSET function within it.

Recommended Articles

This is a guide to the OFFSET Formula in Excel. Here we discuss how to use OFFSET Function in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. OFFSET Excel Function
  2. VBA OFFSET
  3. Excel Median Formula
  4. Write Formula in Excel
Popular Course in this category
Excel Training (23 Courses, 9+ Projects)
  23 Online Courses |  9 Hands-on Projects |  110+ Hours |  Verifiable Certificate of Completion
4.9
Price

View Course

Related Courses

Excel Advanced Training (16 Courses, 23+ Projects)4.9
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download OFFSET Formula Excel Template

EDUCBA

Download OFFSET Formula Excel Template

EDUCBA

डाउनलोड OFFSET Formula Excel Template

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more