Filter data in a range or table

Filtering data in a range or table in Excel allows you to display only the rows that meet specific criteria, making it easier to analyze and view relevant information. You can filter data using simple filters or more advanced custom filters.

Filtering Data in a Table or Range

1. Filter Data in a Table

When you create a table in Excel, it automatically includes filter drop-downs in the header row. Here’s how to use them:

1. Select the Table: Click on any cell inside the table. The filter drop-down arrows will appear in the header row of each column.
2. Click the Drop-Down Arrow: Click on the drop-down arrow in the column header you want to filter.
3. Choose Filter Criteria:
   • Select Specific Items: Check or uncheck the boxes next to the items you want to display. This is useful when you have a list of categories (e.g., “Product Names” or “Regions”) and want to show only certain items.
   • Text Filters (for text columns): Choose from options like Contains, Begins With, Ends With, or Equals.
   • Number Filters (for numeric columns): Choose from options like Greater Than, Less Than, Between, or Equals.
   • Date Filters (for date columns): Filter by options like Before, After, Between, or relative time periods like This Month or Last Week.
4. Apply the Filter: After selecting the filter criteria, the table will update to show only the rows that match your filter.

2. Filter Data in a Range (Without a Table)

You can also apply filters to data that is not in a table format by using the AutoFilter feature:

1. Select the Data Range: Select the range of data you want to filter. Make sure your data has headers for better organization.
2. Enable Filtering:
   • Go to the Data tab on the Ribbon.
   • In the Sort & Filter group, click Filter. This will add filter drop-down arrows to each column in the selected range.
3. Filter Data:
   • Click the drop-down arrow in the column header and follow the same steps as for filtering in a table.

3. Filter Options Available

Text Filters:

• Equals: Shows only rows where the column’s value exactly matches the specified text.
• Does Not Equal: Shows only rows where the column’s value does not match the specified text.
• Contains: Shows only rows where the column contains the specified text.
• Does Not Contain: Shows rows where the column does not contain the specified text.
• Begins With: Shows only rows where the column starts with the specified text.
• Ends With: Shows only rows where the column ends with the specified text.

Number Filters:

• Equals: Shows only rows where the column’s value matches the specified number.
• Greater Than: Shows only rows where the column’s value is greater than the specified number.
• Less Than: Shows only rows where the column’s value is less than the specified number.
• Between: Shows only rows where the column’s value falls between two specified numbers.

Date Filters:

• Equals: Shows only rows where the column’s date matches the specified date.
• Before: Shows only rows where the column’s date is before the specified date.
• After: Shows only rows where the column’s date is after the specified date.
• Between: Shows only rows where the column’s date is between two specified dates.
• This Week, Last Week, Next Week: Shows rows with dates in a relative time frame.
• This Month, Last Month, Next Month: Shows rows with dates in the current, previous, or next month.

4. Using Advanced Filters

Excel also offers Advanced Filter for more complex filtering criteria. With Advanced Filter, you can filter data based on multiple criteria and even copy the filtered results to another location.

Using Advanced Filter:

1. Go to the Data tab on the Ribbon.
2. In the Sort & Filter group, click Advanced.
3. In the Advanced Filter dialog box:
   • Choose Filter the list, in-place to filter the data in the current location or Copy to another location to display filtered data elsewhere.
   • Specify the List range (the data range or table to filter) and the Criteria range (the criteria you want to apply).
   • Click OK to apply the filter.

5. Clear Filters

To remove a filter and show all the data again, you can clear filters in a couple of ways:

• Clear a Filter from a Single Column: Click the drop-down arrow in the column header and choose Clear Filter from [Column Name].
• Clear All Filters: Go to the Data tab and click Clear in the Sort & Filter group to remove all filters at once.

6. Filter Data Using Slicers (Excel Tables)

If you are using an Excel table, you can also use Slicers to filter data visually:

1. Click any cell within the table.
2. Go to the Table Design tab and click Insert Slicer.
3. In the dialog box, choose the fields you want to use as slicers.
4. The slicers will appear as buttons that you can click to filter the table.

Conclusion

Filtering data in Excel helps you quickly display only the rows that meet specific criteria, making it easier to analyze and work with large datasets. You can use basic filters with drop-down arrows, advanced filters for more complex needs, or slicers for a more visual approach. Once applied, filters can be cleared at any time, restoring the original dataset.