Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

[Fixed!] Sort Largest to Smallest Not Working in Excel

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.

Dataset for sorting largest to smallest not working


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.

Numerical Data Converted into Text Format


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.

Use Value Function to Solve Sort Largest to Smallest Not Working in Excel

  • Then AutoFill formula to the rest of the column to change the Text format into the General format for all data.

Use VALUE Function to Convert Text to Numeric Data to Solve Sort Largest to Smallest Not Working in Excel

  • 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.

Use VALUE Function to Convert Text to Numeric Data to Sort Largest to Smallest Not Working in Excel

  • Hence, you will have your sorting issue fixed.

Use VALUE Function to Convert Text to Numeric Data to Sort Largest to Smallest Not Working in Excel

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.

Check for Space to Solve Sort Largest to Smallest Not Working in Excel

  • After that, AutoFill this formula to the rest of the cells to check all the data.

Check for Space to Solve Sort Largest to Smallest Not Working in Excel


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.

Apply TRIM Function

  • Then AutoFill this formula to the rest of column E and remove all the extra spaces.

AutoFill TRIM Functionl

  • 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.

Apply TRIM Function to Remove Spaces to Solve Sort Largest to Smallest Not Working in Excel

  • Consequently, you will have your data correctly sorted.

excel sort largest to smallest not working

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.

Problem of Numbers Generated Using Formula in Sort Largest to Smallest Not Working in Excel


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.

Copy and Paste As Values to Solve Sort Largest to Smallest Not Working in Excel

  • After that, click on cell C5 and press the right button of your mouse.
  • Then select Paste as Values.

Copy and Paste As Values to Solve Sort Largest to Smallest Not Working in Excel

  • 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.

Copy and Paste As Values to Solve Sort Largest to Smallest Not Working in Excel

  • Hence, you will have your issue fixed and get the properly sorted data.

Copy and Paste As Values to Solve Sort Largest to Smallest Not Working in Excel

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.

Mixture of Different Data Types issue in Sort Largest to Smallest Not Working in Excel


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

Use Number Feature

  • 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.

Convert Data into Number Format

  • Finally, the problem will be fixed and you will have correctly sorted data.

Convert Data into Number Format to Solve Sort Largest to Smallest Not Working in Excel

Notes
  • 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.


Related Articles

Alif Bin Hussain

Alif Bin Hussain

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Civil Engineering from Bangladesh University of Engineering & Technology (BUET). I am a fresh graduate with a great interest in research and development. I do my best to acquire new information and try to find out the most efficient solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo