The OFFSET function is not a function you need particularly often, but it's worth knowing about because it allows you to move ranges around very easily.
#GoogleSheets thread. Let's go. π
#GoogleSheets thread. Let's go. π
The OFFSET function returns a reference to a range that is offset from a starting point in a worksheet.
For example, in the table shown in this image, imagine you want to align these columns at the top of the column.
For example, in the table shown in this image, imagine you want to align these columns at the top of the column.
The OFFSET function can do this with a single formula for each column, which is quicker and easier than copy-pasting or creating complex nested formulas.
The OFFSET formula in this example is:
=offset( B2:B6, 1, 0, count(B2:B6) )
The OFFSET formula in this example is:
=offset( B2:B6, 1, 0, count(B2:B6) )
The first argument, B2:B6, is the original range of data we want to move.
The second argument, 1, is the number of rows we want to offset. In this case it's 1 because we want to move down one row to grab the first value.
The second argument, 1, is the number of rows we want to offset. In this case it's 1 because we want to move down one row to grab the first value.
The third argument, 0, is the number of columns we want to offset. In this case we don't have a column offset so we set this to 0.
The final (optional) argument is for the height of the range to return.
The final (optional) argument is for the height of the range to return.
Finally, this new range is output in cell F2, or wherever I've typed the OFFSET function.
Hope that helps!
Hope that helps!
β’ β’ β’
Missing some Tweet in this thread? You can try to
force a refresh
γ
