Users commonly use predefined series of numbers, dates, days of the week or month. Besides these, you can create your own custom list (eg. the foreign names of the days, months, list of counties and cities, etc..), which will accelerate the formation of stereotypes, frequently used, consecutive values.

Custom lists

Creating custom lists is a litlle hidden setting in Excel, according to version differs the procedure.

  • 2003: Tools > Options > Custom lists
  • 2007: Office button > Excel options > Edit custom lists
  • 2010 and newer: File > Options > Advanced > Edit custom lists
Soubor - Možnosti

File – Options (czech)

In the Custom Lists window, select the new list and in the right pane write the list of values in a row separated by commas, or below. If multiple values are entered in a range of cells on a worksheet, you can select them by Import list from cells and confirm the option Import.

Such series are stored in the user profile settings and will work in all Excel files.

Vlastní seznamy

Custom lists

The possibility of sharing settings

If you want to back up the lists for later use, or want to share with others, there are some options. The actual series are not stored in a file, which could be easily copied. The following procedure is for advanced users only.

Searching the registry

Setting up custom lists are stored in the registry:

  1. Run regedit
  2. Search for key HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Excel\Options
    • number of version after \Office\ is derived from the version of MS Office, Office 2013 is 15.0
  3. SmartList  key contains information about custom lists
  4. Options can be exported, create a .reg file and then import the key on another computer

Saving using macros

Using this code you can create a macro, that after running the file, adds the values entered in column A, starting with cell A1 (in the sheet called “value_list”) to set up custom lists. The sample file with the macro can be downloade here.

1
2
3
4
5
Sub Auto_Open()
    On Error Resume Next
    Application.AddCustomList _
        ListArray:=Sheets("seznam").Range("A1", Range("A1").End(xlDown))
End Sub

If this guide has helped you, become a fan on Facebook and recommend this site to your friends, it can be useful for them too.

Similar posts about this topic

Leave a Reply

Your email address will not be published. Required fields are marked *