Readdy Write  
0,00 €
Your View Money
Views: Count
Self 20% 0
Your Content 60% 0

Users by Links 0
u1*(Content+Views) 10% 0
Follow-Follower 0
s2*(Income) 5% 0

Count
Followers 0
Login Register as User

Word: Form letter from pivot table

27.09.2018 (👁6173)


 

How to create a form letter in Word that contains the data from an Excel Pivot table?

 

Task (customer):

I have a list of addresses that I have in Excel. For easier filtering, I changed the data to a pivot table.

Now I would like to create serial letters from the filtered Excel Pivot addresses in Word.

 

Solution:

You can easily create form letters from Excel in Word. However, when linking data sources from Excel, only tables and named namespaces are offered for selection.

So you have to put in Excel the address range as a name variable and then connect this in Word.

 

Manual:

Excel and Word

 

1) Prepare Excel addresses

Word has address data as raw data. In this example, the data is entered manually.

 

From this data, a classic pivot table was created.

In Pivot please change the mode to Classic view and switch off all partial results of the cells.

 

To create the filtered addresses, you must now mark the area that should be the output for Word.

In Pivot you can easily filter the data in the header line. Like here the field: city

 

Name address range:

From the bottom right, mark the area up to the upper left corner.

Here from cell 24G to A4

 

Then enter the address in the upper left corner.

As a result, the area of ​​A4: G27 will be found under address array arr addresses in the future.

 

The area should be chosen large enough. For post-processing you can use the address range at any time

under Menu -> Formulas -> Ribbonbar -> Name Manager

To be changed

 

 

 

2) Create Word Mail Merge

 

In Word you create a new document and select: Menu-> Shipments-> Select recipient

And then use existing list ..

 

Then select yourself up to the Excel file and click on the Open button

 

 

Here you will find the selection arrAdressen

 

Then you have to remove the empty entries in the future, by adjusting the import filter.

On Menu-> Shipments-> Edit recipient list

And then open the filter on last name

 

Then select the selection on (not empty). As a result, only real entries will be imported in the future.

 

This shrinks the result list to the Pivot Filtered List.

 

Now you just have to insert the filter fields in the Word form letter and then examine the preview

 

In the preview, only the reduced mail merge selection will appear