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 doesn’t work on Excel most of the time at current job...... 🤔 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 )

Google photo

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

Twitter picture

You are commenting using your Twitter 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.