Lesson 7: Excel's Custom Lists


Introduction: Sort the way you want

Have you ever sorted data in Excel?

For sure. I organized a small quiz last year and had to sort the teams according the points they scored, from largest to smallest. It worked pretty well.

So, you used the standard Excel sort functionality as shown below:


That is exactly what I did.

And what if you don't want to sort data alphabetically or from smallest to largest but would like to use your own sorting order, how would you proceed?

I absolutely have no idea.

This is where...

Wait! Sorry to interrupt but I actually have an idea! I would create a helper column.

A helper column?

Yes, a helper column. You know, one extra column I would add to my data table and containing a numeric value for instance I can use to sort upon. So if I would have to sort the values "Small", "Medium and "Large" in that order, I would in my helper column combine "Small" with "1", "Medium" with "2" and "Large" with "3", and sort my helper column from smallest to largest.

That would work. However, there's a better way. This is where Excel's Custom Lists come in handy.

The power of Excel's Custom Lists

Have a look at the table below:


It's a list of a company's sales representatives with the region they're responsible for.

Big regions. I guess these people travel a lot and earn quite some miles.

They surely do. Now, I'm the CEO of this company and would like to sort my sales representatives according the region.

Are you kidding me? Just sort them alphabetically and you're done.

I could do that, but I would like to keep all Asian regions together, which won't work if the data is sorted from A to Z. Central Asia would come before Europe whereas South Asia would follow South America. And that's annoying.

My helper column would do the trick. Assign in that new column a "1" to "Central Asia", a "2" to "East Asia", a "3" to "South Asia" etc. and sort that column.

There's a better way. As you can see below, I just listed all regions in the correct sort order, i.e. the order I want them to be sorted:


I formatted my list as a table, but it's just to make the data look good, it has no added value. Now you've to tell Excel that this is a sorting order you'd like to use. To do so, click the File tab, then click Options. Click the Advanced tab and scroll down to display the General section. In this section, click the Edit Custom Lists button:


A Customs Lists dialog now appears:


As you can see, Excel already includes some standard Custom Lists options. We need to add ours either by typing the list directly in the List entries listbox and pressing "Enter" after each entry or by just importing the data from a list we previously created.

For sure you'll use that last option, you created your sorting order a few steps ago.

Correct. I just need to specify the range to use in the "Import list from cells" box and press on the Import button. Look at what happens:


Hey that's easy. Excel took over the data! Now, how do I use it?

Exactly the same way as you would do when sorting your data alphabetically. But instead of choosing A to Z or Z to A, you choose your own Custom List:


Hey this is nice! And easy!

Isn't it? This is the result:



Filling in Data with a Custom List

There's one more feature I'd like to show you regarding Custom Lists. These lists also work with the AutoFill functionality.

I'm not sure I understand.

Have a look. I just enter in a random cell one of the entries of my Custom List, Europe for instance, being the first one but it works with any item in the list. Next, I drag the AutoFill handle through the blank cells:


Look what then happens:


Oh! Excel automatically completes the cells with the list's data.

Exactly. Well that was it. Hope this will be useful!

Good to know, and to remember...

  • Once a Custom List is created, it is available for all workbooks;

  • You can use a reference to a named cell range on a worksheet to import a Custom List. However, the items on the Custom Lists dialog are not linked to the named range. If you change the list on the worksheet, the Custom List will not be adapted;

  • The AutoFill functionality works both vertically and horizontally.

We appreciate your feedback!


About Cintellis

Let us surprise you with how much more you can achieve by using Excel and VBA based products.

Are you looking for an on the level, reliable and trustworthy company that will surprise you with how much more you can achieve by using Excel and VBA based products? Learn more about our philosophy, products, services or contact us for more info.





About Numis

Flexible, cheap and very short implementation time, discover Numis, our Excel based Cash Management and Management Accounting Software and experience a totally new way to analyse and report on your cash positions.

At Numis, we help you to get more out of your financial data than you thought possible. From enhanced reporting functionalities and detailed analysis to providing enterprise budgeting and forecasting solutions, we will help you to achieve your business objectives. When using Numis, you can share your analysis and strategy with your business partners, suppliers or employees as needed.

Numis is a product from Cintellis bvba & FS&P bvba.

CONTACT Us

You can contact us by phone, mail or by filling out the form below.

Corporate address:
Alsembergsesteenweg 588
1653 Dworp
Belgium

Phone Numbers:
+32 (0)471 65 19 66

E-Mail: info@cintellis.com