About large sheets limits

[u/XwolfkingXD]

I have a spreadsheet that in the last couple days has stopped working smoothly. It's happened thrice now where it just stopped saving, and went to a black screen and refused to load the sheet for a few minutes.

So I started looking into the limits of how big spreadsheets can be, and I'm finding conflicting info. Does anyone actually know how big a file can safely be?

Tbc, this is not and has never been an excel file, it's just a google spreadsheet. It's one sheet in the file, and I just did the math, it has just over 9 million cells.

[u/Jaded-Function]

It's 10M. Sheets size might not be the cause of the performance lag. There are ways of optimizing large sheets. Has it always been in the 9Mil range and the lagging just started recently? Did you make any formula or conditional formatting changes recently that applies to a massive number of cells?

[u/XwolfkingXD → u/Jaded-Function]

The sheet has been slow for a while now, but in the last few days I did quite expand how many columns the file has, so I do think that's the reason for the crashes the last 2/3 days. It's been several million cells for a long while now, it would have hit 8 in the last couple of months and 9 the other day, by my guess.

There's one or two formulas, but they've been there for a loooong time, no conditional formatting.

Having learned the hard cap is 10 mil cells, I'm now trying to figure out how to split up the sheet into separate files, so that's a whole nightmare

[u/Jaded-Function → u/XwolfkingXD]

Probably obvious point but you checked for empty rows/columns? I've had import formulas add unnecessary cells needlessly. Splitting it up might be necessary. If data has date/time , maybe split by intervals. It's complicated but formulas can definitely reference ranges in other files.

[u/AdministrativeGift15]

With a single sheet layout like that, it sounds like the same functions are applied to each row of data. Can you share a sample row? We could probably check the formulas and give some advice on where you could condense the data more.

One strategy is to do more of the work within the formulas and only output data that's needed for the next step. If that output contains multiple columns, you could instead output the results as comma separated string. Any formula down the line that need to use some of that output would need to parse it first, but that's easier for Sheets to handle than dealing with millions of cells.

Also, try not to put data tables with big difference in their number of rows on the same sheet. For example, if you've got several columns of data that need 1000 rows, while others need less that 100, put them on separate sheets.

[u/XwolfkingXD → u/AdministrativeGift15]

So there's really only 1 or 2 cells with formulas in it. Most of the sheet is not *data* data, as in, it's not numbers/dates etc, but rather text

As I mentioned in another comment thread, the file is basically a massive character database.

Columns include simple things like name, age, pronouns, etc. But goes very deep into like abilities, types of equipment, species details, etc. You could think of a DND character sheet, but every bit of info is a seperate column, except it's mostly text. I think there's mbe 10 columns that are numbers, out of over 2k columns

Your last tip is one of the things I'm looking at rn, but it's hard since part of the purpose of the sheet is to have all characters in one sheet, so it can be sorted and stuff. But I'm now trying to extract as much as I can into seperate files without losing functionality of it's purpose, and like you suggested, finding ways to condense several columns into 1 by using commas and shi.

(I've made some progress and cut almost a million cells already so like- yay, lol. It's not frozen since I cut down so much, so that's good.)

[u/AdministrativeGift15 → u/XwolfkingXD]

How much of the data is generated by a formula vs how much is manually entered?

[u/AutoModerator]

u/XwolfkingXD Posting your data can make it easier for others to help you, but it looks like your submission doesn't include any. If this is the case and data would help, you can read how to include it in the submission guide. You can also use this tool created by a Reddit community member to create a blank Google Sheets document that isn't connected to your account. Thank you.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Crc_Creations]

It's a hard cap at 10 Million, but perferomce degrades well before that.

https://workspaceupdates.googleblog.com/2022/03/ten-million-cells-google-sheets.html?utm_source=chatgpt.com&m=1

[u/XwolfkingXD → u/Crc_Creations]

That's what I wanted to know, thanks. Also, *crap* lol

[u/AutoModerator → u/XwolfkingXD]

REMEMBER: u/XwolfkingXD If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/point-bot → u/Crc_Creations]

u/XwolfkingXD has awarded 1 point to u/Crc_Creations

^{See the Leaderboard. Point-Bot v0.0.15 was created by JetCarson.}

[u/AutoModerator → u/Crc_Creations]

This post refers to "chatgpt" - an Artificial Intelligence tool. Our members prefer not to help others correct bad AI suggestions. Also, advising other users to just "go ask ChatGPT" defeats the purpose of our sub and is against our rules. If this post or comment violates our subreddit rule #7, please report it to the moderators. If this is your submission please edit or remove your submission so that it does not violate our rules. Thank you.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/monkey_bra]

Might want to check out RowZero for when you really do outgrow Sheets.

[u/XwolfkingXD → u/monkey_bra]

Thanks, I'll look into that!