Finding and Replacing Error Values:

Sometimes we often have huge worksheets with hundreds of rows of calculated values. Inevitably there will be scattered cells with the #N/A error that we would like to all be 0 (or some other value) so that we can use the cells in other formulas. Removing these values from the calculation is usually impossible, and it's very tedious to remove them by hand. It would be easy if there is any way to do the equivalent of a "find and replace" on those error values.
Simply follow these steps:
1. Press F5. Excel displays the Go To dialog box.
2. Click Special. Excel displays the Go To Special dialog box.
3. Make sure the Formulas radio button is selected.
4. The only check box that should be selected under Formulas is Errors.
5. Click OK. Excel selects all cells where the formula returned an error value.
6. Type 0 or whatever value you want.
7. Press Ctrl+Enter.
Note that this approach results in any error values being replaced, not just those with the #N/A error.