Sorting issue with frozen panes

[Subtox]

When I freeze one or more rows, and then I click a single column header and
sort by that column, sometimes the frozen rows are included in the sort, and
sometimes they are not. I don't ever want them to be included, so I'm trying
to figure out what is causing this to happen and how to prevent it.

For example, say I have a "Name" column containing three rows: Tom, Dick,
and Harry. The top row (containing the header) is frozen. If I sort by that
column, I'd expect "Name" to stay in the top row, and the other rows to be
sorted Dick, Harry, and Tom. Sometimes this works exactly as I'd expect, but
other times the column header is included in the sort, so I'll end up with
rows sorted as Dick, Harry, Name, and Tom.

This can really mess things up when I have, say, 6 rows containing a sheet
title, user instructions, and header data frozen at the top and I don't want
any of these rows to be included when sorting. Can anyone tell me how to
control this?

 

[Gord Dibben]

Nothing to do with frozen panes.

If you have one header row.....row 1, simply tell Excel in the Sort process
that you have a header row.

To not sort the top 6 rows, do not include them in your sort range.

This means you have to select from row 7 to end of data rows.

Now sort with no header row.


Gord Dibben MS Excel MVP

 

[Subtox]

Thanks for the reply. I have done what you've said in the past, and it does
work -- selecting only row 7 onward, for example. But I am certain that at
times I have been able to simply click on a column header and sort by that
column, and Excel would just know to sort everything except the first six
rows. Like i said, this sometimes works and sometimes not, and I have no
clue why. It seems to be file-specific, where one file will always allow me
to sort like this, and another will never allow it. It doesn't seem to have
anything to do with the number of frozen rows, but maybe something to do with
how I formatted the rows or merged the cells together...?

Well, if it's not a real feature then I guess no one will be able to tell me
how to control it. I'll consider it an anti-bug that was awesome while it
worked. And from now on I'll select only the rows I want to sort.

This would be a great feature to have though, so you could just click on the
header and sort all the unfrozen rows. Or, to have a mechanism that
designates any number of rows as 'header rows' and therefore doesn't include
them in any sorting operations.

 

[Gord Dibben]

I agree that being able to select number of rows to be designated as a
"header row" would be nice.

I don't know how you managed to select a column and Excel uses the top 6
rows as a header.

If you merged cells in A1:A6 and tried to sort column A, Excel would give
you the "merged cells must be identically sized" message.


Gord

 

[Bob I]

I think OP is talking about Tables

I agree that being able to select number of rows to be designated as a
"header row" would be nice.

I don't know how you managed to select a column and Excel uses the top 6
rows as a header.

If you merged cells in A1:A6 and tried to sort column A, Excel would give
you the "merged cells must be identically sized" message.


Gord

 

[Gord Dibben]

How do you have 6 rows of headers in a Table?


Gord

 

[Bob I]

There can be 6 rows above where the table starts, that the OP is using
as "headers"

 

[Gord Dibben]

A "Table" will accept one row only for headers.


Gord

 

[Bob I]

Yep, the first one, but the OP seems to be "mis-rembering" things.

 

[Gord Dibben]

I would agree about the mis-remembering.


Gord

Yep, the first one, but the OP seems to be "mis-rembering" things.

 

[Subtox]

I'm not talking about tables. I'm talking about a plain, brand new
worksheet. Just open Excel and use the default blank worksheet. Freeze the
top few rows, create a few dummy values in column A and B, and sort by column
A. I know there are times I have done this where it would correctly expand
the selection and sort by only column A without including the frozen rows in
the sort.

My only reason for posting here was that it didn't work consistently, and I
wanted to know if it was a fluke or if I was doing something inconsistently.
I'm chalking it up to a fluke, although you can consider it mistaken
recollection on my part if that makes it easier to explain. In any case,
I'll forget it ever happened.

 

[Gord Dibben]

I will have to agree that it was a fluke.


Gord

I'm not talking about tables. I'm talking about a plain, brand new
worksheet. Just open Excel and use the default blank worksheet. Freeze the
top few rows, create a few dummy values in column A and B, and sort by column
A. I know there are times I have done this where it would correctly expand
the selection and sort by only column A without including the frozen rows in
the sort.

My only reason for posting here was that it didn't work consistently, and I
wanted to know if it was a fluke or if I was doing something inconsistently.
I'm chalking it up to a fluke, although you can consider it mistaken
recollection on my part if that makes it easier to explain. In any case,
I'll forget it ever happened.