Under which section edit custom list button is available in Excel?
Nov 2 Creating and using Custom Lists in Excel by Alexander Frolov | updated on March 4, 2021 If you have to work with a spreadsheet that will always have the same list of information, or maybe you j…
Creating and using Custom Lists in Excel
by Alexander Frolov | updated on March 4, 2021
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
- Creating a custom list from scratch
- Creating a list from an existing worksheet
- Using Custom Lists in Excel
- Sorting by Custom List
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.
Under the Popular menu you'll find Create lists for use in sorts and fills sequences - Click on 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!
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.
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. :-)
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!
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
- Compare Excel spreadsheets and remove duplicates
- Customizing Excel charts: add chart title, axes, legend, data labels and more
- How to merge two columns in Excel without losing data
Excel: featured articles
- Merge multiple sheets intoone
- Combine Excel files into one
- Compare two files / worksheets
- Merge 2 columns in Excel
- Compare 2 columns in Excel for matches and differences
- How to merge two or more tables in Excel
- CONCATENATE in Excel: combine text strings, cells and columns
- Create calendar in Excel (drop-down and printable)
- 3 ways to remove spaces between words in Excel cells
Table of contents
12 comments to "Creating and using Custom Lists in Excel"
Surya says:
September 20, 2019 at 3:37 pmJust like rows to repeat at top, can we make rows to repeat at bottom of every page.???
ReplyAlyssa M says:
March 15, 2018 at 6:02 pmThank you!
ReplyDamir says:
July 19, 2017 at 2:08 pmHello
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 :-)ReplyAudunS says:
May 4, 2017 at 11:08 amHo 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.
Replydavid says:
August 6, 2015 at 4:51 amI 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
Replyjen k says:
July 8, 2015 at 6:32 amcan the created custom list work on cells even it formula in it? will it still work for the sorting? thanks
ReplyJeffry says:
May 7, 2015 at 6:21 pmHi, 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.
ReplyJudy says:
November 19, 2014 at 9:22 amHi
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
ReplyAlexander says:
November 20, 2014 at 3:03 pmHello Judy,
Please have a look at this article:
http://www.extendoffice.com/product/kutools-for-excel/excel-transform-column-to-row.html
If my understanding is correct, this is what you need.Reply
h.h. mccool says:
October 16, 2014 at 1:00 pmhow 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.
Replycelia jones says:
July 23, 2014 at 2:44 pmCan 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??
Reply- July 24, 2014 at 11:48 am
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.Reply
Post a comment
Video liên quan
Related post
Home
Nhà thiết kế WebTôi là admin trang go plus là một người có đam mê với Blogspot, kinh nghiệm 5 năm thiết kế ra hàng trăm mẫu Template blogpsot như" Bán hàng, bất động sản, landing page, tin tức...