Excel to Word Document Automation

This is the next post in the add-ins series, where we are exploring various add-ins that enhance and extend the capabilities of Excel. In this post, we’ll take a look at the Excel-to-Word Document Automation add-in. In summary, it enables us to get Excel data into a Word (or Power Point) document. It creates a link between the documents making it easy to update the Word document for any Excel changes. It can bring in tables, graphics, and values. Once the link is established, the data will continue to flow even if you change sheet names, file names, or file locations.

Objective

Before we get too far, let’s get a general sense of what the add-in does.

It allows you to transfer values, images, and tables from Excel into Word (or Power Point). But … wait up Jeff … I can already do that with a standard Copy/Paste! Yes, and for one-time projects, a copy/paste is all you need.

However, if this is something you do on a recurring-basis, every day, week, or month, this add-in can be very helpful.

You basically identify the specific items in Excel that you want to transfer. Then, in your Word document, you retrieve the selected elements. Once this mapping has been established, you can click a button to have any Excel changes sent to your Word document.

While Excel has had this basic capability built-in for years or decades, this add-in addresses some of the traditional limitations. For example, this add-in will continue to work even if you change sheet names, file names, or file locations.

Plus, the add-in will continue to work if you insert cells, rows, or columns because the add-in relies on defined names … brilliant! Let’s get into the details.

App Store

The Excel-to-Word Document Automation add-in is available in the app store. To get to the app store, just click Insert > Get Add-ins. Install the Excel-to-Word Document Automation add-in and you’ll be ready to go.

Once installed, you’ll see a new Automate Content button on the Home tab.

Note: depending on your version of Excel, you may or may not have access to these add-ins.

Walkthrough

Let’s walk through the entire process. We’ll start with setting up the Excel file.

Excel

We have some stuff in Excel that we’d like to get into a Word document. For example, we want to get the following vendor name, table, and graph from Excel to Word:

Within the Add-in, we define the prefix that we will use to identify any Excel elements we wish to transfer. For example, I set my prefix to tfr_ as shown:

The next step is to use this prefix when naming the various elements you wish to make available to Word.

There are three elements I want to transfer: the Vendor name, the Table, and the Graph.

  • I gave the Vendor name cell the following name: tfr_name
  • I gave the Table the following name: tfr_table
  • And I named the Graph: tfr_chart

Once the individual elements are named, you click the Submit Content button:

You’ll then receive a notification indicating how may elements were uploaded:

Our Excel work is complete! We can now bring these elements into our Word document.

Word

In Word, we need to install the same Add-in. Just head to the app store and install it just as we did in Excel.

We begin by clicking the Get Excel Content button.

We can then select which elements to retrieve from the drop-downs and click the Insert Content/Update Link button:

And the Excel content appears in our Word document, and we can apply standard Word formatting as desired:

We can insert the table as well:

Click Insert Content and bam …

And finally our graph:

And bam…

And now for the moment of truth … the updates.

Updates

Let’s say we update our Excel file next period, like this:

We simply click the Submit Content button in the Excel add-in. The upload notification confirms the elements were successfully uploaded.

We head back to our Word document and click Update Document:

And bam, the updated company name appears, the new row in the table appears, and the next month in the chart appears …

Conclusion

This add-in will be VERY helpful if you create values, tables, or charts in Excel and need to export them to Word/Power Point on a recurring basis. If you have any other add-ins you’d like to suggest, please share by posting a comment below … thanks!

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.

9 Comments

  1. Linda pfingst on August 4, 2021 at 7:37 am

    The best of the best. You are learning and this is awesome, thanks Linda CPA

  2. Tenny on August 4, 2021 at 8:18 am

    Thank you very much for sharing your knowledge!

  3. Bill Palmer on August 4, 2021 at 9:34 am

    Thank you so much, Jeff! I appreciate these extra info updates; they’re a nice benefit of being a student of Excel Univ.

  4. Brendan on August 4, 2021 at 11:00 am

    Could you set up this connection between a template word doc and powerpoint and an excel file? For every new client we have a spreadsheet where we build a chart with products and pricing, then we need that chart to go into a word doc and power point. Right now we have “master” versions of those that we copy then edit. Could this connection between the xls and the doc & ppt survive a file copy to a new location?

    • Jeff Lenning on August 4, 2021 at 11:28 am

      Yo Brendan!! I believe so, yes. Although I haven’t tested this scenario, from what I can tell yes. The reason is that the Excel file elements go through an intermediate online service on its way to word/ppt. This is why renaming or moving files doesn’t break the connection 🙂

  5. Brendan on August 4, 2021 at 11:41 am

    Awesome. I’ll give it a try then. 🙂

    • Jeff Lenning on August 4, 2021 at 11:49 am

      Awesome man, I really hope it works for this situation!!

      Thanks
      Jeff

  6. Teresa on August 5, 2021 at 10:04 am

    This is awesome. Thank you for the tips and continued learning. I have completed Excel University through lesson 401. I have already applied many of the techniques to my daily work life. What a time saver. This is one more function to incorporate into my reporting.

  7. GiGI Zambrano on August 11, 2021 at 7:36 pm

    Thxs Jeff for the update!

Leave a Comment