Creating and using Custom Lists in Excel

If you have to work with a spreadsheet that will always have the same list of information, or maybe you just don't want to use copy/paste every time, it would be most beneficial to have a pre-set list stored so that Excel can help you with what you are trying to do. Having a Custom List is the way to go and I'm going to show you how you can create a custom list in Excel.

Creating Excel Custom Lists

For the sake of this demonstration I'm using a list of school clubs. I need to keep tabs on how much money each account has and want to chart their daily balances. I started off by typing in the club names - information I know I'm going to need on a regular basis. (At this point I would also run a spell check which can be done by simply clicking F7.) I click and drag to highlight that area, click on the Microsoft Menu button on the top left, and then click on Excel Options at the bottom.

Start to create a new custom list from Excel Options

Under the Popular menu you'll find Create lists for use in sorts and fills sequences - Click on Edit Custom Lists.

Edit Custom Lists

If you are using Microsoft 2010 then you'll need to go through a different route. Click on File, then on Options. Click on advanced and scroll down till you find Edit Custom Lists.

The next box you get is where you will add the information you are going to use regularly. If you look at the Custom lists already in Microsoft, you'll see that there are pre-set lists that are commonly used - remember that these cannot be deleted or edited. However, if you want to delete or modify the list you've created you can do so at any time (click on the list and change it however you see fit).

Creating a custom list from scratch

I have two options of adding a custom list. I can add it manually by typing each value info in the List entries section and then clicking Add. If you choose to go this route, there are some limitations. The List Entries field will allow only 255 characters. Be mindful of how many characters each entry has!

Tip. If you're going to manually enter your list in the List entries box, do not include extra space between entries. Microsoft will only pick up the ones listed consecutively.

Creating a list from an existing worksheet

Another way we can add entries to our custom list is to import the data. If you highlight it before going into that screen, it will automatically have the range for you selected and all you have to do is click on Import and Excel will include the text for you. If you didn't select the text, click on the box near the Import button and select the cells with the info for your new list. Remember how we had limitations on how many characters can go in the List Entries box? Not when importing! The maximum size of the list is now approximately 2,000 characters! To finish up this step click on Okay to close the box and Okay again to close the Excel Options box. A custom list can contain only text data. If you need to create a custom list with dates or numbers you will have to use the List Entries field.
Delete or modify the custom list you've created.

Some quick information you should know about your Custom Lists... these lists are computer specific. The settings are saved to the computer you are currently using. If you took the file from work and wanted to work on it at home then you would have to add that same custom list to your personal computer. Now if you used your custom list for sorting then it is embedded into your worksheet but it won't show up on your custom lists.

Using Custom Lists in Excel

Alright, so our custom list is ready to use. Go to any cell and type in any entry from your list. Click on the small box on the bottom right corner, from that point in your list it will fill the series for you! Remember I said I'd show you how to use the other common lists? Well, at the top I type in Monday, click and drag the bottom box and Excel will auto fill it for you. The best part of using this feature is that Excel doesn't care if you are filling a column or a row, if you are going forward or backward, or if you start with the first/middle/last entry of any custom list... as long as you click and drag it's going to recognize what you want to do and it will fill in the data for you. :-)

Custom list is ready to use Type in any entry from your custom list and Excel auto fills the data for you.

Sorting by Custom List

One of the added features of having a custom list is that you can always sort by any one of the lists that is saved on your computer. Click on one or more columns and click on Sort & Filter, drop down the Order menu, click on Custom List..., and find the list by which you'd like to sort. Sorting can always include more than one column. If you want to add another level so that you sort by month first and then by the account, then you can click on Add Level (you can include as many levels as necessary to suit your needs) and define how you want your data displayed. Finish this up by click on Okay. Your data is now sorted by that list!

Sorting by Custom List

It's just that simple! The custom list function is best for those who want to save themselves a lot of time from having to cut and paste over and over again. If you know you have a set of fields that you are always going to use, why not try making a Custom List. Who knows, you might find that using it will save you more time than you thought... leaving the boss thinking you worked very hard on it. Let him think that.

Enjoy!

See also

11 Responses to "Creating and using Custom Lists in Excel"

  1. celia jones says:

    Can a custom list be a list of numbers?? In my job we have "areas" that are identified by numbers (area 8121, area 8122, area 8147, etc) but I do not want the "area" in front of their # when listing, I have tried adding it to the custom list but it will not work..... is there a way??

    • Alexander says:

      Hello Celia,

      Yes, you can create a custom list of numbers as well as of words.
      You just have to enter your numbers either directly in the "List Entries" field of the "Custom Lists" dialog, or if you have the numbers in your Excel sheet, convert them to text by adding ' before each number (e.g. '2), and then you will be able to import a list from cells.

  2. h.h. mccool says:

    how do you delete entries on the custom list? There is a delete button shown, but it does not highlight and will not work.My excel is arbitrarily changing "general" setting to "custom" date setting.

  3. Judy says:

    Hi

    I have a list with over 10000 rows. Say my page can fit 15 rows for print, but now it's printing one row and waste paper. What would I do to automatically insert a break to go to the next column to fill the page with the numbers?

    Been sitting with my hands in my hair about this and copy and paste is a waste of time...

    Judy

  4. Jeffry says:

    Hi, I wanna delete multiple custom list, I have more than 100 but I can't delete one by one is to much wasted of time.

  5. jen k says:

    can the created custom list work on cells even it formula in it? will it still work for the sorting? thanks

  6. david says:

    I would like to make a column in excel only compatible with a few entry options. How can I do this? Example: I want input one of three options into column C, Communications, Transportation, and Maintenance. I don't ever want the column to have any other entries of any kind. Is this possible? If so how do I do this.

    david

  7. AudunS says:

    Ho can I import lists longer than 255 numbers? This limit is way too small for my sorting list. Excel does not even bother to tell me that the imported list was too long and that it stopped on the row 175 of my 800-row custom list.

  8. Damir says:

    Hello
    I have a problem with Custom list. Is it possible to make a list of for example 100 inputs and then when you type the first letter in cell, it offers you possibility so that you can choose between just a few possibilities not the whole list. i know it is possible when the hole list is in every cell so you go down and by first letter it offers possibilities. But in my case i do not have that option, can anybody help?? My English is not so good, i apologize for that :-)

  9. Alyssa M says:

    Thank you!

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2019-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard