Filter and Advanced Filter in MS Excel

 

1. Filter in MS Excel

The Filter feature in Excel allows users to display only specific rows based on given criteria, making data analysis easier.

Steps to Apply a Filter:

1. Select the data range (including headers).

2. Go to Data → Filter or use the shortcut Ctrl + Shift + L.

3. Dropdown arrows will appear in the header row.

4. Click the dropdown arrow and choose filter criteria (e.g., text, numbers, or color).

5. Click OK, and Excel will display only matching records.

Filter Options:

• Text Filters: Contains, Does Not Contain, Begins With, Ends With, Equals, etc.

• Number Filters: Greater Than, Less Than, Between, Equals, etc.

• Date Filters: Before, After, Between, Today, Last Week, etc.

• Color Filters: Filter by cell color or font color.

---

2. Advanced Filter in MS Excel

The Advanced Filter feature allows users to apply more complex filtering criteria compared to the basic Filter function.

Steps to Apply Advanced Filter:

1. Select the entire dataset (including headers).

2. Go to Data → Advanced (under the Sort & Filter group).

3. Choose one of the following:

   • Filter the list, in-place (filters the existing data).

   • Copy to another location (extracts filtered data to another range).

4. Specify the Criteria Range (a separate area where filter conditions are defined).

5. Click OK to apply the filter.

Important Notes:

• The Criteria Range must include column headers matching the dataset.

• Multiple criteria can be applied using different rows.

• AND Condition: Criteria in the same row (e.g., "Sales > 5000" AND "Region = East").

• OR Condition: Criteria in different rows (e.g., "Sales > 5000" OR "Region = East").

---

3. Common Formulas for Filters

1. FILTER Function (Available in Excel 365 & 2019)

The FILTER function extracts data based on specified criteria.

Syntax:

excel
=FILTER(array, include, [if_empty])

• array: The range of data to filter.

• include: The condition(s) to apply.

• [if_empty]: Value to return if no results match.

Example:

Filter all sales greater than 5000:

excel
=FILTER(A2:C100, B2:B100>5000, "No results found")

---

2. Using IF and AND/OR for Advanced Filtering

• To filter data based on multiple conditions, use IF, AND, OR functions.

Example:

Check if sales are greater than 5000 and region is "East":

excel
=IF(AND(B2>5000, C2="East"), "Match", "No Match")

---

3. Extract Unique Values Using Advanced Filter

To extract unique values:

1. Go to Data → Advanced Filter.

2. Check Unique records only.

3. Click OK.

Alternatively, use the UNIQUE function (Excel 365 & 2019):

excel
=UNIQUE(A2:A100)

---

4. Count Filtered Rows

To count the number of visible rows after filtering, use:

excel
=SUBTOTAL(3, A2:A100)

• 3 represents the COUNTA function, counting only visible cells.

---

5. Sum of Filtered Data

To sum only the visible (filtered) rows, use:

excel
=SUBTOTAL(9, B2:B100)

• 9 represents the SUM function, summing only visible data.

---

Key Differences Between Filter and Advanced Filter

Feature	Filter	Advanced Filter
Basic Filtering	Yes	Yes
Complex Criteria	No	Yes
Extract to Another Location	No	Yes
Supports Multiple Conditions	Limited	Yes
Works with Formulas	No	Yes