Table of Contents
Google Sheets has a built-in feature that allows users to sort data while ignoring blank cells. This means that when sorting a range of data, any cells that are left blank will be excluded from the sorting process. This can be useful when working with large datasets that contain empty cells, as it allows for a more efficient and accurate sorting of data. By utilizing this feature, users can easily organize and analyze their data without the need to manually remove or fill in blank cells. This feature can be accessed through the “Data” tab and selecting the “Sort Range” option. Users can then choose to sort by specific columns while ignoring blank cells, providing a streamlined and convenient way to sort data in Google Sheets.
Google Sheets: Sort and Ignore Blanks
You can use the following formula to sort rows in Google Sheets while ignoring rows with a blank value in a particular column:
=QUERY(A1:B11,"select * where B is not null order by B")
This particular formula sorts the rows in the range A1:B11 by column B, ignoring any rows with a blank value in column B.
The following example shows how to use this formula in practice.
Example: Sort and Ignore Blanks in Google Sheets
Suppose we have the following dataset in Google Sheets that contains information about various basketball teams:
We can type the following formula into cell D1 to sort the rows of the dataset based on the value in the Points column, while simply ignoring any rows that have a blank value in the Points column:
=QUERY(A1:B11,"select * where B is not null order by B")
The following screenshot shows how to use this formula in practice:
Notice that the rows have been sorted by the value in the Points column and the rows that contained a blank in the Points column have been ignored.
By default, Google Sheets sorts the data in ascending order.
However, we can use the desc argument to instead sort in a descending order:
=QUERY(A1:B11,"select * where B is not null order by B desc")
The following screenshot shows how to use this formula in practice:
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets:
Cite this article
stats writer (2024). How can Google Sheets sort data while ignoring blank cells?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-google-sheets-sort-data-while-ignoring-blank-cells/
stats writer. "How can Google Sheets sort data while ignoring blank cells?." PSYCHOLOGICAL SCALES, 28 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-google-sheets-sort-data-while-ignoring-blank-cells/.
stats writer. "How can Google Sheets sort data while ignoring blank cells?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-google-sheets-sort-data-while-ignoring-blank-cells/.
stats writer (2024) 'How can Google Sheets sort data while ignoring blank cells?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-google-sheets-sort-data-while-ignoring-blank-cells/.
[1] stats writer, "How can Google Sheets sort data while ignoring blank cells?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can Google Sheets sort data while ignoring blank cells?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.