Excel-to-Excel Updates
Update data in destination workbooks based on data in source workbooks.
- Reliably update hundreds of ranges and tables in seconds
- Use links that don't break when you change file names or location or when you share with others
“Excel-to-Excel Updates” is a feature included in the Excel-to-Word Document Automation Add-In.
“Excel-to-Excel Updates” enables you to: 1) submit values from named ranges and tables in a source workbook. Then, 2) update data in matching named ranges and tables in a destination workbook.
Key Benefits
-
Save time and avoid errors
- Avoid manual copy/paste
- Update hundreds+ of items in seconds
-
Improve reliability
- Use robust links that don't break when you change file names and file location
- Share workbooks with others
-
Simple to use
- “Links” are named ranges and tables
- The add-in makes it easy to create and maintain the links
Use Cases
Summarize Results
- Summarize results from a details workbook to a summary/output workbook
Update Multiple Output Workbooks
- Import data from a reference workbook into multiple output/calculation workbooks
- Create multiple customer-specific output workbooks from a proprietary/detailed configurator workbook
Consolidate Workbook Data
- Consolidate content from multiple workbooks (or multiple contributors) into a single workbook
- Financial consolidation: for example, consolidate budgets from multiple departments
How It Works
Link the workbooks once, then update many times
| Link Workbooks Once | Update Many Times | |
|---|---|---|
| Source Workbook |
Name ranges/tables starting with an "Item Name Prefix" that you want to export. |
click "Submit Content" |
| Destination Workbook |
Name ranges/tables starting with an "Item Prefix (Destination)". For example, the prefix could be "in_", but it could be the same as the source prefix. The item's name should match a source item's name. For example, r_ItemName (source) will update in_ItemName (destination). Use "List Items" to view which ranges/names are matched to source items and to help add items that are not yet added. |
"List Items" to list which ranges/tables will be updated "Update Excel" to update all matching items (where destination item name matches source item name) |
Please Note: Excel-to-Excel updates values only (not formulas or formats)
Excel-to-Excel does not update formulas: the location of dependent cells can change from source to destination. For example, if user inserts a row in one of the files. Another example: the source range may contain a formula referring to a cell on another sheet that may not exist in the destination file. There's no way the add-in can determine the desired target cell location in the destination workbook. That is why the add-in only updates values. The add-in also allows you to optionally skip updating cells that contain formulas (that may already refer to desired cells in the destination file).
Excel-to-Excel does not update formats: You must format your destination range/table as desired. An advantage of tables is that table styles are applied when you re-size tables. If ranges are re-sized, formats will not follow.
Options
Item Prefix
- If the "Item Prefix (Destination)" is blank or the same as the source "Item Name prefix", then destination item names must match source names exactly. For example, r_ItemName (source) will only update r_ItemName in the destination.
- The destination prefix can be different from the source prefix. For example, if the Destination Item Prefix is "in_", then r_ItemName (source) will update in_ItemName (destination).
- Having a unique destination prefix can help avoid updating items that may be intended for updating Word/PowerPoint reports. You may also prefer to have unique source prefix, for example "out_" or "ex_" (for export) to separate items intended for reporting from items intended for Excel-to-Excel updates.
Skip Cells with Formulas
- "Optionally skip updating formulas (any cell starting with '=') in the destination. For example, you can avoid overwriting total/subtotal formula with values in the destination.
- You also may have cells that you want to contain values that come from other locations in the destination workbook, not the source."
- CAUTION: if you don't have "Skip Cells with Formulas" checked, ensure you don't click "Update Excel" in your source workbook: it will overwrite all of your formulas in named ranges/tables with values.
Resizing
- If checked, destination ranges/tables will be resized (e.g. expanded/shrunk) to match the size of the source item.
- "It is more common to allow resizing of tables because the table styles will move with the re-sized table.
- Ranges can be re-sized, but formatting will not follow re-sized ranges."
- CAUTION: ensure ranges/tables don't expand over (and overwrite) existing content.
Hidden rows and columns
- In the source workbook: If you filter or hide rows and you only want the visible rows to be transferred to the destination: ensure the source item has the "Include hidden rows & columns" property (List tab, under Manage Linkable Content) set to "No".
- In the destination workbook, it will always update all rows (visible and hidden). The "Include hidden rows & columns" property has no effect.
Next Steps
Add the Excel-to-Word Document Automation Add-in. See how HERE
- It is easy and safe to install from the Microsoft Store from within Excel
- You can try it for free – no registration required. Optionally sign in to increase usage.
"Insert Sample Content" from the Start tab of the add-in to try out a real example. See the “E2E” worksheet.
Link and update your own workbooks