exceljet.net Open in urlscan Pro
2600:3c00::f03c:91ff:fee7:ea6e  Public Scan

Form analysis
4 forms found in the DOM

GET /search

<form class="search-form" action="/search" method="get" id="header-search" accept-charset="UTF-8">
  <input type="text" name="keys" value="" placeholder="Search" size="15" maxlength="128" class="form-text search-text">
  <input type="submit" name="op" value="Search" class="form-submit search-submit">
</form>

GET /search

<form class="search-form" action="/search" method="get" id="header-search" accept-charset="UTF-8">
  <input type="text" name="keys" value="" placeholder="Search" size="15" maxlength="128" class="form-text search-text">
  <input type="submit" name="op" value="Search" class="form-submit search-submit">
</form>

POST /antibot

<form class="webform-client-form-3946 webform-client-form webform-client-form webform-client-form-3946 antibot" action="/antibot" method="post" id="webform-client-form-3946" accept-charset="UTF-8">
  <div>
    <div class="webform-progressbar">
    </div>
    <div class="form-item webform-component webform-component-textfield webform-component--name">
      <label for="edit-submitted-name">Name </label>
      <input placeholder="Your name" type="text" id="edit-submitted-name" name="submitted[name]" value="" size="60" maxlength="128" class="form-text">
    </div>
    <div class="form-item webform-component webform-component-email webform-component--email">
      <label for="edit-submitted-email">Email </label>
      <input class="email form-text form-email" placeholder="email@example.com" type="email" id="edit-submitted-email" name="submitted[email]" size="60">
    </div>
    <div class="form-item webform-component webform-component-textarea webform-component--problem">
      <label for="edit-submitted-problem">Problem <abbr class="form-required" title="This field is required.">*</abbr></label>
      <div class="form-textarea-wrapper resizable textarea-processed resizable-textarea"><textarea required="required" placeholder="Describe the problem" id="edit-submitted-problem" name="submitted[problem]" rows="5" cols="60"
          class="form-textarea required"></textarea>
        <div class="grippie"></div>
      </div>
    </div>
    <div class="form-item webform-component webform-component-markup webform-component--email-message">
      <p>Your email address is private and not shared.</p>
    </div>
    <input type="hidden" name="details[sid]">
    <input type="hidden" name="details[page_num]" value="1">
    <input type="hidden" name="details[page_count]" value="1">
    <input type="hidden" name="details[finished]" value="0">
    <input type="hidden" name="form_build_id" value="form-f0D4-Rt5bqlYU_Vhkls3Rks5CqNauSQG4DoC-Pt-WC4">
    <input type="hidden" name="form_id" value="webform_client_form_3946">
    <input type="hidden" name="antibot_key" value="">
    <div class="form-actions"><input class="webform-submit button-primary button-primary form-submit ajax-processed" type="submit" id="edit-submit" name="op" value="Submit"></div>
  </div>
</form>

POST https://sendy.exceljet.net/subscribe

<form id="newsletter_signup_footer" class="newsletter-signup-form newsletter-signup-footer-form" action="https://sendy.exceljet.net/subscribe" method="POST" accept-charset="utf-8">
  <label for="email" hidden="">Email</label>
  <input type="email" name="email" placeholder="email address" id="newsletter-signup-footer-email" class="newsletter-signup-footer-email newsletter-signup-email text-input" required="">
  <div style="display:none;">
    <label for="hp">HP</label>
    <input type="text" name="hp" id="newsletter-signup-footer-hp">
  </div>
  <input type="hidden" name="SignupLocation" id="newsletter-signup-footer-SignupLocation" value="footer">
  <input type="hidden" name="list" value="T1JX0z264YewQdKf7gXIig">
  <input type="hidden" name="subform" value="yes">
  <input type="submit" name="submit" id="newsletter-signup-footer-submit" class="newsletter-signup-footer-submit form-submit" value="Subscribe">
</form>

Text Content

Skip to main content
 * Login

Cart
Exceljet
 * Training
 * Videos
 * Functions
 * Formulas
 * Shortcuts
 * Articles


Cart
 * Login

Main menu toggle
 * Training
 * Videos
 * Functions
 * Formulas
 * Shortcuts
 * Articles

 * Login




YOU ARE HERE

Home › Functions › OFFSET Function
 * Previous
 * Next

Excel 2003
Lookup and reference


OFFSET FUNCTION


RELATED FUNCTIONS 

INDEX



SUMMARY

The Excel OFFSET function returns a reference to a range constructed with five
inputs: (1) a starting point, (2) a row offset, (3) a column offset, (4) a
height in rows, (5) a width in columns. OFFSET is handy in formulas that require
a dynamic range.

Powered By

10



XLOOKUP with multiple lookup values


Share

Next
Stay






PURPOSE 

Create a reference offset from given starting point


RETURN VALUE 

A cell reference.


ARGUMENTS 

 * reference - The starting point, supplied as a cell reference or range.
 * rows - The number of rows to offset below the starting reference.
 * cols - The number of columns to offset to the right of the starting
   reference.
 * height - [optional] The height in rows of the returned reference.
 * width - [optional] The width in columns of the returned reference.


SYNTAX 

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


USAGE NOTES 

The Excel OFFSET function returns a dynamic range constructed with five inputs:
(1) a starting point, (2) a row offset, (3) a column offset, (4) a height in
rows, (5) a width in columns.

OFFSET is a volatile function, and can cause performance issues in large or
complex worksheets.

The starting point (the reference argument) can be one cell or a range of cells.
The rows and cols arguments are the number of cells to "offset" from the
starting point. The height and width arguments are optional and determine the
size of the range that is created. When height and width are omitted, they
default to the height and width of reference.

For example, to reference C5 starting at A1,  reference is A1, rows is 4 and
cols is 2:

=OFFSET(A1,4,2) // returns reference to C5

To reference C1:C5 from A1, reference is A1, rows is 0, cols is 2, height is 5,
and width is 1:

=OFFSET(A1,0,2,5,1) // returns reference to C1:C5

Note: width could be omitted, since it will default to 1.

It is common to see OFFSET wrapped in another function that expects a range. For
example, to SUM C1:C5, beginning at A1:

=SUM(OFFSET(A1,0,2,5,1)) // SUM C1:C5

The main purpose of OFFSET is to allow formulas to dynamically adjust to
available data or to user input. The OFFSET function can be used to build a
dynamic named range for charts or pivot tables, to ensure that source data is
always up to date.

Note: Excel documentation states height and width can't be negative,
but negative values appear to have worked fine since the early 1990's. The
OFFSET function in Google Sheets won't allow a negative value for height or
width arguments.


EXAMPLES

The examples below show how OFFSET can be configured to return different kinds
of ranges. These screens were taken with Excel 365, so OFFSET returns a dynamic
array when the result is more than one cell. In older versions of Excel, you can
use the F9 key to check results returned from OFFSET.


EXAMPLE #1

In the screen below, we use OFFSET to return the third value (March) in the
second column (West). The formula in H4 is:

=OFFSET(B3,3,2) // returns D6




EXAMPLE #2

In the screen below, we use OFFSET to return the last value (June) in the third
column (North). The formula in H4 is:

=OFFSET(B3,6,3) // returns E9




EXAMPLE #3

Below, we use OFFSET to return all values in the third column (North). The
formula in H4 is:

=OFFSET(B3,1,3,6) // returns E4:E9




EXAMPLE #4

Below, we use OFFSET to return all values for May (fifth row). The formula in H4
is:

=OFFSET(B3,5,1,1,4) // returns C8:F8




EXAMPLE #5

Below, we use OFFSET to return April, May, and June values for the West region.
The formula in H4 is:

=OFFSET(B3,4,2,3,1) // returns D7:D9




EXAMPLE #6

Below, we use OFFSET to return April, May, and June values for West and North.
The formula in H4 is:

=OFFSET(B3,4,2,3,2) // returns D7:E9




NOTES

 * OFFSET only returns a reference, no cells are moved.
 * Both rows and cols can be supplied as negative numbers to reverse their
   normal offset direction - negative cols offset to the left, and negative rows
   offset above.
 * OFFSET is a "volatile function". Volatile functions can make larger and more
   complex workbooks run slowly.
 * OFFSET will display the #REF! error value if the offset is outside the edge
   of the worksheet.
 * When height or width is omitted, the height and width of reference is used.
 * OFFSET can be used with any other function that expects to receive a
   reference.
 * Excel documentation says height and width can't be negative, but negative
   values do work.



Author


DAVE BRUNS

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help
you work faster in Excel. We create short videos, and clear examples of
formulas, functions, pivot tables, conditional formatting, and charts.


RELATED INFORMATION


FORMULAS

 * Sum every 3 cells
 * Average last n columns
 * Unwrap column into fields
 * Copy value from every nth row
 * Define range based on cell value
 * Moving average formula
 * Sum last n columns
 * Count visible rows with criteria
 * Copy value from every nth column
 * COUNTIFS with variable range


VIDEOS

 * How to create a dynamic named range with OFFSET


FUNCTIONS

 * INDEX Function


LINKS

 * Microsoft OFFSET function documentation


DOWNLOAD 100+ IMPORTANT EXCEL FUNCTIONS

Get PDF Guide


TOPICS

 * Formula Basics
 * Formula Examples
 * Conditional Formatting
 * Pivot Tables
 * Excel Tables
 * Excel Charts
 * Shortcuts
 * More...


KEY FUNCTIONS

 * IF function
 * VLOOKUP function
 * XLOOKUP function
 * FILTER function
 * SUMIFS function
 * COUNTIFS function
 * SUMPRODUCT function
 * INDEX and MATCH
 * 350 Excel Functions


I absolutely LUV your explanations and examples! You have saved me a lot of time
and frustration. Thank you, thank you, thank you.
Katalin
More Testimonials


GET TRAINING


QUICK, CLEAN, AND TO THE POINT TRAINING

Learn Excel with high quality video training. Our videos are quick, clean, and
to the point, so you can learn Excel in less time, and easily review key topics
when needed. Each video comes with its own practice worksheet.

View Paid Training & Bundles

Report a problem with this page


HELP US IMPROVE EXCELJET

You must have JavaScript enabled to use this form.
Name
Email
Problem *


Your email address is private and not shared.


Exceljet
Quick, clean, and to the point


RESOURCES

 * Training
 * Videos
 * Functions
 * Formulas
 * Shortcuts
 * Articles


ABOUT US

 * About
 * Testimonials
 * Topics
 * Contact


NEWSLETTER SIGN-UP

Work faster in Excel. Join more than 100,000 people who get weekly tips from us.
Email
HP
© 2012-2023 Exceljet. Terms of use
 * Visit our Twitter page