登录查看更多内容

How to Replace Error Values Before Printing in Excel - Office 365

工程关注我们，每天学习??

?? Learning & Engineering

发布日期: 2024年3月2日

Dealing with error values in Excel is a common scenario when preparing data for analysis, reporting, or printing. Error values like #DIV/0!, #N/A, #VALUE!, and others can detract from the readability of your document and sometimes may mislead or confuse the intended audience. Excel provides a way to handle these errors gracefully by allowing users to replace them with more meaningful text or values. This can enhance the presentation of your data, especially when it's being shared with others or included in printed reports.

??Purchase our book to improve your Excel productivity

Benefits

Improved Readability: Replacing error values with descriptive text or neutral values makes your data easier to read and understand.
Enhanced Presentation: Clean data without visible error messages appears more professional and is more likely to be well-received by your audience.
Customized Reporting: Tailor the error handling to fit the context of your data or the preferences of your audience.
Error Management: Helps in identifying and managing problematic data points without altering the original data integrity.

Step-by-Step Guide

Step 1: Identify the Error Values

First, review your worksheet to identify any cells that contain error values. This will help you understand the scope and types of errors present.

Step 2: Use the IFERROR Function

The IFERROR function in Excel allows you to check for an error in a formula and replace it with another value of your choice if an error is found.

Example: Replacing errors with a specific message or value in a formula.

Original Formula: Suppose the original formula in cell A1 is =B1/C1.
Modified Formula with IFERROR:To replace any error that the original formula might produce with "N/A", you would modify the formula in A1 to:=IFERROR(B1/C1, "N/A")This formula checks if B1/C1 results in an error. If not, it displays the result. If there's an error, it displays "N/A".

Step 3: Apply to Multiple Cells

To apply this to multiple cells, you can copy the modified formula with IFERROR and use Paste Special or drag the fill handle to fill other cells as needed.

??Purchase our book to improve your Excel productivity

Example

Scenario: You have a sales report in Excel with the following columns:

Column A: Product ID
Column B: Units Sold
Column C: Unit Price
Column D: Total Sales (calculated as Units Sold * Unit Price)

Some rows have a #DIV/0! error in the Total Sales column due to a missing Unit Price.

Objective: Replace all error values in the Total Sales column with "Check Data".

Modify Formula for Total Sales:Original formula in D2: =B2*C2Modified formula: =IFERROR(B2*C2, "Check Data")
Apply Across the Column:After modifying the formula in D2, copy D2.Select the range D3:D100 (assuming your data extends to row 100).Paste the formula into this range. Each cell in the range will now display "Check Data" instead of an error value.

Advanced Tips

Using ISERROR for Older Versions: If you're using an Excel version that doesn't support IFERROR, use ISERROR within an IF statement: =IF(ISERROR(B1/C1), "N/A", B1/C1).
Custom Messages for Different Errors: To display different messages for different types of errors, combine IF with ISERROR, ISNA, etc., though this can make the formula more complex.
Dynamic Replacement Values: Use cell references in the replacement part of the IFERROR function to dynamically change the message or value shown for errors.
Preparation for Printing: Before printing, consider using Find & Select -> Go To Special -> Formulas with errors, to review and ensure all error values are adequately addressed.