How To Clear Sort By Color In Excel

Bob Jones AKA: CyberTaz

Please see the inline responses:

"There are some problems with the filter in Excel 2011
I could not remove the color filter"

How did you try? It should require nothing more than putting a check in the Select All checkbox in the list below the Filter section of the Sort & Filter dialog or clicking the Clear Filter button in that dialog's lower right corner. Alternatively, you can either select any cell in the filtered column & use the Filter button's menu on the Data Tab of the Ribbon to select the Clear Filter command or simply turn the Filter off altogether.

"I could not select a number in a long list, f ex I wanted the number 12345 and started to write 123, when I arrived to 4 it found nothing !"

That suggests that there is something more to be known about the data... keep in mind that if there are any spaces, punctuation marks or other characters (including decimal points) they must be entered into the Search field as well.

"It would be nice to be able to keep the filter on top, but it disappears each time you click outside it."

I'm afraid that isn't an option. It's designed to get it out of the way once the specs have been made. IMHO that design is a far preferable behavior than having to close/move it constantly :-)

"The sorting is also strange
I often need to revert to the unsorted list, after having sorted.
This is not possible after having done many changes."

No, there is no option to "unsort" the list because there is no retained record of the 'default' order. I don't know of any spreadsheet software that provides that kind of capability - it's usually found only in database applications where tables impose a default index based on a specified primary field.

"So what I do is to create a new list 1 2 3 4 ... i e I assign each item in the list a number etc.
Quite cumbersome"

Well, what you're actually doing is the prescribed method for creating a default index as referred to above. It may seem cumbersome but once that field has been included as a part of the list, it's done. That provides each record with a unique identifier which you can use to restore the order to your preferred default. It could be automated using a macro but IMHO it wouldn't be worthwhile unless the preferred default order is based on multiple sort keys. The only other options would be to use Undo or close the file without saving - either of which would cause the loss of any other changes made.

You might want to consider using a means of replicating the main list (such as Advanced Filter or formula links) to a different area. You could then sort/filter the duplicate list without disrupting the order of the source list.

Regards,
Bob J.

AI: Artificial Intelligence or Automated Idiocy???
Please mark Yes/No as to whether a Reply answers your question.

Regards,
Bob J.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Suppose I am filtering out A. And then I also want to filter out A AND B
To do that, I have to start anew. I have to Clear Filter and then start filter both A and B.
Not very convenient
Nor do I agree with you that an option to keep the filter open and visible all the time would be of nu use. I often make changes in the filter.
Those who want to get the filter out of the way could still do so by just clicking outside the filter.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Bob Jones AKA: CyberTaz

"Suppose I am filtering out A. And then I also want to filter out A AND B
To do that, I have to start anew. I have to Clear Filter and then start filter both A and B.
Not very convenient"

Perhaps I don't have a clear picture of what your data list contains or exactly how you're trying to filter it. The filter on any one column can be readily adjusted by altering the checkboxes for the unique items in that column. I'm not seeing any reason why the existing filter need be cleared.

"Nor do I agree with you that an option to keep the filter open and visible all the time would be of nu use. I often make changes in the filter."

You're most definitely entitled to your opinion & perhaps for your workflow such an option would be beneficial. I'm envisioning situations where filtering is done on as many as 8-10 fields (columns) & to have that many filter panes floating around would be an absolute nightmare :-) If you believe you have a viable alternative to suggest don't hesitate to use Help> Send Feedback to communicate the details to Microsoft.

"Those who want to get the filter out of the way could still do so by just clicking outside the filter."

This confuses me - it reflects the current behavior which is one of the complaints in your initial message: "...but it disappears each time you click outside it." As long as you don't select outside the filter pane it does remain visible.

Regards,
Bob J.

AI: Artificial Intelligence or Automated Idiocy???
Please mark Yes/No as to whether a Reply answers your question.

Regards,
Bob J.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

"Perhaps I don't have a clear picture of what your data list contains or exactly how you're trying to filter it."
I admit it was a confused question, I know how to do it now.

"I'm envisioning situations where filtering is done on as many as 8-10 fields (columns) & to have that many filter panes floating around would be an absolute nightmare :-)"
Yes, that would be so. But I am talking of one single filter at a time, not many at the same time.
I could have the filter open all the time, floating on the other windows. It takes only little space.
And when I click on an other column , the filter would change automatically; always one filter, not many !

"This confuses me - it reflects the current behavior which is one of the complaints in your initial message: "...but it disappears each time you click outside it."
I admit it is a confusing remark of mine. Often I would like to edit my posts, but this is not possible and so the result is a lot if gibberish....
What I mean is: I am suggesting a "floating filter" (always on top) which the user could switch off or on. A Floating Filter would not disappear when you click outside of it.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Jim Gordon MVP

What you are describing sounds a lot like the options that are built into the Table feature in Excel 2011. See this:
http://www.dummies.com/how-to/content/choosing-table-options-in-office-2011-for-mac.html

I am an unpaid volunteer and do not work for Microsoft. "Independent Advisors" work for contractors hired by Microsoft. "Microsoft Agents" work for Microsoft Support.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.