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. From the 2013 version of Excel, it 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.

filter cells with formulas in excel overview


Download Practice Workbook

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


Two Suitable Methods to Filter Cells with Formulas in Excel

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 random 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.

using filter and isformula functions to filter cells with formulas in excel

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:

=FILTER(B5:D11,--ISFORMULA(D5:D11)=1,"Not Found")

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

using filter and isformula functions to filter cells with formulas in excel

🔎 How Does This Formula Work?

➤ Here ISFORMULA function searches for all formulas used in Column D and returns TRUE if it finds any formula, otherwise 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.


Similar Readings:


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. And after using the customized function, we have to create a helper column where we’ll apply the Filter command to get the final output.

using vba to filter cells with formulas in excel

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

using vba to filter cells with formulas in excel

➤ 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.

using vba to filter cells with formulas in excel


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

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

=HasFormula(D5)

➤ 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.

using vba to filter cells with formulas in excel


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.

using vba to filter cells with formulas in excel

➤ 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.

using vba to filter cells with formulas in excel

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

using vba to filter cells with formulas in excel


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. Or you can check out our other articles related to Excel functions on this website.


Further Readings

Nehad Ulfat

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo