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… 

But did you know that, Excel may also give you a list of your favorite fruits?

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…

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…

To add your custom list…

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

You should see the newly imported custom list…

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.

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?

Note:  There is a limitation of total characters (including space) for custom list.

Extra: Use custom list in sorting!

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

 (sorted A-Z…)

To fix that, simply sort by Custom List!

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