Excel Spreadsheet Not Populating Correctly

Question

I have two HP 64 bit machines, one running Windows 11 Home with Office Home and Student 2019 and the other running Windows 11 Pro with Office Home and Business 2021. I have a spreadsheet that won't open fully in the Windows 11 Home machine but will open fully on the Windows 11 Pro machine. The spreadsheet initially opens in "Protected Mode" and is not fully populated. When I take off the protection it fully populates for the Pro machine but not for the Home machine. I've attached screenshots of an example page for the machines both before removing the protection and, for the Pro machine only, after. So far for the Home machine I have,

Run SFC and DISM

Reloaded Office 19 Home and Student

Cleared the cache, history etc with Ccleaner

Checked the Excel settings for both machines and they are the same

I've also tried opening the spreadsheet on the Home machine using Open Office and Google Sheets and it still fails to open fully. It therefore implies it may be an operating system difference rather than something specific to Excel. I also thought it could be driver differences but which, I've no idea.

Any suggestions of what I could try next would be greatly appreciated.

Answer 1

Hello RayHalligan,

Welcome to the Microsoft Community.

I understand that you have different versions of Excel installed on your two devices, and that you are unable to open Excel files on your Windows 11 Home device, and understand how you feel.

Based on your description, you seem to have tried a number of options to troubleshoot the issue and I agree with your approach. In addition I would like to know from you about the reloading of Office 19 Home and Student that you mentioned, I would like to know from you how you did it? Is it a reinstallation of Office?

Have you tested if the problem occurs with specific files or with most Excel files? Suggest you try opening other files on both devices to check if it is a specific file or not.

Suggest you try the following options first to troubleshoot if it is an Excel issue:

1. Disable Add-Ins: You can click File -> Options -> Add-Ins, as shown in the figure, click the bottom to select COM Add-Ins and disable:

Excel COM add-ins and Automation add-ins - Microsoft Support

2. Modify the registry: sometimes by modifying the registry can repair some Excel problems, you can refer to this article to modify the

 Registry keys that control the File Repair feature - Microsoft 365 Apps | Microsoft Learn

Disclaimer: Generally, modifying registry subkeys or work group is intended for advanced users, administrators, and IT Professionals. It can help fix some problems, **however, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For further protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs.**For more information about how to back up and restore the registry, click How to back up and restore the registry in Windows - Microsoft Support to view the article. 

In order to better help you, we would like to test your file in the same environment whether it is still the same, in order to troubleshoot whether it is the Office version of the problem or system problems, if convenient, you can share the file with us by private message?

You can send a private message via the ‘Messages’ link at the top right of the page.

 

You can view sent private messages by going to ‘My Profile’ and selecting ‘View Private Messages’ in the left pane of your profile page.

 

I look forward to hearing from you further and if you have any questions, please feel free to contact me, thank you.

Best wishes

Isabella-MSFT | Microsoft Community Support Specialist

Answer 2

Thanks very much for your interest in this.

1. I reloaded Office 19 Home and Student from my Microsoft account. Therefore all Office products were reloaded.
2. I have tested the machine with a number of other spreadsheets and these all appear OK.
3. There appear to be no add ins active. There are only 3 add ins shown under COM add ins and none of these are selected.
4. I'd be happy to edit the registry but I would need to know what particular aspect could be beneficial.

It is worth saying that this particular spreadsheet is rather complex, with cross referencing from other sheets, but I have tried other complex spreadsheets and these all open OK. It would appear that the problem is only with this particular spreadsheet. The spreadsheet itself is OK as, as I have said, I can open it on my Windows 11 Pro machine (and it is also used by other investors). The fact that this same spreadsheet wouldn't open correctly in Open Office, Apache, Open Office or Google Sheets on the Windows 11 Home machine does make me think that it is not Excel that is the problem. I suspect that there is a Windows setting somewhere that stops the functions used by this spreadsheet, when trying to open in Excel or any other software.

Answer 3

Thanks again for your efforts but please don't spend any more time on this as I have established the problem. Examining the data names in various cells I noticed that those on the Windows 11 Home machine had "_xlfn." embedded in them. This means "unsupported function". Clearly Excel 21 has functions in it that Excel 19 doesn't support. I can demonstrate this by opening the spreadsheet on Excel on the web, where it opens correctly. I'm surprised that such basic functions are not incorporated in Excel 19 but that seems to be the case.

Thanks again for your interest.

Answer 4

Hello RayHalligan,

We are glad to hear that your issue has been resolved, we apologise for the issues that Excel 2019 has caused you, the newer versions of Office have higher performance and it is recommended that you change to a newer version for a better experience, thank you for your feedback and understanding.

Best regards

Isabella-MSFT | Microsoft Community Support Specialist