How Can We Handle Data Gaps in Excel?

When working with data we can’t always expect the data to be uniform and cleaned. Sometimes there might be some irregularities in the data like gaps where the cell is empty, but we still have to account for it when creating any kind of graph.

Let’s say that we have a sample data of 20 students and their marks but due to some reasons some students papers were unchecked, and we couldn’t have their marks in the table, and therefore we have data gaps.

DaTa

If not handled properly this data can very easily give wrong information when put in a graph and this is where we need to handle the gaps in the data so that accurate information is displayed when put in a graph. Let me demonstrate by making a graph for this data.

As you can see the graph has broken in between and is inconsistent but to handle these data gaps we need to click on the graph then go to the design tab and click on select data and then choose hidden and empty cells in the popup.

After that, you should have three options:

• Gap
• Zero
• Connect data points with line

If the second option is selected, the graph will still not be completely accurate as it just sets all the gaps to zero.

Here as you can see I have selected the second option and because of that it is showing the empty cells as zero which is wrong as in our example we just have not entered the information in the cells but that does not mean that all those students have score zero marks thus in a similar situation showing data gaps as zero would be inaccurate. 

This is an example showing data gaps as zero.

For handling data gaps and connecting data points with the line select the third option as shown.

Select the shown option i.e. Connect data points with line. That’s it. Just in these simple steps you have successfully handled data gaps in your data and now made your graph more consistent like this.