Letter Headings in Drop Down List

Add single-letter headings in Excel drop down, so it's easier to find items in a long list. Use data validation and optional macros to add and remove headings.

Thanks to Roger Govier who created the original sample file for this technique, before Excel had named tables!

Drop Down Letter 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. NOTE: People will be able to select the single letter headings in the drop down list, as well as the other items. That might cause problems in some workbooks How to Use Drop-Down List with Headings To use the drop down, follow these steps: Type a single letter in the cell, then click the drop down arrow. The list opens at the letter you typed, so you can quickly find the item you need No macros are needed to use the drop down list with letter headings To set up the letter headings: Add the letter headings manually Or, use one of the Add Letter Heading macros shown below, to automatically add the headings.

Workbook Setup

In the sample workbook, there are 2 main sheets:

• List - has an Excel table with product names
• Data Entry - has a small Excel table for selecting product names

List Sheet

On the List sheet, there is an Excel table named tblProducts, with list of product names, sorted A-Z.

• The data rows are a named range -- MyList
• That named range is used as the source for the drop down list on the Data Entry sheet

Data Entry Sheet

On the Data Entry sheet, there is 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

To see how to set up a named Excel table, watch this short video. There are written steps on the Excel Tables page.

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.

Remove Letter Headings Macro

This macro, named RemoveLetters, removes all single-character items in the named range, MyList, on the List sheet.

• You can run this macro on its own, with the Remove Single Letters button on the List sheet.
• This macro also runs automatically at the beginning of the mactos that add the letter headings.

To use this macro in your own workbook, change the sheet name and table name in the code.

• The macro counts the cells in the data rows of the named table, tblProducts
• Then, starting from the bottom of the list, it deletes rows that contain a single-character item

Sub RemoveLetters()
Application.ScreenUpdating = False

Dim wsL As Worksheet
Dim myList As ListObject
Dim rng As Range
Dim rngDB As Range
Dim c As Range
Dim i As Long
Dim lCells As Long
Dim lRow As Long
Dim lRowHead As Long

Set wsL = Worksheets("List")
Set myList = wsL.ListObjects("tblProducts")
Set rng = myList.DataBodyRange.Columns(1)
Set rngDB = myList.DataBodyRange
lRowHead = myList.HeaderRowRange.Row

lCells = rng.Cells.Count

For i = lCells To 1 Step -1
Set c = rng.Cells(i, 1)
If Len(c.Value) = 1 Then
    rngDB.Rows(c.Row - lRowHead).Delete
End If
Next i

On Error GoTo 0
Application.ScreenUpdating = True

End Sub

Add All Letter Headings Macro

This macro, named InsertLettersAll, adds all 26 letters as headings in the named range, MyList, on the List sheet.

• You can run this macro with the Sort and Add All Letters button on the List sheet.
• This macro removes old headings, then adds all 26 letters, and sorts the list.
• With this option, any letter can be typed in the drop down list, without an error message

To use this macro in your own workbook, change the sheet name and table name in the code.

• First, this macro runs the RemoveLetters macro, to remove old headings, if any exist
• Next, the macro counts the cells in the data rows of the named table, tblProducts
• Then, starting from the bottom of the list, it deletes rows that contain a single-character item

Sub InsertLettersAll()

Application.ScreenUpdating = False
RemoveLetters

Dim wsL As Worksheet
Dim myList As ListObject
Dim rng As Range
Dim rngDB As Range
Dim c As Range
Dim RowAdd As Long
Dim lRow As Long
Set wsL = Worksheets("List")

Set myList = wsL.ListObjects("tblProducts")
Set rngDB = myList.DataBodyRange
With rngDB
  RowAdd = .Rows(.Rows.Count).Row
End With

For lRow = 1 To 26
  wsL.Cells(RowAdd + lRow, 1).Value _
    = Chr(lRow + 64)
Next lRow

Set rng = rngDB.Columns(1)

rng.Sort Key1:=Range("A1"), _
    Order1:=xlAscending, _
    Header:=xlYes

Application.ScreenUpdating = True
End Sub

Add Used Letter Headings Macro

This macro, named InsertLettersUsed, adds letter headings in the named range, MyList, on the List sheet. It only add a letter if at least one product name begins with that letter.

• You can run this macro with the Sort and Add Used Letters button on the List sheet.
• This macro removes old headings, then adds all 26 letters, removes the unused letters, and sorts the list.
• With this option, only the used letters can be typed in the drop down list. For unused letters, a data validation error message will appear

To use this macro in your own workbook, change the sheet name and table name in the code.

• First, this macro runs the RemoveLetters macro, to remove old headings, if any exist
• Next, the macro counts the cells in the data rows of the named table, tblProducts
• Then, starting from the bottom of the list, it deletes rows that contain a single-character item

Sub InsertLettersUsed()

RemoveLetters
Application.ScreenUpdating = False

Dim wsL As Worksheet
Dim myList As ListObject
Dim rngDB As Range
Dim rng As Range
Dim c As Range
Dim RowAdd As Long
Dim lCells As Long
Dim i As Long
Dim lRow As Long
Dim lRowHead As Long
Set wsL = Worksheets("List")

Set myList = wsL.ListObjects("tblProducts")
lRowHead = myList.HeaderRowRange.Row
Set rngDB = myList.DataBodyRange

With rngDB
  RowAdd = .Rows(.Rows.Count).Row
End With

For lRow = 1 To 26
  wsL.Cells(RowAdd + lRow, 1).Value _
    = Chr(lRow + 64)
Next lRow

'reset the range
Set rngDB = myList.DataBodyRange
Set rng = rngDB.Columns(1)
lCells = rng.Cells.Count

rng.Sort Key1:=rng.Cells(i, 1), _
    Order1:=xlAscending, _
    Header:=xlYes

For i = lCells To 1 Step -1
Set c = rng.Cells(i, 1)
If Len(c.Value) = 1 Then
  If Left(c.Offset(1, 0).Value, 1) <> c.Value Then
    rngDB.Rows(c.Row - lRowHead).Delete
  End If
End If
Next i

Application.ScreenUpdating = True
End Sub

Get Sample File

To see the sample data and letter heading macros from this page, get the Letter Headings in Drop Down List.

The zipped file is in xlsm format, and contains the macros shown on this page, to add and remove letter headings.

More Tutorials

Data Validation Drop Down List

Named Ranges

Named Excel Table