|
|
In Excel 2003, and earlier versions, an AutoFilter allows only two criteria for each column. If you want to filter for multiple criteria, you can use an Advanced Filter. List all the criteria on a worksheet, and use that list (and its heading cell) as the criteria range.
In Excel 2007 and Excel 2010, the AutoFilter feature has been improved, and you can select multiple criteria in each column.
Record an AutoFilter Macro
If you record a macro while selecting criteria in Excel 2007, it will look something like this:
The criteria are entered as an array, showing all three items that were selected in the drop down list.
Create Your Own Array
In the Contextures mail bag this week, someone asked if it's possible to create this type of AutoFilter criteria array from a list on the worksheet. And the answer is yes, you can!
For this
Continue reading Excel AutoFilter With Criteria in a Range
Over on the Contextures website, I've updated the AutoFilter Intro page, so it now covers the basics for Excel 2007 AutoFilters. However, many people are still using an older version of Excel, so I've moved the original material to the Excel 2003 AutoFilter Basics page.
AutoFilters are easier to use in Excel 2007 and Excel 2010, and the filter and sort options are automatically added in the top row, if you format your list as an Excel Table.
Filter for Dynamic Date Ranges
Among the new AutoFilter features that were introduced in Excel 2007 are dynamic date ranges. A Dynamic Date Range is one that changes automatically, as time moves forward. For example, you could select Yesterday, which will represent a different date, every day that you open the Excel file.
Unfortunately, the dynamic dates are only semi-dynamic, and they don't magically change when you open the workbook
Continue reading Excel 2007 AutoFilter Dynamic Dates
It was like riding a lazy snail through molasses in January -- but slower! Usually an Excel Advanced Filter is a speedy way to extract data from a table, but things weren't working right in a sample file that I got last week. And despite what my high school English teachers might think, you can't mix too many similes, when trying to describe excruciating slowness.
The sample file had code that ran an Advanced Filter in Excel 2007. The code ran quickly in Excel 2003, but screeched to a near halt in Excel 2007. What was the problem?
The Symptoms
When the code ran in Excel 2007, it looked like the extracted rows were being pasted in the second worksheet, one row at a time. Aha! Turn off the screen updating -- a simple solution. You'd think.
Even with the screen updating turned off, the code barely crawled along. It
Continue reading Excel 2007 Advanced Filter Painfully Slow
There was a question about Excel Advanced Filter criteria on the Tech Republic blog recently, and I posted an answer. A couple of weeks later, a Tech Republic mug and flag were delivered to my door, as a reward for answering. The real joy is in solving a problem, but it's fun to get a major award, even if it's not a fancy leg lamp that I can put in the front window. Keep reading, to see what problem the blogger was having with Excel Advanced Filters, and download a workbook with my suggested solution. Set Up an Advanced Filter To use an Excel Advanced Filter, you create a criteria range, with headings that match the ones used in the original table. Then, under one or more of the headings, you enter the filter criteria. For example, in the screenshot below, the criteria would extract all the records
Continue reading Automatically Change Excel Filter Heading
You can use the SUBTOTAL function to count visible items in a filtered list. In today's example, AlexJ shows how to count the unique visible items in a filtered list. So, if an item appears more than once in the filtered results, it would only be counted once. Thanks, AlexJ! Count the Unique Items In the screenshot below, the formula in column D shows a 1 for the first instance of each item, and a 0 for all subsequent instances. For example, in cell D7, there's a 1, counting item 91-AB145, and in the next two rows the count for the same item is zero. The formula in cell D5 is: =(COUNTIF($C$5:$C5,$C5)<2)*1 Apply a Filter However, when the list is filtered to show only the Central region items, the total in cell D2 still shows 16 unique items, and cell D11 shows a zero for item
Continue reading Count Unique Items in Excel Filtered List
There's an Excel holiday dinner planner on the Contextures website, and I'll need that in about a month, when Thanksgiving arrives. In the meantime though, there are lots of daily meals to plan. Now that the kids are back in school, and everyone is involved in fall activities, it can be tough to get the meals organized. To help you make the job easier, I created an Excel weekly meal planner. You can enter your favourite meals, their basic ingredients, and run a macro to create a shopping list for a week's meals. Select Meals for a Week There are a few sample meals in the Excel weekly meal planner, and you can add to the list, or replace the existing meals with your family favourites. Then, select a meal for each weekday from a data validation drop down list. Create a Shopping List There's a button on the
Continue reading Plan Weekly Meals in Excel
If you've added comments to an Excel worksheet, you have a couple of built-in options for printing the comments.
Show the comments on the worksheet, and print them as displayed. Print the list of comments at the end of the worksheet, on a separate printed page.
Printing the comments on the worksheet is okay if there are only a couple of comments, and you can arrange them so they don't cover the data.
For more than a couple of comments, the list at the end of the worksheet is a better choice. However, with the built-in list printing option, you just get the cell address and comment, printed in a long, single column.
Create Your Own List of Comments
Instead of using the built-in list of printed comments, you can use a macro to create your own list of comments on a separate worksheet, and print that list.
Continue reading Print a Customized List of Excel Comments
Last week, you saw a quick tip for filtering by selection in Excel 2007. That's helpful when you've found an item in a list, and want to filter for that item.
There's another new feature in the AutoFilter dropdown, in Excel 2010. When you click the drop down arrow in the AutoFilter heading cell, you'll see a new Search box, that wasn't in previous versions of Excel.
This is a great way to find an item in a really long list -- much quicker than scrolling down, and scanning all the list items.
Use the Search Box
For example, if you type "ri" in the Search box, only the cities with "ri" in their name will be left in the drop down list. In the screen shot below, "riv" is in the Search box, and only one city (Riverview) is showing -- the only city with that string of
Continue reading New Search Feature in Excel 2010 AutoFilter
Last month you saw a quick way to filter for the selected item in a pivot table, and today you'll see a similar technique for a worksheet list in Excel 2007. (For the Excel 2003 quick filter instructions see AutoFilter By Selection in Excel)
In the screen shot below, there's a list on the worksheet, with office supply orders. The list isn't filtered, and it's not formatted as an Excel Table.
(Note: This technique will also work if the list is already filtered, or if it's formatted as an Excel Table.)
Filter for the Selected Item
To quickly focus on the Paper orders:
Right-click on a "Paper" cell. In the popup menu, click Filter, and then click Filter by Selected Cell's Value.
An AutoFilter is automatically applied to the worksheet list, and column B is filtered for "Paper".
Clear the Column Filter
To see the full list
Continue reading Easy Filtering in Excel 2007
An Excel data validation drop down list only shows 8 items at a time, and with a long list of items, it might take you a while to scroll through the list.
When cell B3 in this example is blank, a long list of names shows in cell C3. Instead of showing all the names, you can type a letter (or letters) in cell B3. This runs a macro, to show a short list of names in the drop down, based on the starting letter(s).
Filter the Source List
To create the short list of names, the macro in this example runs an advanced filter, using the typed letter(s) as the filter criteria. The matching names are filtered into a dynamic named range, which is the source for the data validation drop down. The range is named NameList, and uses the following formula:
=OFFSET(ExtractNames,1,0,COUNTA('Sales Data'!$J:$J)-1,1)
Filter the
Continue reading Shorten Data Validation List With Excel Filter Macro
|
|
Recent Comments