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.
Display blanks as zero in chart
Display blanks as zero in chart
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:
Skip blanks in chart
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. |
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
