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.
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:
Linking with Macabacus has a number of important advantages over native Office linking:
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.
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.
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 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:
If multiple instances of your Office applications are running, add-ins may be unable to reliably determine which to reference. Although Macabacus uses advanced methods to mitigate this native limitation, we recommended having a single instance of Excel and PowerPoint/Word running when working with links. Check for multiple instances in Task Manager on the Details tab.
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.
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.
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.
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.
The Source Range column in Manage Links dialog shows the address of the cell(s) to which an object in PowerPoint or Word is linked. Because Macabacus links to the range object itself, rather than the range address, this address is strictly for informational purposes and may be incorrect at times. Think of this address as a "last known" address.
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.
When performing a find/replace operation on all presentations or documents in a folder, be as explicit as possible in your find/replace inputs—use longer strings—to minimize the risk of unintended replacement. Also, note that Macabacus does not check the validity of new paths to source files created by your find/replace operation.
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.
Macabacus > Links > More Linking Tools
menuIn 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.
Macabacus employs a number of methods to ensure the highest possible reliability of links, a couple of which are described below.
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.
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.
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.4.5. 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.