ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Software

Creating a custom list and using sorting in Excel 2007

Updated on September 15, 2012

Introduction

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.

Using the built-in lists

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).

The range of built-in lists in Excel 2007.
The range of built-in lists in Excel 2007. | Source

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.

Illustration of how to use lists in Excel 2007 to auto complete cells.
Illustration of how to use lists in Excel 2007 to auto complete cells. | Source

Creating a custom list

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).

Creating a custom list in Excel 2007.
Creating a custom list in Excel 2007. | Source

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

Using a range of data to begin to create a custom list in Excel 2007.
Using a range of data to begin to create a custom list in Excel 2007. | Source

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.

Custom list created and ready for any edits in Excel 2007.
Custom list created and ready for any edits in Excel 2007. | Source

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.

Editing or removing a custom list

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.

Sorting using custom lists

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

Using the sort command with a custom list in Excel 2007.
Using the sort command with a custom list in Excel 2007. | Source

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

Sorted data using a custom list to sort in Excel 2007.
Sorted data using a custom list to sort in Excel 2007. | Source

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.

Sorting by more than one criteria

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.

Sorting using multiple criteria in Excel 2007.
Sorting using multiple criteria in Excel 2007. | Source

Conclusion

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

Comments

    0 of 8192 characters used
    Post Comment

    • profile image
      Author

      Robbie C Wilson 5 years ago

      Thanks for your kind comment, I am so glad you found it useful.

    • techhound profile image

      techhound 5 years ago

      I love when I learn something new, especially with a tool like Excel that I use so readily. Thanks for this awesome tip which I am totally going to start using.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: "https://hubpages.com/privacy-policy#gdpr"

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)