Welcome! Lets start your excel journey..

Welcome! Register Now to Unlock Exclusive Excel Tutorials

Get Started
  • Exclusive Playlists
  • Downloadable Videos
  • No Ads!
  • Exclusive Templates

Excel OFFSET Function: How to Use with Static and Dynamic Ranges

Admin
Duration: 19:07
Submitted: 8 months ago
Views: 303

Comments (0)

Link to this video:

Description: The OFFSET function in Excel is a very useful tool that can be used in a variety of ways. In this video, we'll be looking at how to use the offset function with static and dynamic ranges. We'll also cover how to create a dynamic named range using the OFFSET function. This can come in handy when you have an expanding list of items and need the name of the range to update automatically as new items are added. 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 First thing first, let's learn the syntax of OFFSET function in excel; =OFFSET(starting_cell,rows_offset,columns_offset) Where: - starting_cell is the cell that you want to start from - rows_offset is the number of rows you want to offset from the starting cell - columns_offset is the number of columns you want to offset from the starting cell Now that we know the syntax, let's take a look at how to use the OFFSET function with static and dynamic ranges. OFFSET For Dynamic Range in Excel. Suppose you have a list of items that is expanding and you want the name of the range to update automatically as new items are added. In this case, we can use the OFFSET function with a dynamic range. =OFFSET(A15,VLOOKUP(A15,"Table","column_number")) Where: - A15 is the starting cell - "Table" is the range of cells that contains the list of items - "column_number" is the column number in the Table range where you want to start counting from. In our example, it would be the second column since we are looking for the item's name in the second column. Now let's take a look at an example where we want to return the cell range B15:E20 starting from cell A15. =OFFSET(A15,rows_offset,-columns_offset) Where: - A15 is the starting cell - rows_offset is the number of rows you want to offset from the starting cell - columns_offset is the number of columns you want to offset from the starting cell In our example, it would be: =OFFSET(A15,rows_offset,-columns_offset) which gives us B15:E20. Create Dynamic Named Range Using OFFSET Function; Now let's say you want to create a dynamic named range using the OFFSET function. In this case, we'll first need to create a table with the list of items that we want to include in our dynamic range. For this example, we'll use the table below: A B C Item A Item B Item C Item D Item E Now let's say we want to create a dynamic named range called "DynamicRange" that includes the cells B15:E20 starting from cell A15. To do this, we'll use the following formula: =OFFSET(A15,0,COLUMNS(B15:E20)) Where: - A15 is the starting cell - 0 is the number of rows we want to offset from the starting cell - COLUMNS(B15:E20) gives us the range of column numbers in our table that we want to include in our dynamic range. In our example, it would be B15:E20. And that's it! We've now created a dynamic named range called "DynamicRange" that includes the cells B15:E20 starting from cell A15. Conclusion; The OFFSET function in Excel is a very versatile and useful tool that can be used in a variety of ways. In this video, we've covered how to use the OFFSET function with static and dynamic ranges as well as creating a dynamic named range using the OFFSET function. Be sure to check out our other Excel videos for more tips and tricks on how to get the most out of this powerful program. Thanks for watching!