Link to Excel

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 or chart to PowerPoint or Word, Macabacus automatically links the pasted object to the source range or chart in Excel. If you modify the Excel data later, you can refresh the linked shape in PowerPoint or Word with a single click to update the data, ensuring your presentations and documents are always current.

While native Office functionality allows linking between Excel and PowerPoint/Word, it comes with significant limitations that often render it practically useless:

• 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 — Pasting cells as links in PowerPoint/Word carries over all formatting and features from the source range, including font color-coding, comment indicators, formula inconsistency indicators, page break preview borders, and gridlines. These elements are inappropriate for presentation-quality materials.
• Bloated file size — Linking cells or charts requires embedding the source workbook in PowerPoint/Word. A presentation with multiple linked tables and charts will contain multiple embedded copies of the source workbook, significantly increasing the file size of the document.

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

• Reliable linking — When Macabacus links to a cell range, it connects to the range object itself rather than the range address. This ensures robust links that can only be broken if the linked range is deleted entirely or if the source workbook is moved, renamed, or deleted. Macabacus uses range names that refer to linked ranges, independent of their actual addresses, similar to the approach used by leading vendors of linking technologies such as FactSet/DealMaven.
• Control over output formatting — Macabacus can automatically adjust the formatting of exported cells to give your output a professional appearance. This includes recoloring fonts to your default color, hiding gridlines, cell comment indicators, formula inconsistency indicators, blue page break preview borders, and chart borders. Many of these options are customizable and can be turned off if not needed.
• Smaller file sizes — Macabacus allows you to export cells to PowerPoint/Word as linked pictures, which are much smaller in size compared to embedded workbooks.
• Version control — Macabacus automatically detects the source range or chart in open workbooks, providing a seamless linking experience in various workflow scenarios. It also checks for newer versions of the source workbook by inspecting file names and other properties of workbooks in the same folder. If newer versions are found, Macabacus prompts you to relink to the newer workbook or refresh from the original source. Using version numbers or dates in your workbook names (e.g., "Model v2.xlsx") helps Macabacus identify newer versions.
• More export options — With Macabacus, you can export a cell range or chart to PowerPoint/Word and paste it as a picture or graphic (recommended), table, chart, or embedded workbook. 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 cannot be achieved using 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. These hidden names are not visible in Excel's Name Manager but can be viewed in Macabacus' Name Scrubber. Macabacus stores the unique name within the destination PowerPoint presentation or Word document, allowing it to locate the linked cells for actions like updating links or viewing the source.

By linking to a range name, Macabacus ensures reliable references to the source range object, even if the structure of your source workbook changes. Additionally, when you save your source workbook as a new version, all range names created by Macabacus for linking carry over, enabling you to relink previously linked objects in PowerPoint/Word to the new source workbook.

If you want to add a column to the right of the source range so that your PowerPoint output shows six columns instead of five, you can do so by inserting the new column within the source range. This leverages native Excel functionality and ensures that the range name and link integrity are maintained.

When Macabacus links to a chart, the link refers to the chart by its name—among other attributes— as shown in Excel's Selection Pane. Therefore, the name of a linked chart must:

1. Be Unique: The chart name must be different from the names of other charts on the same worksheet. If multiple charts share the same name, Macabacus may not be able to distinguish between them and may reference the wrong chart.
2. Remain Unchanged: The name of the chart should not change after linking. Changing the chart name after linking may break the link, causing Macabacus to reference the wrong chart or fail to update the link properly.

Ensuring unique and consistent chart names helps maintain reliable links and prevents issues with chart references during updates or modifications.

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 required to refresh links depends on several factors, including the number and types of links, Macabacus import/export and linking options, the number of linked workbooks, the status of linked workbooks (open or closed), whether Excel is running at the start of the refresh, properties of the source workbooks, other installed add-ins, and available system resources. If a link refresh takes 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

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.

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 update these links, follow these steps:

1. Open the Manage Links dialog and go to the Find/Replace tab.
2. Specify the old text in the source file paths you want to replace.
3. Enter the new text you want to replace it with.
4. Perform this operation on all links within the active presentation or document, or on all presentations and documents in a folder.
5. Spot check the results of your find/replace operation once it is complete.

To minimize the risk of mislinking a large number of files due to an improper find/replace operation, only Macabacus account administrators or Windows administrators can perform a find/replace operation on all presentations or documents in a folder. Note that this feature is not available in SharePoint computing environments.

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.

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

When a workbook open in Excel has the same name as the workbook to which a PowerPoint/Word object is linked, Macabacus prioritizes that workbook for linking operations (e.g., Refresh Link and View Source). This scenario often occurs when:

• Renaming workbooks (e.g., from "Model v1.xlsx" to "Model v2.xlsx").
• Working with local versions of cloud-stored workbooks (e.g., Dropbox, OneDrive).

Checking Process

1. First Check: Macabacus first checks the open workbook with the same name as the linked workbook.
2. Fallback: If the source range/chart is not found, Macabacus searches all other open workbooks.
3. Multiple Matches: If multiple open workbooks contain the source range/chart, Macabacus uses the most recently modified workbook.
4. Final Attempt: If the source is not found in any open workbook, Macabacus attempts to open the source workbook from its last known file path.

Re-linking Prompt

If the source range/chart is found in a workbook with a different file path than the original, Macabacus can prompt you to re-link to the new workbook or transparently re-link to it.

Before opening the source workbook to refresh links, Macabacus checks for newer versions in the same folder. If you append 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 more recent versions.

Procedure

1. Search for Newer Versions: Macabacus looks for file names with higher version numbers or later dates in the same folder.
2. Prompt for Re-linking: If potentially newer versions are found, Macabacus prompts you to re-link to them when updating links.

Requirements

• Version numbers and dates must appear at the end of the file name.
• Dates must be separated from the rest of the file name by a space or underscore.

Limitations

• Version control is not supported in SharePoint.

Macabacus links typically point to the full file path of the source workbook. However, if the workbook is saved in a local Dropbox, OneDrive, or other shared folder, this path may 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 her path is C:\Users\JaneDoe\Dropbox.

Mitigation Strategies

1. Link Autodetection: When the source file is open, Macabacus uses link autodetection to find the correct file.
2. Configure Cloud Storage Paths:
   • Ensure the path to the local Dropbox, OneDrive, or other shared folder on your PC is correctly set in the General > Cloud Storage section of Macabacus' Settings dialog.
   • Ensure other Macabacus users with whom you share files also configure their paths correctly.

By properly setting up the cloud storage paths in Macabacus, you can avoid issues with inaccessible file paths across different users' PCs.

_{This documentation refers to the latest Macabacus version. 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.}