Excel 2007 offers amazing new AutoFilter functionality. Instead of choosing a single customer from the dropdown, you can filter a dataset to show 3 or more customers, or use new virtual filters to show all records from next month or last year. Episode 428 shows you how.
This blog is the video netcast companion to the upcoming book, Excel 2007 Miracles Made Easy. Download a new two minute video every Tuesday and Thursday to learn one of the tips from the book!
This blog is the video netcast companion to the upcoming book, Excel 2007 Miracles Made Easy. Download a new two minute video every Tuesday and Thursday to learn one of the tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast, I'm Bill Jelen.
Just seven days to go until January 30th and we all have a chance to buy excel 2007, which hits store shelves at that point.
Now, last couple of tricks here about tables and also the AutoFilter dropdowns that we now have at the top of the table.
First thing, if you have a large table and you scroll down, I've not used the Freeze Panes on this particular table, but as I scroll down and stay within the table, you'll see that my headings A, B, C, D, E have actually changed to be the headings in the table.
This is kind of a new functionality, it's only there when you have table headings and it only remains as long as your cursor stays in the table.
If you go a few cells outside of the table, it goes away.
So you might still want to use Freeze Panes, but it's an interesting effect: when you have a table to find and scroll off the worksheet, Excel will show you the various tables and you'll see that that was kind of some ghosting there, obviously doesn't work very well here in Excel 2007.
Now, interesting things in the AutoFilter dropdowns, usually in the AutoFilter we could say: I want to see just the customers that match one particular value.
All sorts of new things we can do with customers: the first thing is, we can unchoose (Select All) and then select two different customers.
That'll show us a combination, that's something that was fairly hard to do before, you could always use the custom, but now we can choose three or four, or five customers, which was not something you can do before.
So that's one option.
The other option that we have, are all sorts of new (Clear the Filters), are all sorts of new virtual filters.
So here we have a column that's predominantly dates, I can say I just want to see things from tomorrow or this week, or last month, next month, even down here there are All Dates in the Period.
I can see just the June records and we'll see just the June records.
All sorts of great Date Filters that we have, I’m going to clear that one.
Over here in the numeric fields, I can ask to see just the cells that are greater than a certain sales amount, or just the sales that are Above Average, Below Average or anything like that.
So the AutoFilter functionality has become dramatically better in Excel 2007.
It's automatically turned on when you have a table, or you can turn it on a regular data set using the Home ribbon, under Sort And Filter you can choose Filter.
No matter how you turn it on, you have the functionality that's improved in Excel 2007, really great thing.
Last thing before we end with tables.
If you define table and that you really want to turn that functionality off, if you go to the Table Tools, Design ribbon, there is a setting for Convert to Range.
Choose that, it won't get rid of the green bar formatting for you, but it will convert all of your formulas back to regular formulas instead of table formulas, and it'll be just like a regular spreadsheet that you know and love from Excel 97 through Excel 2003.
Hey, thanks for stopping by, 7 more days until Excel 2007 hits the streets, we'll see you tomorrow for another podcast from MrExcel.
Just seven days to go until January 30th and we all have a chance to buy excel 2007, which hits store shelves at that point.
Now, last couple of tricks here about tables and also the AutoFilter dropdowns that we now have at the top of the table.
First thing, if you have a large table and you scroll down, I've not used the Freeze Panes on this particular table, but as I scroll down and stay within the table, you'll see that my headings A, B, C, D, E have actually changed to be the headings in the table.
This is kind of a new functionality, it's only there when you have table headings and it only remains as long as your cursor stays in the table.
If you go a few cells outside of the table, it goes away.
So you might still want to use Freeze Panes, but it's an interesting effect: when you have a table to find and scroll off the worksheet, Excel will show you the various tables and you'll see that that was kind of some ghosting there, obviously doesn't work very well here in Excel 2007.
Now, interesting things in the AutoFilter dropdowns, usually in the AutoFilter we could say: I want to see just the customers that match one particular value.
All sorts of new things we can do with customers: the first thing is, we can unchoose (Select All) and then select two different customers.
That'll show us a combination, that's something that was fairly hard to do before, you could always use the custom, but now we can choose three or four, or five customers, which was not something you can do before.
So that's one option.
The other option that we have, are all sorts of new (Clear the Filters), are all sorts of new virtual filters.
So here we have a column that's predominantly dates, I can say I just want to see things from tomorrow or this week, or last month, next month, even down here there are All Dates in the Period.
I can see just the June records and we'll see just the June records.
All sorts of great Date Filters that we have, I’m going to clear that one.
Over here in the numeric fields, I can ask to see just the cells that are greater than a certain sales amount, or just the sales that are Above Average, Below Average or anything like that.
So the AutoFilter functionality has become dramatically better in Excel 2007.
It's automatically turned on when you have a table, or you can turn it on a regular data set using the Home ribbon, under Sort And Filter you can choose Filter.
No matter how you turn it on, you have the functionality that's improved in Excel 2007, really great thing.
Last thing before we end with tables.
If you define table and that you really want to turn that functionality off, if you go to the Table Tools, Design ribbon, there is a setting for Convert to Range.
Choose that, it won't get rid of the green bar formatting for you, but it will convert all of your formulas back to regular formulas instead of table formulas, and it'll be just like a regular spreadsheet that you know and love from Excel 97 through Excel 2003.
Hey, thanks for stopping by, 7 more days until Excel 2007 hits the streets, we'll see you tomorrow for another podcast from MrExcel.
