Bugging out and Stopping

[Gaute__]

I have an Excel file with multiple Tables, At most 6 people sit and work in it at the same time. 

We are having problems with the File Taking long to Load and that it crashed and freezes at random times. 

File Size is 1.20MB 

We have tried to move all the data over to another File to see if that helped. 

It Did not. 

Same happens both working in Browser and working in Excel Desktop with the SharePoint File. 

If u take the File out of SharePoint and work Locally and alone there is no Problems.

Any suggestions to what can cause this? 

[#2]

@Gaute__ 

If you're experiencing performance issues with an Excel file that's stored on SharePoint and accessed by multiple users simultaneously, there could be several factors contributing to the problem. Here are some potential causes and solutions:

1. Large File Size: Even though your file size is relatively small (1.20MB), if it contains complex formulas, large datasets, or embedded objects, it can still lead to performance issues. Consider optimizing the file by removing unnecessary data, simplifying formulas, and minimizing formatting.
2. Network Latency: SharePoint relies on network connectivity, and any latency or interruptions in the network can cause Excel to slow down or freeze. Check your network connection and ensure it's stable and fast enough to handle the file access.
3. Concurrent Editing: Multiple users editing the same Excel file simultaneously can lead to conflicts and performance issues. SharePoint does support concurrent editing, but it's essential to coordinate and communicate effectively to avoid overwriting each other's changes.
4. Versioning and Autosave: SharePoint automatically saves versions of files, and Excel for the web supports autosave functionality. While these features are beneficial for collaboration, they can also contribute to performance issues, especially if they're set to save too frequently or if there are many versions of the file.
5. Compatibility Mode: Ensure that all users are using compatible versions of Excel for the web or Excel desktop. Using outdated or incompatible versions can lead to compatibility issues and performance degradation.
6. Browser Compatibility: Different web browsers may have varying levels of compatibility with Excel for the web. Ensure that you're using a supported browser and that it's up to date.
7. Add-ins and Macros: If your Excel file contains complex macros or relies on third-party add-ins, they may not be fully supported or compatible with Excel for the web. Consider removing or optimizing them to improve performance.
8. Document Libraries Configuration: Check the configuration of the SharePoint document library where the Excel file is stored. Ensure that it's optimized for performance, and consider adjusting settings such as versioning, checkout requirements, and metadata.
9. Hardware Resources: Ensure that the servers hosting SharePoint have sufficient resources (CPU, memory, disk space) to handle the workload, especially during peak usage times.
10. Cache and Temporary Files: Clearing the browser cache and temporary files on your computer can sometimes improve performance by freeing up resources and resolving caching issues.

By addressing these potential causes and implementing the suggested solutions, you may be able to improve the performance of your Excel file in SharePoint. Additionally, monitoring and gathering feedback from users can help identify specific issues and areas for optimization. The text was created with the help of AI.

If all these steps don’t help, here is a link with information on how you can quickly find a solution that is most suitable for you.

Welcome to your Excel discussion space!

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

[#3]

@NikolinoDE 

Do you know of any way too see if Formulas is causing this problem. No other Excel files or other SharePoint documents is having this problem. Only this one File.

Like if some formulas are crashing or stopping due to wrong input or looping. Have tried the Error control in the Top Ribbon in Excel with no luck. 

[#4]

@Gaute__ 

To troubleshoot whether formulas are causing performance issues in your Excel file stored on SharePoint, you can use several techniques:

1. Formula Auditing Tools: Excel provides built-in tools for auditing formulas. You can use features like "Trace Precedents" and "Trace Dependents" to visualize the relationships between cells and identify potential circular references or excessively complex formulas.
2. Formula Evaluation: Excel has a feature called "Evaluate Formula" that allows you to step through the calculation process of a formula. This can help you identify specific parts of the formula that may be causing performance issues or errors.
3. Formula Optimization: Review the formulas in your workbook to identify any that are particularly complex or resource-intensive. Consider simplifying or restructuring these formulas to improve performance. For example, replacing volatile functions with non-volatile alternatives or using array formulas more efficiently.
4. Error Handling: Ensure that your formulas include appropriate error handling to prevent unexpected behavior or crashes. Use functions like IFERROR or IF(ISERROR) to handle potential errors gracefully.
5. Calculation Settings: Check the calculation settings in Excel to ensure that they are optimized for your workbook. You can adjust settings such as automatic vs. manual calculation and iterative calculation to improve performance.
6. Performance Profiling: Use performance profiling tools or add-ins for Excel to analyze the execution time of different parts of your workbook, including formulas. This can help you identify bottlenecks and areas for optimization.
7. Testing with Simplified Data: Create a copy of your workbook with simplified or reduced data to isolate the performance issue. Gradually reintroduce elements from the original workbook while monitoring performance to pinpoint the cause of the problem.
8. You can use the "Inquire" add-in in Excel to analyze and troubleshoot issues related to formulas, cell relationships, and workbook structure. The Inquire add-in in Excel is primarily designed to analyze workbooks stored locally on your computer. It may not directly support analyzing Excel files stored in SharePoint or other online platforms. However, you can still use Inquire to analyze a local copy of the Excel file that has been synchronized with SharePoint. You would need to download a local copy of the Excel file from SharePoint, open it in Excel on your computer, and then use the Inquire add-in to perform the analysis. Once you have the local copy of the Excel file open in Excel, you can follow the steps mentioned earlier to run the workbook analysis using the Inquire add-in. This will allow you to identify potential formula-related issues or other workbook structure issues that may be impacting performance.

By using these techniques, you can identify and address any formula-related performance issues in your Excel file stored on SharePoint.

If these steps don't help you, consider adding more information. Information such as the exact Excel version, operating system, storage medium, file extension, and if possible the file itself (without sensitive data). The text was created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

[#5]

Thank you for the Great Tips, We found out that it was a Problem with Conditional formatting Bugging out the whole Sheet

[#6]

I'm glad the tips helped you.
Furthermore, I wish you much success.