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.

Notes

Download the accompanying spreadsheet to see this in Excel.


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...
Change Axis Units on Charts in Excel
Tutorial: You can change the size of the units on a chart axis, their interval, where they start, w...