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.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s