How to Filter Cells with Formulas in Excel (2 Ways)

In Microsoft Excel, there are not too many ways to filter cells that contain formulas. The 2013 version of Excel, has initiated a logical function: ISFORMULA, which is used to find the existence of the formula in a cell. By incorporating this function with the other ones, there are a few techniques to filter cells containing formulas. You’ll learn about the methods and steps with appropriate illustrations in this article.

How  to Filter Cells with Formulas in Excel: Two Suitable Methods

1. Using FILTER and ISFORMULA Functions to Extract Cells Containing Formulas

If you’re using Excel 2013 or any other latest version then you can combine FILTER and ISFORMULA functions together to filter cells containing formulas. In the picture below, Column D contains the outputs of some numbers with percentages. A few outputs in Column D have been obtained by using formulas that are displayed aside.

But assuming, we are not aware of the presence of the formulas in Column D and so we have to find out which cells in that column contain the formulas.

Under the main table, let’s make a header row (13) with the headers from the previous table.

In the output cell B14, type the following formula:

Press Enter and you’ll be shown a resultant array with the filtered data right away.

🔎 How Does This Formula Work?

➤ Here ISFORMULA function searches for all formulas used in Column D and returns TRUE if it finds any formula, it’ll return FALSE.

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

➤ In the include argument of the FILTER function, “--ISFORMULA(D5:D11)=1” defines the criteria for filtering tables.

➤ In the third argument of the FILTER function, an output message has been set if no data are found with the given condition.

2. Using VBA Editor to Filter Cells Containing Formulas in Excel

Now if you’re using any of the older versions of Microsoft Excel (Before Excel 2013) then you have to create a user-defined function to filter cells with formulas. After using the customized function, we have to create a helper column where we’ll apply the Filter command to get the final output.

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

➤ To create and customize a user-defined formula, we have to press ALT+F11 first to open the VBA window.

➤ From the Insert tab, select Module. A new module window will appear where we have to type the following codes:

Option Explicit

Function HasFormula(Cell)

HasFormula = Cell.HasFormula

End Function

➤ Now press F5 and a dialogue box named Macros will open up. We have to name the macro here.

➤ In the Macro Name box, type a name for the function- ContainFormulas or anything else you prefer. You must keep in mind that you’re not allowed to leave a space inside while defining the name of the macro.

➤ After defining the Macro Name, press Create.

Step 2: Use of the Customized Function to Filter Cells with Formulas

➤ Now return to your Excel spreadsheet and in cell E5, type:

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

The formula will return boolean values- TRUE and FALSE for all values from Column D.

Step 3: Filtering Out Cells That Contain No Formulas

➤ Right-click the mouse after putting your cursor on the Contains Formula? Header.

➤ Select Filter by Selected Cell’s Value from the Filter Drop-down.

➤ After assigning the Filter buttons on the headers, open the Filter option from the Contains Formula? header.

➤ Under the Search box, mark the TRUE option only.

➤ Finally, press OK and you’re done.

Like the screenshot below, you’ll find the filtered table with the rows containing only formulas present in Column D.

Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.

Concluding Words

I hope these two simple and quick methods to filter cells containing formulas will now inspire you to apply them in your necessary Excel tasks when needed. If you have any questions or feedback, please let us know through comments. Goodbye!

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