                                                           Kanter, Doug
                                                           Stinson, Craig
                                                           Chow, Raymond
                          Spreadsheets

Excel 2.1 had a great feature that enabled me to replace every occurrence
of a particular font with another font.  Version 3.0 apparently did away
with this, forcing me to highlight each cell involved before changing
fonts.  This is fine if the cells I want to change are contiguous, but
that's usually not the case.  Is there an undocumented way to do what I'm
describing, using Version 3.0?

Doug Kanter Rochester, New York

PC MAGAZINE

Neither Excel 3.0 nor Excel 4.0 has exactly what you're looking for: a
Replace command that lets you search and replace on the basis of
formatting attributes.  Many word processors offer that service; to my
knowledge, no spreadsheets do.

You can achieve the font replaceability you're looking for, but it may
require a change in your worksheet-building habits.  The key is to use
styles.  You can do this in either Excel 3.0 or Excel 4.0.

Let's say you've just entered a series of column-heading labels and
formatted them in 12-point MS Sans Serif bold.  To make this font
"replaceable," click once on the Style box (the text field near the left
edge of Excel's standard toolbar, in either version) and type a name,
such as Headings.  This action creates a "style by example." The next
time you want to apply 12-point MS Sans Serif bold to a cell or range on
this worksheet, instead of going for Format-menu commands, click the
arrow to the right of the Style box and choose Headings from the list
that unfolds.

Applying your defined style to all examples of this formatting
combination gives you the freedom to change your mind.  If you decide you
want all instances of 12-point MS Sans Serif bold to be replaced by
12-point Times New Roman bold italic, all you have to do is redefine the
style.  To do that, pull down the Format menu, choose Style, and select
the name of the style you want to redefine.  Then click the Define
button, followed by the Font button. A normal Excel font dialog box will
appear.  Make your changes, click OK, and your font will be replaced
throughout the worksheet.

The styles approach to font replacement does force you to think ahead a
bit.  In return for that, though, you get the ability to use (and replace
at will) any font installed on your system, not just the four fonts that
Excel 2.1 offered.  More important, perhaps, you can also apply many
other formatting attributes--borders, patterns, numeric display formats,
alignment settings, and protection settings--to named styles and replace
them at will.

Text-Sorting Quirks

I recently discovered an interesting quirk regarding sorting in Lotus
1-2-3, Release 3.1.  According to the manual, Release 3.1's default
collating sequence is "Numbers First," which means that 1-2-3 will sort
your data in the following order (if you select an ascending sort):

1.  Blank cells

2.  Labels that begin with a space character

3.  Labels that begin with numbers

4.  Labels that begin with letters

5.  Labels that begin with other characters

6.  Values

However, there is an interesting exception to this order: 1-2-3, Release
3.1, puts labels beginning with an apostrophe (not the label prefix
character but a visible apostrophe) or a hyphen before any labels that
begin with a space! While this may not create problems for most users, it
results in some strange sorting of labels that include embedded spaces,
hyphens, apostrophes, or other punctuation  symbols.

For example, let's say you want to sort the following names:

Doe, John

Doe Jr., John

Doeman, John

If you perform this sort in Release 3.1, you will get this:

Doe Jr., John

Doeman, John

Doe, John

The separation of Doe Jr.  from plain Doe is probably not what you want.

Release 2.x treats the sorting order a bit differently.  It puts commas
before spaces and all alphabetic characters before the apostrophe.  The
same three names in 1-2-3, Release 2.x, would be sorted as follows:

Doe, John

Doe Jr., John

Doeman, John

This keeps the members of the Doe family together, which is probably more
desirable.  On the other hand, when it comes to names with embedded
apostrophes, Release 3.x's approach may be more satisfactory.  In Release
3.x, you get O'Brien before Owens, which is what you would see in a
properly alphabetized index.  In Release 2.x, Owens comes before O'Brien.

Both versions of 1-2-3 also provide an alternative "ASCII" collating
sequence that sorts all labels according to their ASCII values.  If you
use this option, you'll get the same results in both Release 2.x and 3.x.
 But names with internal capitals will be incorrectly sorted.  For
example, DeJohn, Jane will appear before Deans, Karen.

Raymond Chow Foxridge, Maryland

PC MAGAZINE

What Mr. Chow says is quite right.  Release 3.x (and also Lotus 1-2-3 for
Windows) sorts differently from Release 2.x, and the Release 3.x
Reference's description of its sort order (page 2-76) does not mention
the program's peculiar treatment of spaces and apostrophes.  Sorter and
upgrader beware!

A comparison of these two versions of 1-2-3 with Quattro Pro 4.0 and
Excel 4.0 reveals some other amusing disagreements.  Figure 1 shows how
the Does and Doemans would be sorted in each of the four major
spreadsheet products.  (This example assumes that all programs are set
for an ascending sort in "dictionary," as opposed to "ASCII," order.)

Simple combinatorial logic tells us that three distinct items can be
ordered in six different ways (ABC, ACB, BAC, BCA, CAB, and CBA).  Our
four major spreadsheet programs have laid claim to three of those
variations!

Users who have made the move from 1-2-3, Release 2.x, to Quattro Pro
should note that Quattro Pro in this case matches Release 3.x's sorting
algorithm, not Release 2.x's.  (In most cases, where there are
differences between Release 2.x and Release 3.x, Quattro Pro follows the
pattern of 2.x.)

When it comes to the sorting of embedded hyphens and commas, however,
Quattro Pro matches Excel and shuns both Lotus models.  Figure 2 shows
how each of our four spreadsheet programs would carry out an ascending
sort of the labels "Dow, Charles", "Dow theory", and "Dow-Jones".

As for the sorting of embedded apostrophes, Release 3.x and Excel both
put O'Brien ahead of Owens, while Quattro Pro and Release 2.x do the
opposite.  (Here, finally, we have the expected match of Quattro Pro and
Release 2.x; unfortunately, most indexers would consider both programs'
behavior incorrect.)

There are peculiarities in the handling of capital letters, too.  In a
dictionary sorting order, should a lowercase letter have higher or lower
priority than its upper-case counterpart? There may be no obvious answer,
but one would expect a program to have some consistent policy in the
matter.

Release 3.x does.  True to its documentation, Release 3.x always sorts a
lowercase letter before its capital equivalent.  Excel and Release 2.x,
on the other hand, leave the matter up to you.  If your list of names
includes Dejong and DeJong, these programs' sort commands will treat
these variants as exact equivalents and leave them exactly as they stand.

Quattro Pro also seems to regard the uppercase and lowercase variants as
equivalent.  But for some reason it prefers to flip them.  If you now
have Dejong before DeJong, an ascending sort will reverse the two names.
A second ascending sort will put them back in their original positions,
and so on.  Sorter beware!

One additional bit of trivia may be nontrivial to Excel upgraders:
Microsoft changed Excel's sorting order when it began shipping an interim
version of Excel 3.0: Version 3.0a.  Versions prior to 3.0a adhere to the
classic ASCII and ANSI sequence of punctuation symbols and alphabetic
characters.  That is, some of the punctuation symbols precede the
alphabet and some follow it.  Excel 4.0 uses a more rational sequence in
which all the punctuation symbols have higher priority than the
alphabetic characters.  If you're now working in Excel 4.0 and want the
program to behave as 3.0 did, you can add the line sort=2 to your
EXCEL.INI file.

One thing (and perhaps only one) should be obvious from this quick survey
of sorting quirks: Spreadsheets are not meant to be indexing programs.
They're dandy at putting numbers into correct sequence; for example, they
have no problem ordering numbers by their magnitude, regardless of how
the numbers are formatted.  But if you care about formalities, you should
treat any spreadsheet-sorted list of names as a starting point, not a
finished effort.

You can find authoritative information about how personal and place names
should be sorted (spelled out in thrilling detail) in The Chicago Manual
of Style.  After you've spent a few moments there, you may never again be
tempted to do an index of anything.  And you certainly won't worry about
whether your spreadsheet is more alphabetically correct than your
neighbor's. The Chicago Manual makes it quite clear: They're all wrong.

HINTS,  TIPS,  QUESTIONS

Share your latest spreadsheet discovery through PC Magazine's
Spreadsheets.  We'll pay you $50 for any tip we print.  Please submit
your letter on-disk, together with a printout.  Mail your contribution to
Spreadsheets, PC Magazine, One Park Ave., New York, NY 10016, or upload
it to PC MagNet.

Spreadsheet Sorting Order, Part 1

1-2-3 Release 2.x

Doe, John

Doe Jr., John

Doeman, John

1-2-3 Release 3.x and 1-2-3 for Windows

Doe Jr., John

Doeman, John

Doe, John

Excel 4.0

Doe Jr., John

Doe, John

Doeman, John

Quattro Pro 4.0

Doe Jr., John

Doeman, John

Doe, John

Figure 1: These examples show how four major spreadsheet packages sort
text labels containing spaces, commas, and letters.

Spreadsheet Sorting Order, Part 2

1-2-3 Release 2.x

Dow, Charles

Dow theory

Dow-Jones

1-2-3 Release 3.x and 1-2-3 for Windows

Dow theory

Dow-Jones

Dow, Charles

Excel 4.0

Dow theory

Dow, Charles

Dow-Jones

Quattro Pro 4.0

Dow theory

Dow, Charles

Dow-Jones

Figure 2: These examples show how the spreadsheet packages sort labels
containing hyphens, commas, and spaces.

              Journal:    PC Magazine  Nov 10 1992 v11 n19 p475(2)
              * Full Text COPYRIGHT Ziff-Davis Publishing Company 1992.
