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.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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.)