Tables Part 4: AutoFilter improvements: much more than just multi-select …

re: AutoFilter improvements: much more than just multi-select …

Harlan Grove — Wed, 02 Nov 2005 02:39:13 GMT

This should be useful.

There's one small part of this that strikes me as counterintuitive: using a square in the filter items to mean partial selection when a check mark means selected and an empty box means not selected. I understand interfaces change, but a light grey checkbox background with a dark grey checkmark would be more in keeping with the current interface. The new square fills in more of the checkbox than the checkmark, so it seems odd that it means less of a selection.

re: AutoFilter improvements: much more than just multi-select …

Graham — Wed, 02 Nov 2005 02:45:38 GMT

David,

Does the act of a user selecting a filter trigger any events in excel 12?

re: AutoFilter improvements: much more than just multi-select …

Jean Martineau — Wed, 02 Nov 2005 05:52:39 GMT

This is great stuff, especially the possibility
|to filter more than one dataset on a sheet,..
I was wondering how will it work if 2 different datasets share the same row numbers? It would not make sense that they mutually filtered each other by hiding rows. So how will this example behave?

I find it practical to use the subtotal function on filtered rows. For example, in conjunction with counta function, I can calculate the % of visible records. Will this still be possible on filtered table?

Will custom views work with table filter?

When you have different users that can have a different look at the same data, custom views are very useful. Meanwhile, if there are many different users you can end up having many different custom views that are more user’s specific. Would it be possible to assign a “category” at the creation of a custom view. You could then flag (filter) witch categories of custom views you want to see in the drop down list.

Thank's

Jean

re: Tables Part 4: AutoFilter improvements: much more than just multi-select …

SteveA — Wed, 02 Nov 2005 10:21:37 GMT

Will there be a quick method of detecting (and deleting) duplicates. Often I have merged worksheets from different sources, and then add a countif in a parallel column to count the number of occurences, then sort on the count, so that duplicates appear with a count of 2 or more are at the top of the list. Then can manually delete the duplicates. Alternatively a simple macro does the job? A "find duplicates" (with optional delete) duplicates in one or more columns

re: Tables Part 4: AutoFilter improvements: much more than just multi-select …

Nigel Harper — Wed, 02 Nov 2005 11:25:06 GMT

David,

Possibly some of the most useful features yet for the average user, excellent stuff! I would also like to echo Jean's query about Table conflicts in row filtering.

Also, in the table reference keywords/tokens is there a subset of ther #Data reference like #Visible or #Hidden? This would really beef up the scope of some of the functions and resulting formulas?

Presently the AutoFilter cannot cope with recognizing a CustomNumberFormat when building a list in the custom filter dialog. I mean by that that the number 12345678 in a cell with the CustomNumberFormat "#,,.000" appears in the filterlist dropdown as 12.346 . If I then ask it to filter numbers greater than this amount then it appears to interpret this value as text and not a number thus only filtering out text values in the list. Has this been fixed?

re: Tables Part 4: AutoFilter improvements: much more than just multi-select …

Jean-Marc Decouleur — Wed, 02 Nov 2005 15:14:38 GMT

David,

"filter dropdown groups dates by day/month/year" - no weeks ? Being able to filter on the two first weeks of a month, by example, would be useful.

Also, please, please, please add the ability to filter on styles !

Jean-Marc

re: Tables Part 4: AutoFilter improvements: much more than just multi-select …

Jim Rech — Wed, 02 Nov 2005 15:37:17 GMT

I sure Advanced Filter did not disappear, David, but I didn't see an access point in any of the screen shots, unless it's under the 'Custom' selection. Has work been done on Advanced Filter?

re: Tables Part 4: AutoFilter improvements: much more than just multi-select …

Jim Rech — Wed, 02 Nov 2005 15:57:04 GMT

I like what you did with date fields - rather than list every date in the drop down you display 'bands' by month/year. It has always bothered me that number fields like 'Sales' list each individual amount when it's clear you'd never filter by, say, $1,234.56.

Of course it's only 'clear' when you know what the data in that field represents. If a number field represents a month then you would want to be able to filter by it. So maybe there is no way to know when it makes sense to list each unique item and when not. But if there are 1000 rows and 997 are unique that's a clue<g>. Maybe some ratio of uniques to record count?

So when this ratio is low, so that you're confident you have a 'Sales' like field, what do you do instead of listing each item? Some kind of banding like with dates perhaps. There is nothing quite as obvious as 'month' to band by but ranges could be constructed I suppose. Or maybe even nothing (that's easy!), knowing that Custom is available if you really did want to filter by a specific amount.

re: Tables Part 4: AutoFilter improvements: much more than just multi-select …

Roy — Wed, 02 Nov 2005 16:25:30 GMT

Wow, Dave. This is great stuff! I love having all those date, text and number filters.

re: Tables Part 4: AutoFilter improvements: much more than just multi-select …

headtoadie — Thu, 03 Nov 2005 00:18:31 GMT

These additions will be very useful.

I have one suggestion though, an inverse selction option...When working with this select-in/select-out checkbox structure in PivotTables, I often find that I forget to uncheck the select all box before I start scrolling down through the list. So when I finally scroll down to the one thing that I really want to zero in on I realize I forgot to un-check everything and thus have to start over. It would be very useful to have a right-click option that allowed me to "Select only" at each checkbox.

This would apply to Filters and PivotTables

re: Tables Part 4: AutoFilter improvements: much more than just multi-select …

David Gainer — Thu, 03 Nov 2005 01:59:35 GMT

Hi folks – thanks for the comments and suggestions.

Graham - We haven’t added any events for Excel 12, so you’ll only get the events that you did in Excel 2003 when you filter (only a calculation event if cells depend upon the filtered out rows).

Jean – If two tables share the same rows, things will work as it does in Excel 2003 - the last filter applied to set of rows wins. So if I have two tables – Table1 and Table2 – that share rows and I apply a filter to Table1, when I apply a filter to Table2, the result is the same as if I had not filtered Table1 to begin with. Yes, calculating percent visible will still work, as will custom views. We have not made any changes to custom views, but thanks for the suggestion.

SteveA – You bet. Remove duplicates post coming later this week.

Nigel – We did not add a Visible or Hidden keyword, but it is an interesting idea. We have not made any changes to the Custom Number Format behaviour you describe.

Jean-Marc – Thanks for the feedback. In the next few days I will expand on table styles – I would love to hear your thoughts about filtering after that post.

Jim – No, Advanced Filter did not disappear (nor has it changed). The access point is on the Data ribbon – at some point, probably not for a while till things are more stable in our UI, I will have a post on each ribbon and where the commands are. Also, we considered having grouping strategies for other types of values as well (which would address the problem of showing every unique value in a column that is basically all unique values), but we decided to only include date grouping for this version. Thanks for the suggestion.

re: Tables Part 4: AutoFilter improvements: much more than just multi-select …

Harlan Grove — Thu, 03 Nov 2005 02:45:33 GMT

Something I just thought of when there are multiple tables with filters in the same worksheet. If Table1 begins in row 1 and Table2 begins in row 5, a filter is applied to Table1 which hides row 5. Presumably this would make it impossible to filter Table2 until row 5 becomes visible again. Or would XL12 be smart enough to unhide Table2's header row when the user moves the ActiveCell into Table2? Yes, that is what I would consider the most sensible behavior when there are multiple tables in the same worksheet.

re: Tables Part 4: AutoFilter improvements: much more than just multi-select …

David Gainer — Thu, 03 Nov 2005 03:26:28 GMT

Hi Harlan - the header row is indeed hidden. I suspect that most users will have one table per sheet, but that is currently just my thinking - beta feedback will help here.

re: Tables Part 4: AutoFilter improvements: much more than just multi-select …

Jean Martineau — Thu, 03 Nov 2005 05:41:55 GMT

I like the idea that active filters should be related to the ActiveCell. If a
| user moves the ActiveCell into Table2
the filters that were last applied to Table2 should automatically reappear. This would remove the other filters temporally. Meanwhile, if we move the ActiveCell where there is no filters associated, we should not lose the last applied filters.

Jean

re: Tables Part 4: AutoFilter improvements: much more than just multi-select …

Stephane R — Thu, 03 Nov 2005 09:02:25 GMT

Double-clicking a checked item should inverse the state of all check boxes, making it much easier to filter everything but xxx

re: Tables Part 4: AutoFilter improvements: much more than just multi-select …

Jim Rech — Thu, 03 Nov 2005 15:15:50 GMT

>>Advanced Filter .. (nor has it changed).

I can understand that since we need it less with remove duplicates now in the regular Filter.

My only wish for the AF dialog is that it retain its settings between uses (as most XL dialogs do). I expressed that in the XL planning NG. Oh well<g>.

excel advanced filter one condition in two or more columns

excel advanced filter one condition in two or more columns — Wed, 04 Jun 2008 21:02:57 GMT

PingBack from http://paola.bestdigesthtml.info/exceladvancedfilteroneconditionintwoormorecolumns.html

drop down list depend upon another cell in excel

drop down list depend upon another cell in excel — Fri, 20 Jun 2008 18:47:31 GMT

PingBack from http://ashleigh.holdingunits.com/dropdownlistdependuponanothercellinexcel.html

show more than 1000 records in excel auto filter

show more than 1000 records in excel auto filter — Wed, 25 Jun 2008 15:08:44 GMT

PingBack from http://alonso.stateranch.com/showmorethan1000recordsinexcelautofilter.html

excel autofilter 1000 limit

excel autofilter 1000 limit — Wed, 09 Jul 2008 18:22:20 GMT

PingBack from http://callie.mediafilmonline.info/excelautofilter1000limit.html

limiti di filtro | hilpers

limiti di filtro | hilpers — Wed, 21 Jan 2009 20:35:38 GMT

PingBack from http://www.hilpers.it/1823706-limiti-di-filtro

Microsoft Excel Tables Part 4 AutoFilter improvements much more | unemployment office

Tue, 16 Jun 2009 11:04:51 GMT

PingBack from http://unemploymentofficeresource.info/story.php?id=15617

Microsoft Excel Tables Part 4 AutoFilter improvements much more | fix my credit

Microsoft Excel Tables Part 4 AutoFilter improvements much more | fix my credit — Wed, 17 Jun 2009 05:29:51 GMT

PingBack from http://fixmycrediteasily.info/story.php?id=17582

Microsoft Excel Tables Part 4 AutoFilter improvements much more | patio set

Microsoft Excel Tables Part 4 AutoFilter improvements much more | patio set — Thu, 18 Jun 2009 05:03:30 GMT

PingBack from http://patiosetsite.info/story.php?id=988

Microsoft Excel Tables Part 4 AutoFilter improvements much more | pool toys

Microsoft Excel Tables Part 4 AutoFilter improvements much more | pool toys — Thu, 18 Jun 2009 12:15:33 GMT

PingBack from http://pooltoysite.info/story.php?id=3480

Microsoft Excel Tables Part 4 AutoFilter improvements much more | adirondack chairs

Fri, 19 Jun 2009 12:30:09 GMT

PingBack from http://adirondackchairshub.info/story.php?id=3781

Microsoft Excel Tables Part 4 AutoFilter improvements much more | debt solutions

Microsoft Excel Tables Part 4 AutoFilter improvements much more | debt solutions — Fri, 19 Jun 2009 19:39:39 GMT

PingBack from http://debtsolutionsnow.info/story.php?id=10614