Is Google Sheets not sorting numbers correctly?
If you’re using Google Sheets and have a lot of numerical data, it’s important to be able to organize that data in a specific order for effective analysis.
However, you may encounter a situation where the numbers in a column don’t sort correctly.
For example, if you select the column and try to sort the data in ascending order, they might not be arranged in the expected order, but instead appear in a random order.
Does this mean that Google Sheets is broken?
Absolutely not!
In this article, I will explain why Google Sheets sometimes does not sort numbers correctly and provide you with simple step-by-step instructions to resolve this issue.
Google Sheets Not Sorting Numbers Correctly
Here’s how to fix the “Google Sheets Not Sorting Numbers Correctly” issue:
Step 1: Select the column with the numbers
The reason Google Sheets sorts numbers incorrectly is that the column you’re sorting is formatted as “Plain text” instead of numbers.
When numbers are formatted as text, Google Sheets treats them as such, resulting in incorrect sorting.
However, by formatting the column as numbers, you can sort them correctly.
If your column contains a combination of numbers and text (e.g., 13 km, 12 km, 1 km), sorting may still be incorrect. To solve this issue, jump to this section.
If your column only contains numbers, follow these steps.
Firstly, select the desired column to begin sorting.
Step 2: Format the column as numbers
Once you have selected the column, the next step is to format it as numbers.
To do this, ensure that the column is still selected and navigate to the top menu.
Click on “Format” and choose “Numbers” from the drop-down menu.
Select “Numbers” again from the secondary drop-down menu.
This will designate the data in the column as numbers in Google Sheets.
You don’t need to worry about decimal points at this stage, as we will address that later.
Now, let’s move on to the next step.
Step 3: Sort the data in the desired order
Now, it’s time to sort the data in your desired order.
For example, if you want to sort the numbers in ascending order, simply right-click on the selected column and choose “Sort sheet A to Z” from the context menu.
Your numbers will now be correctly sorted.
Next, let’s proceed to remove the decimal points that were added in the previous step.
Step 4: Go to Format > Number > Custom number format
Once again, select the column.
Then, click on the “Format” option from the top menu, followed by the “Number” option from the primary drop-down menu.
Finally, choose the “Custom number format” option from the secondary drop-down menu.
Step 5: Choose the format “0” and click “Apply”
A “Custom number formats” pop-up will appear, displaying various commonly used formats.
From this pop-up, select the second option: “0” and click on the “Apply” button.
The formatting will now be corrected according to the chosen format.
For columns containing both numbers and alphabets, follow these steps:
When your column contains both numbers and alphabets, sorting the numbers in Google Sheets becomes challenging.
To address this issue, we will follow a simple process.
First, we will remove the text from the numbers, allowing for proper sorting.
Next, we will sort the numbers accordingly.
Finally, we will re-add the text back to the sorted numbers.
By following these steps, we can effectively sort the mixed data in your column:
Step 1: Right-click on the column and choose “Insert 1 column right”
To start, we will insert a column in the sheet to remove the text from the numbers.
Don’t worry, this column is temporary and will be deleted later.
Follow these steps to insert the new column, firstly, right-click on the column you want to sort, then select “Insert 1 column right” from the context menu.
By doing so, a blank column will be added next to the selected column.
Step 2: Enter the formula into the first cell of the newly inserted column
To proceed, select the first cell of the newly inserted column and enter the following formula:
=ARRAYFORMULA(IFERROR(SPLIT(A:A, " km", 0)))
In this formula, “A:A” represents the column where the numbers you wish to sort are located.
If the numbers are in a different column, modify the formula accordingly.
Additionally, note that the numbers in my column are followed by the common text “km”.
Therefore, the purpose of this formula is to remove the “km” text from each number.
After entering the formula, press the enter key on your keyboard.
The text will be successfully extracted from the numbers, and the resulting values will be populated in the newly created column.
Step 3: Copy the new column
Now, copy the contents of the new column by selecting the entire column.
Right-click on the column and choose “Copy” from the context menu.
This action will copy the values from the new column to the clipboard.
Step 4: Paste only the values into the original column
After copying the column, right-click on the original column.
From the context menu, select “Paste special” and then choose “Values only” from the drop-down menu.
This action will paste only the values from the copied column into the original column, replacing the existing data.
Step 5: Delete the new column
To remove the newly created column, right-click on the column and select “Delete column” from the context menu.
This action will delete the column from the sheet since we no longer need it, as we have successfully separated the text from the numbers.
Step 6: Sort the numbers as desired
Now, right-click on the column and choose the desired sorting order, whether it’s ascending or descending.
Upon doing so, you will observe that the numbers are sorted correctly this time, according to the chosen order.
Step 7: Add the text back into the first column
Finally, you can add the text back to the numbers as follows.
Since my numbers had the unit “km,” I’ll reattach it.
Start by selecting the entire column, then navigate to Format > Numbers > Custom number format.
In the format box, input “0 K\m” and click on the “Apply” button.
This will apply the desired format to the numbers, including the “km” unit, ensuring an accurate representation of the data.
Conclusion
In conclusion, Google Sheets may encounter issues when sorting numbers, but it can be easily resolved by following these steps:
- Select the column with the numbers.
- Format the column as numbers.
- Sort the data in the desired order.
- Go to Format > Number > Custom number format.
- Choose the format “0” and click “Apply.”
For columns containing both numbers and alphabets, follow these additional steps:
- Right-click on the column and choose “Insert 1 column right.”
- Enter the formula into the first cell of the newly inserted column.
- Copy the new column.
- Paste only the values into the original column.
- Delete the new column.
- Sort the numbers as desired.
- Add the text back into the first column.