Microsoft Office

How To Filter On Microsoft Excel

To filter in Microsoft Excel, select your data, including its headings, then choose Data > Filter. Open a column's drop-down arrow and select the values or conditions you want to display. Excel hides rows that do not match without deleting them. Apply filters to additional.

Which licences fit your PC setup?

Prices are an AUD snapshot, not a live quote. Confirm variant and total on the listing.

Prepare your data before filtering

Arrange your list with one heading per column and one record per row. For example, an orders list might have Customer, Region, Order Date and Amount columns. Keep each order's details together on the same row.

Select the complete list, including its headings. This helps avoid leaving records outside the filter range, especially when blank rows separate parts of the data. If your data is already an Excel table, its headings normally have filter arrows.

Keep values consistent within each column: amounts should be numbers, dates should be recognised as dates, and names should be text. A date that merely looks correct but is stored as text can produce unexpected filtering options.

Apply a filter to show selected values

These steps use Excel's Windows desktop ribbon. Menu wording can vary on other platforms; Microsoft's filtering guidance includes platform-specific instructions.

  1. Select the range you want to filter, including the header row.
  2. Choose Data > Filter. An arrow appears beside each heading.
  3. Open the arrow for the column you want to use, such as Region.
  4. Untick Select All, then tick the values you want to keep visible, such as North and South.
  5. Select OK to apply the filter.

Choosing North and South displays records matching either value in that column. Each visible row retains its other details, including its customer, date and amount.

Filtering hides rows; sorting changes their order. You can use the sorting commands in the header menu when you also need alphabetical, numerical or date order.

Filter text, numbers, dates and blank cells

Use a condition when selecting individual entries would be cumbersome. Open a column's filter arrow, choose the filter category for its data type, and select a comparison.

  • Text Filters: use Contains to find a word within a description, or Begins With to match the start of a name or code.
  • Number Filters: use Greater Than, Less Than or Between to restrict amounts or quantities.
  • Date Filters: choose a date condition to focus on records from the period you need.
  • Blanks: select the (Blanks) entry in the value list to find records with missing values in that column. Untick it to exclude them.

For example, to show orders above 500, open Amount, choose Number Filters > Greater Than, enter 500 and select OK. This excludes orders equal to 500; choose Greater Than Or Equal To if those should also appear.

Use wildcards for text patterns

In text filter conditions, an asterisk (*) matches any sequence of characters, while a question mark (?) matches one character. For example, AB* matches text starting with AB, and AB? matches AB followed by one character.

To search for a literal asterisk or question mark, place a tilde before it: ~* or ~?. Use these patterns with text conditions rather than numerical comparisons.

Combine conditions without confusing AND and OR

Filter several columns with AND

Filters on separate columns work together using AND: a row must satisfy every active column filter. To show North-region orders above 500, first select North in Region, then apply Greater Than 500 in Amount.

Adding another column filter narrows the current results. If everything disappears, clear one condition and check whether any records actually match the combination.

Apply two conditions within one column

Open a text or number filter condition to access the custom filter dialogue. Enter your first comparison, choose And or Or, then enter the second comparison.

  • And requires both conditions. An amount greater than or equal to 500 AND less than or equal to 1,000 must fall within those boundaries.
  • Or accepts either condition. An amount below 100 OR above 1,000 can fall on either side of the excluded middle range.

These examples illustrate conditions you can set; the values are not Excel limits. A standard column filter's custom dialogue accepts comparisons, rather than arbitrary worksheet formulas.

Use Advanced Filter for OR across different columns

To show rows where Region is North OR Amount exceeds 500, ordinary filters on both columns will not produce the intended result. In desktop Excel, use Data > Advanced with a separate criteria range.

  1. In an unused area, copy the Region and Amount headings exactly as they appear in your list.
  2. Under Region, enter North. Leave the adjacent Amount cell blank.
  3. On the next row, leave Region blank and enter >500 under Amount.
  4. Choose Data > Advanced. Set List range to your complete data range, including headings, and Criteria range to the separate headings and both criteria rows.
  5. Choose Filter the list, in-place and select OK.

Separate criteria rows represent alternatives; conditions on the same criteria row must all match. For more elaborate criteria, including formulas, follow Microsoft's Advanced Filter instructions.

Filter by colour and restore hidden records

If your list uses cell colours, font colours or conditional formatting icons, open the relevant heading's arrow and choose Filter by Color. Select the available colour or icon you need. Excel uses the US spelling “Color” in this menu.

To reset one column, open its menu and choose Clear Filter From that column. To clear all active criteria, choose Data > Clear. This restores the records while leaving the standard filter arrows available. Choose Data > Filter to turn those arrows off.

If values change after filtering, choose Data > Reapply to refresh the results. If newly added records are missing, check that they belong to the filtered range.

For related Office tasks, browse the Microsoft Office guides. If you are also dealing with installation or licensing, separate walkthroughs cover Office activation using Command Prompt and finding an Office product key using Command Prompt.

Start with one column in your own list, select a value you recognise, and check that the visible records match before adding another condition.

Other editions for separate workloads

Other editions for separate workloads; prices in AUD, confirm the total on the listing.

How to check this worked

  1. Open Settings, System, Activation and confirm the edition shown.
  2. Enter the key and run activation; expect a green tick within a minute.
  3. Reboot and re-check activation survives the restart.

FAQ

What else should you know before buying?

What do I need before starting?
Read the requirements and confirm the edition first; the steps assume the machine is compatible.
How do I know the licence is genuine?
Activate on Microsoft's servers and check Settings, System, Activation. A working activation is the test.
Is this a subscription?
Keys sold here are one-time lifetime licences. Microsoft 365 plans are billed by Microsoft instead.
What if the key fails?
Email hi@ms.codes with the order number and a screenshot. Replacement or refund applies within 30 days.

Check who prepared and reviewed this guide

Written by
MS.Codes editorial
Reviewed by
Support team

Last verified: (source review; see test status above).

Recent Post