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.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
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.
You may be insterested in this utility
Navigation--AutoText (add usually used charts to AutoText pane.then one click to insert it when you need.)