How to filter in Excel (Multi criteria, by color, text filter, search multiple items)

Описание: Filtering is actually a query, that is, finding out the items that meet certain conditions from the table.

Chapters:
0:00 How to filter in Excel?
0:33 Multi-criteria Number Filters (Excel filter shortcut)
1:16 How to filter by color in Excel?
1:38 Filter by font color
1:57 Excel text filter
2:23 How to search multiple items in Excel filter?
3:15 How to remove filter in Excel?

Description:

How to filter in Excel (Multi-criteria, by color, text filter, search multiple items)

There are more filtering features in Excel, which can be divided into Number filters, Text filters, Color filters and Search filtes. Among them, Number filters and Text filters can combine a variety of filter conditions, such as equal to, greater than or equal to, less than or equal to, starting with a certain word, ending with a certain word, containing a certain word, etc.; Color filters are divided into filtering by cell background color and by font color; Search filters filter out iters that consist of a specified number of digits, beginning or ending with a specified character.

One. How to filter in Excel?

(1) Single-condition Number Filters (How to filter multiple numbers in Excel)

Suppose we want to filter for clothing in the clothing table with "September sales" greater than or equal to 700. Click D to select the "September Sales", select the "Data" tab, click "Filter", a filter icon appears in the lower-right corner of cell D1, click the icon, select "Number Filters → greater than or equal to", open the "Custom AutoFilter" window, enter 700 to the right of "is greater than or equal to", click "OK" to filter out clothing greater than or equal to 700.


(2) Multi-criteria Number Filters (Excel filter shortcut)

1. Suppose we want to filter for clothing with "October sales" greater than or equal to 600 and less than or equal to 700. Select column E and press Ctrl + Shift + L, and a filter icon appears in the lower-right corner of E1.

2. Click the "Filter" icon, select "Number Filters → Custom Filter", open the "Custom AutoFilter" window, click the list box under "October Sales", select "Greater than or equal to", enter 600 on its right; click the drop-down list box under "And", select "Less than or equal to", enter 700 on its right, after setting, click "OK", then filter out the clothing with "October sales" greater than or equal to 600 and less than or equal to 700, there is only one record.

Tip: If a column of the selected table is filtered, and another column is selected to filter, when clicking the "Filter" icon or pressing the shortcut key will cancel the filter of the previous column, and then click the "Filter" icon again or press the shortcut key once before adding a filter icon to the selected column. When setting the filter condition, there is an "And, Or" between the two conditions, where "And" means and, at the same time, that is, condition 1 is met and condition 2 is also met; "Or" means or, that is, no matter how many conditions there are, as long as one condition is met.


(3) How to filter by color in Excel?

1. Filter by cell color

Suppose we want to filter for clothing with a blue background for "Clothes Name". Select column B, press Ctrl + Shift + L, a filter icon appears in the lower right corner of B1, click it, select "Filter by color"→ Filter by cell color → Blue, only one record will be filtered.


2. Filter by font color

To filter for "No." for example. The operation works are the same as Filter by cell color, except that when Filter by color, select the color under "Filter by font color".


(4) Excel text filter

1. Suppose we want to filter for clothing that starts with "Pink" in "Clothes Name". Select column B, press Ctrl + Shift + L, a filter icon appears in the lower-right corner of cell B1, click it, and select Text Filter → Begins with.

2. Open the "Custom Autofilter" window, enter "Pink" on the right side of "Begins with", click "OK", and then filter out two records starting with the word "pink".


Two. How to search multiple items in Excel filter?

(1) Filter for items that contain a number

Suppose we want to filter for clothing that includes 8 in "September sales". Select column E, press Ctrl + Shift + L, a filter icon appears in the lower right corner of cell D1, click it, enter 8 in the search box, click OK, then filter out records contain 8, only one.


(2) Filters items that consist of the specified number of digits

Suppose we want to filter for clothing of four-figure in "September sales".You do the same as you filter for records that begin or end with a number, but enter four question marks in the search box.