How to Link Excel-to-Excel Tutorial

Excel-to-Excel updates is a feature of the Excel to Word Document Automation add-in. In this tutorial I will show you how to get started, including how to add the add-in and how to insert sample content, but we will primarily cover how to link content so you can submit data from Source workbooks and update data in Destination workbooks if you have not already added the add-in from the office store within Excel. It is very easy to install you can try it for free (sign in to increase usage limits.)

You can insert sample content from the start tab of the add-in. From the features sheet navigate to the E-to-E worksheet. It contains a simple example that you can update. This example contains ranges and tables for both the source and destination workbooks. Normally the source and destination tables would be in separate workbooks, but this allows you to easily experiment with updating content. Click submit content as if in the source workbook then click update Excel and we can see that the table and two ranges have been updated from what was submitted from the source workbook.

In the overview video we saw a simple example of how to submit from a source workbook then update the destination workbook. Before you can update workbooks you first need to link the workbooks once. To link the workbooks, you name ranges and tables start with a prefix in both the source and destination workbooks. Next, I will demonstrate how to link the workbooks. These example workbooks are not yet linked. If I click submit content it says no items have been submitted because no items have been named starting with the item name prefix, which in this example is r_, so I will name the three items starting with the r_ prefix.

First, I will name the table. When I click in the table, the table design menu appears. Paste or type the name into the table name box. I will name the two ranges. The first one I will name r_e-to-e customer name, just type or paste it into the name box. Then the last one. On the list tab we see that the three items are now named. You can also see the items in Excel name manager. Now when I submit content it indicates that the three items have been submitted. This exports the values in the name items to a single storage location for the user. Subsequent submits overwrite prior submits so only the most recent submit is available to the destination.

Submitting is the same whether you are performing Excel-to-Word, Excel-to-PowerPoint, or Excel-to-Excel updates. In the destination add-in, open the Excel-to-Excel update section. If I click list items or update Excel it says that no items start with a prefix. In this case my destination item prefix is in_. I use in to indicate that the data is coming into those ranges. I use a different prefix to separate any items that I may want to use to submit for Excel-to-Word and Excel-to-PowerPoint updates. I often use r_ for those. I could also use a different prefix in the source for Excel-to-Word and PowerPoint versus Excel-to-Excel items.

To add items to the destination, I can either name items manually, as I did in the source workbook or I can use the add-in to add the items. I will start with the table; I will select the top left cell where I want the table to appear. Notice that there is an add column in the unmatched Source items table. For the table I will click TBL (which is for table) and it adds the table but starting with a destination prefix. Now if I click list items, it shows that the table has now been named and matches a source item. I will now add the two ranges. I will format the second range with borders so I can see where it is.

Now if I click list items, it indicates that all three items are named, so I can now click update Excel and we can see that the destination items are updated. Note that the add-in only transfers and updates the values, not formulas or formats. To make the range of the same format as a source, I will just copy and paste the format from the source. In many cases your workbooks will already have the ranges and tables you just need to name them starting with prefix. Now whenever I make changes to the source, and then submit, and then update Excel, in the destination the content gets updated to match the source.

The add-in can update many ranges and tables in seconds. Here is an example where the add-in submits from the source workbook then updates 49 items on multiple worksheets in the destination workbook. This is the sample content workbook, available on the start tab of the add-in.

Finally, two optional features; skip cells with formulas and resizing so the destination range size matches the source range. You can choose to skip updating of cells that contain formulas (which is any cell with a value starting with an equal sign.) For example, you can avoid overwriting total or subtotal formulas with values. You can see that those cells contain formulas. In this example tax comes from another cell in a destination it is also a formula. After I submit from the source I will check skip cells with formulas, then update. You can see that it updated the values but did not overwrite the formulas.

Now I will uncheck skip cells with formulas, then update and we can see that it replaced all formulas with values from the source. Be careful that you do not uncheck skip cells with formulas then submit and update Excel all from your source workbook, because it will overwrite all of your formulas in named ranges and tables with values. Destination ranges and tables can be resized to match the size of the source item. If you click list items, it will show items that would be resized. If you have resized checked then update Excel resizes items, then updates them.

It is more common to allow resizing of tables because the table styles will move with the resized table. Ranges can be resized, but formatting will not follow resized ranges. For example, if you insert a row in the source in the destination workbook the formats will be offset by one row compared to the values. So, if you have row specific formatting and you need to resize ranges it may be easiest to manually resize or reformat them prior to the update. Also ensure that resized ranges or tables do not expand over and overwrite existing content.

By default, the add-in will submit content in hidden or filtered rows and columns. On the list tab under list and manage items you can change that default by selecting the item then set include hidden rows and columns to no. Now it will only submit the visible content, and the add-in will resize the destination table to fit the source data. In the destination workbook it will always update visible and hidden rows the same. The include hidden rows and columns property has no effect. I recommend that you insert the sample content from the start tab of the add-in so you can try it yourself and then link and update your own documents. Thank you.