stop sorting titles please

[CC]

I have an excel document with 35000 rows and 125 columns of information. I
am sorting in almost every title column in multiple formats. is there a way
to sort these with out always having to notify the sort I have headers?

I want to click AtoZ or ZtoA and just go with out taking all the extra steps
to manage the headers.

 

[PY & Associates]

I have an excel document with 35000 rows and 125 columns of information.  I
am sorting in almost every title column in multiple formats.  is there a way
to sort these with out always having to notify the sort I have headers?

I want to click AtoZ or ZtoA and just go with out taking all the extra steps
to manage the headers.

Can you insert blank row in row2 please?

 

[Kevryl]

Suggestion - Here's my trick: Name the range you want to sort, excluding the
titles. Record a macro and create a macro button to perform the sort for you.
I'm on Excel 2000 still, so if I tell you the exact steps I use it probably
will be different for you.

Now be careful - there's a potential problem: Adding rows at the top or
bottom later on will likely result in those rows being outside the named
range and being omitted from the sort. To prevent that happening, you can
insert a blank row at the top and bottom of the range, reduce their height to
very narrow and shade them with (say) grey (be sure to INCLUDE the shaded
rows in the range when derfining the range name). You'll then always know to
add any more rows BETEEN the shaded rows. Those shaded rows will never be
used for your data, but by entering "AAA" or "ZZZ" in each cell - or just the
sort keys - in those rows (and formatting their text colour to the same as
the shading so its tidy and not visible) the shaded row will always sort to
the beginning or end of your sort.

Let me know if you need any more explanation,
Cheers

 

[Kevryl]

I assume your headers are in Row 1, so yes, you'd make row 2 your blank row,
that is, blank until you type "AAA" into each sort key (or each cell). The
"AAA" will ensure that the row always stays at the top if you are sorting
alpha data. I should have made the point if you are sorting numerical data
that you should instead of "AAA" use a numerical value that will be much
larger (for descending sort) or smaller (for ascending sort) than anything
you would be likely to be sorting. If you need to sum the columns, be sure to
cancel those two rows out by contra-ing the positive value with the same
negative value, thus eliminating the need to deduct the "dummy" values in the
Sum formulae.

EG: Column A
Row 1 Header
Row 2 999,999,999 (narrrow & shaded)
Row 3-99 all your data
Row 100 -999,999,999 (narrrow & shaded)
Row 101 "=sum(A2:A100)

Range Name, say, A2-Z100 = "SortArea"

Save a copy of the file before you run the sort ;-)

By the way, its not a bad idea, if you have any formulae that rely on
testing for a zero value to limit the decimal places with a function such as
"Round". Spreadsheets do funny things after about a zillion decimal places,
but its enough to through a zero test off.

Cheers