Problem: I need to select all the oil companies from the filter.

Strategy: Use the search box in the Filter dropdown.

Open the filter dropdown and type oil in the search box.

Excel shows a list of the companies with Oil in the name.

By default, (Select All Search Results) is chosen.

Figure 614 Results of a filter search.

If you do a second search, you can either choose to replace the current filter with the new results, or to add the new search results to the existing filter.

Figure 615 Add a second search.

The result is all customers with either “oil” or “Canada”.

3

Figure 616 Oil or Canada appears as the result.

FILTER BY SELECTION

Problem: Microsoft Access offers an icon for Filter by Selection. Why isn’t this in Excel?

Strategy: Filter by Selection IS in Excel. It is hidden. It is mislabeled. It has been in Excel (hidden and mislabeled) for fifteen years.

To add Filter by Selection to the ribbon, follow these steps:

1. Right-click the Quick Access Toolbar and choose Customize Quick Access Toolbar.

2. Initially, you only see popular commands. Open the left dropdown and change from Popular Commands to All Commands.

3. Scroll down to find AutoFilter. Click on AutoFilter. Click the Add>> button in the center to add this icon to the Quick Access Toolbar. Click OK. You now have Filter by Selection on the QAT.

Figure 617 AutoFilter is really Filter by Selection.

You can now filter very quickly. Say that you want all of the Wal-Mart, East, ABC records from a data set.

It will be six clicks.

1.Select a cell that says Wal-Mart

2.Click Filter by Selection

3.Select a cell that says East.

4.Click Filter by Selection.

5.Select a cell that says ABC.

6.Click Filter by Selection.

Figure 618 Choose the value that you want, click AutoFilter.

The result is filters on three columns, without ever clicking the Filter button and without ever opening a

Filter dropdown.

Figure 619 Filter by Selection will change your life.

Additional Details: How can it be that Microsoft never tells anyone about this? Why did they continue to mislabel the icon as AutoFilter when Excel 2007 changed the term to Filter (and, in fact, they added a Filter icon in Excel 2007). This is one of the great mysteries. I’ve asked the Excel project managers about this. Someone added the code to do Filter by Selection but it was sort of an afterthought, not something they felt worthy of publicizing. If you believe some accounts, they collectively forgot the code was there. It was only during a code review for a lawsuit defense that someone happened upon it again. Filter by Selec- tion is the greatest feature that no one knows about.

Problem: I need to total only the visible cells in a filtered data set.

Strategy: You can use the AutoSum icon after applying a filter. Normally, the AutoSum icon inserts a SUM function. When you apply a filter and then use AutoSum, Excel will insert a SUBTOTAL function instead. This function will ignore rows hidden by the Filter command. Follow these steps:

1. Choose a cell in your data set. Select Data, Filter.

2. Apply a filter to at least one column. Open the Customer dropdown and choose one customer.

3. Select the first visible cells beneath your numeric columns. Below, the last visible row is 539, but the next blank cell is in row 568.

4. Click the AutoSum icon and press Enter. Excel inserts a SUBTOTAL function that uses the correct syntax to skip rows hidden by the filter.

FILTER ONLY SOME COLUMNS

Problem: I don’t want to offer filter dropdowns for Quantity and Revenue. It confuses the people who use my worksheet. I only want the filters to be available on columns A:D.

Strategy: You normally apply a filter by selection the entire data set, or one cell in the data set, or the cell to the right of the last heading. Any of these methods will apply the filter dropdowns to all cells.

Instead, select cells A1:D1 before selecting the Filter icon. This will add the dropdowns to only those col- umns. Of course, if you filter by column A, it will only show you the filtered rows for all of the columns.

Figure 621 Filter only the text columns.

Gotcha: This trick only works on a contiguous section of the data set. If you wanted dropdowns only on Column A, B, and D, you would have to use VBA to hide the dropdown on column C. To hide the dropdown for column C, follow these steps:

1. Alt+F11 for VBA.

2. Ctrl+G for immediate window.

3. Type range(“C1”).AutoFilter Field:=3, VisibleDropDown:=False

To adapt for another column, change both the “C1” and the 3 for another column. Column J would have a Field:=10.

Figure 622 One line of VBA can hide a filter dropdown.

FIND THE UNIQUE VALUES IN A COLUMN

Problem: I have a large database. Before I can produce a report for each customer, I need to identify the complete list of unique customers.

Strategy: Consider using the newer method: "Remove Duplicates" on page 277. Below, learn how you could use the older Advanced Filter to solve the problem. Follow these steps:

1. Copy the Customer heading from D1 to a blank cell.

Figure 623 Copy the customer heading to an output area.

2. Select a single cell in your data range and then select Data, Advanced. The Advanced Filter dialog will appear, offering many confusing options.

3. Choose the Unique Records Only check box. Change the Action section to Copy to Another Location. Selecting this action enables the Copy To range. Place the cell pointer in the Copy To text box and touch the out-of-the-way copy of the Customer heading.

Figure 624 Copy unique records to the output range.

4. Click OK. Excel will find the unique customer numbers and copy them to the range you specified.

Figure 625 Excel produces a list of unique customers.

Gotcha: The list is not sorted. It appears in the same order that the customers appeared in the original data set.