Creating a custom list and using sorting in Excel 2007

Custom lists in Excel 2007 are an excellent feature allowing you to create lists similar to those already built in to Excel 2007 and then use them to auto-fill cells with lists of your own choosing. If you find yourself repeatedly typing the same lists into Excel, then a custom list is definitely for you. You can create a custom list containing anything your heart desires and have Excel 2007 finish lists off automatically for you. You can also sort data using your custom lists which allows you much more flexibility when sorting data. You can sort your data using one or many criteria from your own custom lists and / or the in-built sorting offered in Excel 2007. Before we examine the creation of these custom lists, let’s look at the lists Excel 2007 has already created.

Excel 2007 already has a number of built-in lists, including the days of the week, months of the year and the seasons to name but a few. Numbers already work in the same fashion as a custom list, as do dates and times. The picture below shows the lists Excel has built-in (to get to this screen, click on the Office button, then select Excel Options / Popular and then Edit Custom Lists).

To use the built-in lists, type two values from one of the lists in a row (for example Monday and Tuesday) and select the two cells. If you then drag the cursor from the bottom right corner it will auto complete the list for you as illustrated below.

You may find that you need a list created for your own particular needs beyond those already available in Excel 2007. To achieve this, first create your list in Excel in a single column as shown in my example below.

NOTE: Custom lists can only be created using values such as text, numbers, dates and times. Custom lists cannot be based on a format (for example cell colour or font colours).

Next, select your list and click the Office button, then Excel Options / Popular / Edit Custom Lists. You will notice that Excel has populated the range you selected next to Import as shown below

Click Import and you will see that Excel 2007 has added your custom list on the left and listed the List Entries in the centre as shown below.

Click OK to return to Excel. Now, if I type in Low then Medium into a column I can drag and auto complete to my hearts content to auto-fill as many cells as I desire.

Once created, a custom list can be edited or deleted by once again going to the Office button, then Excel Options / Popular / Edit Custom Lists. To edit a list, click on the list on the left hand side of the dialogue box. The List Entries field will be populated as in the picture above. You can delete the list simply by clicking the Delete button on the right. To edit the list, simply click on the list entry you want to change and make any changes you would like to make before clicking OK to return to your Excel spreadsheet.

Another cool feature of custom lists is that you can sort using them. Using my example of my music shop, I would like to be able to sort my orders based on the media that the music comes on.

So first, I will create a custom list that contains all the media my music is available on exactly as I did above in the previous example.

With my custom list created, I now click on a cell in the range I am looking to sort and click on the Data tab and then the Sort button. If your data has headers (as mine does), ensure that you click on My Data has headers.

NOTE: If you do not have headers or you do not select the option, in the Column / Sort by drop down you will see Column A, Column B and so on rather than meaningful headers. To make sorting much easier to work with, I strongly recommend using headers.

As I am interested in the media my music is on, I am going to choose Media in the Column / Sort By drop down box. I am going to choose Values for Sort On as that best fits my data. For Order, I select Custom List from the drop down box and then select my custom list. You can see my unsorted data and the Sort dialogue box below

Once I click OK, Excel 2007 sorts the data as I requested and my data now appears as below.

I can sort using custom lists on any of the column headings I desire. All I would need to do is quickly create a custom list and then I could for example sort by Artist, or by Unit Cost.

So now that we have sorted our data by one criterion, in this case by Media, suppose that I want to sort by another. In this case, I want to also sort my artists into alphabetical order to make them easier to find when my catalogue gets bigger. To do this, I again click on a cell in the range I am interested in and select the Sort button on the Data tab. To add extra criteria to sort on, first click the Add Level button. In my case I am interested in Artists, so I select that in the Column / Then By drop down box. I select Value to Sort On as before and then A to Z for the Order. The Sort dialogue box for my example looks as illustrated below. This then sorts my data by both criteria as I required. You can see that by sorting on additional criteria, you can greatly increase the sorting options available to you. The number of levels you use is only really limited by how much sorting you need to do on your data.

As I hope I have shown you through my examples, custom lists in Excel 2007 can save you significant amounts of typing if you are currently entering in long lists of data manually into spreadsheets. The same custom lists can also be used to sort data which allows you much more flexibility when sorting your data. You can also sort by any number of criteria you need to get the data exactly how you wanted it sorted. I do hope you enjoyed reading this hub and that you found it useful.

I also have a number of other hubs on aspects of Excel 2007, covering everything from Conditional Formatting to creating charts and graphs. I have an Index hub which also covers how I successfully transitioned from Excel 2003 to 2007 as well as outlining my other Excel 2007 hubs which can be found here

http://robbiecwilson.hubpages.com/hub/How-to-adjust-to-Excel-2007-from-previous-versions-as-well-as-step-by-step-guides-to-many-functions-in-Excel-2007