Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to use the OFFSET function to return a specific range within dataset. Let’s get started.
SAMPLE DATA
The screenshot below shows the sample data with six columns and 22 rows
OFFSET FUNCTION
The OFFSET Function 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.
Syntax
OFFSET(reference, rows, cols, [height], [width])
DEMO: Return a Specific Range
We want to return the first 5 rows from the dataset using the OFFSET Function
- To do that, in cell H2, execute the formula: =OFFSET(A1,1,0,5,6)
- Click Enter. The formula will spill to neighbouring columns and rows respectively being an array formula
Based on the above formula, the first argument of the reference function is reference which is represented by cell A1. Next, since we wanna return all the rows from the starting row, I specified 1 as the rows argument. Additionally, I specified 0 as the input parameter for the columns argument because we want to start from the first column. Next, I specified 5 as the height and 6 as the width. These would return 5 rows and 6 columns
From the screenshot below, we can see that we have been able to successfully returned the top 5 rows
See you in the next tutorial