What Is Sorting and Filtering in Data Analysis?

Sorting is the process of arranging data in a specific order, such as A to Z, Z to A, smallest to largest, largest to smallest, or oldest to newest. Filtering is the process of displaying only the rows that match a given condition while temporarily hiding the remaining rows.

For example, if a dataset contains employee sales, we can sort the data to see the highest sales first. Similarly, we can filter the data to display only the employees who belong to the Sales department.

Difference Between Sorting and Filtering

Why Are Sorting and Filtering Important in Data Analysis?

Raw data can be large and difficult to understand. Before we analyze it, we need to arrange the data and focus on the information we need. In Excel, sorting and filtering help us arrange the data and quickly find the required values.

They can be used to:

• Arrange data in ascending or descending order. 
• Find the highest and lowest values. 
• Group similar records together. 
• Display only the required records. 
• Find duplicates and unusual values. 
• Remove unnecessary data from the view. 
• Prepare data for charts and reports. 
• Compare specific categories more easily. 

Sample Dataset Used in This Article

We will use the following dataset in the examples. The headings are in row 1, and the data is in the range A2:C6.

Common Sorting and Filtering Features in Excel

Excel provides several features and functions that are useful for sorting and filtering data. Some commonly used ones are:

Sorting Data in Excel

Sorting is used to place the data in a particular order. Excel can sort text, numbers, dates, and even cell colors or font colors.

Sort A to Z and Z to A

This option is used to sort text values alphabetically. It is useful when we need to arrange names, cities, or product names.

Steps

1. Click on any cell in the column we want to sort. 
2. Go to the Data tab. 
3. Click on Sort A to Z to sort in ascending order, or Sort Z to A to sort in descending order. 

Example

If we sort the Employee column from A to Z, the result will be:

Output:

Amit, Karan, Neha, Priya, Rahul
 

Explanation:
In the above example, we select a cell in the Employee column. After that, we choose Sort A to Z from the Data tab. Finally, Excel arranges the names in alphabetical order and moves the other columns along with them, so each record stays correct.

Sort Smallest to Largest and Largest to Smallest

This option is used to sort numbers. It is useful when we need to find the top or bottom values, such as the highest sales.

Steps

1. Click on any cell in the Sales column. 
2. Go to the Data tab. 
3. Click on Sort Largest to Smallest. 

Output:

Explanation:
In the above example, we select a cell in the Sales column. After that, we choose Sort Largest to Smallest. Finally, Excel arranges the records from the highest sales value to the lowest.

Custom Sort (Multi-Level Sort)

Custom Sort is used when we need to sort data by more than one column. For example, we may want to sort by department first and then by sales within each department.

Steps

1. Select the data range, including the headings. 
2. Go to the Data tab and click on Sort. 
3. In the Sort by box, select the first column, such as Department. 
4. Click on Add Level and select the second column, such as Sales. 
5. Choose the order for each level and click OK. 

Output:
Department is sorted from A to Z, and Sales is sorted from largest to smallest within each department.

Explanation:
In the above example, we first sort the data by Department. After that, we add a second level to sort by Sales in descending order. Finally, Excel groups the records by department and arranges the sales values from high to low inside each group.

Sort by Color

Excel can also sort data based on the cell color, font color, or icon applied to it. It is useful when rows have been highlighted manually. To use it, open the Sort dialog box, choose the column, select Cell Color or Font Color in the Sort On box, and choose which color should appear first.

SORT Function

The SORT function is used to return sorted data using a formula. The result updates automatically when the original data changes. This function is available in Excel 365 and Excel 2021 or later.

Syntax

It has the following syntax.

=SORT(array, [sort_index], [sort_order], [by_col])

Here, array is the data range we want to sort, sort_index is the column number to sort by, and sort_order is 1 for ascending or -1 for descending. The by_col argument is optional and is used only when sorting by columns instead of rows.

Example

=SORT(A2:C6,3,-1)

Output:

Explanation:
In the above example, we use the SORT function with A2:C6 as the array. After that, we specify 3 as the sort index, which means the third column (Sales), and -1 as the order for descending. Finally, the function returns the whole table sorted from the highest sales to the lowest.

SORTBY Function

The SORTBY function is used to sort one range based on the values of another range. It is also useful when we need to sort by multiple columns using a formula.

Syntax

It has the following syntax.

=SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], ...)

Here, array is the data we want to return, by_array1 is the range used for sorting, and sort_order1 decides whether the order is ascending (1) or descending (-1).

Example

=SORTBY(A2:C6,B2:B6,1,C2:C6,-1)

Output:

Explanation:
In the above example, we use the SORTBY function to sort the table by Department in ascending order. After that, we add Sales as the second sorting range in descending order. Finally, the function returns the data grouped by department, with the highest sales first in each group.

Filtering Data in Excel

Filtering is used to display only the rows that meet a condition. The hidden rows are not deleted, and they appear again when the filter is cleared.

AutoFilter

AutoFilter adds a drop-down arrow to each column heading. We can use these arrows to choose the values we want to display.

Steps

1. Click on any cell inside the data range. 
2. Go to the Data tab and click on Filter. 
3. Click on the drop-down arrow in the column heading, such as Department. 
4. Select the value we want to display, such as Sales, and click OK. 

Output:

Explanation:
In the above example, we turn on the filter for the data range. After that, we open the Department drop-down and select only Sales. Finally, Excel displays the rows where the department is Sales and hides the other rows.

Text Filters

Text Filters are used to filter text using conditions such as equals, does not equal, begins with, ends with, and contains. For example, if we choose Text Filters > Begins With and enter the letter "R", Excel displays only the names that start with R, which is Rahul in our dataset.

Number Filters

Number Filters are used to filter numeric values using conditions such as equals, greater than, less than, between, top 10, and above average. For example, if we choose Number Filters > Greater Than and enter 4500 in the Sales column, Excel displays the following records.

Output:

Explanation:
In the above example, we apply a Number Filter on the Sales column. After that, we set the condition as greater than 4500. Finally, Excel displays only the rows where the sales value is higher than 4500.

Date Filters

Date Filters are used when a column contains dates. Excel groups the dates by year, month, and day and gives options such as Today, This Week, Last Month, This Quarter, and Between. They are useful when we need to analyze data for a particular time period.

Filter by Color

Excel can filter rows based on the cell color or font color. To use it, click the filter arrow, choose Filter by Color, and select the color we want to display.

Clearing a Filter

To remove a filter from one column, click the filter arrow and choose Clear Filter. To remove all filters from the sheet, go to the Data tab and click on Clear. To turn off the filter buttons completely, click on Filter again.

Advanced Filter

The Advanced Filter is used when we need to apply complex conditions or copy the filtered result to another location. It uses a separate criteria range, which contains the column heading and the condition.

Steps

1. Write the column heading and the condition in a separate area. For example, write Department in cell E1 and HR in cell E2. 
2. Go to the Data tab and click on Advanced in the Sort & Filter group. 
3. Select the data range in the List range box. 
4. Select the E1:E2 range in the Criteria range box. 
5. Choose Filter the list, in-place or Copy to another location, and click OK. 

Output:

Explanation:
In the above example, we create a criteria range with the heading Department and the condition HR. After that, we select the list range and the criteria range in the Advanced Filter dialog box. Finally, Excel displays only the records where the department is HR.

FILTER Function

The FILTER function is used to return the rows that meet a condition using a formula. Unlike AutoFilter, the result updates automatically when the source data changes. This function is available in Excel 365 and Excel 2021 or later.

Syntax

It has the following syntax.

=FILTER(array, include, [if_empty])

Here, array is the data we want to filter, include is the condition that decides which rows to keep, and if_empty is the text to show when no row matches.

Example

=FILTER(A2:C6,B2:B6="Sales","No data found")

Output:

Explanation:
In the above example, we use the FILTER function with A2:C6 as the array. After that, we specify B2:B6="Sales" as the condition. Finally, the function returns only the rows where the department is Sales. If no row matched, Excel would show the text "No data found".

FILTER with Multiple Conditions

We can combine conditions using the multiplication sign (*) for AND and the plus sign (+) for OR.

Example

=FILTER(A2:C6,(B2:B6="HR")*(C2:C6>4000),"No data found")

Output:

Explanation:
In the above example, we use two conditions inside the FILTER function. After that, we join them with the multiplication sign so that both must be true. Finally, the function returns only the row where the department is HR and the sales value is greater than 4000.

Using SORT and FILTER Together

We can combine both functions to filter the data first and then sort the result.

Example

=SORT(FILTER(A2:C6,B2:B6="Sales"),3,-1)

Output:

Explanation:
In the above example, we use the FILTER function to get only the Sales department rows. After that, we place it inside the SORT function and use 3 as the sort index with -1 as the order. Finally, the function returns the Sales rows arranged from the highest sales to the lowest.

Conclusion

Sorting and filtering are an important part of data analysis because they help us organize large datasets and focus on the information we need. Sorting arranges the data in a meaningful order, and filtering narrows it down to the required records. By learning features like Sort, Custom Sort, AutoFilter, and Advanced Filter, along with functions like SORT, SORTBY, and FILTER, we can prepare and analyze data more easily in Excel.