Look Who it Was Random video

Excel - Data Validation List that Expands With New Data - Episode 584

786 views · Published 22 June 2009 · 2:17 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Excel Data Validation List that Expands With New Data.
Welcome back to the MrExcel netcast! In today's video, we're going to be discussing a useful feature in Excel - Refreshable Validation. This is a question that was sent in by one of our viewers, Brent, and I'm excited to share this tip with all of you. 

But before we dive into that, I want to touch on a cool spreadsheet that I recently set up for my wife, Mary Ellen. She needed a simple place where she could have a drop-down list and be able to maintain it without having to be an Excel expert. So, I came up with a solution using Data Validation and the OFFSET function. 

If you watched our previous video on Data Validation, you'll remember that we learned how to set up a Data Validation list on another worksheet by providing a named range. Well, for Mary Ellen, I used the Insert＞Name＞Define feature to set up a dynamic range using the OFFSET function. This function allows us to start at a specific cell and then include a certain number of rows and columns. In this case, I set it up to include all the items in column A, minus the heading. 

So, if Mary Ellen adds a new item to her list, the named range will automatically update to include it. And the best part is, when we go back to our original list, the drop-down will also update to include the new item. This is a great time-saving feature, especially for those who are not as proficient in Excel. 

I've created a similar workbook to the one I set up for Mary Ellen and I've uploaded it to the MrExcel podcasts blog, which you can find in the description below. This way, you can download the workbook and take a closer look at how the OFFSET function works. Thanks for tuning in and we'll see you next time for another netcast from MrExcel!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Store your validation lists on a separate worksheet and have the dropdown boxes automatically expand when you add new items to the list. Episode 584 shows you how. If you'd like to try this out, download the sample workbook. Monday in the USA is our Labor Day holiday and I'll be back Tuesday, September 4th.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Table of Contents:
(00:00) Introduction to Data Validation
(00:21) Setting up a Spreadsheet for Drop-Down Lists
(00:32) Using the OFFSET Function for Dynamic Ranges
(01:27) How to Update Drop-Down Lists with New Items
(01:46) Downloadable Workbook Available on MrExcel Podcasts Blog
(01:57) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Contiguous range
Data Validation
Drop-down list
Dynamic range
Format Sheet Unhide
Maintaining lists
Named range
OFFSET function
Spreadsheet

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152473/

More from this channel