With the user-defined function that is provided in this article, the workaround formula is as follows:
How to create the sample function
- In Excel, open the Microsoft Visual Basic Editor.
To do this in Microsoft Office Excel 2003 and in earlier versions of Excel, point to Macro on the Tools menu, and then click Visual Basic Editor. Alternatively, press ALT+F11.
To do this in Microsoft Office Excel 2007, click the Developer tab, and then click
Visual Basic in the Code group. Alternatively, press ALT + F11.
Note To show the Developer tab in the Ribbon, click the
Microsoft Office Button, click Excel Options, click the
Popular category, click to select the Show Developer tab in the Ribbon check box, and then click OK.
- Click Module on the Insert menu, and then type the following macro.
Function IfError(formula As Variant, show As String)
On Error GoTo ErrorHandler
If IsError(formula) Then
IfError = show
IfError = formula
- On the File menu, click Close and Return to Microsoft Excel.
- To use the function, click Insert Function on the Insert menu. In the Insert Function dialog box, click User Defined under Categories, and then click IfError under Select a function. Click OK.
- Next to Formula, type the formula for which you want to hide the error value. Do not include the equal sign (=).
- Next to Show, type what you want to show in place of the error value. If you want to hide the error value, type double quotes ("").
- Click OK.
Article ID: 280094 - Last Review: May 17, 2007 - Revision: 1