How to Filter and Sort Data in Excel
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:
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.
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.
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.
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 Excel Online Training Courses
- Free Computer Courses
Smile! You’ve found free computer courses
- Learn Microsoft Excel 2010 Step by Step, Level 1
This one-day instructor-led course provides students with an overview of the features and functions of Microsoft Excel 2010.
- Excel Basics
- Microsoft Excel 2010
Microsoft Excel 2010 is one of the most popular software applications worldwide and is part of the Microsoft Office 2010 productivity suite. This free online course will help you switch to Excel 2010 from a previous version of the software, and will
- Microsoft Excel Training & Word 2007 Tutorial - Learn Free Office Online Video Course 2010 | Fre
High quality, totally free Microsoft Excel 2007 tutorial videos. You will find here easy help and exercises for beginners (dummies) and for advanced users. This is an online training course, a complete guide to learn at home. Includes also MS Word tr
More Reading On Excel
Useful quick reference cards and reference books to help you get the best from your Excel software.
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 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