ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

How to Filter and Sort Data in Excel

Updated on April 26, 2015

Improve your Spreadsheet Skills with these Simple and Powerful Techniques

In other spreadsheet exercises, I have covered the basics of setting up and formatting worksheets using Microsoft Excel. The page links are below. Here, we are going to look at some useful techniques for filtering data and sorting data. You will need to begin with a simple spreadsheet like the one shown below. You can use Microsoft Office, if you have it. But, the skills are transferable to other spreadsheet programs.

If you are not quite ready for filtering and sorting data, have at look at my pages on:

  • Spreadsheets for beginners : covers the fundamentals: what spreadsheets are, how to use them, rows, column and data types.
  • How to make a Household Expenses Spreadsheet : a step-by-step introduction to recording monthly expenses.
  • How to Format an Excel Spreadsheet : covers formatting of cells to show currency and dates, as well as enhancing the presentation.

Please note that the images on this page are copyright to me, and must not be used elsewhere without my written permission and a link to this page.

Set Up Your Spreadsheet

I have used Microsoft Excel to set up my spreadsheet, and included four columns:

food type

item

unit price

units in stock.

You can copy this information into your own spreadsheet, or make something similar which is more appropriate to your interests.

Applying Filters in Excel - Filtering by Food Type

Make sure you are on the Home Tab on your spreadsheet. This tab includes 7 groups of buttons, from left to right: Clipboard, Font, Alignment, Number, Styles, Cells, Editing. It is the Editing Group that we are concerned with here.

Click on the A at the top of the food types column. This will select the whole column. Then click on the Sort & Filter button, in the Editing Group. From the drop-down menu, select Filter. A small arrow will appear on the right side of cell A1.

Click on the arrow to show the drop-down menu for sorting and filtering this column.

Filtering a Column - The drop-down menu

At the moment, all food types are selected and shown on the spreadsheet. You can see this because of the ticks in the Select All and individual food type tick boxes. The last entry on the list is blanks. This means that blank rows within the list and under it are also shown.

If you untick Select All, and tick just one box, such as Dairy, you can filter out all the other information.

Filtered Data

The spreadsheet is now showing only 4 rows of data. You will also notice that the symbol has changed from an arrow into a little funnel. It's helpful to be aware of this change, so you can keep track of which columns have filters applied at any one time.

Before going on to the next section make sure you remove this filter to show all the data again. To show all data in the column, click the funnel symbol, and then click the box for Select All.

Using Number Filters

Great than, Less than, Equals

Let's imagine that you want to re-order some items that are running low in your stock. You could print out your list, and go through it manually, highlighting the stock items that are low. With a few items of stock, that wouldn't take many minutes. But, what if you had thousands of items to check? Using Number Filters in Excel is much more efficient.

Great Than, Less Than, Equals - Using the Less Than Filter

There are a few choices in the Number Filters, including Great than, Less than, and Equals. The Great than filter is used for finding the rows where the value in the column cell is higher than a certain amount. The Less than filter is used for finding the rows where the value in the column cell is lower than a certain amount. The Equals filter is used for finding the rows where the value in the column cell is the same as a certain amount.

I selected Column D, and turned on the filter arrow. The photo shows that when I clicked the filter arrow in Cell D1, the drop-down menu appeared, from which I selected Number Filters, and from the second drop-down menu, I selected Less Than.... This brought up the Custom Autofilter.

Custom Autofilter in Action

Here I entered the number 10, so the Autofilter will show only the rows containing less than 10 in Items in Stock.

Filtered Data

Now we can see which items are running low in stock, and re-order as necessary.

Sorting Data in Excel

Sorting data automatically

Let's imagine we need to sort the items alphabetically. Even with just 20 rows, it would take a long time to sort by cutting and pasting the rows into the right order. It is quicker, more efficient and probably more accurate to use Excel to do this task.

I can do this either directly by clicking on the Sort & Filter button in the Editing Group or by turning on the column filter first, and clicking the arrow to get the sort & filter drop-down menu that we used previously. The results will be the same.

Sorting Data Alphabetically

In the photo above, you can see what happened when I selected Sort A to Z from the drop-down menu. As I had selected only one column, the Sort Warning box appeared.

Here you have the opportunity to expand the selection before sorting. This ensures that the correct prices and stock records are kept in the same rows as the items.

Below, you can see the results of sorting from A to Z, by item.

Items Sorted Alphabetically

You can also Sort Z to A, if you need to.

For number data, it is possible to sort from smallest to largest, or from largest to smallest, following the same procedure.

Depending on the task you need to complete, you can combine different filters, in different columns, to select and re-order the data. When combining two or more filters, think first about the best order to do it. And, remember that the filter arrow at the top of the column will change to a different symbol when a filter is being applied.

Have fun with Excel!

The Excel Bible - A top book for anyone who wants to learn Excel

If you are looking for a comprehensive reference, you won't go wrong with John Walkenbach's Excel Bible. Whatever your level of skill, from beginners to advanced, the Excel tips, tricks, and techniques will boost your knowledge and confidence.

Excel 2013 Bible
Excel 2013 Bible

John Walkenbach is recognized as an expert on Excel. His writing is comprehensive, clear and easy to follow. This updated version of the Excel Bible to help you understand, and get more out of Excel. It covers Excel basics, worksheet design, use of formulas and functions, making charts and graphics and much more.

 

Excel Filtering Videos

To remind yourself about use of filters in spreadsheets such up in Mircosoft Excel, or to explore more advanced use, have a look at this highly rated video.

More Reading On Excel

Useful quick reference cards and reference books to help you get the best from your Excel software.

Microsoft Excel 2010 Introduction Quick Reference Guide (Cheat Sheet of Instructions, Tips & Shortcuts - Laminated Card)
Microsoft Excel 2010 Introduction Quick Reference Guide (Cheat Sheet of Instructions, Tips & Shortcuts - Laminated Card)

This handy quick reference card is laminated for protection. With easy to use, step-by-step instructions, it is useful as a training handout, or reference guide.

 
Excel 2010: The Missing Manual
Excel 2010: The Missing Manual

Excel 2010: The Missing manual focuses on the features that are useful in real-world situations. In addition to covering Excel's features and functions, you will discover how to update files with new data, how to generate monthly financial reports, and much more. It is written in an easy-to-follow and non-technical way, so is appropriate for all.

 

© 2013 savateuse

Do you have any tips, on using Excel, to share? - What do you use spreadsheets for?

    0 of 8192 characters used
    Post Comment

    • profile image

      topbuilderlist 

      5 years ago

      WOW it's a very useful tool on spreadsheet.

    • savateuse profile imageAUTHOR

      savateuse 

      5 years ago

      @simonwade: Thanks! I hope it is helpful for people who are trying to improve or learn Excel.

    • simonwade profile image

      simonwade 

      5 years ago

      awesome technique, you've done a wonderful contribution for those MS Excel users.

    • profile image

      Kat_Wilshire 

      5 years ago

      Excellent info! I've tried to sort in Excel before and had no idea why the data in the adjacent columns were no longer associated with the column I was sorting. Had no idea what "Expand the selection" meant. I've bookmarked this page.

    • profile image

      fifinn 

      5 years ago

      thanks for this tutorial. I really appreciate. good job.

    • savateuse profile imageAUTHOR

      savateuse 

      5 years ago

      @victoriuh: Excel is very useful. I'm pleased this has helped you.

    • victoriuh profile image

      victoriuh 

      5 years ago

      I am Excel stupid so I appreciated reading this. Very useful!

    • savateuse profile imageAUTHOR

      savateuse 

      5 years ago

      @LisaAuch1: Knowing how to filter data in Excel is a really useful skill - I'm pleased you have found this page helpful!

    • LisaAuch1 profile image

      Lisa Auch 

      5 years ago from Scotland

      I so wanted to know this! now to bookmark it for reference for when I need it! Thanks for this handy how to for Excel, you have made it easier

    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)