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