How to Use Filters in Excel to Find Data

Find Data Quickly with Excel Filters

When you have a spreadsheet with lots of data, filters help you show only the rows you need. This makes finding specific information much easier.

Turning On Filters

Enable Filter Mode

• Click anywhere in your data
• Go to the Data tab
• Click Filter

You will see small dropdown arrows appear in your header row.

Keyboard Shortcut

Press Ctrl + Shift + L to toggle filters on and off.

Using Basic Filters

Filter by Specific Value

• Click the dropdown arrow in the column you want to filter
• Uncheck Select All
• Check only the values you want to see
• Click OK

Only rows matching your selection will show. Other rows are hidden, not deleted.

Filter by Multiple Values

• Click the column dropdown
• Check multiple values you want to include
• Click OK

This shows rows matching any of the selected values.

Text Filters

For text columns, you have additional options:

• Click the column dropdown
• Point to Text Filters
• Choose an option:

- Equals - Does Not Equal - Begins With - Ends With - Contains - Does Not Contain

Example: Find Names Containing "Smith"

• Click the dropdown on the Name column
• Select Text Filters > Contains
• Type "Smith"
• Click OK

This shows all rows where the name contains "Smith" anywhere.

Number Filters

For number columns:

• Click the column dropdown
• Point to Number Filters
• Choose an option:

- Equals - Greater Than - Less Than - Between - Top 10 - Above Average - Below Average

Example: Show Sales Over $1,000

• Click the dropdown on the Sales column
• Select Number Filters > Greater Than
• Type "1000"
• Click OK

Date Filters

For date columns:

• Click the column dropdown
• Point to Date Filters
• Choose options like:

- Today - Yesterday - This Week - Last Month - This Year - Between specific dates

Example: Show This Month's Entries

• Click the dropdown on the Date column
• Select Date Filters > This Month
• Click OK

Sorting Data

Basic Sort

• Click the dropdown in any column
• Choose Sort A to Z (ascending) or Sort Z to A (descending)
• For numbers: Sort Smallest to Largest or Sort Largest to Smallest

Sort by Multiple Columns

• Go to the Data tab
• Click Sort
• Choose your first sort column
• Click Add Level
• Choose your second sort column
• Click OK

This sorts by the first column, then by the second within each group.

Clearing Filters

Clear One Column

• Click the dropdown on the filtered column
• Click Clear Filter From [Column Name]

Clear All Filters

• Go to the Data tab
• Click Clear (in the Sort & Filter group)

Turn Off Filter Mode

Press Ctrl + Shift + L or click Filter on the Data tab to remove the dropdown arrows entirely.

Filter Tips

Recognising Filtered Data

When a filter is active:

• The dropdown arrow shows a funnel icon
• Row numbers may skip (hidden rows)
• The status bar shows how many records are visible

Filtered Rows Are Hidden, Not Deleted

The filter only hides rows temporarily. All your data is still there. Clear the filter to see everything again.

Copy Filtered Data

When you copy filtered data, only the visible rows are copied. This is useful for creating reports with just the filtered results.

Filtering Does Not Change Formulas

If you have formulas like SUM or AVERAGE, filtering does not change their results. Use SUBTOTAL function if you want calculations that only include visible rows.

Using Search in Filters

• Click the column dropdown
• Type in the Search box
• Matching values are shown
• Check the ones you want
• Click OK

This is faster than scrolling through long lists.

Common Uses

• Show only one department's data
• Find transactions above a certain amount
• View data from a specific time period
• Locate entries containing specific text
• Sort by highest or lowest values

Need Help?

For Excel training or help with spreadsheets, contact helpdesk@netlumait.com.au or call 1300 521 162.