Custom List is so good


Did you know… you can create a list of Month and Day of Week by drag and drop?  You probably knew it on the first week of your Excel journey… Excel Tips - Custom List.gif

But did you know that, Excel may also give you a list of your favorite fruits?Excel Tips - Custom List1.gif

That was one of the “wake-up” moments when I conducted in-house Excel Training in my ex-company.  It worked in every class so that I think I should write a post on custom list.

So how to do that?

Prepare your “custom list” on a sheet.

Note: it could be anywhere as long as it is a continuous 1D range i.e. either column or row.  In case you have selected a 2D range, you will be prompted to select either row or column).  In the following example, I input my list into A1:A4

File –> Options –> Advanced (scroll down to almost the end) –> Edit Custom Lists…

Excel tips - Custom list 3.PNG

If this is your first time to go to Custom Lists, you should see that there are at least four custom lists pre-input there…Excel tips - Custom list 4.PNG

To add your custom list…

  1. Have you list ready
  2. Select the range where your list resides
  3. Import

Excel tips - Custom list 5.PNG

You should see the newly imported custom list…Excel tips - Custom list 6.PNG

Note: You may input your custom list directly into “List entries”, list entries to be separated by pressing Enter.  Nevertheless, I prefer pre-input in a range and then import because I can do the spell check first. 🙂

When you are done, press OK.

Excel Tips - Custom List1.gif

As simple as that! 🙂

Well… that’s fun, but how it is going to improve my productivity?

Here’s an example:

If you are working in finance or accounting, you may probably deal with P&L very often.

Do you type all the elements one by one every time you create a new P&L? Or do it with only typing the first element, and then drag it down?

Excel tips - Custom list 7.gif

Note:  There is a limitation of total characters (including space) for custom list.Excel tips - Custom list 8.PNG

Extra: Use custom list in sorting!

Have you ever sort a list of Month and it turns out to be a mess?

Excel tips - Custom list 9.PNG (sorted A-Z…)

To fix that, simply sort by Custom List!

Excel tips - Custom list 10.gif

What’s your idea on using Custom List?  Please share with us in comment.


About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips and tagged . Bookmark the permalink.

3 Responses to Custom List is so good

  1. jejenjenal says:

    Learning Excel Is Funny


  2. jejenjenal says:

    I found it very clear explanation about the topic of custom list in excel.


Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.