Sorting and Filtering Data in Excel

Updated on Tue May 23 2023

Excel is a powerful tool that allows users to manage and manipulate large amounts of data quickly and efficiently. One of the key features of Excel is the ability to sort and filter data, which can be especially useful for working with datasets that contain hundreds or even thousands of rows of information. In this article, we'll explore how to use Excel's sorting and filtering capabilities to organize and analyze data.

Sorting can be done in ascending or descending order based on a single column or multiple columns. Filtering allows users to view specific data based on criteria, such as dates, text, or numerical values. Excel also provides advanced filtering options, such as filtering by color or by using complex criteria.

Sorting Data

Sorting Data by One Column

Sorting data by one column is a straightforward process that allows users to rearrange data based on the values in a specific column. To sort data by one column, follow these steps:

Select the range of cells that you want to sort.
Click the "Data" tab on the Excel ribbon.
Click the "Sort A-Z" button to sort data in ascending order or the "Sort Z-A" button to sort data in descending order. Excel will automatically sort the data based on the values in the selected column, either in ascending or descending order.

Sorting Data by Multiple Columns

Sorting data by multiple columns is a more advanced technique that allows users to sort data based on two or more criteria. This is especially useful when sorting data that has multiple columns that contain related information. To sort data by multiple columns, follow these steps:

Select the range of cells that you want to sort.
Click the "Home" tab on the Excel ribbon.
Click the "Sort & Filter" button to display the "Sort & Filter" menu.
Click the "Custom Sort" option to open the "Sort" dialog box.
In the "Sort" dialog box, select the first column that you want to sort by and choose the sort order (ascending or descending).
Click the "Add Level" button to add another column to sort by.
Repeat steps 5 and 6 until you have added all of the columns that you want to sort by.
Click the "OK" button to apply the sorting criteria.

Excel will now sort the data based on the values in the selected columns, in the order that they were added. For example, if you sorted by column A first and then by column B, Excel would sort the data based on the values in column A first, and then sort the data within each group of values in column A based on the values in column B.

How to Restore the Original Order of Data Rows after Sorting

It can be difficult to get back the original order of data rows after sorting. This is because Excel rearranges the rows based on the criteria used for sorting, which can make it challenging to determine the original order. One solution to this problem is to add an index column to the data before sorting. This index column can be used to track the original order of the data rows, even after sorting. To add an index column, users can follow these steps:

Insert a new column next to the data to be sorted.
Input unique numbers in ascending order as the index value to each row of data. Once the index column has been added, users can sort the data using any criteria they choose. This gives us the sorted data, along with index column, which we can use in case we want to revert to the original data using the below steps.
To return the data to its original order, simply sort the data by the index column in ascending order.

Another option for restoring the original order of data rows is to use Excel's undo function. If users have not made any additional changes to the data after sorting, they can simply click on the undo button (or use the keyboard shortcut Ctrl + Z) to revert the data to its original order before the sorting was performed.

Filtering Data

Filtering Data using AutoFilter

Excel provides a powerful feature called AutoFilter that allows users to filter data based on specific criteria. Filtering data enables users to analyze a subset of data and focus on what is important. In this article, we'll explore how to filter data in Excel using AutoFilter.

Using AutoFilter to Filter Data

To filter data using AutoFilter, follow these steps:

Select the range of cells that you want to filter.
Click the "Data" tab on the Excel ribbon.
Click the "Filter" button to display the AutoFilter options. Once you click the "Filter" button, Excel will automatically add a drop-down arrow to the header row of each column in your selected range. These drop-down arrows allow you to select filtering criteria for each column.
Click the drop-down arrow in the column that you want to filter.
Select the filter criteria that you want to apply. For example, you might choose to show only data that equals, does not equal, is greater than, is less than, or is between a certain value.
Click "OK" to apply the filter. Once you apply the filter, Excel will automatically hide any rows that do not meet your specified criteria, leaving only the filtered data visible.

How to Remove AutoFilter in Excel

To remove the AutoFilter and restore the full data set, follow these steps:

Click the "Data" tab on the Excel ribbon.
Click the "Filter" button to remove the AutoFilter.

Advanced Filtering Options

In addition to the basic filtering options provided by Excel's AutoFilter feature, there are advanced filtering options that allow users to filter data based on more complex criteria. These advanced filtering options provide greater flexibility and allow users to create custom filters to analyze data.

How to Use Advanced Filtering Options

To use advanced filtering options in Excel, follow these steps:

Select the range of cells that you want to filter.
Click the "Data" tab on the Excel ribbon.
Click the "Advanced" button to open the "Advanced Filter" dialog box. In the "Advanced Filter" dialog box, you have two options: "Filter the list, in place" or "Copy to another location". If you choose "Filter the list, in place", Excel will filter the data in the selected range based on the criteria that you specify. If you choose "Copy to another location", Excel will copy the filtered data to a new location.
Choose the criteria for your filter.

Notes on advanced criteria:

The table or range that you want to filter must have the same column headings as the criteria range.

The AND logic applies to criteria that are listed in the same row. The OR logic is used to combine criteria entered on different rows.
Set up the following criteria range, for instance, to filter entries for Rep Gill where no. of Units is higher than or equal to 50: Rep: Gill Units: >= 50

Click the "OK" button to apply the filter. Once you apply the filter, Excel will show only the data that meets your specified criteria.

Now we'll explore some of the advanced filtering options available in Excel.

Text Filters

Excel's text filters allow users to filter data based on specific text criteria. For example, you might use a text filter to show only data that contains a specific word or phrase. To use a text filter:

Click the drop-down arrow in the column header that you want to filter.
Click the "Text Filters" option.
Select the text filter criteria that you want to apply.

Number Filters

Excel's number filters allow users to filter data based on specific numerical criteria. For example, you might use a number filter to show only data that falls within a certain range of values. To use a number filter:

Click the drop-down arrow in the column header that you want to filter.
Click the "Number Filters" option.
Select the number filter criteria that you want to apply.

Date Filters

Excel's date filters allow users to filter data based on specific date criteria. For example, you might use a date filter to show only data that falls within a certain range of dates. To use a date filter:

Click the drop-down arrow in the column header that contains your date data.
Click the "Date Filters" option.
Select the date filter criteria that you want to apply.

Filter by Color

Excel's filter by color option allows users to filter data based on cell color. This can be particularly useful if you have highlighted certain cells to indicate specific information. To use the filter by color option:

Click the drop-down arrow in the column header that you want to filter.
Click the "Filter by Color" option.
Select the cell color that you want to filter by.

Removing Advanced Filters

To remove an advanced filter, follow these steps:

Click anywhere within the range that has been filtered.
Click the "Data" tab on the Excel ribbon.
Click the "Clear" button to remove the filter.

Removing Duplicates

Removing duplicates is a common task when working with large datasets in Excel. Having duplicate data can lead to inaccurate analysis and can make it more difficult to work with the data. In this article, we'll explore how to remove duplicates in Excel.

Removing Duplicates using the Remove Duplicates Tool Excel provides a built-in tool called "Remove Duplicates" that allows users to quickly and easily remove duplicate data. To use the Remove Duplicates tool:

Select the range of cells that contains the data you want to remove duplicates from.
Click the "Data" tab on the Excel ribbon.
Click the "Remove Duplicates" button. This will open the "Remove Duplicates" dialog box.
In the "Remove Duplicates" dialog box, select the columns that you want to search for duplicates. You can either select all columns or specific columns.
Click "OK" to remove the duplicates. Once you click "OK", Excel will remove any duplicate data from the selected range of cells. You can also choose to expand the selection to include additional columns or rows.

Removing Duplicates using Formulas

In addition to the Remove Duplicates tool, you can also remove duplicates in Excel using formulas. Here's how:

In a new column next to the column that contains the data you want to remove duplicates from, enter the following formula: =IF(COUNTIF($E$2:$E44,$E2)>1,"",1)

This formula will return a value of 1 for the first instance of each unique value and a blank cell for any subsequent instances. 2. Drag the formula down to apply it to all the cells in the new column. 3. Copy the column containing the formula and paste it as values over the original column containing the data you want to remove duplicates from. 4. Sort the data by the new column containing the formula. 5. Select all rows that have a blank cell in the new column and delete them. Once you complete these steps, you will have removed any duplicate data from the selected range of cells.