Creating Charts in Excel that Ignore Blank or Error Cells

When creating charts in Excel, it’s common to encounter blank or error cells in your data range. By default, Excel includes these cells in the chart, which can lead to unwanted gaps or incorrect data representation. In this tutorial, we’ll explore how to create charts that ignore blank or error cells, ensuring a cleaner and more accurate visualization of your data.

Understanding Error Cells

In Excel, error cells are represented by values such as #N/A, #NAME?, or #DIV/0!. These errors occur when a formula or function cannot be evaluated correctly. To ignore these error cells in a chart, you need to use the NA() function, which returns a #N/A value without quotes.

Using the NA() Function

The NA() function is essential for ignoring error cells in charts. When used in a formula, it returns a #N/A value that Excel recognizes as an error cell. For example:

=IF(A1>A2, A3, NA())

In this formula, if the condition A1>A2 is false, the NA() function returns a #N/A value, which is ignored by the chart.

Hiding Rows or Columns

Another approach to ignoring blank or error cells is to hide the entire row or column containing the unwanted data. You can do this manually by right-clicking on the row or column header and selecting "Hide," or by using the AutoFilter feature in a table.

When you hide a row or column, Excel excludes it from the chart, effectively ignoring any blank or error cells within that range.

Best Practices for Chart Creation

To create charts that ignore blank or error cells effectively:

1. Use the NA() function: Instead of returning a string "#N/A" or "", use the NA() function to return a #N/A value.
2. Hide unwanted rows or columns: If you have rows or columns with blank or error cells, consider hiding them to exclude them from the chart.
3. Use IFERROR and NA() functions together: Combine the IFERROR function with the NA() function to handle errors and return #N/A values, like this: =IFERROR(A2, NA())

By following these best practices and using the NA() function or hiding unwanted rows or columns, you can create charts in Excel that accurately represent your data and ignore blank or error cells.

Example Use Case

Suppose you have a dataset with x and y values, but some of the cells contain #DIV/0! errors. To create a scatter plot that ignores these error cells, you can insert a new column next to the offending column and use the IFERROR function with the NA() function:

=IFERROR(A2, NA())

Then, create your scatter plot using the new column, and Excel will ignore the #N/A values and not plot them as zeroes.