Chart Ignore Blank Cells in Excel

When you create a chart, sometimes you will find blank cells in rows. If you insert a line chart there your data won’t appear the way you intend. The chart will show no data at all.

In order to ignore blank cells, you have to do the following.

  1. From the table, select the range B2:B29, then while holding the Ctrl key, select the range D2:D29.
  1. Go to Insert >> Charts >> Insert Line or Area Chart >> Line.
  2. This is what the chart looks like.
  1. Right-click the chart and click Select Data. From the Select Data Source window, click Hidden and Empty cells.
  1. Now, you have some options here: Gaps, Zero, and Connect data points with line. The default position is Gaps. Change it to Zero and you will have the following chart.
  1. Choose Connect data points with line and you will get the chart with no gaps. Each data point will be connected to another.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.