Comments on: Excel Advanced Filter - how to create and use

Excel Filter provides a variety of filtering options for text, numbers, and dates. The inbuilt filters can handle many scenarios. Many, but not all! When a regular AutoFilter can't do what you want, use the Advanced Filter and configure the criteria exactly suited to your needs. Continue reading →

Comments page 2. Total comments: 66

  1. Hi,
    Sharing my problem with a thinking that it will be solved at this forum.
    If a content in a cell starts with {,[or | what will be the format to get the filter results.
    Please help if any body know.

    1. Hello Sohaib!
      As a criteria of advanced filter, please use {* or [* where an asterisk replaces any sequence of symbols.
      Hope you’ll find this information helpful.

  2. Hi,
    I want to change the headings names in criteria range but I don't know how.
    For example: if I want to add the column DATE to the criteria range but instead of copying the heading DATE from the main table, I name it YEAR.
    Can I do that and how?

  3. Hi,
    If any condition is failed in my criteria, only 1st row is getting blank and not clearing the my previous filtered data. How to clear my previous filtered data? Please help.

  4. I was wondering if there was a way to filter multiple items from the same column. For example, if I wanted to find under the Item column all rows that have "Apples" and "Cherries" and "Banana".

  5. Hello

    Thank you for below topic was struggling from last 2 days to complete this task.

    "How to copy filtered rows to another worksheet"

  6. How do I look at already defined filters? I created some advanced filters and they work great. However I need to copy the tab and update the filter with the updated new tab. I'm filtering by a name and each tab will have a unique name. When I copied the tab it used the same criteria as the original tab and did not update the name in the new tab. I've click around to find where the Advanced filters were defined but cannot find it. Is there a way to look at defined filters on a tab?

  7. Don’t know if anyone is answering these anymore. Is it possible to do this : ( where “col” = Colomn ).
    (ColA=X. OR colB=Y) AND (colD=Z OR ColE=Q) ??? Note that there actually could be up to 4 items in each of thouse () or statements. Thanks

    1. Another "not sure if this is going to be useful to you, but it might be to someone browsing later". You can do this with a formula - type it as if you are doing it for the first row to be filtered.

      i.e.
      Filter
      =AND(OR(A2="X",B2="Y"),OR(D2="Z",E2="Q"))

      Quotes only required if X,Y,Z,Q are strings. Then your criterion range is the cell containing "filter" (or a blank cell, or a cell containing anything else which is not a heading in the filtered range) and the cell containing the formula, and no other cells.

  8. hello
    i want to formula in that in one cell i will put any date and tat same multiple date will mark
    plz suggest

  9. Lovely clear explanation of this function - it's the best I've seen by far. Thank you Svetlana!

  10. So I currently have a google survey form that my district can input their appointments in for tracking. The form includes the store number that input the appointment but as of right now, they all filter to one sheet. I have added a tab for each store but I am wondering how can I create a formula or a filter that will auto sort the full row of appointment content to the store's respective tabs within the same worksheet.

  11. Hi. I need to select rows, which DESCRIPTION (name of the column) contains words (apple OR orange OR melon) AND (2017). So it should have both apple and 2017 or orange and 2017.

    What should a filter look like?

    Thanks.

  12. Thanks in advance!

  13. Why does my data disappear even though I am entering the criteria and list ranges correctly.Please help

  14. Hi,

    How do I format a table so that it only shows rows with a specific number in them? Here's the project step that I'm having trouble with:

    Filter the table to display only those records with a Number of Days value of 7

    Thanks!

  15. Hi,

    Possible to have running total in a column with advance filter criteria?

    Thanks

  16. i WANT AUTOMATIC FILTER ANOTHER SHEET

  17. Hi Thank you, it is good article.
    I have one question. I want to exclude items which dones not begins with specific letters. I have dump data of sales and I want to exclude invoice number, which does not begins with CN.

  18. hi,
    please, where I can find the workbook for this article.
    thank you

  19. Hi
    how to exclude rows with more than one simultaneous conditions
    eg.: if I want to exclude from my data all the region = west and fruit = coconuts:

    It should be something like:
    Region west and Fruit coconuts

    I mean select all my data except when we have region =West and Fruit = coconuts at the same time

    1. I'm guessing you've either solved your problem or given up by now, but leaving this here in case it is useful for anyone else.

      NOT (AND (A,B)) is equivalent to OR(NOT(A),NOT(B)), so you can do this with:

      Region Fruit
      West
      (blank) Coconuts

      1. The comment form ate my brackets - Both the criteria rows there should have the "not" operator before them (the paired set of angled brackets)

  20. Dear Sir,
    Have a Great Day Ahead,

    Please see below mentioned data that I tried my best for your better understanding.
    I want result in Cell E3 when I filter data in Cell A7, (Vendor Name should be appear in Cell E3). Hope to hear from you as soon as possible.

    BR/
    Hussain

    (Cell E3) Vendor Name: Abdul Aziz Fahad Al Hajri Est

    (Cell A7) Vendor Name
    Mana Al-Haider Cont. Est.
    Ali-Ahmed-Al-Kanfari Gen. Co. Est.
    Abdul Aziz Fahad Al Hajri Est
    Danat Al-Rayan

  21. Hello,

    I'm a beginner at excel and I'm trying to filter more than one criteria that "does not begin with." Is there a criteria range for "does not being with?" I have more than 5 I need to filter from one column.

    1. Hello, Samantha,

      try the following.
      Create an additional table for the Advanced filter, entering your conditions in one line and giving each column the name of the corresponding column to filter – 'name' in our example. Type your criteria: <>x*, where '<>' means 'not equal to' and '*' allows matching any sequence of characters after 'x'. Thus, you will have 'does not begin with x' condition. Add other 5 of your own conditions, and apply advanced filter.
      Advanced Filter

  22. Thanks a lot. very good article. I solved my problem following your tutorial.

  23. Im doing a project on track and field but want to find out athletes at peak (ie season best=personal best) but was disqualified
    how would i write that in criteria cells
    these are the fields
    Time ran , season best, personal best

  24. Hi. I can do an advanced filter using data on Sheet 1 and filter it onto Sheets 2-5 using different criteria for each time. However, I want the filters to automatically update when I add data to Sheet 1.

    For example, I have data in cells A2:E28 on Sheet 1 and have filtered successfully onto the four sheets using different criteria each time. However, when I add a new row of data into A29:E29 on Sheet 1, I want this to automatically be filtered as per the advanced filters without having to reapply the filters each time.

    Any help would be great. Thanks in advance.

    1. Hi, David,

      unfortunately, it's not possible.
      As it is stated in the article: "Advanced Filter cannot be applied automatically since it has no pre-defined setup, it requires configuring the list range and criteria range manually."

  25. thanks alot for exact match i need

  26. I am looking a formula which can filter after satisfying the conditions..

    I have 500 list of activities in column A, dates ( EX: 01-Jan-2017 to 31-Dec-2017 in format ) in Column B and in Column C ( Three different items like Complete, Pending, Ongoing etc)

    Now I would like to extract out of 500 activities in column A , In January Month how many completed , How many pending & how many ongoing status in quantity.

    Please can you help on this. Thanks for your support

    1. Hi Naga,

      You can try a Count if Function.
      =Countif( Range(Month),”January”)
      Or you can convert your data in to pivot table and easily you can count you data montly.

  27. Respected Madam,
    please explain us this topic(Advanced filter) with video demonstration.your guidance with step by step is also very helpful

  28. Hi I want to filter gps data from two columns (latitude,longitude)but I also need to filter them in order to have a range from 1 to 4 lets say is that possible? and how

    Thanks

    1. when i doing advance filtering, i got the the error message after clicking "ok" .."the extract range has a missing or invalid feild name".
      please give me the solution.

  29. THANKS...........

  30. I can't get my advanced filter to allow my criteria to filter my copy-to range. any suggestions? I believe I have followed this example and cannot get a filter to work...

  31. thanks alot for exact match.

  32. Hi I need a formula that will track how many times a patient should been seen every 6 months (ex. Start date 1/1/2016 - ytd how many times was the patient seen?

    1. hi,
      you can use countifs function.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)