Link to Excel

Reliable Linking Between Excel and PowerPoint / Word

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 automatically links the pasted object/text—a picture, table, chart, etc.—to the source range/chart. If you later modify the underlying Excel data, you can refresh 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 refresh 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 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.

Manage Excel Links

The following link operations can be accessed in the Links section of the Macabacus tab, the Manage Links dialog, and context menus for linked objects.

Refresh Links

Refresh links to reflect updated Excel workbook content in your presentations and documents. Neither the source workbook nor Excel need to be open to refresh linked shapes; Macabacus will open them as required. Macabacus will use link autodetection and version control as needed to identify the source workbook.

The time it takes to refresh links depends on many factors, including the number of links being refreshed at once, what types of links they are (e.g., pictures, text), Macabacus import/export and linking options, how many workbooks are linked, whether linked workbooks are already open in Excel or must be opened by Macabacus, whether Excel is running when the refresh begins, properties of the source workbook(s), other installed add-ins, and available system resources. If a link refresh seems to take longer than expected, try the following to improve performance:

• Ensure you have the latest version of Macabacus installed
• Ensure all linked workbooks are open in Excel before initiating the refresh
• Minimize the number of workbooks to which you link in a single presentation or document
• Clean up source workbook(s) using Macabacus' workbook optimization tools (particularly Name Scrubber)
• Disable Macabacus options to recolor fonts and check for formula errors
• Select the "printed" Copy Appearance option
• Avoid embedding workbooks (use pictures and/or charts instead)
• Disable other add-ins in Excel and PowerPoint/Word (temporarily, to see how this impacts performance)
• Restart your computer

View Source

Navigate to the source range or chart. If the source workbook is not already open, Macabacus will open it. Macabacus will use link autodetection and version control as needed to identify the source workbook.

Edit Links

The Edit Link dialog lets you 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 refresh automatically after editing them—you must initiate a link refresh separately.

Break Links

Remove the link between the shape and its underlying Excel data. Breaking links does not affect the content in linked objects, and is not normally required.

Managing Multiple Links

In PowerPoint and Word, you can manage multiple linked shapes with the Manage Links dialog, accessed by clicking the Macabacus > Links > Manage Links 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 refresh all links to a particular workbook, or 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.

Link Discovery

Linked content is typically not visually identifiable as such, but you can use the following tools to surface linked content in your presentations and documents.

• The Highlight Links tool accessed from the Macabacus > Links > More Linking Tools menu
• The Manage Links dialog
• The Explorer pane (PowerPoint only)

In addition, link-related controls on the Macabacus tab will become enabled when the selection contains linked content, and context menus for linked objects may contain an Excel Link menu.

Link Integrity

Macabacus employs a number of methods to ensure the highest possible reliability of links, a couple of which are described below.

Link Autodetection

If a workbook open in Excel has the same name as the workbook to which a PowerPoint/Word object is originally linked, Macabacus linking operations (Refresh 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., Dropbox, OneDrive), 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.

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 can prompt you to or transparently re-link to the former workbook.

Version Control

Prior to opening the source workbook to refresh links, specifically, Macabacus checks its folder for newer versions. If you have appended version numbers or dates to your file names (e.g., "My_Model_v4.xlsx" or "Balance Sheet 2020-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 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. Version control, specifically, is not support in SharePoint.

Cloud Storage Services

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 because on her PC the path to the shared Dropbox folder C:\Users\JaneDoe\Dropbox.

This problem is mitigated somewhat by link autodetection, described above, when the source file is open. To circumvent this problem altogether, ensure that the path to the local Dropbox, OneDrive, or other shared folder on your PC is properly configured in the General > Cloud Storage section of Macabacus' Settings dialog, and ensure that other Macabacus users with whom you share files do the same.

This documentation refers to Macabacus version 9.1.6. Some features and descriptions of these features may not
apply to older versions of Macabacus. Update your Macabacus software to take advantage of the latest features.