excel won't sort the first row in a set of selected rows

Question

Hi,

I'd like to sort some data in an excel spreadsheet alphabetically, according to the content of the second column.

Specifically I have a sheet with stuff from A1 to N29, and I'd like to sort rows 2 to 27 according to column 2

Row 1 (the header), 28 and 29 should stay where they are.

I tried  selecting column rows 2:27 and hit "sort". Excel asks me whether I want to expand my selection, and I say yes.

Then excel does the ordering, but row 2 stays where it is, despite it should not.

I thought excel believed that row 2 is the heades. However I also tried selecting rows 1:27, and the result is the same. Neither row 1 (which I like) nor row 2 (which I do not like) move.

What am I doing wrong?

Thanks a lot for any insight

Francesco

Answer 1

With no examples of the data, itself, I can't be certain, but it sounds like the content of cell B2 simply comes first alphabetically - even though it may not look like that's the case. What happens if you sort the same range in Z-A order rather than A-Z - does the content from B2 go to the bottom of the list?

However, when you do as you're doing, Expand the selection applies vertically as well as horizontally, so rows 28 & 29 are being included even though you indicated that don't want them to be. If you want to exclude rows 1, 28 & 29 I'd suggest that you select A2:N27, tab as necessary to have a cell in column B as the active cell in the range, then sort in alpha order.

Another option would be to insert some rows between 27 & 28, sort the primary range, then delete the extra blank rows.

Answer 2

Hi Bob,

 thanks for your help. Your suggestion about not expanding is very interesting. I was actually wondering how one does that.

I just tried. Here is what I did:

• I selected A2:N27 and tabbed to cell B2.
• I hit the sort button

Here's what happened

• the selection area spontaneously changed to A3:N27. I have no idea why.
• the rows 3-27 sorted the right way
• the selection area spontaneously went back to A2:N27.

Please note that each cell in column B (the one that decides the sorting order) only contains a single uppercase letter (either C or D). For some reason cell B2 (which contains a C) is excluded from the sorting procedure.

The cells in column B were originally "General". I tried converting them to "text" but that did not change the above behavior.

Thanks again

Francesco

Answer 3

Follow directions in this screenshot:

If there is a particular Column or Row Header you want the sort based on, make sure you that you choose that row or column header. 

_________

Disclaimer:

The questions, discussions, opinions, replies & answers I create, are solely mine and mine alone and do not reflect upon my position as a Community Moderator.

Answer 4

Hi Philip,

I think that the window you posted (which I get only if I choose "custom sort" from the dropdown menu inside the sort "button") is just for selecting the sorting criteria.

The criteria I'd select are the default ones: "sort by value" ("cell color", "font color" and "cell icon" are the other possibilities, which I'm  not interested in) and "A to Z" or "Z to A" (I'm interested in alphabetical sorting, not custom).

Anyway, I tried going through that window, but the behavior is exactly the same as I described above: the first row (row 2) is for some reason excluded from the selected range.

This actually happens before my very eyes: after I hit "OK" in the above window (or simply hit the "Sort A to Z" button ) the highlighted area is reduced, excluding row 2, the sorting happens, and the highlighted area goes back to what I originally selected. Beats me why.

cheers

Francesco

Answer 5

See what happens if you:

1. Select B2
2. Go to Edit> Clear> All
3. Re-enter the "C"
4. Then select & sort the range

However, I'm a bit confused by your expectation based on your description of the data, though. If column B only contains the letter C or D & the content of B2 already is a C, why would you expect anything to change in that row when the data is sorted in alphabetical order based on that column? Likewise, if I sort a range in numerical order based on a column that contains positive integers with 1 in the top cell, that record will remain as is in the top row regardless of how many other records may contain the number 1 in that field. To have any different result would require at least one additional sort key.