Learn more:- https://www.exceltutorial.net/

Lookup Playlist:- https://youtube.com/playlist?list=PLhvn6aQRdimXjKB1EOoRWznYZtqRe7B2z

In this video, we will be looking at the OFFSET function in Excel. This is a very useful function that allows you to return cells or ranges of cells that are a specified number of rows and columns from a cell or range of cells. The OFFSET function can come in handy when you need to create dynamic named ranges for example, if you have an expanding list of items and want the name to update as new items are added. In this video, we'll cover how to use the OFFSET function with both static and dynamic ranges as well as creating a dynamic named range using the OFFSET Function .

The OFFSET function is a basic workaround that can be used to return a range of cells. The syntax for the OFFSET Function is as follows:

=OFFSET(reference,rows_offset,columns_offset)

Where:

• reference is the cell or range of cells from which you want to offset .

• rows_offset is the number of rows you want to offset from reference. If you enter a positive value, this will be how many rows above or below your reference cell and if you enter a negative value it will be how many below or above respectively. For example: =OFFSET(A100,+20) would return cells 20 rows down from A100.

• columns_offset is the number of columns you want to offset from reference. If you enter a positive value, this will be how many columns to the right of your reference cell and if you enter a negative value it will be how many to the left respectively. For example: =OFFSET(A100,+20, -15) would return cells 15 columns to the left of A100.

The OFFSET function can also be used in dynamic ranges. This is where you specify the number of rows and columns as an argument rather than hard coding it into the function. So for example, if you have a list that is constantly expanding, you can use the OFFSET function to create a dynamic named range that updates automatically. For example, if you have items A-Z and then AA-AZ etc., instead of having to change your formula every time an item is added or deleted, you would simply add one more letter (AA=A100+20; AB=A101+20 etc.).

To create a dynamic named range using the OFFSET Function , follow these steps:

• Enter your list of items in column A. In this example, we have 26 letters from A to Z and then AA to AZ respectively. Be sure that you place an incremental number behind each letter (i.e., not just A,B,C etc., but A100,A101 etc.).

• In a new column (column B), enter the following formula: =OFFSET(A$100,0,0)

This will return the first cell in your list (A100). Copy this formula down as many rows as you have i ... https://www.youtube.com/watch?v=ilRcjyZax0k