Comments on: How to filter in Excel

If working with large data sets, it can be a challenge not only to calculate data, but also to find the relevant information. Luckily, Microsoft Excel makes it easy for you to narrow down the search with a simple yet powerful Filter tool. Continue reading →

Table of contents

Comments page 2. Total comments: 46

  1. Dear,

    I want remove vlookup function without filter down, how is possible please sugges...

  2. I WANT TO ADD SOME WORD IN FILLTER BUT I CAN NOT ADD THIS WORD

  3. In a single Excel worksheet, I have two different sets of data range. So let's say A1:B10 I have a header "Ranking" and "Team" followed by a list of 9 teams.

    Then another range from A20:B40. Similarly, a header "Ranking" and "Team" followed by a list of 19 teams.

    I select one data range and click Filter, which allows me to filter the data for that data range. But then I want to create another filter for the second data range and when I try to do that, Excel already has the "Filter" button selected and I have to remove the original filter to create a filter for the new data range.

    So my question is, how do I create two separate filters for separate data ranges in a single worksheet WITHOUT using the Table Format. I'm aware I can turn both data ranges into Tables and the issue is gone, but based on the way to Excel sheet is constructed, I don't want to use tables.

    Any advice?

  4. I applied A to Z filter in my Workbook and after that i saved it and my coordinated data has been unpositioned now. So please tell me the option by applying that i can get my original data as same postion back.

  5. Why would the filter only show two numbers in a list of more than 100? I added a filter to the 4th row of data rather than the 1st row, and when I click the filter dropdown, it is only showing the first two numbers. And I am unable to sort within the filter. Why would that happen, and how can I fix it?

  6. How do i add a column with a formula to an already sorted and filtered spreadsheet?

  7. We have Windows 7 Lenovo business laptops with NO touchscreens/pen/tablet input and Office 2013.

    I have a very heavily macro'd Excel .xlsm workbook which turns the auto-filters on and off. No problems there. But if the auto-filters are ON and the user uses the 'Clear' option via the ribbon bar, then Excel freezes. It only happens when the auto-filters are cleared via the ribbon bar options.

    Any ideas please?

  8. Great Article!
    One question though, lets say i have a table for actions for a project, I filter by due date so i know what is the first action to come. If i add a item to the bottom of the table, it does not move into place where it should be, i have to reapply the filter.

    Is there any automated way to do this once i enter the due date it goes to where it should?

    Thank you!!

  9. Hi Sr,

    In excel, when i insert (S)alphabet and enter it always showing one name(Sumanjali) in entire worksheet,please advice me ,how to remove that particular name.

  10. if the generate the group then sheet has been fixed by filter.I am facing a problem that i am not able to remove the filter column and the data column is also unable.

  11. Hi,

    Thank you for sharing.
    Do you know how to find which column is filtered? For example there are many columns (A1:A300)and some of them is filtered. How to find the filtered one?

    Thank u in advance..

    1. The second argument identifies the column to be filtered by. If I use the following function:
      =filter(A:AX,D:D=5)
      The funtions is saying that it is searching all rows down columns from A to AX, and looking for 5 as the value in column D.

      Likely A1:A300 is the data range being filtered, the second argument should identify the column where values are looked for.

  12. Please advise if I can have some button/slicer that can enable me to switch between two different filters for pivot table.

    I have prepared a sales analysis pivot chart. I want an option to switch between total quantity or total sales figure on pivot chart.

    How do I do it?

    1. Hi Osama, write "total quantity" in a cell and write "total sales" in another cell (beside the first one cell or below or wherever you want). Right click the cell you wrote "total quantity" in - select "Hyperlink" - under "Link to:" clink on "Place in This Document" - write the cell name of the "total quantity column or pivot table if both are in different pivots" in "Type the cell reference" e.g. D25 - click OK and the shortcut for "total quantity" is created, now do the same for "total sales"

  13. I really appreciate your guide concerning filtering on excel. I will like to know more on excel, how do I go about it?

    Thank you in anticipation.

  14. What is the shortcut for "Text Filter column with 2 criteria"
    I would like to go to "Contains and Contains" option by one click but I was not able to find it. Can you please explain me how to create shortcut for that? Even this can be with macros.

    1. Use the text filter option and choose contains from the popup
      A new window Custom Autofilter is there wherein you enter both the "contains" with conditions of "And" and click OK.

      This should work for sure.

  15. please advise me

    if too many names - data is available in one sheet.
    and if we need to add some data under anyone's name.

    should i filter according to names and update the data under that.

    when i do that,
    after selecting all data
    the new added data comes at the end of the excel sheet.

    please advise

    1. Hi Sameer, make a column for numbering (unique numbers) and give numbers 1, 2, 3, 4... etc. for names and give 1.1, 1.2, 2.1, 2.2, 2.3... etc. for other related data of each name and in last of your data entry sort as the Smallest to Biggest and you will get each related data for a single person under his name.

      e.g. let suppose A and B are the names and AA & BB are the father names that we need to put under the name of their sons. A is son of AA and B is son of BB. The unique number for A will be 1, for AA will be 1.1 and for B will be 2, for BB will be 2.2. And let suppose after the father name you need to add their age, the number for age of A will be 1.3 and for B will be 2.3.

      I hope it helps you with what you are seeking.

  16. Thank you very much. The formula did worked well.

  17. Hi Dear,

    Is there a formula for below task?

    Suppose our organization works in 3 (A, B and C) Schools. And for schools, our organization only gives School Seminars and Refresher School Seminars about Disaster Risk Reduction.
    e.g. First Seminars are conducted in all schools but Refresher Seminar is just conducted for School B in a specific date.

    I need a formula for Gap Analysis using the dates of the seminars, that will only take the last dates. Means "If the cell "Refresher Seminar Date" is empty, the formula will take the date from the cell "First Seminar Date" but if the cell "Refresher Seminar Date" has data, the cell won't take the date from " First Seminar" but it will automatically take the Date from the cell "Refresher Seminar Date".

    Many thanks for your nice work, please keep it up! :)

    1. Dear Suliman,

      Supposing "First Seminar Date" is in column A, and "Refresher Seminar Date" is in column B, you can use the following formula:

      =IF(B2<>"", B2, A2)

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 :)