Como usar a função OFFSET do Excel

The Excel OFFSET function returns a reference range of cells that are offset (hence the name) from a reference cell. In other words, you specify the offset in terms of the number of rows and columns, and you can also indicate the size of the array that you want to reference.

Sintaxe

• reference is the starting cell from which the results are offset
• linhas is the number of rows you want to offset from the reference cell
• cols is the number of columns you want to offset from the reference cell
• altura is the height of the array you want to return. The default is 1
• largura is the width of the array you want to return. The default is 1

Como usar a função OFFSET do Excel

Now I’ll show the function in action. Take a look at the example below.

I have some data from a clothing store that indicates how many of each item has been sold, per size. I use the OFFSET function to reference data within this table.

As you can see, I type the function =OFFSET(A1,4,2) to pull the data I want from the table. In this case, I start with a reference cell of A1, then tell the function to move 4 rows down and 2 rows across. As a result, it performs that offset and returns the results from cell C5.

If I want to get the sales of large T-shirts, I can use the formula =OFFSET(A1,2,3). This function is the same as in the previous case, except I tell the function to offset 2 rows down and 3 columns to the right of cell A1.

Notice how I didn’t input the two optional parameters, [height] and [width]. This is because I only want to return 1 cell, so I don’t have to input anything for these since they default to 1.

If I wanted to return multiple cells however, I would specify the dimensions of the desired output.

Using OFFSET with other functions

The OFFSET Excel function can also be combined with other functions.

In this example, I combine OFFSET with the SUM function to get the total sales of Large clothing.

A fórmula aqui é =SUM(OFFSET(A1,2,3,7,1)). This time, I input 7 for the height parameter. This makes the function return the range D3:D9, which encompasses all the large items. It then adds them all up with the SUM function.

If you’re still confused about this example, here’s what we tell the function told to do: start at cell A1, then move 2 rows down, 3 rows across, and return the results from an array of 7 (height) by 1 (width).

Finally, the SUM function then adds up this array (D3:D9).

Using OFFSET to create a dynamic range

One of the main uses of the OFFSET function is to create a dynamic range. A dynamic range is a range that updates automatically when new data is added.

Take a look at the example below. Here, I use the OFFSET function to create a dynamic range of the entire table that will update if new data is inserted.

Lastly, I combine this with the AVG function to get the average sales per item.

This is achieved with the formula =AVERAGE((A2:OFFSET(D10,-1,0,))). I use the OFFSET function to identify the lower bound of the range I want to average.

The range goes from cell A2 (fixed) to one cell above D10. The benefit here is that if I insert a new row, the function will automatically update:

A fórmula aqui é =AVERAGE((A2:OFFSET(D11,-1,0,))). The OFFSET updated from D10 to D11, which kept the entire table included in the average calculation. I didn’t change the formula – I just added a new row (shoes) and the function updated automatically!

I hope you now have an understanding of how and when to use the OFFSET function!

OFFSET function

And there you go! Use the OFFSET function to return a reference range of cells that are offset from a reference cell.

Se quiser saber mais sobre as várias fórmulas do Planilhas Google, dê uma olhada em nossa postagem no blog sobre a fórmula ARRAY_CONSTRAIN no Google Sheets.

Como alternativa, confira as postagens relacionadas no blog abaixo!