How to create custom lists in Excel

 Mar 31, 2015

Did you know that Microsoft Excel is very smart?

If you type the name of the month (e.g. January), it knows automatically what comes next in the series so that if you use the copy tool known as the Fill Handle (that little black cross found only on the bottom right corner of any active cell), it will create a list of all the months that follow January. You can fill the series rather than just it copying the same thing to the cells you drag through. This is because this list is programmed into Excel. The same goes for the name of the days of the week and their shortened versions too, such as “JAN” or “Mon.”

In addition, you can create custom lists to use for whatever you want, such as sorting. Perhaps you have a list of store locations that you use all the time and you want to be able to type the first one and then have the rest of them generated automatically without having to type them in each time. You could copy and paste but that is not going to allow you to sort by that order later so it is not the best solution. Let’s try an example.

So let's say your list is made up of all the shopping centres where you have your stores in and you usually use them in order of store floor space, not alphabetical order. In Excel, you would type this list the first time in a column then highlight or select it.

How to create custom lists in Excel

Next, open up your options and go to the “Advanced Tab” and look under the “General Section” for the “Edit Custom Lists” button.

How to create custom lists in Excel

Click on it. Notice the other lists mentioned above with regard to the months and days of the week already are there? Also, notice that in the lower right corner there is an import button? The cells you have highlighted in your list should read this:

How to create custom lists in Excel

Next hit “Import” and you should now see the list of the shopping centres in the same order you typed them up in. Next time, all you need to do is type the first entry of the list (in this case, Bondi Junction) and then use the fill handle to drag down. You should see the rest of the 13 locations that follow be filled in automatically for you. If you then decide to sort them in alphabetical order or by another column, for example, and then you want them to go back to the original order then you can open the sort dialogue box, choose “Custom Sort” and choose the list that you want to sort by. Voila! Now you can type in any one of the suburbs and use the fill handle to complete the list or if you have the list in another order, you can sort it back to the original order by choosing custom list from the bottom of the sort dialogue box.

How do your Excel skills stack up?   

Test Now  

About the Author:

Liz French  

With over 7 years of experience as a trainer, and an extensive skill set that spans across the entire Microsoft Office suite of applications, Liz brings an incredible amount of knowledge, expertise and care to each course she delivers. Having worked as a tutor in the education sector for many years prior to her career at New Horizons, Liz has gained an extensive amount of experience in providing training to individuals and groups of varying skill levels. As a certified Microsoft Office Master, Liz is a highly capable trainer who possesses the ability to connect with students of all levels and backgrounds in order to help improve their skills across a wide range of common Desktop Applications.

Read full bio
top