4 Ways to Remove Blanks from Data Validation List in Excel

Organizing data using various techniques is quite common in Microsoft Excel. Records are arranged in a tabular form here. You can create a drop-down list by Data Validation in Excel, but you need to remove the blanks in it manually. This article will help you with four ways to remove blanks from the data validation list in Excel. It is applicable for multiple cells in Excel and you can eventually modify it according to your requirements. All the methods have step-by-step instructions with a figurative description.


๐Ÿ“ Download Excel File

Download the Excel file below.


Learn to Remove Blanks from Data Validation List in Excel with These 4 Approaches

The article is going to introduce four approaches in Excel to autofill sequential letters. The methods will help you understand the uses elaborately. The following discussion will cover the uses Go To Special option and formula-based application using. You can also use the IF, COUNTIF, INDEX, COUNTBLANK, INDIRECT, ADDRESS, and FILTER functions. Therefore, users will get a walkthrough of these solutions. A dataset will be modified in the following methods below to help you understand.

Dataset to autofill sequential letters in Excel


approach

1. Using Go To Special Option

The Go To Special is an option to select the cells from a range according to specific types of data within it. After the selection, you can easily delete them and remove blanks from the data validation list afterward in Excel.

โฌ‡๏ธโฌ‡๏ธ STEPS โฌ‡๏ธโฌ‡๏ธ

  • Initially, select cell range C6:C11.
  • Next, click on the Go To Special option from the Find & Select drop-down, under the Editing group of the Home tab.

  • Here, you should get a Go To Special dialogue box.
  • Then, choose the Blanks option and hit the OK button.

Trying Go To Special option to remove blanks from the data validation list

  • Now, you can see that Excel has selected the blank cells.
  • Next, click on the Delete icon from the Cells group under the Home tab.

Using Go To Special and Delete to remove blanks from data validation list in Excel

  • Then, select cell D6 and click on the Data Validation option from the Data Tools under the Data tab.
  • Now, from the Data Validation dialogue box, choose List from the Allow option.
  • After that, choose the source as C6:C9 from the worksheet.
  • Then, hit the OK button.

  • Finally, you can see the drop-down list without any blanks.

Using Go To Special option to remove blanks from data validation list in Excel


approach

2. Merging COUNTIF and INDEX Functions

To remove blanks from the data validation list in Excel, you can merge the COUNTIF function and the INDEX function. To get better results, you can add the IF, ROW, SMALL, and ROWS functions.

โฌ‡๏ธโฌ‡๏ธ STEPS โฌ‡๏ธโฌ‡๏ธ

  • To begin with, enter the following formula on cell C6.

=IF(COUNTIF($B$6:$B$11,โ€?*โ€)<ROW(B6)-5,โ€โ€,INDEX(B:B,SMALL(IF(B$6:B$11<>โ€โ€,ROW(B$6:B$11)),ROWS(B$6:B6))))
  • Now, hit the Ctrl+Shift+Enter buttons.

  • Then, using the Fill Handle icon, drag the following formula down till cell C11.

Merging COUNTIF and INDEX functions for removing blanks from data validation list in Excel

  • After that, repeat the Data Validation process from Method 1.
  • Finally, you can see the drop-down list without any blanks.

๐Ÿ”จ Formula Breakdown

IF(COUNTIF($B$6:$B$11,โ€?*โ€)<ROW(B6)-5,โ€โ€,INDEX(B:B,SMALL(IF(B$6:B$11<>โ€โ€,ROW(B$6:B$11)),ROWS(B$6:B6))))

๐Ÿ‘‰ย  Here, ROW brings the row number B6. It comes in handy for the IF function.

๐Ÿ‘‰ย  Also, ROWS returns the number of rows of B6:B6.

๐Ÿ‘‰ย  Again, IF fulfills the condition if B6:B11 has any blanks. It follows the outputs of the ROW function.

๐Ÿ‘‰ย  Here, SMALL shows the smallest output according to the IF and ROWS functions.

๐Ÿ‘‰ย  Now, INDEX returns the array. It gets the row number of the SMALL function.

๐Ÿ‘‰ย  Then, COUNTIF counts cell numbers given that the function fulfills the condition.

Finally, we get โ€œLisaโ€.


approach

3. Combining COUNTBLANK and INDIRECT Functions

To create a data validation list in Excel, you need to remove blanks by using the COUNTBLANK function and the INDIRECT function together. You can combine the IF, ROW, ROWS, ADDRESS, SMALL, and COLUMN functions with these as well.

โฌ‡๏ธโฌ‡๏ธ STEPS โฌ‡๏ธโฌ‡๏ธ

  • First, enter the following formula on cell C6.
=IF(ROW()-ROW($B$6:$B$11)+1>ROWS($B$6:$B$11)-COUNTBLANK($B$6:$B$11),โ€โ€,INDIRECT(ADDRESS(SMALL((IF($B$6:$B$11<>โ€โ€,ROW($B$6:$B$11),ROW()+ROWS($B$6:$B$11))),ROW()-ROW($C$6:$C$11)+1),COLUMN($B$6:$B$11),4)))
  • Now, hit the Ctrl+Shift+Enter buttons.

Combining COUNTBLANK and INDIRECT functions for removing blanks from data validation list in Excel

  • Then, using the Fill Handle icon, drag the following formula down till cell C11.

  • After that, repeat the Data Validation process from Method 1.
  • Finally, you can see the drop-down list without any blanks.

Implimenting COUNTBLANK and INDIRECT functions for removing blanks from data validation list in Excel

๐Ÿ”จ Formula Breakdown

IF(ROW()-ROW($B$6:$B$11)+1>ROWS($B$6:$B$11)-COUNTBLANK($B$6:$B$11),โ€โ€, INDIRECT(ADDRESS(SMALL((IF($B$6:$B$11<>โ€โ€,ROW($B$6:$B$11),ROW()+ROWS($B$6:$B$11))),ROW()-ROW($C$6:$C$11)+1),COLUMN($B$6:$B$11),4)))

๐Ÿ‘‰ย  Here, ROW brings the row number B6. It comes in handy for most functions.

๐Ÿ‘‰ย  Also, ROWS returns the number of rows of B6:B6.

๐Ÿ‘‰ย  Now, COLUMN gives the column number. It depends on B6:B11.

๐Ÿ‘‰ย  Again, IF fulfills the condition if B6:B11 has any blanks. It follows the outputs of the ROW function.

๐Ÿ‘‰ย  Here, SMALL shows the smallest output according to the IF and ROWS functions.

๐Ÿ‘‰ย  Then, ADDRESS calls the cell address. The output depends on the SMALL function.

๐Ÿ‘‰ย  Also, INDIRECT brings the specified text string from the ADDRESS output.

๐Ÿ‘‰ย  Now, COUNTBLANK counts the number of empty cells.

Finally, we get โ€œLisaโ€.


approach

4. Implementing FILTER Function

The FILTER function is the easiest method to remove blanks from the data validation list in Excel.

โฌ‡๏ธโฌ‡๏ธ STEPS โฌ‡๏ธโฌ‡๏ธ

  • In the beginning, enter the following formula on cell C6.

=FILTER(B6:B11,B6:B11<>โ€โ€)
  • Now, hit the Ctrl+Shift+Enter keys.

  • Then, using the Fill Handle icon, drag the following formula down till cell C11.

Implementing FILTER function for removing blanks from data validation list in Excel

  • After that, repeat the Data Validation process from Method 1.
  • Finally, you can see the drop-down list without any blanks.


๐Ÿ“„ Important Notes

๐Ÿ–Š๏ธย  You should modify your formulas according to your dataset.

๐Ÿ–Š๏ธย  The last method is only applicable to Microsoft 365 users.


๐Ÿ“ Takeaways from This Article

The article lets the readers understand the variety of options available to remove blanks from the data validation list in Excel.

๐Ÿ“Œย  Primarily, you can use the Go To Special option.

๐Ÿ“Œย  Also, you can merge COUNTIF and INDEX functions to remove blanks from the data validation list in Excel. You can use IF, ROW, ROWS, and SMALL functions with it.

๐Ÿ“Œย  Here, you can combine COUNTBLANK and INDIRECT functions to remove blanks from the data validation list in Excel.

๐Ÿ“Œย  Finally, you can use the FILTER function as well.


Conclusion

To remove blanks from the data validation list in Excel, four methods are useful. You can use the Go To Special option and formula-based application using. You can try the IF, COUNTIF, INDEX, COUNTBLANK, INDIRECT, ADDRESS, and FILTER functions to autofill sequential letters in Excel as well. In case of further queries, readers are requested to leave a comment for the author. The author will try their best to come up with a suitable solution. Follow ExcelDen to get more access to solutions regarding your Excel problems.

(Visited 28 times, 1 visits today)
Farihat

Farihat

An Excel enthusiast who wants to share and loves to solve Excel problems. I try to explore available options to guide readers to the maximum ways how they can solve. The world is limitless, and so are the problems in it. Let's help each other to grow. I would love to hear feedback and queries from readers. You can visit my LinkedIn for further information.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo