How to Ignore Error Values When Creating a Chart in Microsoft Excel and Google Sheets

Microsoft Excel

1. Open the Excel workbook containing the data you want to chart.
2. You'll first want to replace the error values with a logical function. Suppose the data you want to chart is in cell range A2:A10, replace each cell's data with the following formula: =IF(ISERROR(original_formula),"",original_formula). Replace "original_formula" with the formula that's producing the error.
3. After applying the formula, the error values will be replaced with blank cells.
4. Now, highlight the corrected range of cells, go to the Insert tab in the Ribbon, and create your chart as you normally would.

More about creating a chart in Excel and how to use the IF function.

Google Sheets

1. Open your Google Sheets document containing the data you want to chart.
2. Similar to Excel, you can replace the error values with a logical function. If your data is in cell range A2:A10, replace each cell's data with the following formula: =IF(ISERROR(original_formula),"",original_formula).
3. After applying the formula, the error values will be replaced with blank cells.
4. Now, highlight the corrected range of cells, click on the Insert menu, and create your chart as you normally would.

More about creating a chart in Google Sheets and how to use the IF function.

Summary This tutorial guides you on how to ignore error values when creating a chart in Microsoft Excel and Google Sheets. By using a logical function to replace error values with blank cells, you can create accurate charts free of distortions.

References

(1) Microsoft Excel - Create a Chart
(2) Microsoft Excel - IF Function
(3) Google Sheets - Create a Chart
(4) Google Sheets - IF Function
(5) Microsoft Excel - ISERROR Function
(6) Google Sheets - ISERROR Function