Alif Bin Hussain
While working with numbers, it is often required to sort data from largest to smallest. Although it is a very easy task, sometimes it might not work correctly due to a few reasons. In this article, we will show you four common reasons for the problem sort largest to smallest not working in Excel, and useful solutions to fix it.
Download Practice Workbook
Download this practice workbook to exercise while reading this article.
4 Reasons with Solutions When Sort Largest to Smallest Not Working in Excel
In this article, we will demonstrate four reasons for the issue sort largest to smallest not working in Excel, and solutions to fix them. We will use the following dataset for this purpose.
Reason 1: Numerical Data Converted into Text Format
The issue can occur if the data we are trying to sort is in Text format. We can only sort data from largest to smallest if the data is numerical. If the data is in Text format, we won’t be able to sort it from largest to smallest.
Solution: Use VALUE Function to Convert Text to Numeric Data
To solve this issue, follow the steps discussed below.
Steps:
- First of all, add a new column named Converted Data.
- Then select cell D5 and write down the following formula.
=VALUE(C5)- After that, press Enter to convert the data into the General format.
- Then AutoFill formula to the rest of the column to change the Text format into the General format for all data.
- Next, select all the data and press Alt + A + S + S to open the Sort dialogue box.
- In the box, check the box of My Data has headers.
- Then select Converted Data in Sort by.
- After that, choose Largest to Smallest in Order and press OK.
- Hence, you will have your sorting issue fixed.
Read More: How to Find Largest Lookup Value in Excel (3 Easy Ways)
Reason 2: Extra Space Before or After Numerical Entry
The second reason for getting the sort from largest to smallest not working in Excel can occur because of the presence of extra spaces before or after a numerical value. To see if any extra space is present in the data, follow the next steps.
Steps:
- First, insert a column named Check.
- Then select cell D5 and type the following formula to check if there is any extra space.
=ISNUMBER(C5)- The formula will show FALSE if extra space is present and it will show TRUEÂ otherwise.
- After that, AutoFill this formula to the rest of the cells to check all the data.
Solution: Apply TRIM Function to Remove Spaces
Now to fix this problem, read the following process.
Steps:
- First of all, add a new column named Converted Data and then write the following formula in cell E5.
=TRIM(C5)- After that, hit Enter , and it will remove any extra space that is present in the cell.
- Then AutoFill this formula to the rest of column E and remove all the extra spaces.
- Now select column E and press Alt + A + S + S .
- As a result, the Sort dialogue box will pop up.
- In the box, check the My data has headers option.
- Select Converted Data and Largest to Smallest.
- Finally, press OK.
- Consequently, you will have your data correctly sorted.
Read More: How to Lookup Next Largest Value in Excel (4 Easy Ways)
Reason 3: Numbers Generated Using Formula
Another reason this problem can occur is if you try to sort numbers that are generated using any formula. In the following figure, the numbers are generated using the RANDBETWEEN function. If we try to sort these numbers, the numbers get randomized and the sort doesn’t work properly.
Solution: Copy and Paste As Values
We can easily solve this issue by following the process discussed below.
Steps:
- First, select all the data generated using a formula and right-click on it.
- Then select Copy.
- After that, click on cell C5 and press the right button of your mouse.
- Then select Paste as Values.
- Now to sort the data, press Alt + A + S + S to open the Sort pop-up box.
- In the box, make sure the box of My data has headers option is checked.
- Then choose Quantity Sold in Sort by and Largest to Smallest in Order.
- Finally, hit the Enter button.
- Hence, you will have your issue fixed and get the properly sorted data.
Read More: How to Find Second Largest Value with Criteria In Excel
Reason 4: Mixture of Different Data Types
Sort largest to smallest not working in Excel can also occur if the data we are trying to sort are of different types. If some data are in General and others are in Text format, the Sort feature won’t work correctly.
Solution: Convert Data into Number Format
We can fix this problem by changing all the data types into Number format. Read the following steps to learn how to do it.
Steps:
- First, select all the data from column C.
- Then click on the Home tab and go to,
Home → Number → Number
- Once all the data are in Number format, open the Sort dialogue box.
- In the box, check the box of My Data has headers.
- Then select Converted Data in Sort by.
- After that, choose Largest to Smallest in order.
- Then press OK.
- Finally, the problem will be fixed and you will have correctly sorted data.
- When the data you want to sort is generated by any formula, it is better to copy and paste those data as values before sorting.
Read More: How to Use Excel Large Function with Criteria( 4 Suitable Ways)
Conclusion
Thanks for making it this far. I hope you find this article useful. Now you know four reasons and solutions for the problem sort largest to smallest not working in Excel. Please let us know if you have any further queries, and feel free to give us any recommendations in the comment section below.
