1

tl;dr How can we force Excel to lock-in a filter for a Column which only has 1 unique value within it?

I am having a very hard time coming up with a nice "label" for this problem, so I believe it is best if I do it with an example. In these examples, I am using an excel sheet where Column 1 represents a list name and Column 2 represents values within each list.

Example that works

I first filter on column 2, searching for "Royalties".

Image showing only Royalties filter applied to column 2

Now, I see that multiple lists from Column 1 match. I am only interested in one of them, so I apply a filter to column 1.

Image showing filters applied on both columns

Finally, I want to see all values that are within that list, so I remove the filter from Column 2.

Image showing filter from Column 2 removed

Example that is frustrating

So, I want to do the same thing except when the filter on Column 2 only results in a single list name in Column 1.

Let's repeat Step 1 from above, but by filtering Column 2 on "Rent or Royalties"

Image showing filter on Column 2 that results in 1 result in Column 1

Now, Column 1 only has 1 result. What I would like to do is "lock in" a filter on Column 1 even though it is not necessary. The reason is that, then, I want to remove the filter from Column 2 and see all values that fall within that list.

However, I can find no way to do that. Instead of locking in the filter, Excel won't apply a filter to Column 1. Then, when the filter is removed from Column 2, it shows all values in both columns.

Question

The current workaround is to copy the value from Column 1 and paste it into Notepad (you can't keep it in memory because the filter action wipes the copied value). Then, remove the Column 2 filter and then apply an "equal to" filter to Column 1. However, I am wondering whether there is a better way to do this.

Please note that I would prefer a general solution. The spreadsheets change and I would rather not use a macro / VBScript unless it is generic.

Thank you for taking the time to read this long post.

Regards, Sachin.

1

You can avoid the copy to Notepad step if you reverse the order. First copy the value in Column 1 then apply a "Text Filters > Equals..." filter to Column 1 and paste the copied value. Now you can clear the filter from Column 2.

  • Excellent point. And that actually does end up forcing a filter on Column 1 (since it must now do an explicit 'Equals' match). – zakaluka Apr 18 '16 at 20:00

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.