Limitation (or bug?) with Filter by Color

Did you know that you can filter by color since Excel 2007?  Yes, that’s almost 10 years.  I wont’ say it is a new feature notwithstanding the fact that many people are not aware of it yet. 😛

Filter by Color is nice and easy to use, which I think there is no argument.Excel Tip - Filter by color.gif

Interestingly, the Filter by Color may not be working properly if we are working with a very large table.  What I mean by “large” here is a table with a column of more than 10001 unique items.

The following demonstrates the situation:

The first filled color appears on row 10003, which is the 10002th unique item on column B.  Surprisingly, Excel fails to detect the filled color and hence does not offer me the Filter by Color.

Excel Tip - Filter by color1.gif (Note: If the filled color starts on the 10001st unique item, it works normally… @_@)

 

By default (or limitation), Excel only displays the first 10,000 unique items in filter.Excel Tip - Filter by color.PNG

I am not sure if it is the reason for Excel’s failure in detecting the filled color that falls on/after the 10,002nd cell.

Here’s a a not-so-perfect workaround…

  • Go back to the very top of the list
  • Apply the filled color that you need to filter (tip: copy and paste format is safer in order to get the filled color of interest)
  • The Filter by Color is back.  Yeah!

Excel Tip - Filter by color2.gif

I experienced this in Excel 2010.  I didn’t test it on other Excel versions.  Please let me know if it is not an issue in Excel 2013 or 2016.

Also please share with us if you have other solutions or workarounds by leaving comment.

Advertisements
This entry was posted in Excel Tips and tagged , . Bookmark the permalink.

9 Responses to Limitation (or bug?) with Filter by Color

  1. Igor says:

    No, my data was not unique, that’s probably the catch. I haven’t try unique records, sorry.

    Like

    • MF says:

      No worry. Maybe you may test it and tell me the result later.
      Cheers,

      Like

      • Igor says:

        I have a question, actually not related to this topic. I have a table with employees and their id numbers, also there is a column with their organizational roles, in a separate column I have formula which looks up their rate from another table.
        I need a formula (true or false) which will compare text in column E “Staff Role” to another table with roles and rates and if the formula won’t find the exact role it will say “No role” or something like that. Do you have any ideas what should I use?

        Like

        • Igor says:

          I don’t think there are many formulas to compare text in a cell to text in array based on criteria.

          Like

          • Igor says:

            When I said: “I need a formula (true or false) which will compare text “, I mean – I want to compare text in the “staff role” column to the list of all roles in another table and return Yes (the same text is on the list) or No (the same text is not on the list). But I also want to check this text (staff role description) against each employee, so the criteria would be employee id.
            I think SUMIFS has its syntax similar to what I need (SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, …), the problem is that it works perfectly with numbers, but not with text. If I could somehow change “sum_range“ to “check_range“ that would be great.

            Like

          • MF says:

            Hi Igor,
            Are you trying to count the match? If so, try COUNTIFS.

            Like

        • MF says:

          Hi Igor,
          VLOOKUP is on top of my mind.
          Nevertheless, I am not sure what do you mean by
          “I need a formula (true or false) which will compare text in column E “Staff Role” to another table with roles and rates”… does the “Rates” one of your criteria?
          Please try to elaborate more, ideally with a simple example, and I will see if I could assist.
          Cheers,

          Like

  2. Igor says:

    Hi Fung,
    Hopefully you are doing well!
    I do some operations with color filter and it was interesting for me that you noticed it doesn’t work for more than 10,000 lines, so I tried it myself and found that it all works good on mu computer. I created 100,000 records and painted 15,836 of them, applied filter and works perfectly.
    (I would love to insert a screenshot here but I can’t.

    Like

    • MF says:

      Hi Igor,
      Thanks for your comments.
      Do you data contain more than 10,000 unique items?
      In my case, the failure seems to be a result of too many unique items on the column and the color-filled cells happened on the very last position (beyond 10,001th unique items).
      Nevertheless, I haven’t tried that in other computer. So I am glad that you took the effort to test it and let us know the result.
      Cheers,

      Like

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s