Tuesday, June 28, 2016

Creating A Custom List In Excel; An Interesting Example.

If you are an average or above average Excel user, you will be very familiar with Excel autofill. A common example is you typing numbers 1 and 2, then dragging to have Excel do to 10 for you. Or having January and February, then dragging to have Excel do the other months of the year for you.

What if I tell you that you can create your own list. A list of states in Nigeria which you can autofill whenever you need to recreate them. Or a list of your products; you simply type the first two and drag to create the rest (autofill). It even helps you with sorting in the order you've created the list. This can be super useful for companies that use codes for aspects of their operations -- base station address code, branch code, product code and so on. Caution: The custom list has a quite limiting size limit. 

So here is the example of how I created a custom list of states in Nigeria, arranged alphabetical order.

First, I have the states typed out in Excel, in the order I want it (alphabetical order).

And we are half-way done to having it permanently in Excel as an autofill-able list.

Go to File, Options, Advanced, and Edit Custom Lists.

In the Custom List Dialog box that comes up, import the list of states you already have typed into Excel.

And you are done.

Now in a new Excel file or Sheet, type in the first two states, select them and drag down. Excel will start autofilling the other states.

Congrats! Now you've created a custom list. No more typing out states in Nigeria, just type the first (or first two) and have Excel autofill the rest for you.