Obscure Life Hack: Filtering Large Excel Spreadsheets By Colour

This is a weird thing I just discovered, so I thought I'd post it just in case it helps someone else.

If you use Microsoft Excel, it's possible to filter rows by colour. But it turns out there's a limitation; any kind of filtering only works for up to 10,000 rows if the cells in them have unique values.

So in this case, I wanted to filter for cells highlighted in orange.

As you can see by the row numbers on the left, we're down at about row 18,000. I've also got the screen split and locked after row 5, so that the headers are always visible.

But as you can see, when I select to filter by cell color, orange doesn't appear as an option.

Now for the solution.....

Yep, you need to make the top cell in the selection match the colour you want to filter by.

Like this....

Then, when you go to filter, orange mysteriously appears as an option. Ta-da !

When you've finished doing whatever you're doing, don't forget to change the top cell back to whatever colour it was before.

But I'm starting to transition to Libre Office as I de-Microsoft my business (and my life), so I've no idea if the same thing will happen over there, and if it does whether the solution is the same.....

Posted using The BBH Project