US Trends

how to create filter in excel

To create a filter in Excel, turn your header row into clickable drop-downs, then use those drop-downs to show only the rows you care about.

Quick Scoop

1. Basic steps: create a filter

Use this when you have a normal data list with column headers (like “Name”, “Date”, “Amount”).

  1. Make sure your first row contains clear headers (no blank header cells).
  1. Click any cell inside your data range (or select the header row A1:E1, etc.).
  1. Go to the Data tab on the ribbon.
  1. In the Sort & Filter group, click Filter.
  1. Little drop‑down arrows will appear in each header cell – that means filtering is turned on.

Now your table is “filter-ready”.

2. How to use the filter drop‑downs

Each header’s arrow lets you control what rows appear.

For any column:

  1. Click the drop‑down arrow in that column’s header (for example, “Month” or “Status”).
  1. In the menu, you’ll see a list of all unique values in that column with checkboxes.
  1. Click (Select All) once to clear everything, then tick only the values you want (e.g., just “Completed”).
  1. Click OK – Excel hides all rows that don’t match your choices.

You can repeat this on multiple columns at once (for example, filter Status = Completed and Month = January together).

3. Handy variations (text, numbers, dates, colors)

Excel filters are smarter than just checkboxes – there are special options depending on the column type.

  • Text columns
    • Use the Text Filters menu: “Equals”, “Contains”, “Begins With”, etc.
* Example: Filter a “Product” column using **Text Filters → Contains… → "Bananas"**.
  • Number columns
    • Use Number Filters : “Greater Than…”, “Between…”, “Top 10…”, etc.
* Example: **Number Filters → Between…** and set a lower and upper bound (like 10 and 100).
  • Date columns
    • Use Date Filters : “This Week”, “Next Month”, “Between…”, etc.
* Example: Show only items from the first 10 days of April by choosing **Between…** and entering your start and end dates.
  • Filter by selected cell’s value/format
    • Right‑click a cell that has the value, color, or icon you want to filter on.
* Point to **Filter** , then choose **Filter by Selected Cell’s Value / Color / Font Color / Icon**.

4. Quick shortcuts & dynamic filtering

If you work with filters a lot, a couple of extras are useful.

  • Turn filters on or off with the keyboard:
    • Press Ctrl + Shift + L to toggle filters for your current range or table.
  • Use the FILTER function (Microsoft 365 and later) for “live” filtered results:
* General structure: `=FILTER(array, include, [if_empty])`.

* Example idea: Keep your full data on one sheet and use **FILTER** on another to show only rows matching certain criteria (like only rows where a status column equals “Active”).

This formula‑based approach gives you a dynamic, always‑up‑to‑date filtered view separate from your original table.

5. Removing or clearing filters

When you’re done and want everything visible again:

  • To clear the filter from one column:
    • Click its drop‑down arrow and choose Clear Filter From “ColumnName”.
  • To remove filters from the entire range (no arrows at all):
    • Click anywhere in the data, go to the Data tab, and click Filter again to toggle it off.

Simple example in action

Imagine a small sales table with columns Month , Days , and Season.

  • You select the header row, go to Data → Filter , and arrows appear on each header.
  • In Days , open the drop‑down, click Select All to clear, tick only 31 , hit OK , and now you see only months that have 31 days.

That’s the core pattern you’ll reuse for almost any “how to create filter in Excel” scenario.

Information gathered from public forums or data available on the internet and portrayed here.