Connect the Line for a Chart in Excel and Skip Blanks

Add to Favorites

How to prevent a line chart from having a blank spot or also dropping to zero when there is a blank cell or a zero value that should be blank cell.

Here is the problem:

Connect the Line for a Line Chart

  1. Click the desired chart and then go to the Design tab that should appear and click Select Data.
  2. In the window that opens, click Hidden and Empty Cells

  3. In the smaller window that opens click Connect data points with line and hit OK.
  4. That's it!

Zeros that Should be Blanks

If you have zeros in your data that you are using to represent a blank value, the chart will not understand this and will, instead, represent it as zero in the chart.

As such, DO NOT use a zero to denote emptiness in a set of data that you will put into a chart or it won't look right.


Download the accompanying spreadsheet to see this in Excel.

Question? Ask it in our Excel Forum

Downloadable Files: Excel File

Similar Content on TeachExcel
Change the Name of a Chart in Excel
Tutorial: How to change the name of a chart in Excel. This allows you to use a more intuitive char...
Update, Change, and Manage the Data Used in a Chart in Excel
Tutorial: In this tutorial I am going to show you how to update, change and manage the data used by ...
Add Image Background to a Chart in Excel
Tutorial: How to use an image for the background of a chart in Excel. Simple but fun way to spruce...
Get Values from a Chart
Macro: This macro will pull the values from a chart in excel and list those values on another spr...
Reverse the Contents of a Cell in Excel - UDF
Macro: Reverse cell contents with this free Excel UDF (user defined function). This will mir...
Guide to Creating Charts with a Macro in Excel
Tutorial: How to add, edit, and position charts in Excel using VBA. This tutorial covers what to do ...