{"id":3159,"date":"2020-12-03T10:58:10","date_gmt":"2020-12-03T09:58:10","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=3159"},"modified":"2024-05-22T21:28:03","modified_gmt":"2024-05-22T19:28:03","slug":"how-to-create-a-custom-list-in-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/how-to-create-a-custom-list-in-excel\/","title":{"rendered":"3 Quick Ways on How To Create A List In Excel"},"content":{"rendered":"

A Custom List<\/strong> in Excel<\/a> is very handy to fill a range of cells with your own personal list.<\/p>\n

It could be a list of your team members at work, countries, regions, phone numbers, or customers. The main goal of a custom list is to remove repetitive work and manual errors.<\/strong><\/p>\n

It is extremely useful when you need to fill in the same data from time to time<\/strong>. There are two options<\/strong> to create a list in Excel<\/a> that can be used repeatedly by using the fill handle.<\/p>\n

Let’s look at each of these methods one-by-one!<\/p>\n

Download this Excel Workbook and follow along with the tutorial on how to create a list in Excel :<\/p>\n

\n

download excel workbook<\/strong> <\/i><\/span>Custom-List-1-1.xlsx<\/span><\/a><\/p>\n<\/div>\n

 <\/p>\n

<\/a>Using Pre-existing List<\/span><\/h3>\n

At first, it might seem like magic how Excel does this!<\/p>\n

There are some lists that are already stored<\/strong> in Excel like days of the week and months in a year.<\/p>\n

\"3<\/a><\/p>\n

 <\/p>\n

To demonstrate the power of Excel’s Custom Lists, we\u2019ll explore what\u2019s currently in Excel’s memory as a default list:<\/p>\n

STEP 1<\/strong>:<\/span> Type February<\/em> in the first cell<\/p>\n

\"3<\/a><\/p>\n

 <\/p>\n

STEP 2<\/strong>:<\/span> From that first cell, click the lower right corner and drag<\/strong> it to the next 5 cells to the right<\/p>\n

\"3<\/a><\/p>\n

 <\/p>\n

STEP 3: <\/strong><\/span>Release and you will see it get auto-populated to July (The succeeding months after February)<\/p>\n

\"3<\/a><\/p>\n

 <\/p>\n

<\/a>Create a list in Excel manually<\/span><\/h3>\n

You can also manually add new values in the Custom List box and re-use them whenever you wish to.<\/p>\n

Let us go straight into the Options in Excel to view how it\u2019s being done, and how you can create your own Custom List:<\/strong><\/p>\n

STEP 1: <\/strong><\/span>Select the File<\/strong> tab<\/p>\n

\"3<\/a><\/p>\n

 <\/p>\n

STEP 2: <\/strong><\/span>Click Options<\/strong><\/p>\n

\"3<\/p>\n

 <\/p>\n

STEP 3: <\/strong><\/span>Select the Advanced<\/strong>\u00a0option<\/p>\n

\"3<\/a><\/p>\n

 <\/p>\n

STEP 4:<\/strong><\/strong> Scroll all the way down and under the General section, click Edit Custom Lists.<\/span><\/p>\n

\"3<\/a><\/p>\n

 <\/p>\n

Here you can see the built-in default Excel lists<\/a> of the calendar months and the days.<\/p>\n

If you click on a Custom List, you will see under List entries<\/strong> that it is greyed out and you cannot make any changes. \u00a0This indicated that it is a default Excel Custom List.<\/p>\n

\"3<\/a><\/p>\n

 <\/p>\n

STEP 5:<\/strong><\/span> You can create & add your own Custom List<\/strong> under the List entries<\/strong><\/em> section.<\/p>\n

Click on NEW LIST<\/strong> under the Custom Lists area and then manually enter your list<\/strong>, entering one entry per line:<\/p>\n

\"3<\/a><\/p>\n

After typing the values, click Add<\/strong>.<\/p>\n

In our screenshot below, we added the values of the Greek alphabet (alpha, beta, gamma, and so on)<\/p>\n

Click OK<\/strong> once done.<\/p>\n

\"3<\/p>\n

 <\/p>\n

STEP 6: <\/strong><\/span>Click OK<\/strong> again<\/p>\n

\"3<\/a><\/p>\n

 <\/p>\n

STEP 7: <\/strong><\/span>Now let’s go back into our Excel workbook to see our new Custom List in action. Type alpha on a cell.<\/p>\n

\"3<\/a><\/p>\n

 <\/p>\n

STEP 8:<\/strong><\/span> From that cell, click the lower right corner and drag it<\/strong> to the next 5 cells to the right<\/p>\n

\"3<\/a><\/p>\n

 <\/p>\n

STEP 9: <\/strong><\/span>Release and you will see it get auto-populated to zeta, which is based on our Custom List created in Step 8<\/strong><\/p>\n

\"3<\/a><\/p>\n

 <\/p>\n

Next up is a demonstration of how to make a list in Excel by importing data from another worksheet.<\/p>\n

 <\/p>\n

<\/a>Import from another worksheet<\/strong><\/span><\/h3>\n

You can easily import a custom list from another worksheet. Follow the steps below to get this done:<\/p>\n

STEP 1:<\/strong><\/span> Go to the File<\/strong> Tab.<\/p>\n

\"3<\/a><\/p>\n

 <\/p>\n

STEP 2:<\/strong><\/span> Select Options<\/strong> from the left panel.<\/p>\n

\"3<\/a><\/p>\n

 <\/p>\n

STEP 3:<\/strong><\/span> In the Excel Options dialog box, select Advanced<\/strong>.<\/p>\n

\"3<\/a><\/p>\n

 <\/p>\n

STEP 4:<\/strong><\/span> Under the General section, click on the Edit Custom List<\/strong> button.<\/p>\n

\"3<\/a><\/p>\n

 <\/p>\n

STEP 5:<\/strong><\/span> In the Custom List dialog box, select the small arrow up<\/strong> button.<\/p>\n

\"3<\/a><\/p>\n

 <\/p>\n

STEP 6:<\/strong><\/span> Select the range containing the custom list.<\/p>\n

\"3<\/a><\/p>\n

 <\/p>\n

STEP 7:<\/strong><\/span> Click on Import.<\/p>\n

\"3<\/a><\/p>\n

 <\/p>\n

STEP 8:<\/strong><\/span> Once the list will appear under list entries and click OK<\/strong>.<\/p>\n

\"3<\/a><\/p>\n

 <\/p>\n

STEP 9:<\/strong><\/span> Click OK<\/strong>.<\/p>\n

\"3<\/a><\/p>\n

Now, your custom list is stored in Excel!<\/p>\n

STEP 10:<\/strong><\/span> Type the first entry of the list “XS”<\/strong> in cell A8.<\/p>\n

\"3<\/a><\/p>\n

 <\/p>\n

STEP 11:<\/strong><\/span> From that first cell, click the lower right corner and drag<\/strong> it to the next 6 cells to the right.<\/p>\n

\"3<\/a><\/p>\n

 <\/p>\n

The entire list will be displayed in the selected range!<\/p>\n

\"3<\/a><\/p>\n

 <\/p>\n

You can even create this list vertically<\/strong>. Simply, type the XS in a cell. Click on the lower right corner and drag the cell downwards.<\/p>\n

\"3<\/a><\/p>\n

The list will appear vertically!<\/p>\n

 <\/p>\n

Conclusion<\/span><\/h3>\n

In this article, you have learned how to make a list in<\/a> Excel so that you don’t have to type the same list over and over again<\/strong>. You use the Custom list feature in Excel<\/strong><\/a> and store it in Excel and use it whenever necessary.<\/p>\n

You can either use the pre-existing list<\/strong>, manually type<\/strong> the list or link it from another worksheet<\/strong>!<\/p>\n

Further Learning:<\/strong><\/p>\n