156 views since Nov 26, 2023

@power-reconcile

Get Power PDF Filler add-in from Microsoft Store: 👉 ​ ​https://appsource.microsoft.com/en/pr... 👈​

00:00 The challenge: PDF Form fields are not created in the order we preferred
00:49 Load PDF Files and generate template Excel sheet
01:09 Preview PDF Form to find out the order visually
02:08 Use Excel Formula to link the data from Registrations sheet to the generated template sheet
03:18 Update data in Registrations sheet to confirm this approach works
03:50 View the completed PDF Forms that are automatically populated by Power PDF Filler

Hello and welcome to our tutorial on using Power PDF Filler for seamless data mapping from Excel to PDF forms. Often, we find ourselves stuck when the fields of a PDF form aren't in the same order as our data in Excel. This mismatch can make data mapping a challenging task.

In this video, we'll explore a solution that avoids the need to recreate PDF form fields from scratch, a task that is sometimes not feasible. We'll demonstrate how to generate a new template sheet within Excel and link your data(in this example, it is Registrations sheet) to this template sheet(in this example, it is the sheet named S20231206T201312), simplifying the entire process.

Later, all you need to manage is the data from Registrations sheet, which will be populated into the PDF form as you wish.

Let's dive into a practical demonstration using a sample PDF form. Our goal is to batch populate this form using data from an Excel sheet titled Registrations.

Step 1: We begin by opening the Power PDF Filler add-in within Microsoft Excel. Here, we select our PDF file and click 'Next.'

Step 2: We click 'Generate' to create a template sheet. Click “Next”.

Step 3: Now, we click 'Preview.' This step is crucial as it allows us to visually understand the linkage between the Registrations sheet and our newly generated template sheet.

In the PDF Preview window, observe how the 'Given Name' and 'Family Name' fields are ordered correctly. However, you'll notice discrepancies like Address 1 field being labeled as 17th in the PDF Form, while it should be the 3rd one. This misalignment is what we aim to fix.

To address this, we'll update the header row in the generated template sheet, making it easier to link the data in the Registrations sheet later. We'll focus only on the fields that matter to us, which are the top 7 text fields in the PDF form.

Once the headers are updated in the generated template sheet, we use Excel Formula for data linkage. For example, 'Given Name' is linked with =Registrations!A2. Family Name with =Registrations!B2. Address 1 is positioned as the last column in our template. Let's update it with =Registrations!C2. We'll continue this process for all the necessary fields.
There is a 0 in Address 2, which is not desired.

For assistance, we use Power GPT, our latest Excel AI Assistant, to help us adjust the formula accordingly.

After linking the data, we clean up any fields we don't need.

Next, click “Preview” again to ensure everything is correctly aligned and the content looks good.

Now we should be able to update the data in our Registrations sheet and it should automatically change the data in the generated template sheet.

Let’s give it a try.

Awesome!

Finally, let’s apply the formula to the entire column. For assistance, we use Power GPT again.

With this done, we can populate data into PDF forms as usual. When the job is completed, we can save the generated PDF files and take a look.
Using this approach, it ensures that our data is managed in an order we prefer, independent of the PDF form's fields arrangement.

We hope this tutorial simplifies your experience with batch filling PDF forms, especially when dealing with out-of-order fields.

Thank you for watching!

If you have any questions or suggestions, please feel free to comment below or reach out to us via email.