Excel Tips: Creating Dynamic Named Ranges for Better Data Management

Do you have a table that has data added/removed on a recurring basis and it's an input to other tables? When you add or remove records, you may have needed to change the references from the other tables. You can offset these work by creating a dynamic named range or an Excel table. Today, we’re unlocking the secret to dynamic named ranges, a feature that will transform how you work with data. A dynamic named range in Excel expands and contracts as you add or remove data, ensuring your formulas always include the correct range.

This tutorial will show you how to create and implement dynamic named ranges in your spreadsheets, making your data management tasks quicker and more efficient. Since I used the word "offset" in an earlier sentence it's a clue to the type of function that will be introduced. One way to create a dynamic named ranges is to use the OFFSET and COUNTA functions. It's a bit more involved in trying to understand what the combination of these two functions do, but if you're counting on learning some cool tips, check it out.

If this video helped you, don’t forget to like and subscribe for more productivity hacks and Excel tutorials. Hit the notification bell to stay updated with our latest content.

📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!

#excel
#msexcel
#doughexcel

-~-~~-~~~-~~-~-
Please watch: "Convert Table in a PDF File to Excel"
-~-~~-~~~-~~-~-

[stpark]

Great function! Thank you for the lesson.

[charlesstevens]

This idea is very helpful. Thanks so much for sharing.

[diya]

Great video and explanation! It has really changed my life!

[jonahmcphaul]

Very well done Doug.  Keep them coming

[jimbolio]

You're the best. Thanks for this walk through. It really helped me update some older/larger workbooks i had to bring back to life.

[ncnc]

Hi Doug, thank you lots for taking the time to teach us those tricks!! Excel is really magical!cheers mate

[saqibabbasi]

Thanks Doug . Your video really helps me to get my 'offset' issue fixed.

[billtaylor]

That is the best explanation of creating a dynamic range using multiple columns that I have seen so far. Most people say that is what they teach, but end up defining a column or row that they think will never be used.  How do you sort that dynamic range in VBA using something other than column "A" as the sort criteria?

[scottsummit]

THANK YOU! I used this for a work problem I had, and spent 2 days trying to figure out. This helped so much.

[diya]

you said in the video that in the newer version of excel you don't have to write the offset formula- and you can use the "table" feature- can you tell me how to do that? or post a video on that?

[fjj]

Hi Doug, firstly, thanks for such a great well explained use of OFFSET(). Could the "Refers To" formula be copied from the Named Manager in it's existing form be used directly within VBA i.e. built as a VBA Function ?

[wcthrill]

Good tip...but wouldnt an easire wrkaround be to simply change Data Source after you add the new Col or Rows?  You still need to refresh the PT on both examples. You can even select more rows and columns when you create the PT. Youll just have to filter 'blanks'

[nigelmartin]

doesnt the new column get added to the pivot data automatically or do we have to choose it form the pivot table file list?

[ElViejoBeto]

Hi Doug!

Thanks for sharing this video.

I wrote down the formula in the same way you did into a cell, then I copied to the clipboard, but instead of creating a new range name, I edited an existing fixed range name called CLIENTS_LIST (via name manager) and pasted the formula in the refers to box.

The CLIENTS_LIST was associated to a combobox by "ListFillRange" property, but now the combo doesn't seem to recognize the new dynamic range and also I cannot find the range name in the name box. I can only find the new edited range name when I press name manager, but I cannot work with my CLIENTS:LIST anymore.

I have even erased and created the CLIENTS_LIST dynamic range from zero, but still excel doesn't seem to recognize the name.

Any suggestion?

Best regards / Alberto

[malcolmt]

This is great but can it be done by using an external workbook.

[madhukrishnavallabhajosyul]

Excel 2016. Pivot is not taking reference of table with name. I put the offset formula for table name just to see an error message pop up. "PivotTable field name is not valid"

[alzbeto]

How can I extract from any given number of sheets both fixed and variable data. In this specific case is information out of packing lists. I need to extract fix data as the date and the consecutive number of packing list. The variable data consists in what was shiped in that specific paking list. On that part I need the parts sent and its quantity. Just in the January Excel Document there are 256 sheets. Each one of the a different packing list.
The ideal outcome would be Date, Paking list, Part 1, Quantity, Sheet number.
Hope you can help me.

[lucproulx]

I've created another account so I could give you 2 thumbs up. (I lied, but you deserve it!)