Sometimes a null string (i.e., “” without the quotes) gets imported into a cell, and people don’t see anything, so they wonder about that too. But it returns “”, why isn’t that blank? Because “” is a piece of text, albeit zero characters long. The answer is, because the cell contains a formula. People will enter a formula like =IF(A1>0,A1,'') and wonder why it’s blank, because it makes a cell look blank too. The answer is, because it is simply a transparent piece of text. People will type a space character in a cell and wonder why that’s not blank, it looks blank. Part of the confusion over this whole issue is what Excel considers a blank cell. I’ll explain what the Empty Cells settings mean below, including some of their idiosyncrasies. By default, Excel will not chart data in rows or columns which have been hidden, but checking this box causes hidden cells to be plotted. There is also an option to Show Data in Hidden Rows and Columns. There are three options for Show Empty Cells As: Gaps, Zero, and Connect Data Points with Line. The Hidden and Empty Cell Settings dialog appears. Click on Hidden and Empty Cells in the bottom left of the Select Data Source dialog that appears. Right-click on the chart and choose Select Data, or choose Select Data from the ribbon.

You can easily tell Excel how to plot empty cells in a chart. Excel’s Hidden and Empty Cell Settings Dialog tl drĪ new feature introduced in a late build of Excel 2016, Show #N/A As An Empty Cell, solves the pain and frustration experienced by generations of Excel users trying to avoid plotting what look like apparently blank cells. New behavior introduced in Excel 2016 makes these older workarounds unnecessary. I discuss the “plot blank cells” issue in several tutorials in this blog: Mind the Gap – Charting Empty Cells, Area Chart With Gap, Another approach to plotting gaps in Excel charts, and VBA Approaches to Plotting Gaps in Excel Charts, among others.

There is a lot of confusion about plotting of hidden and empty cells, about what constitutes a blank cell, and about various workarounds that purport to produce blank cells that will or will not be displayed in a chart. A common problem around web forums and blogs is how to plot blank cells in Excel charts.

How to plot a graph in excel 2010