Ignore Blanks in Data Validation Lists in Excel

4 ways to remove blanks from Data Validation lists in Excel - including a formula to use for Excel 365 and one formula for older versions of Excel.

This tutorial covers multiple methods for blank or empty cell removal so that you can pick the one that either works best for you or is easiest for you to remember.

You will learn how to use new dynamic array functions for Excel 365 as well and the spill features and pound sign, and also how to use the older clunky arrays for Excel.

NOTE: To use the older formula from this tutorial, you must input it using CTRL + SHIFT + ENTER or it won't work - this is because it is an array formula.

Other Resources:

Excel Courses:

#msexcel #howto #tutorial

[TeachExcel]

I will be taking some time off soon, so this will probably be the last week of tutorials until september. I can hear a beach and some rum calling my name lol.

Note: To use the older formula from this tutorial in older versions of Excel, you must input it using CTRL + SHIFT + ENTER or it won't work - this is because it is an array formula. (I forgot to show this because its not required in Excel 365.)

[kalart]

I love how clearly you explain everything. Extremely helpful and very well spoken. Great content! Thank you.

[MDRLOz]

This is terrible. You haven't actually ignored any blanks here you simply have ways of removing them. You should simply state that there is no way for a drop down to ignore blanks from a list. You can make a static seperate list but you can never make a drop down of an an area that can be dynamically adjusted to add in new values. Even that expanding list you made cannot have a drop down pointed at it dynamically.

[staceyjizzaine]

Thank you so much. This worked perfectly for me when I needed to create a DV drop down based on a selection we would be making additions to. Wondeful and clear teaching style. Thanks again!

[berticusmaximus]

You just saved me. Filter formula. Didn't even know about it.

[DanCorrin]

The validation list can have an indirect function defined, so if you provide a range as text e.g. "A1:A"&(COUNTIF(A:A, "<>")-1) and use that as the source of the indirect then the list can dynamically change.

[HellfireCarnage]

Perfect! Amazing! Thanks a stack! I have been dying to find a solution for showing dropdowns without empty rows for ages now. Great solution!

[mwmkhungo]

Actually used that old method. Thanks. Time to use the new method now that I'm on a the new version of excel.

[wayneedmondson]

Great! Thanks for the examples. Thumbs up!!

[pedrojorgetavaresrdealmeid]

Hello there! Thanks for the video, great stuff!! I found an issue on it: in my case I have a #Spill! mistake which do not allow me to see the data validation list without blanks or double info inside. Any tips? Thanks a lot again

[darrylmorgan]

Cool Tutorial Though The Old School Formula Brought Me Out In Cold Sweat's lol...Thank You Sir :)

[mr.write]

I want to put it on data validation but i can no long write inside. Its annoying

[sayediftekharraheman]

Sir amezing information.
Thankyou sir

I have a question.
Sir I'm having 20 rows with data validation drop-down, so I want to copy all the rows, but paste only which cell having data and black cell will not be pasted.
Only paste cell value not blank cell.


⚠️All rows are having data validation drop-down⚠️


Please sir help me 🙏🙏🙏

[LucasMarodindepaiva]

Well, Filter function doesn't work inside the Data Validation list! Just good to know if you're looking for this... I still can't find how to select an array for a data validation list using all values except one

[RafaelSouza]

For some reason the # in the end of a data validation just works in a column that has been previously sorted with the =Sort. If someone does it, it works! Thanks.

[moshicloud]

thanks a lot, crazy how shitty it is compared to google sheets, there its piss easy

[liquidmist]

So what's the fucking point of the "ignore blank" checkbox?? Wtf Microsoft

[chocolatecosmos]

Why can't any of you Excel content creators actually answer the question that people ask. Who the hell wants to go through all this stupid steps. There are blanks in my list because it helps me to visualize my data when I enter the list if I didn't need the blank spaces I wouldn't have put them in the first place. Thanks for wasting my time.