Excel sort custom list wildcard

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 whenI conducted in-house Excel Training in my ex-company. It worked in every class so that I think I shouldwrite 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 inputyour 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 thats fun, but how it is going to improve my productivity?

Heres an example:

If you are working in finance or accounting, you may probablydeal 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] forcustomlist.

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!

Whats your idea on using Custom List? Please share with us in comment.

Video liên quan