Discussion on:
View:
Show:
you might want to add the "clear all filters" to the QAT as well. It quickly clears all filters used in a spreadsheet which can prevent other mistakes.
From the Excel Options - Quick Access Toolbar step, Select Choose commands from "Data Tab". Select the filter icon that has the red "x" beside it, and Add this icon to the Quick Access Toolbar. This is the "clear all filters" button that Stuart Forbes is referring to - and it works as expected.
once again 2003 does it much quicker and simpler
One word of caution on using the Show All/Clear All Filters option: if you have a very wide and long spreadsheet, it can take ages as it resets every column even if not filtered. In such cases it's quicker to toggle the autofilter off and on again.
One word of caution on using the Show All/Clear All Filters option: if you have a very wide and long spreadsheet, it can take ages as it resets every column even if not filtered. In such cases it's quicker to toggle the autofilter off and on again.
I tried doing this in 2003, but it doesn't behave the same way. Can you tell me where it is in the menu?
I can do a normal AutoFilter, but that doesn't immediately filter the list based on the value selected like 2010 does.
Thanks
I can do a normal AutoFilter, but that doesn't immediately filter the list based on the value selected like 2010 does.
Thanks
I was asking how "you" do it in 2003? Not 2010.
I tried the Auto-filter option in Excel 2003, and it's doesn't do what is described in the article above. You're saying it's harder in Excel 2010 than Excel 2003, but it appears to be impossible in Excel 2003 without some VBA code.
Or am I missing some option in Excel 2003?
I tried the Auto-filter option in Excel 2003, and it's doesn't do what is described in the article above. You're saying it's harder in Excel 2010 than Excel 2003, but it appears to be impossible in Excel 2003 without some VBA code.
Or am I missing some option in Excel 2003?
so maybe I shouldn't comment on 2010, but it seems that there's always more instructions needed for 2010 to achieve anything (so only a small step back)
What I did (many years ago now) is exactly what Susan says in the article (I might expand step 2 to read "From the Categories list on the Commands tab, select Data")
It's very straightforward, just note that this "AutoFilter" is different to the one found on the Data, Filter submenu from the main menu (despite having the same name and icon)
This one (the one found via the Customise dialogue) sets up a filter based on the contents of the current cell.
The other (found via the Standard toolbar) just toggles the filtering ability for the current range - for which you then have to select an item from a dropdown to actually filter your data
So, having dragged the correct icon from the customise dialogue to your toolbar, all you need to do is select a SINGLE CELL within your range (and not in the 1st row) and click the button. An autofilter will then be set up on the current cell's contiguous area (if not already present) and the area filtered, based on the current cell's contents
hope that's clear now and I haven't missed your point entirely; please let us know
What I did (many years ago now) is exactly what Susan says in the article (I might expand step 2 to read "From the Categories list on the Commands tab, select Data")
It's very straightforward, just note that this "AutoFilter" is different to the one found on the Data, Filter submenu from the main menu (despite having the same name and icon)
This one (the one found via the Customise dialogue) sets up a filter based on the contents of the current cell.
The other (found via the Standard toolbar) just toggles the filtering ability for the current range - for which you then have to select an item from a dropdown to actually filter your data
So, having dragged the correct icon from the customise dialogue to your toolbar, all you need to do is select a SINGLE CELL within your range (and not in the 1st row) and click the button. An autofilter will then be set up on the current cell's contiguous area (if not already present) and the area filtered, based on the current cell's contents
hope that's clear now and I haven't missed your point entirely; please let us know
The Table feature limits a number of built-in features, so I'm not surprised it doesn't work with a Table. I should've checked it myself, but just didn't think to. Thanks for letting us know.
Thanks for sharing this Susan - I've used Excel many years and had always been frustrated with the number of mouse clicks required to use AutoFilter. This takes away all of the pain. I love it!
If you edit and display the description to include an accelerator (ie use a & before the letter you want to designate - I use c for "Cell Contents") then you don't even have to leave the keyboard; just press alt-c (or whatever you've chosen) and the current area is filtered by the contents of the active cell
Even if there's no autofilter in place, Excel will set one up and add the arrows at the head of each column in the range
Even if there's no autofilter in place, Excel will set one up and add the arrows at the head of each column in the range
this won't work for the header row in a range of data
- but it will set up the autofilter for you, just not apply it
- but it will set up the autofilter for you, just not apply it
I'm glad you found this useful. I'm not sure why Excel hides it -- it should be much easier to use.
- Keyboard Shortcuts:
- Prev
- Next
- Toggle
