Excel AutoFilter Makes Reports More Useful.

June 27, 2008

Two of the Excel filters that transform an ordinary excel file into valuable tool are AutoFilter and Custom AutoFilters. These are features Microsoft added with Excel 97, but few use it. Using these filters, we can turn an ordinary Excel spreadsheet into something more useful and versatile. By the way, if you are Mac user, or you are using Linux, there are similar features available for you in their "table processors".

Let's start with the example. You are looking for a stock to buy. You feel fairly knowledgeable in the, let's say, Biotechnology industry. You want to find a Biotechnology company with the good financial numbers, low credit and you would prefer that technical indicators will recommend you to buy it today. Isn't it what you are really interesting in? Isn't it a description of buying a wonderful business in the right time?!

Stock2own's Market Analysis Reports have all data you may need in order to find stock you will like and can answer to all your questions in a matter of seconds. All you need to do is open report, turn auto filter on and set... Let's slow down a bit.

What is AutoFilter?

AutoFilter is an easy way to turn the values in Excel column into specific filters based on the cell contents. As example, by adding AutoFilter to the report, we could filter the "Industry" column to only display items for Biotechnology. All the other industries remain on the Excel list, but don't display.

To turn on AutoFilter,

• Click any cell within your range.
• From the Data menu, select Filter.
• From the submenu, select AutoFilter.

Once you've enabled this feature, your columns display with a drop-down arrow to the right. If you click the arrow control, you'll see all the values for that particular column. To turn off the feature, you deselect AutoFilter from the same submenu.

In the example above, I can see all the entries that show in the Industry column. What's appealing about this filter feature is that the displayed list is dynamic. If I shift to the Country column, you'll see completely different set of values.

What is equally useful is these filters also adjust based on other AutoFilters. For example, if you filter the "Industry" column for "Biotechnology" as shown below, your AutoFilter list for "Exchange" only displays "NASDAQ" and "NYSE". It means that Biotechnology companies represented in the report are traded only on those two exchanges in the US.

Excel Custom AutoFilters

As handy as these filters are, there are times when you need to filter based on specific criteria within a cell. For example, you may want to see all symbols where Cumulative Alert is "Buy" or "Get Ready to buy".

To set a simple custom AutoFilter,

• Enable AutoFilter for your spreadsheet using the steps in the section above.
• Click the down control arrow in the column you wish to filter.
• From the drop down list select (Custom...) The Custom AutoFilter dialog opens. Your column name should show above the first list box.
• In the first list box, click the drop down arrow and select your criteria.
• In the list box to the right, either type a value or select one from the list.
• Click OK.

In the example below, we have elected to filter for rows containing "Buy" or "Get Ready to buy" values in the Cumulative Alert column.

Excel is versatile in the filter settings. You're not limited to just items containing a specific value. You could also use:

• equals
• does not equal
• is greater than
• is greater than or equal to
• is less than
• is less than or equal to
• begins with
• does not begin with
• ends with
• does not end with
• contains
• does not contain

Essentially what Excel's AutoFilters allow you to do is to slice and dice your information.

Example

Let's finish example we started with: if I want to find a Biotechnology company with the good financial numbers, low credit and technical indicators recommendation "buy it today"; I would have to set the following AutoFilter:

• Industry equals to "Biotechnology"
• Equity equals to "good"
• EPS equals to "good"
• Sales equals to "good"
• Free Cash Flow equals to "good"
• Cash equals to "good"
• ROIC equals to "good"
• Debt Ratio - custom filter: is less than 3 AND is greater than or equal to 0
• Cumulative Alert - custom filter: equals to "Buy" OR equals to Get Ready to Buy

Please, play with the filters and set your own filter parameters to better describe what kind of business you are looking for!