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

Get FREE Advanced Excel Exercises with Solutions!

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. ## 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. 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. 🔎 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.

### 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. #### 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. Read More: Shortcut for Excel Filter (3 Quick Uses with Examples)

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

`=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. Read More: How to Perform Custom Filter in Excel (5 Ways)

#### 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. Read More: How to Filter Data in Excel using Formula

## 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. 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 Advanced Excel Exercises with Solutions PDF  