Table of Contents
Creating a chart in Google Sheets is a simple and effective way to visually represent data. However, when dealing with large sets of data, it is common to have blank cells that can affect the accuracy of the chart. To address this issue, Google Sheets offers a feature that allows users to ignore blank cells when creating a chart. This can be done by selecting the data range, right-clicking, and choosing the “Select data range” option. From there, select the Hidden and empty cells” option and choose “Skip” to exclude any blank cells from the chart. This feature ensures that the chart accurately reflects the data and eliminates any potential errors caused by blank cells.
Google Sheets: 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:
The following tutorials explain how to perform other common tasks in Google Sheets:
Cite this article
stats writer (2024). How can I create a chart in Google Sheets that ignores blank cells?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-create-a-chart-in-google-sheets-that-ignores-blank-cells/
stats writer. "How can I create a chart in Google Sheets that ignores blank cells?." PSYCHOLOGICAL SCALES, 27 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-create-a-chart-in-google-sheets-that-ignores-blank-cells/.
stats writer. "How can I create a chart in Google Sheets that ignores blank cells?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-create-a-chart-in-google-sheets-that-ignores-blank-cells/.
stats writer (2024) 'How can I create a chart in Google Sheets that ignores blank cells?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-create-a-chart-in-google-sheets-that-ignores-blank-cells/.
[1] stats writer, "How can I create a chart in Google Sheets that ignores blank cells?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I create a chart in Google Sheets that ignores blank cells?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.