# Filtering Data

Course: [Excel](https://www.devtutorials.ai/learn/excel) · Section: Data Management
Source: https://www.devtutorials.ai/learn/excel/data-management/filtering-data

> Apply and remove filters, and filter by value, number condition, date, and color.

Sorting changes the order of rows. **Filtering** hides the rows you do not need right now, so you can focus on the ones you do need, such as only this month’s orders, only the North region, or only sales over 1000. The hidden rows are not deleted. They come back as soon as you clear the filter. In this lesson, you will apply and remove filters, and filter by value, by date, and by color.

Open the **Excel sandbox** and follow along. Filter tools are under **Sort & Filter** on the **Home** tab, and on the **Data** tab.

## Apply filters

1. Click any cell in your table. The table needs a heading row.
2. Click **Sort & Filter** > **Filter** or **Data** > **Filter**. In Excel, the shortcut is **Ctrl + Shift + L**.

A small **drop-down arrow** appears in every heading cell. Click an arrow to filter by that column.

## Filter by value

1. Click the arrow in a column heading, such as **Region**.
2. A list of every value in the column appears, each with a check box.
3. Untick **Select All**, tick the values you want, such as **North**, and click **OK**.

Only the matching rows are shown. Notice:

- The **row numbers** of the visible rows turn blue in Excel and skip numbers, because the other rows are hidden.
- The column’s arrow changes to a **funnel** icon, which shows that a filter is on.
- You can filter several columns at once. Each filter narrows the rows further.

For numbers, use **Number Filters** for conditions like **Greater Than** `1000`, **Between**, or **Top 10**. For text, **Text Filters** has **Contains**, **Begins With**, and more.

A filter **hides** rows; it does not delete them. Totals made with **SUM** still include hidden rows. To total only the visible rows, use **SUBTOTAL**, such as `=SUBTOTAL(9, B2:B50)`, which Excel’s AutoSum inserts for you on filtered data.

## Filter by date

When a column holds real dates, its filter list groups them by **year**, **month**, and **day**, and offers **Date Filters**:

- **This Month**, **Last Month**, **Next Week**, and similar options.
- **Before**, **After**, and **Between** specific dates.

Tick a month to see just that month’s rows.

Sort and filter work well together: filter to the rows you care about, then sort what is left. Sorting a filtered list only reorders the visible rows.

## Filter by color

If cells have a fill or font color, such as overdue rows marked red, you can filter by that color. Click the arrow and choose **Filter by Color**, then pick the color.

**Filter by Color** may not be available in the sandbox. In Excel, it is in the column’s filter menu. As another option, add a **Status** column with text like `Overdue`, and filter by value instead. Text is also easier for others to understand than color.

## Remove filters

- **Clear one column’s filter:** click its funnel icon and choose **Clear Filter From…**, or tick **Select All** again.
- **Clear all filters but keep the arrows:** **Sort & Filter** > **Clear**.
- **Turn filtering off completely:** click **Filter** again. The arrows disappear and every row is shown.

## Common mistakes

| Mistake | What happens | How to fix it |
|---|---|---|
| Forgetting a filter is on | Data seems to be missing | Look for funnel icons and clear the filter |
| A blank row inside the table | Rows below it are not included in the filter | Remove the blank row and apply the filter again |
| Dates stored as text | No date groups or Date Filters | Convert them to real dates |
| Using SUM on filtered data | The total includes hidden rows | Use `SUBTOTAL(9, …)` |
| Several filters stacked up | Far fewer rows than expected | Clear all filters, then add one at a time |

## Exercise

Click **Reset** in the sandbox header. Type this table in **A1:C7**:

| A | B | C |
|---|---|---|
| `Order` | `Region` | `Amount` |
| `101` | `North` | `450` |
| `102` | `South` | `1200` |
| `103` | `North` | `1500` |
| `104` | `East` | `300` |
| `105` | `South` | `800` |
| `106` | `North` | `950` |

Then do each task and press **Check**.

## What to remember

- **Filter** adds drop-down arrows to the heading row.
- Filters **hide** rows; clearing them shows the rows again.
- Filter by value with check boxes, or with **Number**, **Text**, and **Date Filters**.
- A **funnel** icon means a column is filtered.
- **SUM** includes hidden rows; **SUBTOTAL(9, …)** adds only visible ones.
- Click **Filter** again to turn filtering off.

In the next lesson, you will control what people can type with data validation, and name ranges.