Using Filters in Excel to Quickly Sort Data

Excel spreadsheets are effective for tracking information, performing mathematical calculations, charting data, providing learning tools by way of conditional formatting and more. Spreadsheets also provide features that allow the data within them to easily be navigated and searched. These tools are the sort and filter features.

Sorting data groups like data together. For instance, you could sort by zip code to find out how many letters were being mailed within a certain city. Filters narrow the amount of data by displaying only those items that meet the criteria of the filter. Filters are an effective way to view data because they display results instantly without actually changing the data. Filters simply manipulate what data is being displayed and once turned off, all data will be displayed.


The ease of using sorting and filtering data to provide detailed results, makes using these options one of the easiest ways to find information within an Excel spreadsheet.

Using Filters in Excel to Quickly Sort Data

The example below uses basic information to show how sorting and filters can be used, but is only a small sampling of the many possible uses.

Once basic information is entered into a spreadsheet, it can be sorted or filtered. Here is an example of some sample data depicting customer orders, the dates of the orders, the items ordered, and how many.

From this basic data, we can extrapolate different types of information. We will show how to display who is purchasing specific items, what was ordered on any specific date, and display all orders from a specific customer.

To sort data:

• Right-click on a column heading, hover over sort, then select A-Z or Z-A, or custom sort if desired.
  
  
  
• In this example the data is sorted by customer so it can be easy to see what items any particular customer has recently been ordering.
  
  
  
• To sort this data further, select custom sort to sort by multiple columns at once. Right click and hover over sort, then select "Custom sort". 
• In the Sort box, modify the first sort level.
• Click "Add Level" to add a secondary item to sort by.
• In the "Then by" level, chose what to sort by and how to sort it.
  
  
  
• Here is what the data looks like after sorting first by customer then by item purchased.
  
  
  

Filters work similarly except they remove any data that does not meet the filter requirements. This allows you to focus on a very specific set of data.

• Right-click on the name of a column and hover over filter, then select "Filter by Selected Cell's Value".
  
  
  
• Without any data selected, there is no data to display but filter icons have been added to each column.
  
  
  
• Click on the filter icon next to any column, and select the criteria for what data to display and click OK.
  
  
  
• In this example, the filter next to Date was selected and October 9th was selected. Notice the number of the rows in the results are the actual row numbers where the data lives. This shows how certain data is filtered out.
  
  
  

Data can also be sorted using many parameters that include between, equals, before, after, above average and many more. This allows data to be filtered down to the most granular level. For instance, the amount sold could be filtered to represent only those orders where at least 10 items were sold and less than 45 items were sold. 

• Click on the filter next to the column for the amount sold.
• Hover over "Number filters" and select "Between".
  
  
  
• In the Custom AutoFilter window, enter the numbers relative to the data to display.
  
  
  
• Here is what the data looks like after being filtered in this example.
  
  
  

To remove a filter, simply click on the filter icon and select "Clear Filter From 'Name of Filtered Item'" which will display all of the original data in its original state.

Many different types of data can be stored in spreadsheets to track data. Once the data is entered, over time it can grow to a level where searching for a certain set of data is inefficient. When this happens, sorting and filtering data are the best way to find the desired information.

Sorting data groups certain information together so they can be assessed. Filtering data provides granular control over what data is displayed and the most specific data can quickly and easily be found with the correct filters applied. Once done, simply remove filters and all of the original data is displayed.

As always, knowing how to get the information you need out of the data you already have, is more efficient than starting over!

Enjoy this post? Subscribe to our Blog