Search

# Using Offset Function in Excel

Offset returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference returned can be a single cell or a range of cells.

Let's first see the formula in detail and then try its practical usage. Offset function has the following parameters.

=offset(reference, rows, columns, [height], [width])

Let's see each parameter in detail

Reference: This is a range or a cell which would be the starting point.

Row: This is the number of rows up/down away from the starting point.

Column: This is the number of columns left/right away from the starting point.

[height]: The number of rows to be considered.

[Width]: The number of columns to be considered (generally it is 1)

Now let's use the below table to understand how to use basic offset function. In the table below we are sure that our data starts in cell A1.

I want to have the salary of 3rd employee. In this case its 'Sam' and his salary is 3000.

I want the result in cell A11.

Now this is a very basic use of offset and one can argue that a direct reference would work without using 'offset' something like this in cell A11: =D4

As said, this is just to understand how Offset works and we can see its actual use in couple of more examples which would follow.

So to get the salary of 3rd employee where data starts from A1, I would use offset function and tell it to start in Cell A1 (reference), move 3 rows down (rows), move 3 columns right (columns), only 1 cell to be considered so the next 2 parameters are 1 (height) and 1 (width).

If the formula is written in A11 it would be something like the below.

This would result in the answer as 3000

Now let's expand the requirement. We need the sum of salaries of Sam and Carl who are 3 rows and 4 rows away from cell A1.

So to get the sum using offset the parameters inside the sum function would be Cell A1 (reference), move 3 rows down (rows), move 3 columns right (columns), 2 cells to be considered so the height is 2 and width is 1.

If the formula is written in cell A11 it would be something like the below.

This would result in the answer as 5000

Taking a step forward in the next post we will see how to use offset function to get YTD (Year to Date) sum or running totals.

11 views

### Recent Posts

See All

#### Stop computer from locking windows and keep status Active on Skype / Teams / Jabber using VBA scrip

With the pandemic pushing almost everyone to work from home and most of us are required to stay active on collaboration tools even if there is no need for you to sit in front of laptop. Like a friend

#### TEST YOUR EXCEL SKILLS IN EXCEL

So you want to know how good you are in excel then make sure you download our test now. Its not a multiple choice question style test but an actual test in excel with excel data. Download it and try t

#### FREE SQL TRAINING FOR HYDERABAD STUDENTS

Hi All- There has been a request from most of the students attending excel and VBA classes to show what SQL queries are and how SQL can be used with Excel and VBA. SQL is a query language to retrieve,