Support
OFFSET function
Applies To
Effortlessly highlight, filter,
and sort data with Copilot in
Excel
We're giving you a free trial of Copilot Pro.
Activate now
This article describes the formula syntax and usage of the
OFFSET function in Microsoft Excel.
Description
Returns a reference to a range that is a specified number of
rows and columns from a cell or range of cells. The reference
that is returned can be a single cell or a range of cells. You
can specify the number of rows and the number of columns
to be returned.
Syntax
OFFSET(reference, rows, cols, [height], [width])
The OFFSET function syntax has the following arguments:
Reference Required. The reference from which
you want to base the offset. Reference must refer
to a cell or range of adjacent cells; otherwise,
OFFSET returns the #VALUE! error value.
Rows Required. The number of rows, up or down,
that you want the upper-left cell to refer to. Using
5 as the rows argument specifies that the upper-
left cell in the reference is five rows below reference.
Rows can be positive (which means below the
starting reference) or negative (which means above
the starting reference).
Cols Required. The number of columns, to the left
or right, that you want the upper-left cell of the
result to refer to. Using 5 as the cols argument
specifies that the upper-left cell in the reference is
five columns to the right of reference. Cols can be
positive (which means to the right of the starting
reference) or negative (which means to the left of
the starting reference).
Height Optional. The height, in number of rows,
that you want the returned reference to be. Height
must be a positive number.
Width Optional. The width, in number of
columns, that you want the returned reference to
be. Width must be a positive number.
Remarks
If rows and cols offset reference over the edge of
the worksheet, OFFSET returns the #REF! error
value.
If height or width is omitted, it is assumed to be
the same height or width as reference.
OFFSET doesn't actually move any cells or change
the selection; it just returns a reference. OFFSET can
be used with any function expecting a reference
argument. For example, the formula
SUM(OFFSET(C2,1,2,3,1)) calculates the total value
of a 3-row by 1-column range that is 1 row below
and 2 columns to the right of cell C2.
Example
Copy the example data in the following table, and paste it in
cell A1 of a new Excel worksheet. For formulas to show
results, select them, press F2, and then press Enter. If you
need to, you can adjust the column widths to see all the
data.
Formula Description Result
=OFFSET(D3,3,-2,1,1) Displays the value 4
in cell B6 (4)
=SUM(OFFSET(D3:F5,3,-2, Sums the range 34
3, 3)) B6:D8
=OFFSET(D3, -3, -3) Returns an error, #REF!
because the
reference is to a
non-existent
range on the
worksheet.
Data Data
4 10
8 3
3 6
Need more help?
How can we help you?
Want more options?
Discover Community
Explore subscription benefits, browse training courses, learn
how to secure your device, and more.
Microsoft 365 subscription benefits
Microsoft 365 training
Microsoft security
Accessibility center
Was this information helpful?
Yes No
What's new
Surface Pro
Surface Laptop
Surface Laptop Studio 2
Surface Laptop Go 3
Microsoft Copilot
AI in Windows
Explore Microsoft products
Windows 11 apps
Microsoft Store
Account profile
Download Center
Microsoft Store support
Returns
Order tracking
Certified Refurbished
Microsoft Store Promise
Flexible Payments
Education
Microsoft in education
Devices for education
Microsoft Teams for Education
Microsoft 365 Education
How to buy for your school
Educator training and development
Deals for students and parents
Azure for students
Business
Microsoft Cloud
Microsoft Security
Dynamics 365
Microsoft 365
Microsoft Power Platform
Microsoft Teams
Microsoft 365 Copilot
Small Business
Developer & IT
Azure
Developer Center
Documentation
Microsoft Learn
Microsoft Tech Community
Azure Marketplace
AppSource
Visual Studio
Company
Careers
About Microsoft
Company news
Privacy at Microsoft
Investors
Diversity and inclusion
Accessibility
Sustainability
English (United States)
Your Privacy Choices
Consumer Health Privacy
Sitemap Contact Microsoft Privacy Terms of use Trademarks
Safety & eco Recycling About our ads © Microsoft 2024
