How to Filter Data in Microsoft Excel 2016

When your Excel worksheet has hundreds or even thousands of rows, finding specific information can be frustrating. Luckily, Excel’s Filter feature makes it super easy to display only the data you need. In this beginner-friendly guide, I’ll show you step-by-step how to filter data in Microsoft Excel 2016.

Step 1: Prepare Your Worksheet

Before you start filtering, make sure your worksheet is well organized.

• Ensure your data has a header row. This means each column should have a label like “ID#”, “Type”, or “Equipment Detail”.
• There should be no completely blank rows or columns within your data range.

Step 2: Apply a Basic Filter

Now, let’s apply the filter:

• Click anywhere inside your data range.
• Go to the Data tab on the Ribbon.
• Click the Filter button. You’ll see small drop-down arrows appear in your header row.

That’s it! Your data is now ready to be filtered.

Step 3: Filter a Column

Here’s how to filter a specific column:

• Click the drop-down arrow in the column you want to filter.
• Uncheck the box next to (Select All).
• Then, check only the items you want to view.
• Click OK.

Now, Excel shows only the rows matching your chosen criteria. The rest of the data is hidden, not deleted.

Step 4: Apply Multiple Filters

You can filter more than one column at a time. For example:

• Filter the Type column to show only Laptops and Projectors.
• Then, filter the Date column to show only items checked out in August.

Excel will display only the rows that meet both conditions.

Step 5: Clear or Remove Filters

• To remove a filter from one column: Click the drop-down arrow and choose Clear Filter From [Column Name].
• To remove all filters at once: Go to the Data tab and click the Filter button again.

Step 6: Use Advanced Filtering Options

Search Filtering

Need to find something quickly?

• Click the filter arrow in a column.
• Type your keyword or number in the search box.
• Excel will instantly show only matching rows.

Text Filters

For text-based columns, try this:

• Click the drop-down arrow and hover over Text Filters.
• Choose an option like Does Not Contain.
• Type the word you want to exclude and click OK.

Number Filters

To filter numbers by range:

• Click the arrow in your numeric column.
• Hover over Number Filters and select Between.
• Enter a range (for example, 3000 to 6000) and click OK.

Date Filters

For date-based columns:

• Click the drop-down arrow and hover over Date Filters.
• Choose Between and enter your date range (e.g., July 15 to August 15).
• Click OK.

Quick Filters by Cell Value

Here’s a shortcut many users love:

• Right-click a cell that contains the value you want to filter by.
• Hover over Filter.
• Select Filter by Selected Cell’s Value.

Excel instantly shows all rows with that same value. Super handy!

5 FAQs of Filtering Data in Excel 2016

1. What is the purpose of filtering in Excel?

Filtering helps you view only the data you need, making it easier to analyze or compare information.

2. Can I filter multiple columns at once?

Yes. You can apply filters to several columns, and Excel will show only rows that meet all conditions.

3. How do I remove all filters in Excel?

Go to the Data tab and click the Filter button again. This removes all filters from your worksheet.

4. What’s the difference between sorting and filtering?

Sorting rearranges your data order, while filtering hides rows that don’t match your chosen criteria.

5. Can I save a filtered view?

Yes, you can save the filtered version as a new file or keep the filter active when you save the workbook.

Conclusion

Filtering in Microsoft Excel 2016 is a powerful yet simple tool that helps you manage large sets of data with ease. Whether you’re finding specific text, numbers, or dates, filters can save you time and effort. Practice using different filter types, and soon you’ll be sorting through data like a pro.