-------------------------------------------------------------------
THE OFFICE LETTER
STANDARD EDITION
Tips, Tricks, Tools, and Techniques for Microsoft Office
Volume 4, Number 21 November 8, 2004
-------------------------------------------------------------------
IN THIS ISSUE
1) Excel: Finding Duplicates with Conditional Formatting
2) Word: Creating the Index
3) Word: Double-Space Shortcuts
4) Last Week for Free Software Offer
5) Premium Edition Extra: Word Index Tricks
-------------------------------------------------------------------
1) EXCEL: FINDING DUPLICATES WITH CONDITIONAL FORMATTING
-------------------------------------------------------------------
An Office Letter reader wrote to ask if there is a simple way to
find duplicates in an Excel list. Of course, with the list sorted,
you could manually search through the values, looking for
duplicates, since they'd be in adjacent rows, but that's tedious
for all but the shortest list.
I went looking for techniques for highlighting duplicates, since
you most likely will want to inspect data in the remaining columns
of each row to see which of the duplicate values should actually be
removed.
After reading some solutions on the Web (most of which were
complicated and 2 or 3 pages long), I decided there had to be a
simpler solution.
It isn't particularly elegant (or inelegant, for that matter), but
I think it's a good (and quick) solution for long lists. It
highlights duplicate values; you'll have to select which duplicate
is deleted. The technique uses conditional formatting to find and
highlight the duplicates.
Assume we have a column (column A of the worksheet) of e-mail
addresses. Cell A1 has the column heading "E-mail address." We
want to find duplicates in cells A2 through the end of the data so
we can send a message to a recipient only once -- a process called
"de-duping a list."
Begin by selecting the first data cell in the column: A2. Use the
Format/Conditional Formatting command.
In Condition 1, select: "Cell value is" from the first pulldown
box. In the second box, choose "equal to." In the third box,
enter
=A3
Click the Format box and choose a font style of bold and the color
red. (This makes the data stand out.) Click OK twice to return to
the worksheet.
Thus, for cell A2 we're asking Excel to turn the cell's contents
bold red if the value in cell A2 is the same as cell A3.
Now copy the formatting from cell A2 through the end of the data.
(If your data consumes every single row of a worksheet, then copy
through to the end of the data minus one row, since you can't
compare the value in the last row to the "following" row, since
you've run out of rows.) To do this, use the Edit/Copy command in
cell A2, then select cells A3 through the end of the data and use
the Edit/Paste Special command (and choose Formats in the Paste
section).
Of course, this technique only highlights the first of two
duplicate records. You can expand on this technique by adding a
second condition to the conditional formatting: choose cell A3, for
example, and enter condition 2 as "Cell value is," "equal to", A2
and make the format red and bold. That is, in addition to checking
the following cell (as condition 1 does), check the previous cell
for a match. That way, you'll always highlight a duplicate cell.
Copy this new formatting to the remaining cells.
The technique assumes that your list is in sorted order. If not,
you'll obviously have to use the Data/Sort command.
If you know of a better way, please let us know:
[EMAIL PROTECTED]
-- James E. Powell
-------------------------------------------------------------------
SPONSOR: GET THE WHOLE ENCHILADA!
-------------------------------------------------------------------
The new Camtasia Studio Add-in records all the richness of your
PowerPoint presentation. Preserve all animations, transitions, and
sound effects -- even your live audio and on-the-fly annotations!
Share your recorded presentation in the format that best suits your
audience, including Web site, CD, Flash or streaming video.
To find out more and download a free trial, visit us at:
http://www.officeletter.com/red/camtasia.html
-------------------------------------------------------------------
2) WORD: CREATING THE INDEX
-------------------------------------------------------------------
For the last two weeks we've discussed techniques for marking which
text within your document will be indexed. We've explained how to
create cross-references, how to format the entries, and how to
build entries individually or using an automatic marking technique.
Now it's time to create (compile) those index entries into an
index.
TO CREATE AN INDEX
1. Move to the location in your document where you want the index
to appear.
2. Use the Insert/Index and Tables command (Word 97, 2000) or
Insert/Reference/Index and Tables (Word 2002, 2003).
3. Select the Index tab. Be sure the Language option is correct.
4. Choose the appropriate type: Indented puts each sub-entry on a
separate line; sub-subentries also get a separate line; each entry
is indented. The Run-in option lists all sub-entries one right
after the other; it may save space but your readers will probably
find it difficult to use.
5. Chose the Columns option. The field value is set to 2 by
default. Select from 1 to 4, or use "Auto" to use the column
formatting from the document itself. Note that you can always
generate an index, then select the index and use the Format/Columns
command to change the index layout.
6. By default, Word uses a comma to separate an index entry from
the page number. Assuming you selected the Indented index type
(step 4), you can right-align page numbers by checking the "Right
align page numbers" box. This enables the Tab leader pulldown
menu, where you can select the leader character -- spaces, periods,
dashes, or underlines.
7. Select a predefined format from the Formats pulldown list. Word
displays the formatting in the Print Preview window. The principle
difference is how the headers for the index ("A", "B", "C", etc.)
are formatted. The Modern template also changes the character
separating an index entry from the page number.
8. Click on OK to create the index.
If Word now displays the field code {Index} instead of the index,
click on the Hide/Show button in the toolbar (the one with the
paragraph symbol -- a backward "P" with two vertical lines -- on
its face) or Alt + F9.
UPDATING YOUR INDEX
If you revise your document, you may need to regenerate your index.
Fortunately, it's simple. Move to any spot within the generated
index. Now either:
-- press F9
-- right-click your mouse and choose Update Field
REMOVING AN INDEX
It's also simple to remove an individual index entry: display the
field codes (press Alt + F9) and delete the {Index} field entry.
-- James E. Powell
-------------------------------------------------------------------
SPONSOR: WORDTOYS SUPERCHARGES WORD FOR FREE
-------------------------------------------------------------------
WordToys gives you dozens of utilities that make Word easier to use
and more fun to work with. Create unique graphical Favorite Fonts,
Favorite Symbols, and Favorite Bullets menus. Insert any accented
character. Select the parts your mouse can't reach. Backup while
you save. Copy/paste any format. Print to any printer. Lots more
goodies for Word 2000, XP, and 2003. Best of all: WordToys is FREE.
No nag screens. No time limits.
Download now from http://www.wordtoys.com
-------------------------------------------------------------------
3) WORD: DOUBLE-SPACE SHORTCUTS
-------------------------------------------------------------------
Formatting your text to double space is set using the
Format/Paragraphs option. Choose the text to be formatted in this
way (or press Ctrl + A to select the entire document), then:
1. Use the Format/Paragraph command from the main menu.
2. In the Spacing section (in the middle of the dialog box), choose
the Line spacing option from the drop-down menu. (Shortcut: Press
Alt + N, then D, then the Tab or Enter key.)
>From the keyboard there are several shortcuts available for
changing line spacing. Select the text you want to change, then
press:
Ctrl + 1 for single spacing
Ctrl + 5 for line-and-a-half spacing (1.5 spacing -- hence the
"5" in the shortcut)
Ctrl + 2 to turn on double spacing
You can add toolbar buttons for these three spacing options. Use
the Tools/Customize command from the main menu, click on the
Commands tab, choose the Format category, scroll down the Commands
list (the spacing commands are about half-way down), and drag the
icons to any toolbar area.
If you copy double-spaced text to another document, using the
Edit/Paste command will paste in double-spaced text. To keep the
formatting (single-spaced, for example) of the destination
document, use the Edit/Paste Special command and choose the
"Unformatted text" option.
-- James E. Powell
-------------------------------------------------------------------
SPONSOR: EASY-TO-USE WEB HOSTING FROM $9.95
-------------------------------------------------------------------
All plans include 24/7 technical support, unlimited e-mail, state
of the art web site builder, control panel admin, FrontPage and ASP
support. Domain names just $12.95. No contracts required, and
we'll set you up for FREE in under 20 minutes. Sign up today at:
http://www.officeletter.com/thehostgroup.html
-------------------------------------------------------------------
4) LAST WEEK FOR FREE SOFTWARE
-------------------------------------------------------------------
Last week we announced a limited time offer -- a special bonus for
Premium Edition subscribers of a free copy of ActiveWords SE, a $20
value.
Response has been extremely positive, but the offer must end on
Friday, November 12. To learn more about the offer, check last
week's issue: http://www.officeletter.com/previous.html. To
subscribe to the Premium Edition ($12/year) and receive access to
all back issues, keyword search of our archives, extra content each
week, HTML delivery, and no ads, visit:
http://www.officeletter.com/subscribe.html
-------------------------------------------------------------------
SPONSOR: WORD ADD-ON FOR CD AND DVD JEWEL CASE INSERTS AND LABELS
-------------------------------------------------------------------
Create your own beautiful and professional CD and DVD jewel cases
and CD labels --even DVD or VCD labels -- in just minutes! Perfect
for business, home, or hobby. Works with any type of paper or CD
label including centered label sheets. It's easy to make both front
and rear CD jewel case covers with side spines as well as CD labels
-- all in one package!
http://www.amfsoftware.com/affilcd.pl?affil+10057
-------------------------------------------------------------------
5) PREMIUM EDITION EXTRAS THIS WEEK
-------------------------------------------------------------------
This week Premium Edition subscribers are learning two special
tricks for Word indexes: how to index only a portion of a document,
and how to alter the format of the index headings.
Why not subscribe to the Premium Edition today for just $12/year?
HTML format, extra content, access to all back issues, a fast
search engine, a printer-friendly format option, and no
advertising! Visit:
http://www.officeletter.com/subscribe.html
-------------------------------------------------------------------
CONTACT POINTS
-------------------------------------------------------------------
SUBSCRIPTION CHANGES
Your subscription record shows the following:
Email address: [EMAIL PROTECTED]
First name: subscriber at [EMAIL PROTECTED]
Last name:
Full name:
If you would like to edit the above data, click here:
http://www.office-letter.com/cgi-bin/mmp/[EMAIL PROTECTED]
To unsubscribe, visit:
http://www.officeletter.com/sub/substdremove.html
and enter your e-mail address.
SHARE YOUR TIPS: Send them to [EMAIL PROTECTED] and include
your permission to acknowledge you by name.
FEEDBACK: Send your suggestions and comments about The Office
Letter to [EMAIL PROTECTED]
RECOMMEND THE OFFICE LETTER TO YOUR FRIENDS AND COLLEAGUES
Point them to http://www.officeletter.com/current.html
MORE FREE TECH E-NEWSLETTERS: http://www.techletters.com
----------- THE OFFICE LETTER ----- www.officeletter.com ----------
Tips and Tricks for Microsoft Office - Published Weekly
Copyright 2004 Masterware, Inc. All rights reserved
Now In Our Fourth Year - ISSN: 1543-5768
Editor in Chief: James E. Powell
Contributing Editors: Jim Boyce (www.boyce.us)
Dick Archer (www.diseno.com)
