Table of Contents
The IFERROR function in Excel allows users to display a blank cell instead of an error message when a formula returns an error. This can be helpful in creating a clean and organized spreadsheet, as it prevents error messages from cluttering the data. To use the IFERROR function, users can simply wrap their formula in the function and specify the desired result if an error occurs. This function can be particularly useful when working with large datasets and complicated formulas. By using the IFERROR function, users can ensure that their data remains accurate and visually appealing.
Excel: Use IFERROR Then Blank
You can use the following methods in Excel to return a blank value instead of an error value when a valid value isn’t returned from a formula:
Method 1: IFERROR Then Blank with Some Formula
=IFERROR(B2/A2, "")
Method 2: IFERROR Then Blank with VLOOKUP
=IFERROR(VLOOKUP(E2, $A$2:$C$12, 3, FALSE), "")
The following examples show how to use each method in practice.
Example 1: IFERROR Then Blank with Some Formula
Suppose we use the following formula to divide the values in column B by the values in column A in this particular Excel spreadsheet:
=B2/A2
Notice that for each cell in column C where we attempt to divide by a blank value, we receive #DIV/0! as a result.
To return a blank value instead of an error value, we can type the following formula into cell C2:
=IFERROR(A2/B2, "")
We can then copy and paste this formula down to every remaining cell in column C:
Now for each cell in column C where we attempt to divide by a blank value, we simply receive a blank value as a result.
Example 2: IFERROR Then Blank with VLOOKUP
VLOOKUP(F2, $A$2:$C$12, 3, FALSE)
Notice that for each cell in column G where we encounter an empty value in the VLOOKUP function, we receive #N/A as a result.
To return a blank value instead of a #N/A value, we can type the following formula into cell F2:
=IFERROR(VLOOKUP(F2, $A$2:$C$12, 3, FALSE), "")
We can then copy and paste this formula down to every remaining cell in column F:
Now for each cell where we encounter an empty value in the VLOOKUP function, we simply receive a blank value as a result.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). “How can I use the IFERROR function to display a blank cell instead of an error message in Excel?”. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-the-iferror-function-to-display-a-blank-cell-instead-of-an-error-message-in-excel/
stats writer. "“How can I use the IFERROR function to display a blank cell instead of an error message in Excel?”." PSYCHOLOGICAL SCALES, 29 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-the-iferror-function-to-display-a-blank-cell-instead-of-an-error-message-in-excel/.
stats writer. "“How can I use the IFERROR function to display a blank cell instead of an error message in Excel?”." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-the-iferror-function-to-display-a-blank-cell-instead-of-an-error-message-in-excel/.
stats writer (2024) '“How can I use the IFERROR function to display a blank cell instead of an error message in Excel?”', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-the-iferror-function-to-display-a-blank-cell-instead-of-an-error-message-in-excel/.
[1] stats writer, "“How can I use the IFERROR function to display a blank cell instead of an error message in Excel?”," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. “How can I use the IFERROR function to display a blank cell instead of an error message in Excel?”. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.