Excel and Word 2010 – Keeping your Number and Date Formats When Using a Spreadsheet for an Form Letter

Before we discuss creating a form letter for email or snail mail, there is a HUGE fix you must complete in MS Word.  If you do NOT do this fix, no matter how beautifully you format your numbers and dates in your Excel spreadsheet, Word will NOT pick up that formatting.  Word will simply guess randomly and DIFFERENTLY for all the numbers and dates it is taking from your spreadsheet.

I wouldn’t be YELLING, if this wasn’t such an important issue.  It took me four or five hours of Googling to find this simple fix. Last month, I got a new computer at work, and this fix had completely slipped my mind.  That meant none of my existing mail merge documents would work on the new computer until I remembered.

In MS Word, go to the File tab>Options>Advanced.

In the Advanced window, scroll all the way down to the last section:  General.  Click the box “Confirm file format conversion on open” checkbox.  Click “OK.” 

 

Now, on this computer, Word will confirm that you want Excel formatting when you open a spreadsheet for a mail merge.  There are still a few complications in Word, but we’ll talk about those steps later.

You will need to do this on all of your computers:  your desktop, your laptop, your home computer, your work computer.  The information is NOT saved with your mail merge document in Word.  Maybe, just maybe, someday Microsoft will make this the default and not the option – I can dream, can’t I?