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

    Like

  2. jejenjenal says:

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

    Like

Comments, suggestions, corrections are welcome.

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

WordPress.com Logo

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