OFFSET Function in Excel For Dynamic Ranges | Create Dynamic Named Range in Excel
Learn more:- https://www.exceltutorial.net/
============================================================
Lookup Playlist:- • Lookup Formulas In Excel
============================================================
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 items in your list.
In column C, enter the following formula: =OFFSET(A$100,0,columns_offset)
This will return the range of cells that are offset by the number of columns you specify. So for example, if you have 26 items in your list and want to return a range that starts at A101 and is offset by 20 columns, the formula would be: =OFFSET(A$100,0,20)
In column D, enter the following formula: =OFFSET(A$100,rows_offset,columns_offset)
This will return the range of cells that are offset by both the number of rows and columns you specify. So for example, if you have 26 items in your list and want to return a range that starts at A101 and is offset by 20 rows and columns, the formula would be: =OFFSET(A$100,20,20)
To name this dynamic named range, highlight cells C&D (the range of cells that you want to name) and go to Insert then Name then Define. In the "Name" field, enter a name for your dynamic range (e.g., MyDynamicRange). Click OK.
Your dynamic named range is now created and will automatically update as new items are added or deleted from your list!
Table Of Content
00:00 Introduction
01:35 OFFSET Function in Excel
07:20 OFFSET For Dynamic Range in Excel
12:44 Create Dynamic Named Range Using OFFSET
18:40 Conclusion
Other Playlists:-
Cell References in Excel - • Cell References In Excel
Logical Functions in Excel :- • Logical Functions
Count and Sum Functions in Excel - • Count And Sum Function
Learn Data Validation In Hindi: • Learn Data Validation In Hindi
Learn Excel Tutorials Online:- • Learn Excel Tutorials In Hindi
Excel Analysis Tutorial: • Excel Analysis Tutorials
Watch video OFFSET Function in Excel For Dynamic Ranges | Create Dynamic Named Range in Excel online without registration, duration hours minute second in high quality. This video was added by user Excel Tutorial 15 December 2021, don't forget to share it with your friends and acquaintances, it has been viewed on our site 3,108 once and liked it 23 people.