Link to Excel

Create reliable links between Excel and PowerPoint / Word that update with the click of a button.

Overview

One of Macabacus' most popular features is the ability to link PowerPoint and Word to cell ranges and charts in Excel. When you export a range/chart to PowerPoint/Word, Macabacus links the pasted shape—a picture, table, chart, etc.—to the source range/chart. If you later modify the underlying Excel data, you can update the linked shape in PowerPoint/Word with the click of a button to reflect the new data.

Macabacus vs. Native Office Linking

Problems with Native Office Linking

Linking between Excel and PowerPoint/Word is possible with native Office functionality, but the limitations of this functionality render it practically useless. These limitations include:

• Unreliable linking — Native Office links to Excel are easily broken because Office links to a cell range address, not the range itself. Suppose you copied range A1:A5 in your source workbook and then pasted it as a link in PowerPoint. Now, when you insert a single row above original cell A1 in your source workbook, the link still refers to cells A1:A5, rather than A2:A6, as we would expect.
• Undesirable output formatting — When you paste cells as links natively in PowerPoint/Word, any features or formatting present in the source range will be present in the output. This includes font color-coding, red cell comment indicators, green formula inconsistency indicators, blue page break preview borders, and gridlines. These features and formatting have no place in your presentation-quality materials.
• Bloated file size — When pasting cells or charts into PowerPoint/Word as links, you must paste embedded workbooks. If you have a presentation with twenty tables and charts linked to Excel, you will have twenty copies of the source workbook embedded within your presentation. This may increase the file size of your presentations and documents considerably.

Advantages of Macabacus Linking

Linking with Macabacus has a number of important advantages over native Office linking:

• Reliable linking — When Macabacus links to a cell range, it links to the range object itself rather than the range address. This results in robust links that can only be broken if the linked range is deleted altogether or the source workbook is moved, renamed, or deleted. To do this, Macabacus uses range names that refer to linked ranges and are independent of their actual addresses. This is the same approach used by leading vendors of similar linking technologies (e.g., FactSet/DealMaven).
• Control over output formatting — Macabacus can automatically recolor certain fonts to your default font color (typically black), hide gridlines, hide cell comment and formula inconsistency indicators, hide blue page break preview borders, hide chart borders, and more when exporting cells to PowerPoint/Word to give your output a professional appearance. Many of these options are customizable, and can be turned off if not desired.
• Smaller file sizes — Macabacus lets you export cells to PowerPoint/Word as linked pictures that are much smaller in size (in terms of bytes) than embedded workbooks.
• Version control — Macabacus automatically detects the source range or chart in open workbooks to provide a seamless linking experience in a variety of workflow scenarios. Macabacus also checks for newer versions of the source workbook by inspecting the file name, among other properties, of other workbooks in the same folder. If potentially newer versions of the source workbook are found, Macabacus prompts you to relink to a newer workbook or update from the original source workbook. Appending version numbers to your workbooks names (e.g., "Model v2.xlsx") or dates will help Macabacus identify newer versions of source workbooks.
• More export options — With Macabacus, you can export a cell range or chart to PowerPoint/Word and paste it as a picture (recommended), table, chart, or embedded workbook, as applicable. You can also export the value of a single cell as in-line text within a paragraph, which is extremely useful for authoring memos containing spreadsheet data and something that cannot be achieved using native Office linking.

How Linking Works

Linking to Cells

To understand how Macabacus links to cells, it is first important to understand range names (or "defined names," if you prefer). Range names refer to a cell or range of cells, and are normally managed using Excel's native Name Manager. They are commonly used in formulas like =SUM(FirstQuarterSales), where the name "FirstQuarterSales" refers to cells C5:F5, for example. Suppose we insert a row above row 5. Now, the name "FirstQuarterSales" refers to cells C6:F6. By using the range name "FirstQuarterSales," we can ignore the actual address of the cells to which the name refers, which can change as you insert/delete rows/columns or otherwise change the structure of your workbook.

When you export a range to PowerPoint or Word, Macabacus automatically links to the range by defining a unique, hidden name for the range in the source workbook. Hidden names are not visible in Excel's Name Manager, so you cannot view these names there, but you can view hidden names in Macabacus' Name Scrubber. Macabacus then stores the unique name within the destination PowerPoint presentation or Word document so that Macabacus knows where to find the linked cells when you want to work with links (e.g., update links, view the source) in those applications.

Since Macabacus links to a range name, which reliably refers to the source range object (unless the range was deleted altogether), rather than the source range's unreliable address, you can safely modify the structure of your source workbook without affecting link integrity. In addition, when you save your source workbook as a new version, all range names created by Macabacus for linking purposes carry over to the new version (as do all names), making it possible to relink previously linked objects in PowerPoint/Word to the new source workbook.

Now, what if we want to add a column to the right of the source range, so that our output in PowerPoint shows six columns instead of five? Since range names created by Macabacus for linking work like any other range name, this is simply an exercise in working with native Excel functionality—you simply insert the new column within the source range, rather than next to it.

Linking to Charts

When Macabacus links to a chart, the link refers to the chart by its name—among other things—as shown in Excel's Selection Pane. Therefore, the name of a linked chart must (a) be different from the names of other charts on the same worksheet, and (b) not change after linking. If multiple charts on a worksheet share a name, Macabacus may be unable to distinguish among them and for linking purposes may reference the wrong chart.

Link Autodetection & Version Control

If a workbook open in Excel has the same name as the workbook to which a shape or other object is originally linked, Macabacus linking operations (Update Link and View Source) look in that workbook first for the source range/chart. This workbook's file path may be different from the path to the workbook to which you originally linked, which is common when "saving up" your source workbooks (e.g., renaming from "Model v1.xlsx" to "Model v2.xlsx"), working with local versions of source workbooks shared using cloud storage services (e.g., Box), and in other scenarios.

If the source range/chart is found in an open workbook with the same name as the source workbook, Macabacus looks no further. However, if the source range/chart is not found in that workbook, Macabacus looks in all other open workbooks. If multiple open workbooks contain the source range/chart, Macabacus will use the most recently modified workbook. If Macabacus does not find the source range/chart in any open workbook, it will attempt to open the source workbook from its last known file path. Prior to opening the source workbook to update links, specifically, Macabacus checks the source file's folder for newer versions of the source file.

If the workbook in which the source range/chart is ultimately found does not have the same file path as the workbook to which you originally linked, Macabacus transparently re-links to the former workbook.

Dropbox & OneDrive

Macabacus links normally point to the full file path of the source workbook. However, if this workbook is saved in a local Dropbox, OneDrive, or other shared folder, this path would be inaccessible from other users' PCs. For example, if John Doe creates a link to C:\Users\JohnDoe\Dropbox\Source.xlsx, Jane Doe cannot access the source workbook using Macabacus because on her PC the path to the shared Dropbox folder C:\Users\JaneDoe\Dropbox.

This problem mitigated somewhat by link autodetection, described above, when the source file is open. To circumvent this problem altogether, specify the path to the local Dropbox, OneDrive, or other shared folder on your PC in Macabacus' Common Settings dialog, and ensure that other Macabacus users with whom you are sharing files do the same.

Managing Excel Links

Updating Links

Select one or more linked shapes in PowerPoint or Word and click the Macabacus > Update Link button. Neither the source workbook nor Excel need to be open to update linked shapes; Macabacus will open them as required. If you have appended version numbers (generally recommended) or dates to your file names (e.g., "My_Model_v4.xlsx" or "Balance Sheet 2015-12-31.xlsx"), Macabacus automatically searches for file names with more recent version numbers and dates in the same folder as the current source workbook. If any potentially newer versions of the source workbook are found, Macabacus prompts you to relink to them when updating links. For Macabacus to find newer versions of source workbooks, version numbers and dates must appear at the end of the file name. If you use a date in your file name, the date must be separated from the rest of the file name by a space or underscore character.

Editing Links

Select one or more linked shapes in PowerPoint or Word and click the Macabacus > Edit Link button. This opens the Edit Link dialog that allows you to change the source workbook, source range, source chart, or linked shape type. For example, you can use this dialog to relink a linked shape to a newer version of the source workbook, and change the linked shape from an embedded workbook to a picture. Links do not update automatically after editing them—you must initiate a link update separately.

View Source

Select one or more linked shapes in PowerPoint or Word and click the Macabacus > View Source button. Macabacus will navigate to the source range or chart. If the source workbook is not already open, Macabacus will open it.

Break Link

Select one or more linked shapes in PowerPoint or Word and click the Macabacus > Break Link button. This removes the link between the shape and its underlying Excel data.

Managing Multiple Links

In PowerPoint and Word, you can manage multiple linked shapes with the Manage Links dialog, accessed by clicking the Macabacus > Manage button. With the Manage Links dialog you can perform the operations described above on multiple linked shapes at once. For example, you would use this dialog to relink all linked shapes in a presentation to a newer version of the source workbook.

Find / Replace

When you move or rename folders containing presentations and documents with Macabacus links, those links may no longer point to the correct source files. To point links to the correct files after moving or renaming folders, use the Find/Replace tab in the Manage Links dialog. Specify the old text in source file paths you want to replace, and the new text you want to replace it with. You can perform this operation on all links within the active presentation or document, or all presentations and documents in a folder. You should spot check the results of your find/replace operation once it is complete.

To minimize the risk of inadvertent mislinking in a large number of files caused by an improper find/replace operation, you must be a Macabacus account administrator or a Windows administrator to perform a find/replace operation on all presentations or documents in a folder. This feature is not available in SharePoint computing environments.

This documentation refers to Macabacus version 8.8.15, and may differ from the PDF documentation
on your computer that installed with your version of Macabacus.