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

Data Analysis

  • How to create running total in an Excel Table
  • Randomize/ Shuffle List in Excel
  • Reverse List in Excel
  • How to create a Histogram in Excel
  • Use Data Form to input, edit and delete records in Excel

References

  • Basic INDEX MATCH approximate in Excel
  • Extract all partial matches in Excel
  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions
  • Extract data with helper column in Excel
  • How to reference named range different sheet in Excel

Data Validations

  • Excel Data validation exists in list
  • Excel Data validation specific characters only
  • Excel Data validation must begin with
  • How To Create Drop-down List in Excel
  • Excel Data validation with conditional list

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.

Worked Example:   Filter Data Based on Date in Excel

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.

Worked Example:   Number and Text Filters Examples in Excel

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.

Worked Example:   Add Outline to Data in Excel

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:

How to multiple users can work on a Workbooks at same time

Next Post:

How To Load Analysis ToolPak in Excel

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

  • Invoice status with nested if in Excel
  • Complete List of Excel Logical Functions, References and Examples
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation
  • FALSE function: Description, Usage, Syntax, Examples and Explanation
  • IFNA function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Convert decimal minutes to Excel time
  • Calculate date overlap in days in Excel
  • How to enter Today’s Date or Static Date and Time in Excel
  • Convert Excel time to decimal minutes
  • MINUTE function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Map inputs to arbitrary values in Excel
  • Categorize text with keywords in Excel
  • Running count group by n size in Excel
  • Group times into unequal buckets in Excel
  • Group numbers with VLOOKUP in Excel

General

  • Basic text sort formula in Excel
  • Find Most Frequently Occurring Word in Excel Worksheet
  • Count cells that do not contain errors in Excel
  • List worksheet index numbers in Excel
  • Count cells that contain errors in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning