-------------------------------------------------------------------
THE OFFICE LETTER
STANDARD EDITION
Tips, Tricks, Tools, and Techniques for Microsoft Office
Volume 4, Number 25 December 6, 2004
-------------------------------------------------------------------
IN THIS ISSUE
1) Excel: Duplicates Using Two Columns
2) Free WordToys Pro Drawing for Premium Subscribers
3) Excel: Chart Challenge Solutions
4) Review: DemoCharge
Premium Edition Extra:
Outlook: Manage Duplicate Messages
-------------------------------------------------------------------
1) EXCEL: DUPLICATES USING TWO COLUMNS
-------------------------------------------------------------------
Reader Derek Yates wrote in response to our recent tip about
finding duplicates in Excel when data in two fields (columns) must
match.
- - -
Great letter as usual.
One addition to Glenn's solution to Finding Duplicates in two
columns of data.
You could use conditional formatting and a slight addition to your
base technique from the 11/8 Office Letter.
Highlight the data section, select conditional formatting, and use
the following formula,
=(($A2=$A3)+($B2=$B3))=2
Select your format (I like the red fill and white text myself) and
all dupes will be hard to miss.
(See the animated demo in this week's online edition.)
Note: this formula applies only if the first cell selected is A2
when highlighting your data. If you select from the bottoms up,
you would need to adjust your formula based on your active cell.
Of course this won't work if your data isn't properly sorted, but
it's very quick and easy.
- - -
Office Letter reader Joseph D. Smith took a slightly different
approach to the problem. He writes:
I am new the newsletter and have watched the last couple of issues
talk about finding duplicates. The responses have all avoided
talking about nested IF statements. If the data has last names in
column A and first names in column B You can insert a column C and
put
=IF(a1=a2,IF(b1=b2,"DUPE?",""),"")
in C1 and copy it down to the end of the data rows.
Then when you sort by highlighting the first two columns, and using
column A as the first key and column B as the second key, the
column C cells will indicate the proper response DUPE? when it
finds a match in the first two columns. This method only solves
for a second column match if there is a first column match.
-------------------------------------------------------------------
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) FREE WORDTOYS PRO DRAWING FOR PREMIUM SUBSCRIBERS
-------------------------------------------------------------------
No add-in we've seen offers as many Word shortcuts as WordToys.
The standard version is free, and the Pro version is $20. (You can
find our complete review at
http://www.officeletter.com/blink/wordtoys.html.)
On December 16, The Office Letter will give away a copy of the
Professional version of WordToys to three lucky Premium Edition
subscribers.
If you've been thinking about subscribing to the Premium Edition --
with extra content, HTML delivery, an index of all back issues, and
no advertising -- here's another reason to make the move. Visit
http://www.officeletter.com/subscribe.html to subscribe to the
Premium Edition today.
Premium subscriptions also make excellent holiday gifts for your
friends and colleagues.
-------------------------------------------------------------------
SPONSOR: SHARE OUTLOOK CALENDARS WITHOUT EXCHANGE SERVER
-------------------------------------------------------------------
OfficeCalendar is a simple software application that lets you share
Outlook calendar and contacts with your colleagues and create group
calendar views for group scheduling. OfficeCalendar is easy to
install and use, affordable, and highly effective. Best of all, if
you already know how to use Microsoft Outlook, there's nothing new
to learn. Sharing Outlook calendars doesn't get any easier.
Visit our Web site at http://www.officecalendar.com/offlet.asp
to download a free trial version.
-------------------------------------------------------------------
3) EXCEL: CHART CHALLENGE SOLUTIONS
-------------------------------------------------------------------
At first there seemed to be some confusion about our reader
challenge last week. We published a request from a reader asking
how to include three pieces of data in a chart:
Product % Count
A 20% 40
B 30% 61
C 50% 99
The chart should list the product name, the numeric value of the
percent (not just show pie slices and let the audience "guess" as
to the value of the percents), and the count.
Many readers submitted suggestions that showed the Product name in
the legend and the count or percent next to each pie slice, but
that wasn't enough. Our reader wanted to show the percent value
and the count value, along with the product name.
It turns out there are some particular quirks in the way different
versions of Excel treat charts. While the updates to Excel 2002
and 2003 have been fairly minimal (I still use Office 2000 for my
daily work), it turns out that there's an important charting
difference, as we'll explain after the first reader-provided
solution below.
The solutions that accomplish this are listed below. Illustrations
for the solutions can be found in the online version.
- - -
Arjan Hummel wrote to say the reader's problem was easy to solve:
Hello,
I would like to present a possible solution to the pie chart
problem that Rajesh Venkataramani wrote about.
It is actually not necessary to graph the percentage column,
because the count values already represent the percentage values
when making a pie chart. In the chart options, the Data Labels tab
lets one select the Value and Percentage checkboxes [Editor's note:
see Figure 1 in our online edition]. That way both items will be
drawn next to the slices of the pie chart as shown below.
The percentages are not the same as in the original problem but
that is because the original ones were rounded. It looks like
Excel doesn't always round 0.5 the same way (49 is actually 49.5
and 31 is actually 30.5).
Hope this helps!
Editor's note: Figure 1 (in the online edition) shows Arjan's
solution.
In Excel 2000, the Value and Percentage options on the Data Labels
tab are radio buttons, so that version only allows for one of these
values to be selected, which was the problem facing Rajesh. It
wasn't until Excel 2002 that Microsoft enabled all options with
checkboxes rather than having you pick just one option using a
radio button.
That's explains why Rajesh was having such trouble, and why, when I
tried to create a chart, I couldn't come up with a solution,
either. I've never found a reason to upgrade from Office 2000.
Perhaps now I have.
Here's the interesting part. Many readers offered the same
solution as Arjan, but when I opened their solution (remember, I'm
using Excel 2000), I saw only percent and product name, not all
three values displayed. I wrote to several readers saying thanks,
but I don't think you understood the problem. It was only after
TOL reader Sharanjit Paddam wrote with the solution, and I wrote
him saying it didn't solve the problem, and he wrote back saying it
DID work that he helped me realize that the problem had to do with
the version of Excel used to open the file. Opening Sharanjit's
solution in Excel 2002 showed he was right, but opening the same
file in Excel 2000 didn't display all three sets of values.
My apologies to all the TOL readers who sent working solutions but
who received e-mail from me saying they'd missed the mark. You
were right, I was wrong.
- - -
So, with Arjan's solution in Excel 2002 and 2003 in hand, I turned
to other TOL readers for solutions that *would* work in Excel 2000.
All of the remaining solutions have been tested with Excel 2000.
Allen Baumgard suggested entering the product name and count into
Column A, then entering the percent in Column B:
Product Percent
and Count
A, 40 20
B, 61 30
C, 99 50
That makes the data labels "A,40" (and so on), and the Percent
column is used for Excel to know how big to make the slices. The
product, count, and percent can be shown in the chart.
- - -
Jean Tayler suggested putting text boxes on top of the chart;
that's somewhat easier to do than using Word Art, and the results
look better (see Figure 2).
- - -
Nancy Van Hof made this observation:
My suggestion is to chart the name and count only, and then use the
chart options feature. Go to the data labels tab, then select all
the items you want to show. I selected
series name, value and percentage. The correct percentage will
automatically appear. [Editor's note: again, this assumes using
Excel 2002 and above.]
The other (messy) solution is to click once on a data label, then
click again after a second. This will open the text label, and you
can edit the label directly. I did that just to complete the
information. [Editor's note: again, like our use of Word Art, this
works but isn't elegant, as Nancy admits, but it does work.]
BTW, I am in version XP. What version is Rajesh using? And I want
to thank you as well for a GREAT newsletter. There have been some
true gems in here.
Nancy's approach is shown in Figure 3.
- - -
Doug Klippert, a regular contributor of solutions to the Office
Letter, had this smart twist:
A better way might be to:
1. Select just the Product and Count data.
2. Create a Pie chart with Data label showing Percent.
3. Go to the Formula bar, type =, and the click on cell B1. Press
Enter.
4. There is now a text box tied to the data entry. It will change
as the data changes. Do the same for the other products.
You can select the text boxes and format them however you want.
Editor's notes:
Doug's solution is shown in Figure 4.
In Step 3, be sure you click on the cell; typing in its cell
address doesn't work.
We like this solution because it's clever without being
complicated. The text display changes automatically whenever you
change the data in the original cells, unlike our solution, which
required manually changing the text display when a value changed.
We found, however, that we had to click slightly under the box for
Excel to realize we wanted to edit the text box, and even then we
sometimes couldn't get Excel to recognize what we wanted to edit.
It's best to edit (and position) the text box immediately after you
create it when Excel gives it focus. Otherwise, regaining focus
can prove elusive.
- - -
Jon Peltier, a Microsoft Excel MVP (http://PeltierTech.com/), took
yet another approach.
This is not so hard. Select any cell, probably easiest if it's a
blank cell surrounded by blank cells. Start the chart wizard.
In step 1, select a pie chart.
In step 2, click on the Series tab, then click on Add. Now click in
the Values box, and select the range of cells with the Count
values. Click in the Category Labels box, and select the two-column
range of cells with the Product and Percent values.
In step 3, on the Data Labels tab, choose the option that displays
category labels (it differs somewhat from version to version).
[Editor's note: In Excel 2000, we chose "Show label percent."]
In a bar chart, this approach will produce two row category labels,
with percent showing above the category name. If instead you want
data labels to show both sets of values, you can concatenate them
into another range of cells with a formula like =C2&" ("&B2&")" in
D2. Then make the chart, and use a third party chart labeler. Two
which are free and easy to use can be downloaded from
http://appspro.com (Rob Bovey's Chart Labeler) and
http://j-walk.com (John Walkenbach's Chart Tools).
- Jon
Figure 5 shows the results of Jon's instructions.
- - -
Finally, a TOL reader suggested using a double-scaled bar chart.
That worked well; see Figure 6.
- - -
Thanks again to everyone for writing. Have a question or problem
for our readers? Write us at [EMAIL PROTECTED]
-- 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) REVIEW: DEMOCHARGE
-------------------------------------------------------------------
Sometimes instructions are best explained through pictures, not
step-by-step written instructions. Trouble is, creating those
pictures can often be a lot of work. Thanks to DemoCharge, simple
demonstrations can be created quickly and easily, with easy-to-use
editing tools that help you eliminate mistakes, temporarily hide
part of a demo, or add text balloons with comments; you can even
highlight areas of the screen to capture your audience's attention.
DemoCharge ($49.95, http://www.yessoftware.com/) works much like a
still-image screen capture program: you tell the program what part
of the screen you want to capture (a window, a portion of the
screen, or the entire screen), then click a button to start the
capture. You perform the demo "live" -- open dialog boxes, move
the mouse, enter text -- and when you're done you simply press a
keyboard shortcut to stop the capture. Then DemoCharge goes to
work creating frames of each significant portion of your demo --
creating a separate frame for when you moved the mouse, another for
a string of text you entered, and so on -- and lays out your demo
in a timeline (see illustration in our online edition this week).
The thumbnail views of each frame (� la PowerPoint) help you
navigate to the frame(s) you want to adjust.
You can delete a frame, reorganize frame order in the presentation,
or hide frames (perfect if you have to create two versions of your
demo for different audiences). You can make an additional capture,
then insert the new frame(s) into your current project, which is
helpful if you've overlooked something you want to illustrate.
We tested DemoCharge with a number of tips from The Office Letter;
the tip this week on highlighting duplicates shows what's possible
(see our online edition this week and view the animated demo for
the tip on highlighting duplicates). We simply started recording,
went through the tip's steps, then stopped the recording. We
adjusted the duration of some of the frames -- so our text entry
would look speeded up. (You can select multiple frames and
compress their combined duration in proportion to their original
length, too.) We deleted other frames where we'd made typos; this
was a great alternative to re-capturing the entire sequence.
You can also use the Presentation mode to capture a series of
screens and turn them into a presentation, but we found such
demonstrations less compelling.
You can modify one frame at a time, then look at the preview to see
how your demo will appear. Fine tuning frame properties lets you
control where they appear on screen (50 pixels from the top and 100
pixels from the left, for example).
You can highlight an area of any frame or superimpose a text-filled
balloon to point out key points; these special effects can span
frames, too. They're incredibly easy to set up.
Once your preview looks good, it's time to turn the project into an
animated GIF file (support for Java Applets and Flash are promised
in the Professional version for the first quarter of 2005). You
can pick the size (we preferred 100%, but a half-size demo would be
perfect as a teaser in an e-mail or for a PowerPoint slide) and
resolution of the output. In less than 5 seconds, our .GIF file
was ready.
There are still some areas where the program can improve: you can't
draw on your capture (you can't add arrows, for example), and you
can't crop a frame (if you're idle for a couple of minutes and want
to take out just that "dead air", you can't).
On the plus side, it won't take you much time to learn the program,
and I was able to grasp the basic concepts just by playing around
with it after watching some sample demos on the company's Web site.
A 20-day demo is available on the company's site. A Pro version
(which will be available early next year) will sell for $199.95
($149.95 if you order now) and adds AVI file compression and demos
with sound.
If an animated picture is, indeed, worth a thousand words,
DemoCharge is a perfect, low-cost way to build that picture.
-- James E. Powell
-------------------------------------------------------------------
SPONSOR: GIVE US 10 MINUTES, YOU'LL SAVE ONE HOUR ... GUARANTEED!
-------------------------------------------------------------------
AMF Daily Planner and Personal Information Manager Software
organizes your tasks, orders your address book and contacts,
manages due dates, and keeps you prepared for any event!
- Organize and prioritize your projects, errands, and more
- Easily share your data with other users over any network
- Keep in touch with those who mean the most to you
- Overdue items are highlighted on your to-do list
- Never forget a birthday, anniversary, or special occasion again
Get it now: http://www.amfsoftware.com/affilpiml.pl?affil+10057
-------------------------------------------------------------------
5) PREMIUM EDITION EXTRAS THIS WEEK
-------------------------------------------------------------------
This week Premium Edition subscribers are reading about how to
manage duplicate messages in Outlook.
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)
