One of the popular question in the community is does Excel provide the flexibility to create user defined list that would be heavily used and can be auto populated – Its actually very simple. For example, suppose that you often have to enter a standard series of student name as the column or row headings in new spreadsheets that you build. Instead of copying the list of students from one workbook to another, you can create a custom list containing all the students in the order in which they normally appear in your spreadsheets. Lets take a look.
First, create the list that you would use frequently
Select File -> Options -> Advanced. Scroll down to “Edit Custom Lists..”
There are options to upload the custom list. First to manually enter the list as shown below:
Once the list is added, hit “Add”. The newly created list is shown in the left hand side
The other way of adding the list is to import the created list. This options works when the list is big. Click on the “Up arrow” button in the Custom list dialog box:
Ensure the entire cell range of the list is covered to add the list.
Click on Import button which would add the list.
Now that we have done creating the custom list. Lets see how to use them.
Whenever you need to use Autofill of the created list, simply type the first value (name in our case), and drag/drop the fill handle to get the entire list automatically. Shown below:
You don’t have to necessarily start from the starting name – you can type in any name and drag the fill handle down to get the list.
The drag also works sideways if you need to fill the rows.
Please note, the list once created can only be deleted and cannot be edited to either add or remove a name.
I hope you liked this tutorial. Please encourage us with your valuable feedback to improve overall content of this blog.