Custom List in Excel Explained

Written by Amey Dabholkar

July 16, 2015


You must be aware that when you write Monday in a cell and you click and drag it you could see that all the names of the days are populated in those cells which you have selected and even if you drag more than 7 it again starts the list from Monday. Do you know that you could also create these types of the list at an ease? Such lists are known as a custom list. Today I will be showing you 2 different ways by which you can create your own custom list. One method is the regular method and another one is a bit difficult method by using Excel VBA. Learning Excel VBA method is useful for 2 reasons:
1. Learn a bit of coding to implement in further projects
2. You want your clients to create their own list and hence give them ease of use by creating a list

Understanding Predefined Lists

List are two types one is a Predefined list and another is a custom list. Predefined lists are those list which is available to you when you initially install the Microsoft Excel. This type of list cannot be deleted rather modified. Another type of lists is Custom list which we are going to create in Excel. This list is created by us and can be modified deleted by us.

In the video demonstration above you could see that as and when we type and drag down the fill handle we get populated with the names we intend to write next.

Creating Custom Lists via Settings

In Excel 2013 we have to go to File –> Options and in Advance Tab search for custom lists. In Custom list selects the cells which you want to import as a custom list and then click import from Range, Once imported you can use that list as normally as a normal predefined list.

Creating Custom List Via VBA

To Extend the functionality of Custom list you can use Excel VBA to Create Lists. The first step is to go to VBE (Visual Basic Editor) and then select the relevant worksheet in the project Explorer and then add a new module. A module is basically a placeholder of VBA codes which can be used in that sheet.

Type the code mentioned below in the VBA Module and close the screen of VBA. You could also toggle between screens by pressing Alt + F11 or a general windows command Alt + Tab to shift between application.

Sub Add_Custom_list()
Dim List As Varient
List = Application.Selection
Application.AddCustomList List
End Sub

Sub and End Sub is the way to start writing a custom VBA Procedure in Excel.

Dim means we declare the variable as a Variant.
We have now declared List as a Variant and now we define it as a selected range
We use a code Application.AddCustomList and then we mention the name of the range i.e List.

Extend Functionality

You can now Extend the Functionality of this VBA Module by Adding a button onto worksheet or creating an Add-in. Creation of an add-in will be explained in future blog posts.

Download the Sample File used in this Blog Post

Please Share



Submit a Comment

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

Pin It on Pinterest

Share This