Excel custom lists can be used to fill out cells or to sort data in a user-defined order. Excel comes with some pre-defined custom list that can be viewed by going to the File-Options-Advanced menu. Scroll down until you find the Edit Custom Lists button.
When you click the button, a new dialog window will open where you can see the pre-defined custom lists:
This custom list can be anything from the months, to colors, brands, etc. To create a new custom list, just select the NEW LIST on the “Custom lists” field and start typing the entries on the “List entries” window, pressing Enter on each entry to move to the next line of the list. When you’re finished, press the Add button and your new list will be created and will appear on the “Custom lists” window, like on this example, where I’ve created a colors custom list:
You can also also write on a range of cells the values that you want to use to create your custom list and use the “Import list from cells” option where you specify the range of cells that have your data, click on the Import button and your custom list will appear on the “Custom lists” window, like on this example:
Now, let’s see how to use this custom lists. For example, on cell A1, type “Jan”. Then select the cell and drag to the right, by pushing the right bottom little black square that you see on the cell to see what happens.
You will get something like this, depending how far did you pushed the cell to the right:
You can use this method to fill out cells with data from other custom lists that you have on your Excel. You don’t need to start with the first value on your custom list. For instance, instead of inserting “Jan” on cell A1, if I insert “Mar” and push the cell to the right, I would get a list of “Mar, Apr, May…”.
You can use your Custom List to sort data using the order that you defined when you created the list. For instance, I’ve this table of data:
I want to sort the columns by months. I already have a Custom List build with the correct order (“Jan”, “Feb”, “Mar”…) that I can use. Just select the range A1:C2, go to the Data-Sort option on the menu bar and when the Sort dialog box opens, on Row, select “Row 1”, because we want to sort by this row, and on Order select “Custom List…”. This will open the Custom Lists dialog where you need to choose the right custom list to use.
Click OK on both dialog boxed and you should get you table sorted on the correct order: “Jan”, “Feb” and “Apr”.
