Tips for formatting data in Office Excel 2007 for Mail Merge

Today’s blog comes from Microsoft’s “Help and How-to RSS feed:

If your data file is an Office Excel worksheet that includes percentages, currency values, or postal codes, you can preserve the numeric formatting of the data by using Dynamic Data Exchange (DDE) to connect to the Excel worksheet from Word. For example, you can make sure a five-digit postal code of 07865 from your data file is not displayed as the number 7865 (without the leading zero).

Before you connect to the worksheet, do the following in Word:

1. Click the Microsoft Office Button , and then click Word Options.


2. Click Advanced.


3. Scroll to the General section, and select the Confirm file format conversion on open check box.


4. Click OK.


5. With the mail merge main document open, in the Start Mail Merge group on the Mailings tab, click Select Recipients, and then click Use Existing List.


6. Locate the Excel worksheet in the Select Data Source dialog box, and double-click it.


7. In the Confirm Data Source dialog box, click MS Excel Worksheets via DDE (*.xls), and then click OK.

Note If you don't see MS Excel Worksheets via DDE (*.xls), select the Show all check box.

8. In the Microsoft Office Excel dialog box, for Named or cell range, select the cell range or worksheet that contains the information that you want to merge, and then click OK.

Note To prevent being prompted every time you open a data file, you can clear the Confirm conversion at Open check box after you connect to the worksheet.

Recover Deleted Items in Outlook 2007

Did you know that you can recover deleted items in Microsoft Office Outlook 2007 even after you have emptied the Deleted Items folder. In past versions of Outlook, once the Deleted Items folder was emptied, those items were permanently deleted.

But now you can recover these deleted items if you have a Microsoft Exchange Server account. If you don't have an Exchange Server account here at Scripps Research, it's easy to get one. Just submit a service request to IT Services at http://www.scripps.edu/rc/hd/ or call the Help Desk x4-9369 and ask for one.

Our Exchange Server administrator specifies the retention time for items that are deleted permanently on the server running Exchange. Our retention time is 30 days. After this time elapses, you cannot recover the deleted items.

You can view and recover deleted items, including the items that were deleted permanently, when you selected items and pressed SHIFT+ DELETE or SHIFT+.

1. In the folder from where you deleted the item, or in the Deleted Items folder, click Recover Deleted Items on the Tools menu.
   
   
   Click Recover Deleted Items on the Tools menu.
   
2. Click an item and then click Recover Selected Items .
   

To select multiple items, press CTRL as you click each item.
Each recovered item is restored to the folder from which it was deleted.

You cannot recover an item if it does not appear in the Recover Deleted Items dialog box.
.
.

What Happened to the Out of Office Warning in Outlook 2007?

The Out of Office message that warned us the Out of Office auto-replies was on in Outlook 2003, has moved and remains on the screen for only 15 seconds in Outlook 2007. In Outlook 2003, the warning appeared in the middle of the Outlook screen and stayed there until you dismissed it. It was kind of hard to miss, a very in-your-face reminder the Out of Office responder was on.


New location of Out of Office warning in Outlook 2007


Close-up of Out of Office warning

Now, in Outlook 2007, the reminder appears in the lower right corner of the Outlook window when you start it up and then disappears after 15 seconds. The same reminder appears when you first turn on the Out of Office Assistant. But this too disappears after 15 seconds. However, an Out of Office button remains in the right corner of the Task Bar.


Out of Office button on Task Bar

When the button is clicked, it will display a pop-up with two options. The first option, Out of Office Assistant... will pull up the Out of Office Assistant window. The second option, Turn off Out of Office auto-replies, does just that. When you turn off the Out of Office Assistant, the button disappears from the Task Bar.


Two options appear when Out of Office button is clicked

The Out of Office Assistant can also be turned off through the menu: Tools / Out of Office Assistant... select "I am currently In the Office" from the Out of Office Assistant window.


Tools menu in Outlook 2007


Select "I am currently In the Office" to turn off the Out of Office auto responder.

The 2007 Microsoft Office Button

The user interface has been significantly redesigned in the following 2007 Microsoft Office system programs: Word, Excel, PowerPoint, Access, and Outlook (in the composing and reading windows). Instead of menus and toolbars, the new interface uses a "ribbon" to organize commands.


Office Button in the Ribbon (Office Fluent User interface) in Word 2007

The Microsoft Office Button replaces the File menu and is located in the upper-left corner of the Ribbon in the Microsoft Office programs. This button provides access to functionality common to all Office applications.

When you click the Microsoft Office Button, you see the same basic commands available in earlier releases of Microsoft Office to open, save, and print your file. The commands available in the Office Button menu are: New, Open, Save, Save As, Print, Prepare, Send, Publish and Close.



The new commands, Prepare, Send and Publish give the user more options in finishing a document. Prepare will prepare the document for distribution, through such tasks as adding a signature or encryption. Send will send the document to another user by email or fax, and Publish makes the document publicly available via a document server or a public web space.

Commands are listed on the left, and on the right appear recently opened documents. If you can’t see a list of recently opened documents it could be that you have a menu selected on the left. If this is the case, just move the cursor away from that menu to deselect it.


The Tools menu/Options command moved to Word Options in Word 2007

In earlier versions of Microsoft Office, you could set your preferences for specific view, display, and editing settings in the Options dialog box (Tools menu, Options command). As part of the 2007 Office system, the Tools menu has been moved so that it is under Word Options, Excel Options, PowerPoint Options or Access Options in the lower corner of the Microsoft Office Button window.

As you open more documents, the list of recently opened documents grows; the more recent ones appear at the top. As you can imagine, the older ones will eventually disappear from this list. You can however ‘pin’ a document to the list so that it always appears there, no matter how big the list gets. Simply click the pushpin to the right of the document name and the file is pinned. To ‘unpin’ it, just click on the pushpin again.


'Unpinned' document in Recent Documents


Click on pushpin to 'pin' the document in Recent Documents

In Microsoft Office Outlook 2007 when you read or create a message, task, contact, or calendar item, you see the new Microsoft Office Button.

Outlook - Got Connection?

I have several mailboxes in my Outlook (on Exchange) other than my own email mailbox and suddenly was unable to open them. I got this error: “The set of folders could not be opened.” I thought I was online because I could receive and send email. But no, Outlook was offline and I didn't see it because Clippit was covering up the connection status! Outlook would connect only to upload and download email and then it would go offline.


Connection indicator located in right hand corner displaying Offline status.

Clippit on top of the Connection indicator.

Okay, I admit I should have given Clippit the boot a long time ago. And yes I know Clippit was detested among most users as intrusive and annoying. I cannot remember when I activated Clippit but I do know I never got irritated enough to turn it off...until now.

How did Outlook become offline if I did not change its setting? I was told that if there is a long enough disruption in the connection to the Exchange server, Outlook could switch to an offline status and remain like that until you reconnect using the connection button in the lower right corner, or by selecting File / Work Offline. We had a couple of email disruptions the past couple of weeks which might have triggered the Outlook to disconnect from Exchange.

Click Work Offline to change connection
status and work online.

So when I clicked the Work Offline command, the check mark disappeared (which means it was deselected) and Outlook quickly connected to the Exchange server. My other mailboxes were accessible, and all was well again.

Connection indicator displaying online status.

Flag Email for Follow-up in Outlook

Assign flags to your email messages in Outlook to remind yourself to follow up on an issue. You can also use flags to categorize messages in your inbox. There are six colors - red, blue, yellow, green, orange, and purple - that can be used to mean different things. Message flags are displayed in the last column of the Inbox view.


Flag icons are displayed on the right side of each message.
The flag drop-down menu appears when icon is right-clicked.

You can use message flags to do the following:

• To flag a message with a particular color, right-click the flag that corresponds to the message and then select the flag color.
• To flag a message as complete, right-click the message, and then click Flag Complete.
• To sort your messages by color, click the flag column heading.
• To clear a flag from a message, right-click the mesage, and then click Clear Flag.
  

Switch Excel Columns to Rows

Have you ever wanted to change a column of data into a row in Excel?
Here's how you do it:

1. Open the spreadsheet you need to change.
2. Click the first cell of your data range such as D1 (Third Quarter in figure 1).
3. Shift-click the last cell of the range. Your selection should highlight.
4. Right click and select Copy from the right-click context menu (figure 1). Or go to the Edit menu and select Copy.


figure 1

5. Click on the Cell you want to paste the range of data. We will use A7.
6. Right click cell A7. From the Right-click context menu, select Paste Special... (figure 2).


figure 2

7. The Paste Special dialog should appear (figure 3).
8. Click the checkbox for Transpose.


figure 3

9. ..Click OK.
10. Your data from D1:D5 is now displayed in Row(A7:E7) (figure 4).


figure 4

You can go from columns to rows, or rows to columns using the same procedure.
.