Excel will not sort all columns on my worksheet

Question

I have a very basic worksheet with multiple columns and rows. I usually will sort one column to sort a-z and normally all data for those rows would follow the sort. Suddenly I am finding that if I sort one column, the remaining columns do not sort, even though I choose the "Expand The Selection". I have also noticed that after sorting, the worksheet is split into one set of columns on the left side that did sort, another set of columns on the right did not follow the sort. It is split. I have about 45 columns and 150 rows so the worksheet is particularly large.

Completely befuddled.

Answer 1

I have found in some circumstances that the table simply needs to be resized.

Follow these steps and see if they help:

1) click anywhere within your table

2) click Design

3) at the far left of the ribbon, click Resize Table

4) fix the data range so that the entire table is within the range, i.e. if the range says, C1:E114, and the A and B columns through row 114 should be included in range, adjust the range to A1:E114.

Hope this helps when all else fails.

Answer 2

This happens when all the column is not a part of one table, even though it is within one sheet.

1. Ctrl + A
2. Clear all filters or sorting. Home tab -> Sort & Filter -> Clear
3. Make sure that the headings are only present in the first column.
4. Select the complete table region only.
5. Home tab -> Format Table As -> Choose any of the options and check the heading available when prompted.
6. Try sorting it either ways and it should work for all columns.

Answer 3

Excel automatically tries to find your total data range, and it stumbles over blank fields. So if your cursor is in a line where there a blank fields in some columns, Excel could miss the columns behind the blanks fields (and similar for blank rows). This is still true when you chose 'extend'.

One solution is to 1. use Autofilters, that will make Excel remember what is in your range; another is to 2. put the cursor in a field where there are no blank fields in the row (and column); a third way is to 3. select the whole range yourself; and of course, the problem goes away if you 4. don't have blank cells anywhere.

I recommend the Autofilter solution if it doesn't bother you otherwise, as it makes sure that you don't accidentally sort some day a part only because you forgot to do 2. or 3.

Answer 4

In my case it was because I had empty hidden columns between the content columns and for some reason Excel was considering them as some sort of separator. For example if I clicked Ctrl + A while having a cell selected, it would only select the columns between the nearest empty columns.

In order to fix this, remove the empty columns.

Answer 5

If you selected yourself the range of cells, and it does not sort properly, you may have created tables within what you consider your table. To find out:

1. Go right above the first line, on the top left, where the cell number appears (F16, M34, whatever)
2. Select the arrow going down.
   • Case a) nothing appears, it is all blank, then just try the solutions in the other comments
   • Case b) some stuff appears. If so, these are tables, you need to get rid of them (it does not delete your cells)

To get rid of the tables:

1. Click on each of the lines that appeared (the ones that appeared in case b). Basically, when you click on one, a bunch of cells are highlighted.
2. Right click on any of the highlighted cells (basically you are right clicking on the whole range anyway)
3. Go to table and delete the table (if "table" is not part of the menu when you right click, you did not have any table created)
4. Repeat these steps for all of the tables (i.e., for each line that appeared when you clicked on the drop down menu next to the cell name)

You can now happily sort your data.

Answer 6

Does your data have filters? If so, select header row with filters, then remove all filters (right click, Filter, clear filter). Then select header row again, and add filters back. After doing so I was able to sort my data and all columns sorted. Hope this helps!

Answer 7

You most probably added new columns after filtering former columns, so new column headers "have NOT filter signs". Excel exclude these columns when you filter. So, clear filtering by clicking on Sort$Fliter>Filter, then add the filter sign to "all" columns again by hitting the Filter button again. There you go!

Answer 8

Replying to this in 2024 because this was the first post I encountered when searching for a solution to this.

I'm unfortunately using Excel for Mac (v 15.32) and was experiencing the same behaviour, but Excel doesn't provide a 'resize table' option on Mac.

The cause of my sorting issue was that any totally empty column in the table breaks the 'expand the selection' option—Excel will only sort from the selected column UP TO any empty column in either direction (and not beyond that). Simply putting a random letter in one cell in the empty column resolved the issue and now the whole table sorts correctly.

Hope this helps future users.

Answer 9

I have a solution. U have to have Sort column next to your data and it will do the right thing. Hope will help u.

Answer 10

Excel puts blanks at the bottom when you sort.

1. Ctrl-G (Go to)
2. Click Special
3. Check Blanks, click OK

All blanks will now be selected. Select a background color to change all blank cells.

Now you can sort by color and have blanks appear at the top.