Filtering Data
By the Syncfusion DevTutorials team · Last updated
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
- Click any cell in your table. The table needs a heading row.
- 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
- Click the arrow in a column heading, such as Region.
- A list of every value in the column appears, each with a check box.
- 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.