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.
- Open your Excel workbook
- Go to the named range window. In Excel for Mac, click
Insert / Name / Define Namein the menu tree. In Office 365, named range configuration is found under
Formulas / Defined Names / Name Manager.
- Create a new data range named:
- Insert this formula in the cell range text box:
- Select all cells in your Transactions sheet’s Category column
Data / Validation...in the menu tree
=CategoriesNoBlanksfor the source
Let me know if these steps work for you (and the instructions can be improved/clarified).