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.
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.
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.
Let’s walk through the entire process. We’ll start with setting up the Excel file.
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.
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 now for the moment of truth … the 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 …
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!
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.
The best of the best. You are learning and this is awesome, thanks Linda CPA
Thank you very much for sharing your knowledge!
Thank you so much, Jeff! I appreciate these extra info updates; they’re a nice benefit of being a student of Excel Univ.
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?
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 🙂
Awesome. I’ll give it a try then. 🙂
Awesome man, I really hope it works for this situation!!
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.
Thxs Jeff for the update!
I chanced upon this while trying to automate the preparation of financial statements. I had been using the paste-special=>excel embedded object (link) to update Excel objects in Word. But when I tried to convert my Word document from .doc or .docx format, the Word document would take so long to open (due to it trying to download the source file each time for every Excel object).
Anyway, may I ask where are the Excel elements uploaded to, so that Word can pick them up? Is some form of cloud service required for the add-in?