My File Won't Open In Google Sheets

I've had this (or a very similar) issue myself, where an Excel file wouldn't load.

The workaround I've used has been to save the Excel file in an older format, such as .XLS.  If you are able to open it in Excel itself, saving a copy as an .XLS file is easy.

I've also used a free online file conversion service - you can search for something like "Excel file format conversion" and find your own.  The one I used was Zamzar.

Once converted to .XLS format, I was able to open it in Sheets.

Please let me know if this helps as a workaround.

Cheers,

Gill

There's one other very bizarre thing. I have another EXCEL File saved in the newer XLSM format. I uploaded it to Google Sheets and it works perfectly fine in Google Sheets. The only difference between the XLSM File that WORKS in Google Sheets, and the XLSM File that does NOT WORK in Google Sheets. Is the file that works is 480 KB large. The file that does not work is 3.3 MB large. There are exactly the same in every way. One of them is just larger because it has more worksheets (pages) in it.

That seems to imply the size of the file is the cause of problem? But 3.3 MB isn't too big for Google Sheets? So what gives?

I'm not quite sure what to suggest.  If your Excel file is saved on your Google Drive, and you want to share that one file, I'd love to have a look at it to see if I can figure something out.

For example, you've said that the smaller Excel file that does load is exactly the same except for the volume of data.  I'd be interested if you gradually pasted more and more data from the large file into the smaller Excel file, does the smaller file eventually reach a stage where it finally also doesn't load into Sheets?

 

This shouldn't be necessary, of course, and I'm not aware of any constraints on an Excel sheet that should prevent it from loading into Sheets.

 

Gill

Sorry, one other thought.  Were you importing as a new sheet (tab) into an existing Google sheet, or replacing an exisiting sheet?

 

Did you try the "Create new spreadsheet" option before selecting Import Data?

 

Gill

The EXCEL file that works is called Ameed's workout. It's saved in XLSM and it's 438 KB big.

The EXCEL file that doesn't work is called Deep's workout. It's saved in XLSM and it's 3.3 MB big. The only difference between them is the file size.

<<The only difference between them is the file size.>> Yes, and I think that is probably the issue.

Is there any way you could make it smaller (e.g. does it have empty rows and columns that could be eliminated altogether). There are size limits but I'm not sure what they currently are (used too be 1M cells I believe).

I think this will make it much easier for us to figure out the problem. I uploaded the two EXCEL FILES to my Google Drive and I will share the link with you. So you can open the files using the link and you can see what's going on. Remember, the file that works perfectly fine is AMEED'S WORKOUT which is 457 KB. The file that won't open no matter what I do, is DEEP'S WORKOUT which 3.3 MB. I googled how big a file can be in Google Sheets and Google said 20 MB. So 3.3 MB should be fine I think.

Deep's Workout is a much bigger file because it has like 40 extra sheets in it. It has 52 sheets for the 52 weeks of the year (one sheet for each week). Ameed's Workout is much smaller because it's only like 16 weeks long (instead of being 52 weeks long).

I could make Deep's Workout smaller by deleting some of the weeks. Instead of being 52 weeks long, I could reduce it to 12 weeks. But that means when I fill up the 12 weeks, I'll have to create a whole new file for the next 12 weeks and start over again. That's I am hoping I can just keep all 52 weeks in the file so I can use the same file for the entire year. But if there's no other choice, I will delete some of the weeks to reduce the size of the file.

I tried to find an online program which could compress the size of the file too. Like PDF compressors which reduce the size of PDF files without changing the contents of the file. But I couldn't find any.

https://drive.google.com/file/d/1dGNl6sgsH2jTzMcxgdgTnJnP0FVzfM6l/view?usp=sharing

You can test and experiment with my EXCEL FILES if you want. But I wanted to let you know, this is what I've found so far.

I opened the EXCEL FILE and I physically counted the number of columns in each worksheet.

Each workout is BELOW 256 columns by quite a lot. Or does Google Sheets add all of the columns from each worksheet together?? And it uses the grand total as the number?? Because it add them all together, it will be much higher than 256 columns?

I did try to shrink the size of the EXCEL FILE. But I can't find a way to do that. Unless I delete data from the EXCEL FILE, such as deleting some of the worksheets or columns or rows.

I think I may have found the issue, but not the answer.

 

When I load the big file in Excel, and then do a Save As to go to format .xls, it gives a "compatability" report.

 

Here's what it said:

 

But I've looked through the sheets it identifies, and can't find what it says.  But it is Excel reporting the issue, so probably that is what is causing the problem for Sheets as well.

 

I wonder if deleting tabs Week4, Week10, Week50 amd Week51, the ones that are flagged, would then let the file load into Sheets?

 

 

Cheers,

Gill

All of the unused columns and rows were hidden, just to make the EXCEL Sheet look more tidy and clean. But for some weird reason, that was the reason why it wouldn't open. When I unhide the unused rows and columns, it suddenly started working fine. I didn't change anything else at all. For some reason, the file shrunk in size from 3.3 MB to 1.6 MB when I unhide the unused rows and columns too. It's very very weird.

There is a NEW PROBLEM now, though. But hopefully this problem will be much easier to solve. I shared the EXCEL Document on Google Sheets with my client named Deep. The EXCEL document is stored in Google Sheets which means Deep should be able to add information into the document using his phone or computer. And the information Deep entered, will automatically appear in the document when I look at it from my end on my phone or computer. The document should update in real-time for everyone who has instant access to it. But that currently isn't happening. Whenever Deep opens Google Sheets, opens the document, enters information into the document, and leaves. The information he entered doesn't appear when I go into the document. It's just blank, like nothing was changed or entered.

I double-checked it. And Deep does have full permission to access and edit the file. I clicked on "SHARE" and added Deep to the document in Google Sheets, and I assigned his privileges as Editor. Do you know why it's not updating in real-time like it should be??

Here is a link to Deep's File in Google Sheets (which opens now).

That is strange about the hidden columns.  I'll have to monitor questions posted in these support forums to see if others are encountering similar behaviour, so thanks for finding that!

 

Now for the new issue.  We have had some reports of people using phones having issues updating sheets, but typically they can't edit at all, even with Editor rights.  You're saying Deep seems to be editing the sheet normally, but you can't see their edits, right?

 

If they leave the sheet, and go back in, do they still see their edits?

And they have edited the file from both a phone and a computer, and the symptoms are the same?

 

Next, the file is an Excel file that is shared from your Drive.  I believe they can open it with any application they like, not just Sheets.  Do we know that they are using Sheets, or are they possibly opening it in Excel?

I believe they must be opening it in Sheets - to load it into Excel, they would have to donwload a local copy, or save it on OneDrive, I think, no?  Is there any chance they've done that, which would explain why you don't see their edits?

 

I don't have any answer here, I'm just trying to think of some diagnostic steps to take to narrow this down.

If you go into File - Version history on the sheet, do you see any evidence that they've been in the sheet and made a change?

 

Let me know.

Thanks,

Gill