How to skip blank cells while creating a chart in Excel?
If there are some blank cells in a list, the relative chart of the list will be shown as below screenshot which is not pleasing to the eye. In this article, I will talk about how to skip the blank cells while creating a chart in Excel.
To display blank cells as zeros in chart, you just need to check an option after creating the chart.
1. After creating the chart by the values, right click at the chart and click Select data form the popped context menu. See screenshot:
2. Then in the Select Data Source dialog, click Hidden and Empty Cells, and in the Hidden and Empty Cells Settings dialog, check Zero option. See screenshot:
3. Click OK > OK to close dialogs, and the blank cells have been displayed as zeros. See screenshot:
To skip blanks directly in a chart, you need a formula before creating the chart.
1. Select a blank cell next to the values you want to create chart by, and type this formula =IF(ISBLANK(B2),#N/A,B2), B2 is the cell you use, and drag auto fill handle down to the cells you need to apply this formula. See screenshot:
2. Then keep the formula cells selected, click Insert tab, and insert a chart as you need in the Charts group. See screenshot:
Then the blank cells have been ignored in the chart.
Tip: If you usually use complex charts in Excel, which will be troublesome as you create them very time, here with the Auto Text tool of Kutools for Excel, you just need to create the charts at first time, then add the charts in the AutoText pane, then, you can reuse them in anywhere anytime, what you only need to do is change the references to match your real need. Click for free download it now.
You are guest
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 4 months agoIf I use an if-formular like =if(A1=1:A2:"") and result is false, then the cell isn't empty and I get a zero in the chart. any work around?
To post as a guest, your comment is unpublished.· 8 months agoThank you
This was very helpful
But I have a question here..
What if the blank cels I want to skip are the last 2 or 3 cells for example, and I want to resize the figure afterwards? How can that be done?