How To Handle Missing Data In Excel Charts !!!

Ever come across a situation when you have some blanks in data for which you need to create a chart for? Or have any hidden rows or columns in the data? We know that it is very logical how excel create a chart in this situation but sometimes we don’t want to present it in that way. So just to get rid of this we can use one of the below smartest options.

So let’s start guy’s, Just have a look at the data and the chart we are going to use to understand this.  (You can also download the exercise file by following the link at the bottom of this post)

Click anywhere inside the chart
to select it. Now you can see the Chart Tools group in the ribbon.

Under Design Tab à Select Data

Inside the Select Data Source, click on “Hidden and Empty Cells”

Now in “Hidden and Empty Cell Settings”, you can see here that “Gaps” are selected by default. Here we can either choose “Zero” or “Connect Data points with Line”. Just look at the below screen shots for both options.

Filling Gaps using “Zero” option.

Filling Gaps using “Connect data points with line” option.

It entirely depends on how you want to present the chart, accordingly you can use “Zero” or “Connect Data points with Line”.

If you like this post, then you can just put in your email ID at the top right corner of the page and you will get a FREE weekly newsletter with amazing Excel tips and notification of my latest post on this blog.