Please Note: This article is written for users of the following Microsoft Word versions: 2007, 2010, and 2013. If you are using an earlier version (Word 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Word, click here: Wrong Values Merged from Excel.

Wrong Values Merged from Excel

Written by Allen Wyatt (last updated October 3, 2023)
This tip applies to Word 2007, 2010, and 2013


2

Derek is performing a mail merge in Excel, but some of the values are being imported incorrectly. For instance, a value that appears as 5.2 in Excel may be imported into Word, during the merge, as 5.20000000002. Derek is wondering why this occurs.

It is important to understand that the value in Excel is more than likely 5.20000000002, but is rounded or formatted in the worksheet so that only 5.2 is displayed.

The actual merging of data from the Excel workbook is done using a technology called OLEDB. This technology ignores the formatting of anything that is merged. Thus, you get the full number instead of the rounded or formatted number.

There are three possible solutions to this problem. The first has to do with changing the data in your Excel worksheet. This approach will only work if the values being merged from Excel are, in fact, values and not formulaic results. In that case, simply make sure that the shorter value (5.2) is in the cell instead of the longer value (5.20000000002).

The second possible solution is to try to coax Word to use DDE to do the data transfer from Excel rather than OLEDB. You do this by following these steps:

  1. Display the Word Options dialog box. (In Word 2007 click the Office button and then click Word Options. In Word 2010 and Word 2013 display the File tab of the ribbon and then click Options.)
  2. Click Advanced at the left side of the dialog box.
  3. Scroll down until you see the General section. (See Figure 1.)
  4. Figure 1. The advanced options in the Word Options dialog box.

  5. Make sure the Confirm File Format Conversion On Open check box is selected.
  6. Click OK.

Now, do your merge as you normally would. When Word goes to open the Excel file, it should prompt you for the way you want the connection to be established. You should choose DDE as the connection method; this assures that the formatting is moved over from the Excel worksheet along with the numbers.

The final solution is to modify the merge field used to transfer the value to the Word document. This solution works great if the values being merged in a specific field all follow the same general pattern. For instance, let's assume that the merge field normally appears as follows:

{MergeField MyValue}

If you want the field to always display the merged value with a single digit to the right of the decimal point, you can change the field so it appears like this:

{MergeField MyValue \# ""0.x""}

The curly brackets represent the merge field braces; they are not typed. You can display your current merge field by selecting it and press Alt+F9. Then you can add the appropriate switch. You can find additional information on how to format using picture switches at this Web site:

http://www.gmayor.com/formatting_word_fields.htm

WordTips is your source for cost-effective Microsoft Word training. (Microsoft Word is the most popular word processing software in the world.) This tip (9632) applies to Microsoft Word 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Word here: Wrong Values Merged from Excel.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Hiding the Reviewing Toolbar

Does the Reviewing toolbar keep popping up whenever you create a document? Here's a possible reason, along with what you ...

Discover More

Understanding Subroutines

When developing macros, you can create subroutines. This is a great way to reuse common code and make your programming ...

Discover More

Conditionally Deleting Rows

Want to delete a bunch of rows in a worksheet based on the value in a certain cell of each row? There are a couple of ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More WordTips (ribbon)

Using Mail Merge to Complete Documents

Mail merge can be used to put together groups of documents that rely on common information. This tip shows how mail merge ...

Discover More

Proper Comparisons for Dates in Merge Fields

How Word merges a data source with a document is controlled by merge fields. In those fields you may want to compare ...

Discover More

Checking Your Data File

When you get ready to merge a document with a data source, you'll want to make sure that everything is "as expected" ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in WordTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 2 + 8?

2018-07-04 23:06:03

Penny Edwards

I have used DDE for many years when merging using an Excel workbook. However, I understand that Microsoft disabled this functionality back at the end of 2017 because the feature was being used to install malware. We are really only left with a picture switch now.


2014-11-30 23:17:17

Phil Reinemann

In Mac Office 2011 I think it's the same as in the Word>Preferences> General, Confirm conversion at opening, but I have not confirmed that as I don't have anything to convert at this time.


This Site

Got a version of Word that uses the ribbon interface (Word 2007 or later)? This site is for you! If you use an earlier version of Word, visit our WordTips site focusing on the menu interface.

Videos
Subscribe

FREE SERVICE: Get tips like this every week in WordTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.