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.
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
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.
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:
- Run regedit
- 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
- SmartList key contains information about custom lists
- 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.