How to Filter Cells with Formulas in Excel – 2 Methods

This is an overview.

Method 1 – Using the FILTER and the ISFORMULA Functions to Extract Cells Containing Formulas

In the datset below, Column D contains the output, some of which were obtained by using formulas (displayed beside).

To find the presence of formulas in Column D:

• Create a header row at the bottom of the table (13) with the headers.
• In B14, enter the following formula:

• Press Enter and an array with the filtered data will be displayed.

Formula Breakdown

The ISFORMULA function searches for all formulas used in Column D and returns TRUE if it finds any formula. Otherwise, FALSE.

The use of Double-Unary(--) before the ISFORMULA function converts TRUE and FALSE to numeric values- 1 and 0.

“--ISFORMULA(D5:D11)=1” defines the criteria for filtering tables.

In the third argument of the FILTER function, an output message is set if no data meets the given condition.

Method 2 – Using the VBA Editor to Filter Cells Containing Formulas in Excel

Step 1: Preparing Macros to Filter Cells with Formulas in Excel

• To create and customize a user-defined formula, press ALT+F11 to open the VBA window.
• In Insert, select Module.
• Enter the code in the module window:

Option Explicit

Function HasFormula(Cell)

HasFormula = Cell.HasFormula

End Function

• Press F5 and name the macro in the Macros dialog box. Here, ContainFormulas.
• Click Create.

Step 2: Use a Customized Function to Filter Cells with Formulas

• Go back to your Excel spreadsheet and in E5, enter:

• Press Enter and use the Fill Handle to autofill Column E.

The formula will return boolean values- TRUE and FALSE.

Step 3: Filtering Cells That Contain No Formulas

• Place the cursor on Contains Formula?.
• Right-click.
• Select Filter by Selected Cell’s Value in Filter.

• After assigning the Filter buttons to the headers, open the Filter option in Contains Formula?.
• In the Search box, check TRUE.
• Click OK.

This is the output.

Download Practice Workbook

Download the Excel workbook.

<< Go Back to Data | Filter in Excel | Learn Excel