-------------------------------------------------------------------
THE OFFICE LETTER
STANDARD EDITION
Tips, Tricks, Tools, and Techniques for Microsoft Office
Volume 4, Number 12 September 6, 2004
-------------------------------------------------------------------
IN THIS ISSUE
1) Excel: Counting and Highlighting Values
2) Outlook: Keep Your Mail Separated
3) Office: Test Your Office Smarts
4) Review Update: The Logo Creator
5) Review: WordToys
Premium Edition Extra: Word Macro Deletes the Current Document
-------------------------------------------------------------------
1) EXCEL: COUNTING AND HIGHLIGHTING VALUES
-------------------------------------------------------------------
Works with Excel 97 and above
If you have a long list of values and want to know if a particular
value exists in the list, you can use the Edit/Find command to
search through the cells.
If your list contains a smaller set of known values, you may want
to take a different approach. For example, suppose you have a list
of sales for your company. In the first column is the sales
agent's last name. You want to know how many times that agent's
name appears in the list, if at all. While the Edit/Find command
can tell you if the value (agent's name) exists (appears in the
list), it doesn't count the number of occurrences. Furthermore, it
does nothing to spotlight all the occurrences.
We'll use an array formula to count the occurrences, then use a
conditional format to highlight the matching values.
COUNTING OCCURRENCES
In this discussion, assume we have a list of agents in cells A4
through A14
Adams
Baker
Carson
Adams
Dover
Emerson
Baker
Carson
Dunhill
Carson
Dundee
Here's how to count the occurrences:
1. In cell A1 enter:
Agent to find:
2. In cell A2 enter:
Number of times found:
3. Widen column A so the text is fully contained in the cell.
4. You'll enter the agent value to find in cell B1.
5. Move to cell B2 to enter the formula shown below. After
entering the formula, press Shift + Ctrl + Enter so Excel will
recognize the formula as an array formula. (If you don't, Excel
returns an error.) Here's the formula:
=SUM(IF(B1=A4:A14,1,0))
6. To test your formula, enter Baker (capitalization doesn't matter
-- you can enter the name in all lowercase) in cell B1. Press
Enter or click on another cell. Cell B2 should now show the value
2.
Note: If you know that column A will contain nothing but Agent
names, and you expect to have fewer than, say, 1000 entries, you
can enter a larger range, such as
=SUM(IF(B1=A4:A1003,1,0))
In this way, as you extend the range (add lines of data), you don't
have to adjust the array formula in cell B2 as the data range
grows.
HIGHLIGHTING THE MATCHES
We've looked at how to use conditional formatting in past issues
(Premium Edition subscribers, check our Back Issue index of Excel
articles for "Conditional Formatting"); we're using the same
technique we've used in the past to make the background yellow for
all agents matching the value entered in cell B1.
Here's how:
1. Select the cells that will contain the agent names. In our
small example, select A4 through A14.
2. Use the Format/Conditional Formatting command from the main
menu.
3. For Condition 1, choose the cell condition "Cell value is" and
pick "equal to" in the next drop-down list. In the last box, enter
=$B$1
4. Choose the Format button, select the Patterns tab, and click on
the small bright-yellow box in the Color section.
5. Click on OK, then on OK again.
Enter "Baker" (without the quotes) in cell B1 and press Enter (or
move to another cell); cells containing Baker in the range A4:A14
will now be displayed with a yellow background. (See illustration
in our online edition.)
MINIMIZING INPUT
If you know that by entering the first few characters of an agent's
name you'll have entered enough to find a unique match, you can
shorten the number of keystrokes you have to enter in cell B1.
For example, suppose you know that entering Bak is enough to make
for a unique match (Baker, in our sample data).
Change the array formula (in Step 5 of "Counting Occurrences"
above) to check for two conditions -- the agent's name must be
greater than or equal to the text entered, but cannot be greater
than the text plus a string of Z's added to the end.
=SUM(IF(B1<=A4:A14,IF(B1&"ZZZZZZ">=A4:A14,1,0),0))
That is, if you enter "bak" (without the quotation marks) in cell
B1, we want to count the agent names that are greater than or equal
to bak but less than or equal to bakZZZZZZ.
Note: the number of Z's isn't important. You can just as safely
use only two or three. I like to use several so they stand out.
For the conditional formatting, choose to format the cell based on
a value BETWEEN two values. For the first value, enter =$B$1; the
in second value, enter
=$B$1&"ZZZZZZ"
To further test the formulas, enter the letter "d" in cell B1.
Three cells should be highlighted: Dover, Dunhill, and Dundee.
-- James E. Powell
-------------------------------------------------------------------
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
-------------------------------------------------------------------
2) OUTLOOK: KEEP YOUR MAIL SEPARATED
-------------------------------------------------------------------
Office Letter subscriber Bill Riley writes with this question about
Outlook:
I've been a premium member for at least the past two years and
really enjoy the newsletter. I've received a lot of good tips
and advice.
My question deals with Outlook and managing different e-mail
addresses. I have three different e-mail addresses plus my wife
has one as well. I can set them up in Outlook but would like to
be able to separate them. I currently have Outlook 2002 and
Windows XP Professional edition.
For instance my wife would like to be able to open Outlook and
just see her mail or have the ability to switch to her e-mail
account. I'd like to open Outlook and be able to look at my
mail or have a way to switch to my accounts for each different
e-mail address.
I've read about rules and profiles but have not been able to
understand how to successfully make any of them work. Is there a
way to do this?
Thanks.
Our Outlook guru, Jim Boyce, has the solution.
First, I recommend that you use a separate logon account for each
computer user. Doing so will not only keep your e-mail separated
(because Outlook will create a separate profile for you), but also
keeps your desktops and documents separated. You don't have to
worry about deleting someone else's documents, rearranging your
desktop shortcuts every time you log on, or searching through
someone else's browser history to find the Web site you ran across
last week.
Before you create a new logon, however, export your existing
Outlook data to a backup PST file. This will enable you to restore
the data to your new profile after you create a new logon account.
(Premium subscribers: Visit
http://www.officeletter.com/prem/v04/n05.html#import
to read a tip explaining Outlook data import and export.)
Next, how you create a new logon account depends on the version of
Windows you are using. For Windows 98 or Me, just enter a new logon
name in the logon dialog box. For Windows 2000 or Windows XP, log
on with an account that has administrator privileges, open the
Control Panel, and use the User Accounts applet to create a new
user account and assign it a password.
Just using a different account to log on to Windows will, by
itself, cause Outlook to create and use a new Outlook profile. The
first time you open Outlook with the new logon account, Outlook
will add a profile and prompt you to add an e-mail account to it.
If you need to import your e-mail, contacts, and other Outlook
data, do so now from the backup PST file you created previously.
If you don't want to create other logon accounts for Windows, or
you simply want to keep mail from your own multiple accounts
separate, you can use another Outlook profile. An Outlook profile
stores information about your e-mail accounts and Outlook data
folders. You can use one profile at a time in Outlook; to use a
different profile, you must stop and restart Outlook, which then
can prompt you to select a profile.
Start by creating a new profile. Open the Mail applet from the
Control Panel, click Show Profiles, and then click Add in the Mail
tab. Enter a name for the profile when prompted and click OK.
Outlook should launch a wizard that will step you through the
process of adding e-mail accounts. If not, click the newly created
profile in the list and click Properties. When you add an e-mail
account, Outlook automatically adds a PST to the profile to store
your data.
Finally, configure Outlook to prompt you for a profile when Outlook
starts. On the Mail dialog box, choose the option "Prompt for a
profile to be used" and click OK. The next time Outlook starts it
will prompt you to choose the profile you want it to use for that
Outlook session.
-- Jim Boyce
-------------------------------------------------------------------
SPONSOR: GET ORGANIZED WITH FEATURES OUTLOOK DOESN'T OFFER
-------------------------------------------------------------------
AMF Daily Planner & PIM includes a complete Rolodex(tm)-style contact
manager, a full calendar, and a sophisticated scheduling system for
your appointments and events. It even keeps track of your notes and
tasks. Its folder-organized system offers multiple data views,
powerful search, and easy import/export. Plus, unlike Outlook, you
don't need a server to add networked users. Get more information
here:
http://www.amfsoftware.com/affilpim.pl?affil+10057
-------------------------------------------------------------------
3) OFFICE: TEST YOUR OFFICE SMARTS
-------------------------------------------------------------------
Think you're up on Word skills? Do you know the quickest method
for adding a total row to a list in Excel 2003? Can you explain
how to make an inserted sound file play continuously over several
PowerPoint slides?
Take one of Microsoft's quizzes at
http://office.microsoft.com/en-us/FX010380471033.aspx
and find out. The quizzes are scored immediately and there's an
explanation of each correct answer.
-------------------------------------------------------------------
4) REVIEW UPDATE: THE LOGO CREATOR
-------------------------------------------------------------------
When we last looked at The Logo Creator (see the review at
http://www.officeletter.com/blink/updates.html), the excellent
logo-creation program was $69.95. We liked it so much we've used
it to create The Office Letter's logo. That price is a bargain,
compared to what it would cost you to commission a custom-designed
logo.
We're happy to report that the creators, Laughingbird Software,
have lowered the price to a jaw-dropping $29.95. Logo template
packages have also been reduced to $29.95. This is a remarkable
bargain. Version 3.6, just released, revises the user interface
and fixes a .gif export glitch. Plus there's a new tutorial movie.
Now, in addition to being available online, The Logo Creator is
available at CompUSA, BestBuy, OfficeMax, and other retailers. For
more details, and sample logos, visit
http://www.thelogocreator.com
-------------------------------------------------------------------
5) REVIEW: WORDTOYS
-------------------------------------------------------------------
WordToys is one of the few Word add-ons that truly lives up to its
productivity-enhancement promises. WordToys adds its own toolbar
full of buttons (and replaces some of the existing toolbar buttons,
such as the Print and Bullet-List buttons) to save you time and
make tasks more convenient (and enjoyable).
WordToys comes in two flavors: a free Personal edition and a $20
Professional edition. In both editions you'll find buttons that
provide access to its rich set of features. In fact, most of the
buttons do double duty (or even triple duty). For example, the
Writing Tools button organizes a variety of writing tools and
settings into a simple drop-down list (providing shortcuts to
options such as showing or hiding spelling errors), plus it gives
you document statistics (page, word, and character counts, for
example). Ctrl + click the Writing Tools button to start the
spelling checker, or Shift + click the button to get the word
count. Don't worry if you can't remember what key + click combo to
use; simply hover over the button and the tool tip lists all the
options.
The Pro version goes one better with its Favorite Print Jobs
feature. WordToys replaces the Print button which, when clicked,
causes Word to display the WordToys printer drop-down menu where
you can define (and then reuse) groups of settings. I defined one
job as printing two copies of the current document in landscape
mode (something I do a lot for a particular monthly report I
create), set another setting to cause Word to print just the first
page of a document, and a third to print four logical pages on a
physical sheet of paper. Then, when I want to use one of these
settings, I just choose it from the list WordToys creates for me.
If you can create a combination of settings using the standard
Print dialog box, you can save them as a favorite print job. But
that's not all the new Print button can do. Ctrl + click the
button and you'll print to the default printer (just as the
standard Print button does in Word); Shift + click and Word opens
the Print dialog box (similar to using the File/Print command).
Many utilities claiming to enhance Word are simply collections of
buttons that do what Word already does -- just organized
differently. WordToys, on the other hand, saves you steps, and
does it in a logical fashion. For example, you can select text and,
with a single click, create a bookmark with the selected text as
the bookmark name. That's a tedious, multi-step process in Word.
There are lots of other nice touches: The Workspaces feature is
modeled on Excel's ability to remember the names and positions of
your open documents, then re-open them in the exact layout in the
future. If you repeatedly work on the same set of documents,
there's nothing better than a workspace. You can even define an
"auto-open" workspace to open every time you start Word.
Document Recall keeps track of the last documents you've worked on
(way more than the puny 9 that Word keeps track of), and lists them
along with the date they were last used. Click a file from the
list and WordToys opens the document to the last place edited.
Double-Save will save the current document to its existing location
and make a backup to another folder. (The Pro version can create
timed double-save backups of open documents. You can even create
multiple copies as backups (each copy gets a prefix of "Copy xx of"
(where xx is an auto-incremented number). Don't confuse this with
Word's versioning; Double-Save creates separate physical files each
time you use it.
The Pro version lets you add a file type to be recognized as a Word
document; you can add ".inv" as a Word type and files with that
extension will be displayed when you use the File/Open command.
(In the Pro version, these files will also be displayed in Document
Recall file list.)
The buttons do more than just provide shortcuts to Word
functionality. Many of the tools actually enhance Word. Consider
AutoPilot. It offers a list of 20 commands. Check the ones you
want to execute and AutoPilot will perform them in order. For
instance, you can tell AutoPilot you want to spell check the
document, open the Shrink to Fit dialog when spell checking is
complete, then save the document and print it. If that's a
sequence you perform often, you can save it as the default. Sadly,
unlike the Favorite Print Jobs, you can't create multiple AutoPilot
task collections -- one to save and print, another to shrink and
print, etc. That's a feature I'd really like to see in the next
version.
If you don't like how Word works, you can make some quick setting
changes. For example, you can pick the color used to underline
spelling, grammatical, and formatting errors as well as text to
which a Word Smart Tag has been applied.
Recently we've written about inserting special symbols -- accented
letters used in foreign languages, for instance. The trouble is
that you need to remember the four-digit Unicode value or find the
symbol in a long list of characters. WordToys makes things easier.
Simply type a letter and press F10. WordToys pops up a list of all
the available symbols for that letter (see Figure 1). Very, very
nice. A Favorite Symbols list is also available for quickly
inserting copyright, trademark, Euro, and Greek symbols.
Among the new toolbar buttons WordToys adds to the top of your
screen: Exit, Close All, and Close All But Current Document (I
particularly like that one).
WordToys also adds a Navigation bar to the bottom of your screen,
adding a button for each open document. In the Pro version you can
Shift + click the button to close the document without activating
it first, or Ctrl + click the button to save the document without
having to activate it.
There's much more inside WordToys, from removing personal
information to deleting all hyperlinks, bookmarks, versions, or
footnotes/endnotes. You can tell Size to Fit how many pages you
want your new document to be (the Pro version gives you extra
control over how properties are adjusted), quickly insert pre-
defined fields (such as file creation date or author name), and a
one-click option to add selected text to the document's keyword
property.
WordToys works with Word 2002 (in Office XP) and 2003 only. For
more information, or to download the free Personal edition, visit
http://www.wordtoys.com.
-- James E. Powell
-------------------------------------------------------------------
PREMIUM EDITION EXTRAS THIS WEEK
-------------------------------------------------------------------
This week Premium Edition subscribers are reading about how to
delete a file in Word without saving it first.
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)
------------------------ Yahoo! Groups Sponsor --------------------~-->
$9.95 domain names from Yahoo!. Register anything.
http://us.click.yahoo.com/J8kdrA/y20IAA/yQLSAA/BCfwlB/TM
--------------------------------------------------------------------~->
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/kumpulan/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
