Your Excel spreadsheet is too cluttered. Do you feel like you’re drowning in an ocean of data? Don’t panic, this article is here to help you find the relevant data with the Filters in Excel.
What is the role of a Filter in Excel?
The Filter in Excel, also known as an automatic filter, is a practical approach to displaying only the data that’s important at a given moment and hiding all other data. In Excel worksheets, you can filter rows by value, format and criteria. You can copy, edit, create charts or print only those rows visible after applying a filter without reorganizing the complete list.
In addition to a variety of filtering choices, Excel AutoFilter also includes sorting options for each column:
- Sort A to Z, Sort Z to A, and Sort by color for text values.
- Sort numbers in the following order: smallest to largest, largest to smallest, and by color.
- Sort oldest to newest, newest to oldest, and sort by color for dates.
Here’s the distinction between sorting and filtering in Excel:
When you sort data in Excel, it rearranges the whole table, for example alphabetically or from smallest to largest value. Sorting does not hide any entries; it simply reorganizes the data.
By contrast, when you filter data in Excel, only the items you want to see are displayed, and all unnecessary items are temporarily hidden.
💡Related articles:
How do I add a filter in Excel?
For Excel AutoFilter to work properly, your dataset will need to include a header row with column names, as shown in the screenshot below.
The dataset will include a header row where the filter buttons will be placed.
Select any cell in your dataset and use one of the following techniques to insert a filter once the column headers are in place.
In Excel, there are two ways to add Filter in Excel:
- Click on the Filter button in the Sort & Filter group of the Data tab.
- Click on Sort & Filter > Filter in the Home tab, in the Edit group.
There’s another way to filter in Excel:
- Activate or deactivate filters using the Excel Filter shortcut: Ctrl+Shift+L
- The drop-down arrows will appear in each header cell, regardless of the approach used.
- The presence of a downward arrow in the column header indicates that filtering has been introduced but not yet applied. An on-screen indication appears when you hover over the arrow (Show All).
To filter data in Excel, proceed as follows:
- To filter a column, click on the drop-down arrow next to it.
- To easily deselect all data, uncheck the (Select All) box.
- Click OK after ticking the boxes next to the data you wish to see.
How do I filter multiple columns with Filter in Excel?
Simply repeat the above procedures for the number of columns to which you wish to apply an Excel filter.
To widen or lengthen the Excel filter window, hover over the handle at the bottom and drag it down or to the right as soon as the double arrow appears.
How to filter cells?
To filter data in Excel and eliminate blanks or non-blanks, use one of the following methods:
- Click on the automatic filter arrow, check the box (Select all), then uncheck (Empty) at the bottom of the list to filter out blanks and display non-blank cells. Only rows with any value in a specific column will be displayed.
- To display only blank cells and filter out non-blank cells, deselect “select all”, then select “blank”. This will display only those rows in a particular column that have an empty cell.
How do I filter text data?
When you need to filter a column of text for something specific, Excel provides a number of advanced options, including:
- Filter cells that begin or end with a specific character.
- Filter text cells that do or don’t include a specific character or term.
- Filter cells that are exactly equivalent or not to a particular character.
Text filters appear automatically in the “Text Filters” menu after you have applied a filter to a column containing text values:
How do I filter numbers in Excel?
The Excel number filter lets you filter numerical data in the following ways:
- Filter numbers, greater than, less than, or within the range of specific numbers.
- Filter cells with numbers above or below the mean.
- Filter numbers equal to or not equal to a certain number.
How do I filter dates in Excel?
The date filter offers a wide range of choices, allowing you to show or hide data for a particular day, week, month, quarter or year, before or after a given date, or between two dates.
The screenshot below shows all available date filters:
Excel offers a wide range of filtering and sorting options. Each one serves a specific purpose. Just scroll through the various filter menus to see the power of the tool. And now it’s your turn to usea Filter in Excel!
