-------------------------------------------------------------------
THE OFFICE LETTER
STANDARD EDITION
Tips, Tricks, Tools, and Techniques for Microsoft Office
Volume 4, Number 9 August 16, 2004
-------------------------------------------------------------------
IN THIS ISSUE
1) Excel: Custom Formats for Validation
2) PowerPoint: Datasheet Quick Tricks
3) Outlook: Controlling Importance
4) Review: ExplorerPlus
Premium Edition Extra:
Outlook: Add Your Inbox to Outlook Today
-------------------------------------------------------------------
1) EXCEL: CUSTOM FORMATS FOR VALIDATION
-------------------------------------------------------------------
Works with Excel 2000 and above
In previous issues we've explained a variety of tricks with Excel's
custom formats. Here's one more: using custom formats based on
cell values.
Typically, custom formats are broken down into three parts: a
format for positive numbers, a format for negative numbers, and a
format for cells that evaluate to zero. The format, such as:
#,##0.00;[Red](#,##0.00);"--"
tells Excel to display a value using commas and two decimal digits
for positive values, display a value in red (and with added
parentheses) for negative values, and display two dashes if the
value in the cell is zero.
However, we can override the positive/negative/zero breakdown. For
example, suppose you have a data range where cell values must be
between 0 and 10. If the value is less than 0, you want the cell
to display "TOO SMALL". If the value is greater than 10, you want
to see "TOO BIG".
To create this custom format:
1. Select the range of cells to format.
2. Use the Format/Cells command from the main menu.
3. In the Format Cells dialog box, choose the Number tab. In the
Categories list choose Custom.
4. Click inside the Type field and enter the custom format. In our
example, enter:
[>10]"TOO BIG";[<0]"TOO SMALL"
5. Click on OK.
Now, cells in the range assigned this custom format will display
"TOO BIG" if they contain a value over 10.
You can add more conditions to the custom format. For example, to
display TOO BIG or TOO SMALL in red, change the custom format
string in step 4 to:
[red][>10]"TOO BIG";[red][<0]"TOO SMALL"
-- 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) POWERPOINT: DATASHEET QUICK TRICKS
-------------------------------------------------------------------
Works with PowerPoint 2000 and above
One of the handy PowerPoint features I really like is the
simplicity of creating a chart without having to resort to
embedding an Excel worksheet into a slide. Instead, I just click
on the Insert Chart toolbar button and PowerPoint displays a chart
and a datasheet for entering values.
While I'm a dyed-in-the-wool fan of keyboard shortcuts, my
colleague is happier using the mouse. Fortunately, there are
datasheet shortcuts for each of us.
To select:
-- A row with the keyboard: move to a cell in the desired row and
hold down the Shift key as you press the spacebar.
-- A row with the mouse: click on the gray row header (the gray box
with the row number and a chart symbol at the far left of the
datasheet) corresponding to the row you want.
-- A column with the keyboard: move to a cell in the desired
column, hold down the Ctrl key, and press the spacebar.
-- A column with the mouse: click on the column header at the top
of the datasheet (the one with the letters A, B, C, etc.)
corresponding to the column you want.
-- All cells with the keyboard: Press Ctrl + A.
-- All cells with the mouse: click on the small gray box in the
upper left corner of the datasheet.
Once selected, you can insert or remove the rows or columns. To
remove them, simply press the Delete key or right-click (or use the
right-mouse key on your keyboard -- it's one of those fat "extra"
keys on a 104-key keyboard) and choose Delete. To insert data,
right-click (or use the right-mouse key on your keyboard)and choose
Insert from the pop-up menu.
Note: when you move away from a datasheet (and click somewhere
else), PowerPoint hides the datasheet. To see it again, double-
click on the chart.
SELECTING DATA FOR YOUR CHART
Clicking on the column or row heading isn't just a handy way to
select data. Double-click on the row or column heading and you'll
exclude that data from the chart without removing the data from
your datasheet. PowerPoint dims the column or row to gray so you
can still see the now-excluded data.
To restore the data to the chart, simply double-click the column or
row heading again.
-- James E. Powell
-------------------------------------------------------------------
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) OUTLOOK: CONTROLLING IMPORTANCE
-------------------------------------------------------------------
In mid-July we published a reader question about automatically
controlling the priority of a message to a particular recipient:
I have a contact named contactname, e-mail address is
[EMAIL PROTECTED] I want to have the Importance
automatically set to LOW when I enter this account in the
TO: or CC: field.
I can set Importance to LOW for this contact manually in
the contact form by clicking on the "All Fields" tab,
selecting "Importance," and changing the value to LOW.
However, when I enter the contact in the e-mail TO: or CC:
box, the Importance for the e-mail is not changed to LOW.
How do I get this to work?
We asked for your solutions, and Office Letter reader Paul Seaman
wrote us with these instructions:
You could use a Rule to set all messages sent to this specific
contact to low priority (this is regardless of whether the contact
itself is set to low priority).
In Outlook XP:
1. From the Inbox, choose Tools then Rules Wizard.
2. In the Rules Wizard window that appears, click the New button.
3. Click "Start from blank rule" at the top of the next window.
4. Select "Check Messages after sending" and click Next.
5. Click in box next to "Sent to people or distribution list" in
the top half of the next window.
6. The condition appears in the lower window -- click on the
underlined words here to select the recipients from your address
book.
7. Click Next and tick "Mark it as importance" in the next list.
Once again, click the underlined word when the phrase appears in
the lower window and choose Low.
8. Click Next and add any exceptions needed.
9. Click Next and assign a name to the rule and then click Finish
Any messages with the specified addresses in the To: or cc: fields
will be marked as low importance when you click the Send button
after composing the mail. Note that this doesn't happen if the
contact is added to the Bcc field.
-------------------------------------------------------------------
4) REVIEW: EXPLORERPLUS
-------------------------------------------------------------------
I've long been an avid fan of PowerDesk, a multifaceted file
management utility (see the review at
http://www.officeletter.com/blink/powerdesk.html). When I read
about a competitor, ExplorerPlus 6.2 ($39.95), I was surprised to
learn that it came from the same original developers. One look at
a screen shot and the similarities were remarkable. Turns out that
with PowerDesk sold to VCOM, PowerDesk's original designers had
started again. Though ExplorerPlus lacks some of PowerDesk's
auxiliary utilities (such as a graphic display of free/used space
so you can find space hogs), the fundamental features are here --
and then some -- and it's $10 less than PowerDesk.
ExplorerPlus does everything you'd want a file manager to do: move,
copy, delete, compress, and rename, with the ability to open two
different folders, tiling the display either horizontally or
vertically. You can print an individual file or a list of files in
a directory (as well as the directory tree itself), whether they be
on your own hard drive or a network drive, change a file property
(toggle the read-only property off, for example), see files
(including Office files) in a preview window, synchronize files
between two folders or two computers, and attach one or more files
to an e-mail message with just a couple of clicks.
Hover over a file and you'll see its properties plus a thumbnail
image in what's called a FileTip. You can attach a note to a file
(up to 1000 characters long); you can view notes in the file list
or in the FileTip. You can also use the Filters feature to limit
the files listed, and click on column headings to sort the file
list (by date or name, for example).
If you work with graphics, ExplorerPlus can convert between file
formats (BMP, TIFF, Photoshop, JPG, GIF, and more -- some 35
formats in all), including control over file properties, such as
compression level for JPG files, and the ability to convert files
in batches. If you move files to or from the Web, you'll like the
simple built-in FTP functionality. You can create a Zip file
(including self-extracting files) or test a compressed file's
integrity. (The program handles a variety of compression formats,
including tar and gz.)
Into music more than graphics? ExplorerPlus lets you create or
edit playlists of your favorite audio files, and edit MP3 track
tags, without launching your audio player.
In many ways, ExplorerPlus and PowerDesk are virtually identical,
from the menu layout to the way you customize the toolbar. Some of
ExplorerPlus' distinctive features are merely window dressing: you
can change the color of a folder's icon (to one of eight colors),
apply a different "color scheme" to change the colors of elements
such as buttons and backgrounds -- I found the default blue theme
satisfactory.
The program includes a tabbed layout so you can jump between the
regular and tiled views of your system; you can create new tabs,
but you can only specify whether your tab includes one of several
features, such as a toolbar. Yawn. While you can create buttons
to launch your favorite programs, it would be helpful if you could
create tabs that would open a folder you specify -- I'd like to set
up a tab for each of my most-frequently-used folders, but
ExplorerPlus can't do it. (in fairness, neither can PowerDesk) (You
can add a favorite file or folder to its pulldown QuickAccess menu,
but it's not the same, and giving a QuickAccess item a different
name isn't intuitive).
To simplify access to your most-recently-used files and folders,
ExplorerPlus offers both a File Tracker and a Folder Tracker
feature that adds an icon to the standard File Open/File Save
dialog boxes of Windows applications. When clicked, the icon lists
the most-recently-used files or folders (respectively). It even
works with the File dialog boxes in Office applications, which are
notorious for not using the standard Windows file dialog boxes.
Other features don't work within Office applications: for example,
you can't click on a magnifying glass icon to preview a file before
opening it from a File dialog box (but you can in Notepad). Though
the trackers let you resize most file dialog boxes -- the size is
remembered between sessions -- this feature, too, doesn't work with
Office applications.
ExplorerPlus does distinguish itself with a feature I really like:
the FastFind tool includes not just the file name but also a
sampling of text from the area of the document that matched your
search term. The search is also remarkably fast (much more
efficient than Window's built-in search tool), even though
ExplorerPlus doesn't index the files on your drive.
With PowerDesk (now at version 6) selling for $49.95, ExplorerPlus
gives you most of the same file management functionality -- and a
few extra features -- for $10 less. For more information or to
download a 30-day trial version, visit
http://www.novatix.com/Products/ExplorerPlus/
-- James E. Powell
-------------------------------------------------------------------
PREMIUM EDITION EXTRAS THIS WEEK
-------------------------------------------------------------------
This week Premium Edition subscribers are reading about how to add
your Inbox to Outlook Today.
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 --------------------~-->
Yahoo! Domains - Claim yours for only $14.70
http://us.click.yahoo.com/Z1wmxD/DREIAA/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/
