Table Not sorting properly

Question

I have an excel spreadsheet that contains ~70 sheets. I then have 1 page which has all of the information from the sheets on it by doing =(cell on sheet 1). This works flawlessly for about 80% of the cells, but i have a few that just will not sort.

I have confirmed that they are all formatted as numbers and seem to all be exactly the same.

Thank you ahead of time for your help. thank you!

Answer 1

Hi Charles,

What you’re describing is most often caused by some values that look like numbers but are actually being treated as text by Excel. When that happens, Excel sorts them alphabetically instead of numerically, which leads to results like 1, 10, 100, 2, 20 instead of 1, 2, 10, 20, 100.

This can happen for a few common reasons:

• The source cell contains a number stored as text
• There’s a leading apostrophe (for example '123)
• The cell contains hidden spaces or non‑printing characters
• Since the summary sheet is pulling values from other sheets, the issue often originates in the source sheets, not the summary sheet itself

A quick way to confirm this is to test one of the problematic cells with: =ISNUMBER(A2)

If this returns FALSE, Excel is not recognizing the value as a true number.

For a practical workaround, you can add a helper column next to the affected data and use: =VALUE(TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))))

Then sort by that helper column. If the sort behaves correctly, that confirms the issue is caused by text values or hidden characters being carried over from the linked cells.

It’s also a good idea to check one of the original source cells directly. If the value on the source sheet is stored as text, any formula like =Sheet1!A1 will carry that issue forward. In that case, you can adjust the link to force numeric conversion, for example: =VALUE(Sheet1!A1) or =--Sheet1!A1

Since you’re working with around 70 sheets, fixing each formula manually could be time‑consuming. While Paste Special > Multiply can sometimes help convert text to numbers, it should only be used on cells that already contain values. Not formulas because applying it directly to linked formulas can overwrite them or return errors.

Please let me know if this proves useful to you, or if you would like further assistance.

I'm looking forward to your reply.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment”.   

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Some cells not sorting with the rest of a column are usually caused by subtle data differences, even when they look identical. For the scenario described, check specifically for these issues in the “problem” cells and their source cells on the 70 sheets:

1. Mixed number/text types in the same column
   Even if the format is set to Number, some entries may still be stored as text.
   • Select the column.
   • Press Ctrl+1 → Number tab → under Category, choose Number (or General).
   • Re-enter one of the problematic values after changing the format and try sorting again.
   • If some values still sort incorrectly, they are likely stored as text; fix them as below.
2. Numbers stored as text
   If some cells are numbers and others are numbers stored as text, Excel sorts all true numbers first and then text values.
   • In the column, look for values left-aligned (typical for text) vs right-aligned (typical for numbers).
   • To convert text numbers to real numbers, reformat the cells to Number or General (Ctrl+1), then retype or use a helper conversion method as described in Fix text-formatted numbers by applying a number format.
   • After conversion, apply the sort again.
3. Leading or hidden spaces in the source sheets
   If the referenced cells on the 70 sheets contain leading spaces, the values pulled into the summary sheet will also have those spaces and sort differently.
   • In the summary sheet, insert a helper column next to the problematic column and use =TRIM(reference_cell) to remove leading/trailing spaces, as suggested in the TRIM function.
   • Copy the helper column and paste values back over the original column.
   • Sort again.
4. Hidden rows or columns affecting sort
   Hidden rows are not moved when sorting rows, and hidden columns are not moved when sorting columns.
   • Unhide all rows and columns on the summary sheet before sorting.
   • Then reapply the sort.
5. Locale and heading settings
   • Confirm the sort is applied to the full range, not just part of the column.
   • In Home → Sort & Filter → Custom Sort, verify whether My data has headers is correctly checked or unchecked so the first row is treated properly.
   • If working with text-like numbers and a non-default locale, check the system’s Regional Settings to ensure the expected sort order.

If the above are corrected (especially mixed number/text and leading spaces) and the sort is reapplied, the previously “unsortable” cells should fall into the correct order with the rest of the column.

---

References:

• Sort data in a range or table in Excel
• Use a screen reader to sort or filter a table in Excel