Google Sheets: How to Create a Chart and Ignore Blank Cells

Often you may want to create a chart in Google Sheets using a range of data and ignore any blank cells in the range.

Fortunately this is easy to do using the Plot null values feature in Google Sheets.

The following example shows how to use this function in practice.

Example: Create Chart in Google Sheets and Ignore Blank Cells

Suppose we have the following dataset that shows the sales of some product during each month in a year:

Now suppose we would like to create a line chart to visualize the sales during each month.

We can highlight the cells in the range A1:B13, then click the Insert tab along the top ribbon, then click Chart:

The following line chart will appear:

Notice that there are two gaps in the line chart where we have missing values for the months of May and August.

To fill in these gaps, double click anywhere on the chart.

In the Chart editor panel that appears, click the Customize tab, then check the box next to Plot null values:

The gaps in the line chart will automatically be filled in:

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

Google Sheets: How to Ignore Blank Cells with Formulas
Google Sheets: How to Use ISBLANK with Cell Range
Google Sheets: How to Sort and Ignore Blanks

Leave a Reply

Your email address will not be published. Required fields are marked *