How do you filter formulas in Excel 2010?

You can filter formulas in Excel 2010 using the Advanced Filter feature, which allows you to define specific criteria for which formulas (or rather, the results of those formulas) should be displayed. Here's how:

Steps to Filter Formulas Using Advanced Filter:

1. Prepare your data: Ensure your data has a header row. This is crucial for Excel to understand the columns you're working with.

2. Create a Criteria Range: This area is where you'll specify your filtering criteria.

   • Choose an empty area on your worksheet.

   • In the first row, enter the column header you want to filter (e.g., "Total" if your formulas are in the 'Total' column). Make sure this header exactly matches the header in your data table.

   • Beneath the header, enter the criteria you want to use to filter the results of the formulas. For example:

     Total
     >100

     Other possible criteria include:

     • =100 (Equal to 100)
     • <100 (Less than 100)
     • >=100 (Greater than or equal to 100)
     • <=100 (Less than or equal to 100)
     • <>100 (Not equal to 100)

3. Access the Advanced Filter:

   • Go to the Data tab on the Excel ribbon.
   • In the Sort & Filter group, click Advanced.

4. Configure the Advanced Filter Dialog Box:

   • Action: Choose whether to filter the list in place (hide rows that don't meet the criteria) or to copy the filtered results to another location.
   • List range: This is your entire data range, including the header row. Excel usually tries to guess this correctly, but double-check it.
   • Criteria range: This is the range you created in step 2. Include the header cell and the criteria cell(s). For example, if your header "Total" is in cell A10 and your criteria ">100" is in A11, your criteria range would be $A$10:$A$11. Crucially, use absolute references ($A$10, $A$11) to prevent the references from changing if you copy the filtered data.
   • Copy to: If you selected "Copy to another location," specify the cell where you want the filtered data to begin.
   • Unique records only: Check this box if you want to filter out duplicate rows. This is generally not relevant when filtering formulas.

5. Click OK: Excel will apply the filter based on your criteria.

Example:

Let's say you have the following data in cells A1:B6:

Name	Total
Alice	=10*5
Bob	=20+10
Carol	=5*5
David	=100/2
Eve	=50*2

And you want to filter the data to show only rows where the "Total" is greater than 30.

1. Criteria Range: In cells D1:D2, you might enter:

   Total
   >30

2. Advanced Filter Settings:

   • List range: $A$1:$B$6
   • Criteria range: $D$1:$D$2

After clicking "OK", Excel will filter the data, showing only Alice, David and Eve's rows (assuming you are filtering in place). Bob's row will not be filtered because his Total = 30, and the criteria is greater than 30.

Important Considerations:

• The criteria in your criteria range directly affects the filter results.
• Ensure your headers in the criteria range match the headers in your data range exactly.
• Advanced Filter filters rows based on the values resulting from the formulas, not the formulas themselves. You cannot, for example, filter for rows where the "Total" formula uses a specific function. You can only filter based on whether the result of that function matches your specified condition.
• To remove the filter, go to the Data tab and click Clear in the Sort & Filter group.