Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Create Scatter Chart in Excel
  • How To Perform and Interpret Regression Analysis in Excel
  • How To Create Frequency Distribution in Excel
  • Managing Conditional Formatting Rules in Excel
  • How to create dynamic reference table name in Excel

References

  • Count rows with at least n matching values
  • How to retrieve first match between two ranges in Excel
  • How to use Excel VLOOKUP Function
  • Approximate match with multiple criteria in Excel
  • Extract data with helper column in Excel

Data Validations

  • Excel Data validation allow weekday only
  • Excel Data validation date in next 30 days
  • Excel Data validation number multiple 100
  • Excel Data validation must contain specific text
  • Excel Data validation date in specific year

Advanced Filter in Excel

by

Filter setting using — And Criteria, Or Criteria or Formula as Criteria

This example teaches you how to apply an advanced filter in Excel to only display records that meet complex criteria.

When you use the Advanced Filter, you need to enter the criteria on the worksheet. Create a Criteria range (blue border below for illustration only) above your data set. Use the same column headers. Be sure there’s at least one blank row between your Criteria range and data set.

And Criteria

To display the sales in the USA and in Qtr 4, execute the following steps.

1. Enter the criteria shown below on the worksheet.

2. Click any single cell inside the data set.

3. On the Data tab, in the Sort & Filter group, click Advanced.

4. Click in the Criteria range box and select the range A1:D2 (blue).

5. Click OK.

Notice the options to copy your filtered data set to another location and display unique records only (if your data set contains duplicates).

Result.

No rocket science so far. We can achieve the same result with the normal filter. We need the Advanced Filter for Or criteria.

Or Criteria

To display the sales in the USA in Qtr 4 or in the UK in Qtr 1, execute the following steps.

6. Enter the criteria shown below on the worksheet.

7. On the Data tab, in the Sort & Filter group, click Advanced, and adjust the Criteria range to range A1:D3 (blue).

8. Click OK.

Result.

Formula as Criteria

To display the sales in the USA in Qtr 4 greater than $10.000 or in the UK in Qtr 1, execute the following steps.

9. Enter the criteria (+formula) shown below on the worksheet.

10. On the Data tab, in the Sort & Filter group, click Advanced, and adjust the Criteria range to range A1:E3 (blue).

11. Click OK.

Result.

Note: always place a formula in a new column. Do not use a column label or use a column label that is not in your data set. Create a relative reference to the first cell in the column (B6). The formula must evaluate to TRUE or FALSE.

Post navigation

Previous Post:

TRUE function: Description, Usage, Syntax, Examples and Explanation

Next Post:

Excel Pie Chart

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • AND function: Description, Usage, Syntax, Examples and Explanation
  • How to return blank in place of #DIV/0! error in Excel
  • How to use Excel OR Function
  • TRUE function: Description, Usage, Syntax, Examples and Explanation
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Basic timesheet formula with breaks in Excel
  • How to calculate most recent day of week in Excel
  • ISOWEEKNUM function: Description, Usage, Syntax, Examples and Explanation
  • TODAY function: Description, Usage, Syntax, Examples and Explanation
  • Steps to create Dynamic calendar grid in Excel

Grouping

  • Running count group by n size in Excel
  • If cell contains one of many things in Excel
  • Map inputs to arbitrary values in Excel
  • Calculate conditional mode with criteria in Excel
  • How to randomly assign data to groups in Excel

General

  • Sum by group in Excel
  • AutoFit Column Width, AutoFit Row Height in Excel
  • Cell References: Relative, Absolute and Mixed Referencing Examples
  • Basic text sort formula in Excel
  • Create dynamic workbook reference to another workbook in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning