Cell References breaks when upload excel file from desktop to excel online.

Question

Hello,

I am having a long going issues with excel cell references breaking when uploading from Desktop to Excel Online.

The cell reference would appear #REF! instead of the cell reference in the correct worksheet.

For example. SUM( sheet1!F2:F2221 ) would look like SUM( sheet1!#REF! ).

No part of the file is referencing another workbook, all cells uses local references.

The excel file is used to teach staff how to use excel, so it has prewritten formulas as examples. When this file is transferred to another person and they upload it online. Cell reference breaks even thought I have already double checked it on the Desktop version, as well as Online Version.

This behavior seems to be random, it breaks at random locations and sometime data is missing. I am not sure what more can I do to prevent this.

I have read on forums that Excel Online has their own version checker when the file is first open and that is the cause of the cell reference breaking.

Any help or suggestion would be appreciated.

Answer 1

From the Account. This is the version.

Microsoft® Excel® for Microsoft 365 MSO (Version 2306 Build 16.0.16529.20100) 32-bit

Answer 2

Kindly Provide the version of the excel you are using

Answer 3

Here are some suggestions that can help you resolve the issue you are facing:

Use absolute references: To avoid cell reference problems, consider using absolute references in your formulas. An absolute reference ensures that the cell references remain fixed when you copy or move the formula. To do this, add a dollar sign ($) before the column and row references, like this: SUM(sheet1!$F$2:$F$2221).

Check for compatibility issues: Excel Online might encounter compatibility problems with certain features or formulas used in the desktop version. To identify potential issues, utilize Excel's Compatibility Checker. Resolve any compatibility concerns before uploading the file to Excel Online.

Fix broken links: If your workbook contains links to data in other files, those links may break when uploaded to Excel Online. You can fix broken links by updating the path of the source file or removing the link altogether.

Check for corrupted files: If your Excel file is partially or fully damaged, it can lead to cell reference issues. Attempt to repair the file using Excel's built-in repair tools or third-party software.

Avoid deleting rows, columns, or sheets: Deleting rows, columns, or sheets that contain cell references used in formulas can cause the #REF! error. To prevent this, avoid deleting these structural parts or use range references instead of individual cell references in your formulas.

Update Excel versions: Ensure that you are using the latest version of Excel on your desktop and Excel Online. Updating to the latest version can resolve compatibility issues and improve the overall performance of the application.

If you have tried these suggestions and are still experiencing issues, consider reaching out to Microsoft Support for further assistance. They can provide more specific guidance based on your particular situation.