Creating charts with ignoring error or blank cells

Basic Description

When you refer the chart to a defined Range, it plots all the points in that range, interpreting (for the sake of plotting) errors and blanks as null values. You are given the option of leaving this as null (gap) or forcing it to zero value. But neither of these resizes the RANGE which the chart series data is pointing to. From what I gather, neither of these are suitable. If you hide the entire row/column where the error (like #N/A, #DIV/0!, etc.) data exists, the chart should ignore these completely. You can do this manually by right-click | hide row, or by using the table AutoFilter. In this manual process, your data also hide which is not required all time. Better practice is to use a error handling function like IFERROR which is more efficient and visible for data and graph both. So, to solve this problem, follow below steps.

For an example, I have a report looks like below:

error database

In the above database, you see that, in Mar-14 there is no target against sale, for that reason, we found an error in our achievement. Again in Jul-14, we have no sale against the target because of some unavoidable issues. Now we want to create a graph where achievement line graph will be presented with months but May-14 and Jul-14 should be avoid in graph.

With functions to show without blank:

1. Firstly, in the achievement cell nesting functions is required for removing error and zeros. I use =IFERROR(IF(D3/C3=0,””,D3/C3),””) this function for removing error from required cells.

2. Create your chart and format chart as per your requirement. My formatted charts looks like below:

formated chart

3. To ignore above mention months, right click on the graph and select “Select Data” option. New window popup and select “Hidden and Empty Cells” to ignore error or blank. Now select Gaps from the “Show Empty Cells As” group.

select data gaps charts

4. Your graph is now showing without blank but if now showing then you should manually delete cells value for getting blank which is looks like:

final graph


Hide entire row process:

Just hide required rows by right click and your graph looks like:

final graph hidden rows

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>



clearPost Comment