Filter by Color not working

BarbaraPietersen

New Member
Joined
Mar 30, 2011
Messages
7
Hi
I am using Excel 2007 and suddenly I can no longer filter by color. The option is greyed out and so is the sort by Color option. Does anyone have any idea why this would happen?

Thanks in advance.
 
Hello,

I resolved this for someone in our organization. This is for sorting but the same should apply for filtering. It was working for the person and then stopped; I tested it on other machines and even recreated a section of the spreadsheet from scratch with same results; Size of the spd does not matter.

Here is what happened; this person was used to highlighting the spreadsheet then selecting Sort & filter, Custom Sort, change the 'Sort on' to Cell color and then selecting the cell color choice but now she is getting 'no cell color' in that drop down.

This is resolve by also selecting a column in the 'Sort by' field. Once that was done she now has her color choices back.

The reason for this is as follows. Before the issue she used the same color for the entire row; then she started to highlight certain fields within a row a different colour and all sudden Excel no longer knew what color to present as some of the rows within her selection had different colours. Selecting a specific column to sort on allowed it to determine the selection of colors again and then sort on that.

Hope this helps
G

I had the same issue, 20,000 rows of data a dozen columns.
Had one column with conditional formatting on it and started out working on the file with filter by colour.
Came back to the file and stupidly removed the filter. Then when I wanted to reapply it, it wouldn't show up.

Just found a fix.
I highlighted the cell in row 2 the same colour as what I wanted to filter by, the option came back up.
Seems once there are no instances of a cell having that colour after a certain number of rows it drops off the filter option. I'm at row 13,000 so must be somewhere before that.
 
Upvote 0

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
ggevaert, your "Sort by" solution is probably working by bringing one of the coloured cells in the filtered column below the 10,001st unique item threshold that is all the filter preview can see. You will see a warning like "Not all items showing".

It's not that you have more than 10,000 rows or records, but where the first coloured cell is.

In the spreadsheet I'm working on today:
> the Lastname column 10,001 threshold is row 42729
> in the ID column (with every value being unique) the threshold is row 10,002 (taking the header into account)
> in another column with only two unique values, the threshold is the entire list at row 238770.

As you've discovered, sorting the list to bring at least one coloured cell below the threshold fixes this, or colouring a cell in the first few rows - as others have suggested.
 
Last edited:
Upvote 0
Hi
I am using Excel 2007 and suddenly I can no longer filter by color. The option is greyed out and so is the sort by Color option. Does anyone have any idea why this would happen?

Thanks in advance.

Over eight years later, Barbara, and you may no longer be interested, but the probable reason is in my answer to ggevaert above...
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top