My excel spreadsheet won't sort/alphabetize properly

Question

I have a spreadsheet with multiple columns and over 4000 rows. I need to sort, then alphabetize my report and everytime I do, there are 43 rows that do not sort with the rest of the report; they go to the top or bottom of the report and are alphabetized as a separate group. I've tried deleting them and re-entering them and trying all over to get them to alphabetize with the rest of the report but they continue to stay separate. Why is this happening? Any help will be appreciated.

Answer 1

Sounds like those 43 rows each has a column with a leading blank. Blanks precede printable characters in common sorting algorithms. That would account for the behavior you describe.

Answer 2

I just encountered a similar issue. The answer ended up being that some of my cells in the sort column were entered as text, and some were vlookups from another page. The cells with vlookup formulas were not sorting properly either through a filter or through a sort. I copied the spreadsheet and pasted as values over the top of it and it sorted properly after that (although I lost they dynamic nature of the lookups.

Answer 3

This can also happen if you create a filter, then add new data. Try removing the filter and sorting again.

Answer 4

The most common reason for data not sorting correctly is due to the leading space ahead of the text. Many people using encounter this problem. The text with leading space is sorted at the top in ascending and at the bottom in descending order sort. Try correcting this, and it will work.

Answer 5

This just happened to me - sometimes when you cut and paste information there are additional blank rows in the data and when you sort it will only sort to the blank rows. Go through and find the empty rows and delete them and then try and sort again.

Answer 6

This usually happens when the field have blank space in front. Try removing blank preceding space. i have also encountered with this situation few weeks back, and solved by removing blank space.

Answer 7

Excel's sort function does not always work as expected when rows are grouped together. If you are sorting on grouped rows, try un-grouping the rows and sorting again.

Answer 8

If you've reentered the data it is likely you've gotten rid of blanks. It is possible the rows are not included in the sort because they are not selected. If those 43 rows are not included in the sort grouping, they will not be sorted in the order you choose and will appear in their own group. Verify they are being included and retry the sort.

Also, make sure you don't have another conflicting sort or filter which is causing your issue.

You've not told us what type of data you are using. Mixed data can cause all kinds of issues with sorting. If it is mixed, it should be formatted as text. Here is more info about how to troubleshoot sorting.

Answer 9

I copied and pasted my data into a new sheet, without the headers. It sorted properly. Be sure to save your work prior to trying anything new!

Answer 10

This happened to me, I had to cut the rows that wouldn't sort and paste them into the data that WOULD sort. Then sorting them seemed to function.

Answer 11

You might have some of your rows in a table and some not. If you have banded rows, then those are probably in a table. Check the Design tab, Properties group, click Resize Table and make sure the range covers all of your rows.

Answer 12

My data had been copied to a new worksheet, and then at some point copied again. There were no visible filters and it sorted correctly until I added two rows of data. At that point, unless I sorted from elsewhere in the worksheet instead of from the headers, it missed two rows. I finally went to the Advanced filers dialog box and saw that a range had been entered at some point and it carried over, even though there was no remaining filter. Baffling. When I updated the range to include my two additional rows, it sorted correctly. I cannot clear the Advanced filter setting, so have learned to work with it as I add rows. I believe if I copied the data to another worksheet without the headers, I might solve the problem. An experiment for another time.

Answer 13

In my case some of the formulas had references to the sheet even when that cell was on the same sheet. I removed those and it worked. Never had a problem with that before though.

Answer 14

I had this problem and after check for leading spaces and getting rid of the filters, I finally solved it by copying the non-sorting cells and paste special them as "unformatted text" into Word and then copying and pasting them back into Excel. No idea how they picked up some kind of un-sortable formatting as they appeared normal.

Answer 15

My solution:

I was sorting by date, and found that some of my dates didn't make sense (ie: February 31st)

Perhaps some of your values are similarly nonsensical.

Answer 16

I tried all the solutions here and none of them worked for me. What eventually did was to UNFREEZE PANES that I had previously frozen.

I've never had that problem with frozen panes before but it was fixed immediately when I unfroze them, so I know that's what it was.

Never a dull day with Excel!