Excel Sorting and Filtering Data

When working with large datasets, finding specific information quickly is crucial. Sorting arranges data in a particular order — such as alphabetically or numerically. Filtering temporarily hides rows that do not meet certain criteria, showing only the relevant data. Together, these tools make data analysis far more efficient.

Sorting Data

Sorting reorganizes the rows of a spreadsheet based on the values in one or more columns.

Quick Sort: Ascending and Descending

1. Click any cell inside the column to sort by.
2. Go to Home → Sort & Filter or Data tab.
3. Click Sort A to Z (ascending) or Sort Z to A (descending).

• A to Z (Ascending): Numbers go from smallest to largest; text goes from A to Z; dates go from oldest to newest.
• Z to A (Descending): Numbers go from largest to smallest; text goes from Z to A; dates go from newest to oldest.

Example

  Before sorting by "Score" (ascending):
  | Name  | Score |
  | Bob   | 75    |
  | Alice | 90    |
  | Carol | 60    |

  After sorting by Score, A to Z:
  | Name  | Score |
  | Carol | 60    |
  | Bob   | 75    |
  | Alice | 90    |

Custom Sort (Multiple Levels)

Custom Sort allows sorting by more than one column. For example, sort by Department first, then by Name within each department.

1. Go to Data → Sort.
2. In the Sort dialog box, select the first sort column and order.
3. Click Add Level to add a second sort criterion.
4. Click OK.

Example

  Sort by: Department (A to Z), then by Salary (Largest to Smallest)
  → All employees will be grouped by department.
  → Within each department, the highest-paid employees will appear first.

Sorting by Cell Color or Icon

Excel also allows sorting by fill color or icon (applied through Conditional Formatting). In the Sort dialog, change Sort On from "Cell Values" to "Cell Color" or "Cell Icon".

Filtering Data

Filtering hides rows that do not meet the filter criteria. The data is not deleted — it is just hidden from view temporarily.

Enabling AutoFilter

1. Click any cell inside the data range.
2. Go to Data → Filter, or press Ctrl + Shift + L.
3. Dropdown arrows will appear in the header row of each column.

Applying a Filter

1. Click the dropdown arrow on the column to filter.
2. Uncheck the values to hide, or use the search box to find specific entries.
3. Click OK.

Only rows matching the selected criteria will be displayed. The row numbers of hidden rows will appear in blue.

Example

  Dataset:
  | Name  | Department | Salary |
  | Alice | Sales      | 50000  |
  | Bob   | IT         | 60000  |
  | Carol | Sales      | 55000  |
  | David | HR         | 45000  |

  Filter applied to Department: "Sales"

  Filtered view:
  | Name  | Department | Salary |
  | Alice | Sales      | 50000  |
  | Carol | Sales      | 55000  |

Clearing a Filter

• Click the dropdown arrow on the filtered column and select Clear Filter From [Column Name].
• Or go to Data → Clear to remove all filters at once.

Turning Off AutoFilter

Go to Data → Filter again (or press Ctrl + Shift + L) to turn off AutoFilter and remove all dropdowns.

Number Filters

When filtering a column that contains numbers, Excel provides additional filtering options under Number Filters in the dropdown.

Examples of Number Filters

• Greater Than: Show only rows where salary > 50000.
• Between: Show only rows where score is between 60 and 80.
• Top 10: Show only the top 10 (or bottom 10) values.
• Above Average: Show only rows with values above the column average.

Text Filters

For columns containing text, Text Filters provide options like:

• Contains: Show rows where the cell contains a specific word.
• Begins With: Show rows where text starts with specific letters.
• Does Not Contain: Exclude rows with a specific word.

Example

  Text Filter on "Name" column → Contains "an"
  → Shows all names containing "an": Daniel, Ananya, Ian, etc.

Date Filters

For date columns, date-specific filters are available:

• This Week, Last Month, Next Quarter, etc.
• Before or After a specific date.
• Between two dates.

Sorting and Filtering with Tables

When data is formatted as an Excel Table (via Insert → Table), AutoFilter dropdowns are added automatically. Tables also maintain sorting and filtering behavior when rows are added.

Summary

• Sorting reorders rows based on the values in one or more columns.
• Quick Sort sorts a single column; Custom Sort allows multi-level sorting.
• Filtering hides rows that do not match selected criteria — the data is not deleted.
• AutoFilter is activated with Ctrl + Shift + L and adds dropdown arrows to headers.
• Number Filters, Text Filters, and Date Filters provide advanced filtering options for different data types.