Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How To Create Drop Down List Ignore Blank Cells In Excel?
Drop-down lists are a great way to create more efficient and accurate data entry in your Excel spreadsheet. However, when your data includes blank cells, it can be tricky to create a drop-down list that only includes the non-blank cells. Fortunately, there is a simple solution to this problem. In this tutorial, we will guide you step-by-step on how to create a drop-down list while ignoring blank cells in Excel. By the end of this tutorial, you will be able to create a drop-down list that only includes the relevant data, making your data entry experience more efficient and streamlined. So, let's get started!
Create Drop Down List Ignore Blank Cells
Here we will first list the values without the blank cells and then create a data validation list. So let us see a simple process to know how you can create drop-down lists in Excel and ignore blank cells.
Step 1
Consider an Excel sheet where you have a list of names with some blank cells similar to the below image.
First, click on an empty cell in our case cell C1 and enter the formula as =LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(A:A,SMALL(IF($A$1:$A$12<>"",ROW($A$1:$A$12)),ROWS($C$1:C2))))) and click CTRL + SHIFT + ENTER to get the first value.
Empty cell > Formula > CTRL + SHIFT + ENTER.
Step 2
Then drag down from the first value using the auto-fill handle to get all the results.
Step 3
Now click on an empty cell, in this case cell E2, then click on data and select data validation.
Then in the pop-up window, set allow to list and source as the range of cells, and click OK to get the data validation list.
Empty cell > Data > Data Validation > Allow > Range.
Then the final output will be similar to the below image.
Conclusion
In this tutorial, we have used a simple example to demonstrate how you can create a drop-down list and ignore blank cells in Excel to highlight a particular set of data.
226 Views
