If an Excel drop down has a long list of items, it can take lots of scrolling to find the item you need. To make things easier, add single-letter headings in the list. You’ll be able to get to any starting letter quickly. See Roger Govier’s technique for setting that up, and an updated version of his macros.
Video: Drop Down List Headings
For data validation drop downs with a long list of items, add letter headings, to make navigation easier. This short video shows the steps, and there are written steps below the video.
Drop Down List Headings
Long ago, before Excel had named tables, Roger Govier created a sample file for my Contextures site.
Roger’s file has macros that add or remove single letter headings in a worksheet list.
(Do you recognize these product names from the old Northwinds database?)
Letter Headings
The macros also create a named range, MyList, for the list.
On another sheet, the MyList named range is used as the source for a data validation drop down. After you add letter headings to the product list, it’s easy to get to any part of the list.
- Type a letter in the cell
- Click the drop down arrow, to open the list at that spot.
Roger’s Sample File
Last week, I created a new version of that workbook, but you can still download Roger’s workbook from Excel Sample Files page on my Contextures site.
- In the Data Validation section, look for DV0046 – Add Headings for Navigation.
- The file is in xls format, and contains 3 macros to add and remove headings in the list.
List Headings in Excel Tables
Last week, I created a new version of Roger’s workbook, using named Excel Tables. There’s a video below, if you need the steps for setting up a named table.
- Here’s the product table, named tblProducts.
- The MyList named range is set for the data cells in that table.
- The macro doesn’t need to reset the named range now, because its size adjusts automatically
Data Entry Table
On another sheet, there’s an Excel table named tblData.
- Each cell in the table has a data validation drop down list, where you can select a product name
- The MyList named range is the source for these drop down lists
There’s a video below, if you need the steps for setting up a data validation drop down list.
Letter Heading Macros
If you need to have macro-free workbooks, don’t worry! You don’t need to use macros for this letter heading technique.
- You could set up the letter headings manually, if you prefer.
- Macros aren’t needed to use the drop down lists
But, to make things easier, there are 3 macros in the sample workbook. Those macros simply add or remove the letter headings in the product list. They aren’t needed for anything else
The sample file has 3 buttons that run the macros:
- RemoveLetters, removes all single-character items in the named range, MyList, on the List sheet.
- InsertLettersAll, adds all 26 letters as headings in the named range, MyList, on the List sheet.
- InsertLettersUsed, adds letter headings only if at least one product name begins with that letter.
I use the macro that inserts all the letters. With that option, any letter can be typed in the drop down list, without a data validation error message.
NOTE: The code is in the sample file, and it’s also shown on the the Letter Headings in Drop Down List page on my Contextures site.
Get the Sample File
To see more details, and to get the Letter Heading sample workbook, go to the Letter Headings in Drop Down List page on my Contextures site.
The zipped Excel file is in xlsm format, and contains three macros, to add and remove the single-letter list headings.
Make a Drop Down List
To see how to make a drop down list in a cell, watch the steps in this short video, and the written instructions are on the Excel Drop Down List page on my Contextures site.
Create an Excel Table
To see how to set up a named Excel table, watch this short video. There are written steps on the Excel Tables page.
_______________________
Add Letter Headings in Excel Drop Down List
_______________________