Custom lists are very useful in Excel because they are a quick way to get multiple entries into cells and sort pivot tables in the desired manner. They are constantly used to get a sequence of month names or names of days such as Monday, Tuesday….
You can see which Custom Lists are already installed by going to
FILE/Options and then Advanced, and scroll almost to the bottom and then choose Edit Custom Lists….
At this point you will see the Custom Lists that are available to you.
In the same window you can add your own custom list by entering the list manually divided by semicolon or by selecting cells containing the new list and pressing Import.
But this can be quite time consuming and can be achieved faster by the following VBA code
Sub XET_CustomLists_CreateFromCells() Dim rows As Long, cols As Long Dim sheetlist(1024) shc = 0 rows = Selection.rows.Count cols = Selection.Columns.Count If (rows > 0) And (cols > 0) Then For Each c In Selection If IsNumeric(c.Value) Then MsgBox "Numbers not valid Custom List values" Exit Sub End If If c.Value = "" Then MsgBox "Null values not valid Custom list values" Exit Sub End If Next Application.AddCustomList Selection Else MsgBox "No selection, no fun" End If End Sub
This Code can also be found in an Xnet Excel Tools AddIn for Excel which is free and can be downloaded here.