Using Catalyst Excel Files in SharePoint

If you're working with a Catalyst-connected Excel file stored in SharePoint, OneDrive, or Teams and experiencing crashes, merge conflicts, or refresh failures, you're encountering a platform limitation rather than a Catalyst defect. Most issues stem from how Excel handles external data connections in shared, auto-save enabled environments where multiple users attempt to edit simultaneously. This guide explains what's happening and how to work around it.

Common Symptoms You May Experience

When Catalyst-connected Excel files are accessed by multiple users in SharePoint, you may see several types of errors or unexpected behavior. These symptoms typically appear when Excel attempts to merge real-time changes while maintaining external data connections.

Unexpected Crashes and Display Issues

• Excel closing unexpectedly without warning
• Screen flickering or jumping during use
• Data failing to refresh or load correctly
• Workbook becoming unresponsive during filter or pivot operations

Merge Conflict and File Lock Errors

You may encounter messages such as:

These messages indicate Excel has detected conflicting changes and can't determine which version to keep. This is especially common when the file contains OLAP formulas, pivot tables, or other complex data connections like those used by Catalyst.

Why This Happens

Catalyst Excel files rely on live external data connections through the EBM Excel Add-In and Excel's connection engine. While these connections work reliably in single-user scenarios, they conflict with Excel's real-time collaboration features.

What Is Co-Authoring?

Co-Authoring is Microsoft Excel's feature that allows multiple users to edit the same file simultaneously when stored in SharePoint, OneDrive, or Teams. Changes normally appear in real time without requiring manual save or refresh. This works well for standard workbooks with formulas and simple data.

The Platform Limitation

Excel's Co-Authoring feature was designed for standard workbooks. It does not reliably support:

• External OLAP or Analysis Services connections
• Background data refresh processes
• COM add-ins that interact with workbook structure
• Simultaneous refresh operations from multiple users
• Complex formulas that cause slow recalculations

Contributing Factors

Several additional factors can increase the likelihood of conflicts:

• Slow recalculations: OLAP formulas pulling from Catalyst can keep the file "in use" longer, preventing Excel from syncing changes properly
• Device sync delays: If your device isn't syncing quickly with OneDrive or SharePoint, Excel can't update changes in real time
• Inconsistent Excel versions: Users on different versions of Excel (e.g., Excel 2019 vs Excel 365) can handle co-authoring differently, leading to compatibility issues
• Network interruptions: Temporary connection drops to SharePoint or OneDrive can create version conflicts

When multiple users open and edit a Catalyst-connected file at the same time, Excel attempts to merge workbook changes in real time, save to SharePoint automatically, maintain active cube connections, and recalculate pivots and formulas. These processes can conflict with each other and cause instability.

Best Practices for Catalyst Files in SharePoint

Following these guidelines will help you avoid conflicts and maintain stable access to your Catalyst-connected Excel files.

1. Always Use Excel Desktop

Do not use Excel Online for Catalyst-connected files. From SharePoint, select Open in Desktop App to ensure full compatibility with external data connections.

2. Avoid Simultaneous Editing

Treat Catalyst files as analytical tools, not collaborative working documents. If multiple users need access:

• Coordinate usage to ensure only one person is editing at a time
• Create separate working copies for different users or departments
• Avoid refreshing data while others are viewing or editing the file

3. Turn Off AutoSave

AutoSave can trigger cloud saves during refresh or recalculation, which increases crash risk. Turn AutoSave off while working in Catalyst files.

To disable AutoSave: In Excel, locate the AutoSave toggle in the top-left corner of the ribbon and switch it to Off.

4. Confirm Trust Center Settings

In Excel Desktop, verify that external connections are enabled to prevent connection errors:

1. Go to File → Options
2. Select Trust Center from the left menu
3. Click Trust Center Settings
4. Navigate to External Content
5. Enable options for external data connections as needed

5. Standardize Excel Environment

For stability across your team:

• Use 64-bit Excel (recommended for Catalyst)
• Ensure all users are on a consistent Microsoft update channel
• Confirm everyone is using the latest EBM Excel Add-In version

6. Separate Data Tabs from Editing Tabs

If your workflow requires collaboration, consider structuring your files to minimize conflicts:

• Keep Catalyst-connected data tabs (with pivots, OLAP formulas, and cube connections) in a separate file
• Create a second file for collaborative editing where team members input data, make notes, or build presentations
• Link the editing file to the data file using cell references or formulas
• This separation prevents multiple users from triggering refresh conflicts on the same file

7. Verify OneDrive/SharePoint Sync

Ensure your local OneDrive or SharePoint sync is functioning properly:

1. Check the OneDrive icon in your system tray (Windows) or menu bar (Mac)
2. Verify it shows "Up to date" or a green checkmark
3. If you see sync errors or warnings, resolve those before opening Catalyst files
4. Consider pausing sync temporarily while working in complex files, then manually sync when finished

If You Must Use Co-Authoring

If your team needs live collaboration on a file despite the risks, follow this workaround to minimize conflicts.

Temporary Add-In Disable Method

1. All users should disable the EBM Excel Add-In before editing
2. With the add-in disabled, basic Excel connection refreshes still work through Data → Queries and Connections → Connections
3. Re-enable the add-in only when a full Catalyst refresh is required (for example, after structural changes like new hierarchies or attributes)
4. Use the EBM Office Bridge ribbon → Refresh All Cubes for full refresh

How to Disable or Enable the EBM Add-In

1. In Excel, go to File → Options
2. Select Add-Ins from the left menu
3. At the bottom, change the dropdown to COM Add-Ins and click Go…
4. Uncheck EBM Excel Add-In to disable it, or check it to re-enable
5. Click OK

Troubleshooting Decision Tree

Use this decision tree to quickly identify and resolve your specific issue:

Summary: When Catalyst Excel Files Work Best

Catalyst-connected Excel files are stable and reliable when used with the following configuration:

When treated as collaborative cloud documents with multiple simultaneous editors, conflicts are likely. This is a limitation of Excel's platform, not a problem with Catalyst.

Looking Ahead

EBM is actively enhancing the EBM Excel Add-In. Future versions will require reinstalling when they become available and will aim to support Co-Authoring alongside other Microsoft collaboration features. We'll notify customers when improved co-authoring support is available.

Need Help?

If you continue experiencing issues after following these guidelines, we're here to help. To expedite resolution, gather the following information before submitting a request.

Required Diagnostic Information

Pre-Support Checklist

Before contacting support, verify you've completed these steps:

• ☐ Confirmed using Excel Desktop (not Excel Online)
• ☐ Turned off AutoSave
• ☐ Verified no other users are currently editing the file
• ☐ Checked Trust Center settings for external content permissions
• ☐ Restarted Excel and reopened the file
• ☐ Confirmed OneDrive/SharePoint sync shows "Up to date"
• ☐ Tested with EBM Add-In both enabled and disabled
• ☐ Cleared Excel's cache (File → Options → Advanced → General → Empty Auto Recover folder)

Common Advanced Issues

Catalyst-connected Excel files work reliably in Excel Desktop when AutoSave is disabled and only one user edits at a time. The merge conflicts and crashes that occur with co-authoring are due to Microsoft Excel's platform limitations with external data connections, not Catalyst itself. By treating these files as analytical tools rather than collaborative documents, coordinating usage across your team, and using Excel Desktop with proper settings, you can avoid data loss and maintain stable access to your Catalyst data.