Removing blank category rows in Excel data validation

Microsoft Excel
,
1

A user noticed that, when clicking the Category-column dropdown in the Transactions sheet in Excel, the data validation dropdown includes many blank rows below the pre-populated values from the Categories sheet.

By default, the data validation is configured to pull from cells A2:A98 in the Categories sheet. When there are blank rows at the bottom— most of us don’t use 97 categories— those blanks appear in the dropdown.

It’s a little technical, but these blank rows can be removed if you follow the steps below. We are going to create a named range that points to a dynamic range. Then, we will link our data validation to this new named range.

  1. Open your Excel workbook
  2. Go to the named range window. In Excel for Mac, click Insert / Name / Define Name in the menu tree. In Office 365, named range configuration is found under Formulas / Defined Names / Name Manager.
  3. Create a new data range named: CategoriesNoBlanks
  4. Insert this formula in the cell range text box: =Categories!$A$2:INDEX(Categories!$A$2:$A$98,COUNTIF(Categories!$A$2:$A$98,"?*"))
  5. Click Ok
  6. Select all cells in your Transactions sheet’s Category column
  7. Click Data / Validation... in the menu tree
  8. Input =CategoriesNoBlanks for the source
  9. Click Ok

screenshot_2019-11-22_13-40-00
screenshot_2019-11-22_13-40-001308×1068 153 KB

Let me know if these steps work for you (and the instructions can be improved/clarified).

Cheers,
Randy

1 Like
2

Thank you- this worked for me, with one tweak. I am using Excel for Microsoft Office 365, and the named range function is found in a different spot: Formulas>Defined Names>Name Manager.

2 Likes
3

Glad to hear the solution worked, @pjsiebert. Thanks for your correction on the menu location in Office 365. I updated the solution with your feedback.

Randy