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 4 years ago

      WOW it's a very useful tool on spreadsheet.

    • savateuse profile image
      Author

      savateuse 4 years ago

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

    • simonwade profile image

      simonwade 4 years ago

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

    • profile image

      Kat_Wilshire 4 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 4 years ago

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

    • savateuse profile image
      Author

      savateuse 4 years ago

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

    • victoriuh profile image

      victoriuh 4 years ago

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

    • savateuse profile image
      Author

      savateuse 4 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 4 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

    Click to Rate This Article