OFFSET Function In Excel

This Excel Tutorial demonstrates how to use the Excel OFFSET Function in Excel to create a reference offset from an initial cell, with formula examples.

OFFSET Function Description:

The OFFSET Function Starts with a defined cell reference and returns a cell reference a specificed number of rows and columns offset from the original referece. References can be one cell or a range of cells. Offset also allows you to resize the reference a given number of rows/columns.

Formula Examples:

offset function examples

Example Formula Result
Simple Offset =OFFSET($B$4,F5,G5,H5,I5) 7
Resize Columns =OFFSET($B$4,F6,G6,H6,I6) 15
Resize Rows and Columns =OFFSET($B$4,F6,G6,H6,I6) 24

Syntax and Arguments:

The Syntax for the OFFSET Formula is:

Function Arguments ( Inputs ):

reference – The initial cell reference from which you want to offset.

rows – The number of rows to offset.

cols – The number of columns to offset.

height – OPTIONAL: Adjust the number of rows in the reference.

width – OPTIONAL: Adjust the number of columns in the reference.

Additional Notes

Use the OFFSET Function to return a cell value (or a range of cells) by offsetting a given number of rows and columns from a starting reference. When looking only for a single cell, OFFSET formulas achieve the same purpose as the INDEX Formulas, using a slightly different technique. The real power of the OFFSET Function lies in its ability to select a range of cells to be used in another formula.

When using the OFFSET Function, you define an initial starting cell or range of cells. Then you indicate the number of rows and columns to offset from that initial cell. You can also resize the range; add or subtract rows or columns.

Return to the List of all Functions in Excel

How to use the OFFSET Function in Excel:

To use the AND Excel Worksheet Function, type the following into a cell:
After entering it in the cell, notice how the AND formula inputs appear below the cell:
offset formula syntax
You will need to enter these inputs into the function. The function inputs are covered in more detail in the next section. However, if you ever need more help with the function, after typing “=OFFSET(” into a cell, without leaving the cell, use the shortcut CTRL + A (A for Arguments) to open the “Insert Function Dialog Box” for detailed instructions:
how to use the offset function in excel

For more information about the OFFSET Formula visit the
Microsoft Website.


Posted in