Sort/Header

  • Thread starter Thread starter Minkx
  • Start date Start date
M

Minkx

Many times I will sort data and the sort command automatically recognizes the header.
I have a new sheet that Sort does not recognize the header so it mixes the header in with the dataset.
How do I identify or mark row 1 as my header for a data table?
Thanks,
Minkx
 
You need to make it distinguishable from the data to help Excel guess
that you have a header, so format it with Bold, or a different
background colour, or a larger font size.

Hope this helps.

Pete
 
You can help XL automatically recognize a header row by doing anything that
changes that row from the rest of the datalist - bold, larger font size,
different color.

Primarily, just check the box at the bottom of the initial sort window which
stipulates that you *do* have a header row.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Many times I will sort data and the sort command automatically recognizes
the header.
I have a new sheet that Sort does not recognize the header so it mixes the
header in with the dataset.
How do I identify or mark row 1 as my header for a data table?
Thanks,
Minkx
 
Does this work in compatibility mode? 'Cuz it ain't working for me at all. I changed the font & made it bold with a blue backgound. But it still jumps into the sort with the rest of the data. I know about the check box, but a simple sport A->Z shouldn't require I open the dialog box and click, click, click, click, click....
You can help XL automatically recognize a header row by doing anything that
changes that row from the rest of the datalist - bold, larger font size,
different color.

Primarily, just check the box at the bottom of the initial sort window which
stipulates that you *do* have a header row.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Many times I will sort data and the sort command automatically recognizes
the header.
I have a new sheet that Sort does not recognize the header so it mixes the
header in with the dataset.
How do I identify or mark row 1 as my header for a data table?
Thanks,
Minkx
 
In microsoft.public.excel on Sat, 10 Nov 2007, RagDyeR
You can help XL automatically recognize a header row by doing anything that
changes that row from the rest of the datalist - bold, larger font size,
different color.
Click to expand...

I always wondered why excel sometimes seems to recognise header rows,
and not at other times!
 
You may want to try feezing the panes so that row 1 (or where ever the headers
stop) is always visible.

ps. You may have noticed that this is a plain text newsgroup. HTML posts and
attachments aren't appreciated by most people.
 
Do I read into your post that you use the sort icons on the toolbar?

I strongly suggest that you *never, never, never* use those icons.

XL may be right most of the time, but as you may be experiencing, *not all*
of the time.

In fact, I have removed those icons from the toolbars of all the machines in
all of my plants.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
Does this work in compatibility mode? 'Cuz it ain't working for me at all. I
changed the font & made it bold with a blue backgound. But it still jumps
into the sort with the rest of the data. I know about the check box, but a
simple sport A->Z shouldn't require I open the dialog box and click, click,
click, click, click....
You can help XL automatically recognize a header row by doing anything
that
changes that row from the rest of the datalist - bold, larger font size,
different color.

Primarily, just check the box at the bottom of the initial sort window
which
stipulates that you *do* have a header row.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Many times I will sort data and the sort command automatically recognizes
the header.
I have a new sheet that Sort does not recognize the header so it mixes the
header in with the dataset.
How do I identify or mark row 1 as my header for a data table?
Thanks,
Minkx
 
If you highlight the entire data range (incl headers) you should be
able to tell XL that the first row is a header, even if it isn't
bolded or colored in some way. When you choose Data|Sort there should
be an option "My list has" -- you should choose "Header Row"


HTH,
JP
 
Thanks for the notice of this being a plain text NG. I was not aware of
that. I will adjust my settings accordingly.
Freezing the panes does not stop the sort from including that row in the
sort.The resulting sort places data in that row.
Any other ideas?

You may want to try feezing the panes so that row 1 (or where ever the
headers
stop) is always visible.

ps. You may have noticed that this is a plain text newsgroup. HTML posts
and
attachments aren't appreciated by most people.
 
If you can't get excel to guess correctly that you have header rows, maybe you
can use a macro to sort your data--if you always sort by a certain sequence of
fields, it may even work.

I don't recall you posting the version of excel you're using, but in xl2003
(maybe xl2007, too), you can apply data|filter|autofilter and use the dropdown
arrow to choose to sort that table by that field/column.

Or you may like a macro that uses invisible rectangles over the headers that
allows you to sort your data based on the column of the rectangle that you
clicked.

If you want to try that, check out Debra Dalgleish's site:
http://contextures.com/xlSort02.html

ps. I never trust excel to guess what I want sorted. I always select my
complete range and use data|sort and specify if I have a header row.
 
Have you actually:
Selected your *entire* datalist, *including* the header row,
Clicked on <Data> <Sort>,
And under "My List Has"
Checked the <Header Row> option,
And notice that XL *de-selects* the header row,
And then complete the sort?

Have you done this?
 
I have and this works find. But I have several different fields that I am
sorting between. I have a number of other files that work just fine. i.e.:
select any cell in a column | click the Sort A->Z and XL knows there is a
header row. This one file does not play well with Sort and includes the
header as part of the dataset. Very frustrating. There must be a way to set
a header in XL without going through the Custom Sort dialog box and checking
My data has headers.
Thanks,
Minkx

Ragdyer said:
Have you actually:
Selected your *entire* datalist, *including* the header row,
Clicked on <Data> <Sort>,
And under "My List Has"
Checked the <Header Row> option,
And notice that XL *de-selects* the header row,
And then complete the sort?

Have you done this?
Click to expand...
 
What's the difference between the Toolbar and the Ribbon? I'm using the
ribbon - I presume we are talking about the same button.
Minkx
 
I have and this works find. But I have several different fields that I am
sorting between. I have a number of other files that work just fine. i.e.:
select any cell in a column | click the Sort A->Z and XL knows there is a
header row. This one file does not play well with Sort and includes the
header as part of the dataset. Very frustrating. There must be a way to set
a header in XL without going through the Custom Sort dialog box and checking
My data has headers.
I use the sort function ALOT. I want this to work properly and as expected
or to know what I have to do to make the header row a header row.
I can also highlight the whole worksheet except the header and go through
the sort dialog without the My data has a header. BUT that does not make
sense! If I select nearly any other function, once I indicate the header it
should always be my header. It's not "sticking".
 
So, it sounds like you're using XL07.

I would say it seems that this is *another* one of the items that MS
*hasn't* fixed in this newest version.

SO ... apparently, I can still stick to what I said about *never* allowing
XL to choose the sort range ... no matter which version you're using!
 
I understand that the sort button usually works, but the results are
unpredictable because you are asking Excel to make guesses about your
data. Unless you are ruthless about keeping everything in a tabular
format, no blank rows/cells, bolding the first row, etc, eventually
the sort button will fail you. You can guarantee success by always
selecting your entire data range first.

If you are good with the keyboard you can try Alt-D,S to quickly get
to the Sort menu, or record some macros with your favorite sorting
routines. For example you could assign the macro below to a toolbar
button.

Sub SortAscending_Header()
Cells.Sort Key1:=ActiveCell, _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub
(taken from http://www.mvps.org/dmcritchie/excel/sorting.htm)

Just select the header cell in the column that you want to use for the
sort before running.


HTH,
JP
 
Back
Top