Nehad Ulfat
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.
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.
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.
Similar Readings:
- How to Filter Multiple Columns Simultaneously in Excel (3 Ways)
- Filter Multiple Rows in Excel (11 Suitable Approaches)
- Filter Multiple Criteria in Excel (4 Suitable Ways)
- Excel Filter Data Based on Cell Value (6 Efficient Ways)
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.
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.
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.
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.
