Function Problems

How to Use IF and ISFORMULA to Return Zero for Formulas in Excel

WPS Content Manager Sep 25, 2026 869 views

Question details

The user needs a formula to check if a cell contains a formula (like SUM) and return 0 if true, or the original cell value if false.

How to Use IF and ISFORMULA to Return Zero for Cells Containing Formulas

Product: Excel
Device & OS: not provided
Scenario: Data validation or conditional data extraction where formula-driven cells need to be masked or excluded by returning zero.
Observed behavior: The user wants to conditionally output 0 for formula cells and the actual hardcoded value for non-formula cells.

Before you start

Ensure you know the exact cell reference you want to check, and verify that your version of Excel supports the ISFORMULA function (Excel 2013 or newer).

Solution 1Recommended

Combine the IF and ISFORMULA Functions

Use the ISFORMULA function nested inside an IF statement to dynamically check for formulas and return 0.

The ISFORMULA function returns TRUE when a target cell contains any formula. By wrapping it inside a standard IF statement, you can specify exactly what to output when a formula is detected (0) and what to output when it is not (the original cell value).

Select an empty cell

Click on an empty cell where you want the conditional result to be displayed.

Enter the nested formula

Type the formula =IF(ISFORMULA(A1), 0, A1) into the formula bar, replacing A1 with the reference to the cell you want to check.

Apply the formula

Press the Enter key on your keyboard to apply the formula and view the result.

Copy to other cells

Click and drag the fill handle at the bottom right corner of the cell to copy this formula to other cells in the column or range.

Customizing the Output: You can replace '0' in the formula with any text (like "Formula") or another number if you want to output a different specific value for cells containing formulas.

Advanced Data Processing

Easily Manage Complex Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports standard functions like IF and ISFORMULA, allowing you to seamlessly process your data and automate conditional outputs for free with high performance.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook in WPS Spreadsheet.
2. Select your target cell: Click the cell where you want the final checked value to appear.
3. Input the formula: Type =IF(ISFORMULA(A1), 0, A1) into the cell and press Enter.
4. Apply across your data: Use the drag handle to quickly apply this logic to entire rows or columns.

100% compatible with Microsoft Excel formulas and .xlsx formatsFree and lightweight alternative for spreadsheet managementBuilt-in formula auditing and error-checking tools

microsoft office alternative - wps office

Frequently Asked Questions

Does ISFORMULA work in older versions of Excel?

ISFORMULA was introduced in Excel 2013. If you are using Excel 2010 or older, this function will not be recognized and will result in a #NAME? error.

How can I highlight cells containing formulas instead of changing their value?

You can use Conditional Formatting. Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =ISFORMULA(A1), choose a background color, and click OK.

What happens if the referenced cell is completely empty?

If the referenced cell is empty, the formula =IF(ISFORMULA(A1), 0, A1) will output 0. Empty cells are not considered formulas (so ISFORMULA is FALSE), but Excel evaluates an empty cell reference as 0 by default.

Excel & Data Problems