Solved

Any secret with regards to "what to do" when Google Docs spreadsheet gets "too full"?

Posted on 2014-11-11
2
1,439 Views
Last Modified: 2015-03-02
Hello. I have a shared Google spreadsheet. It is hanging up and no longer allows data to be input. It is very large.

Is there any secret with regards to "what to do" when Google Docs spreadsheet gets "too full"?

I'm pretty sure that is my problem.

I get error messages if I try to download it or create a copy of it. See screenshots:

1

2
Should I just start deleting data?
0
Comment
Question by:gregholl
2 Comments
 
LVL 9

Accepted Solution

by:
bas2754 earned 500 total points
Comment Utility
From the following: https://support.google.com/drive/answer/37603?hl=en

Click on the link that says "More information about spreadsheet size limts"

A couple items of interest are quoted below:

"If you're close to exceeding Google spreadsheets size limits, you'll see a message at the top of your spreadsheet that indicates what limit you're about to reach.

    Number of cells: 400,000 total cells across all sheets
    Number of columns: 256 columns per sheet
    Number of formulas: 40,000 cells containing formulas across all sheets
    Number of tabs: 200 sheets per workbook
    GoogleFinance formulas: 1,000 GoogleFinance formulas
    ImportRange formulas: 50 cross-workbook reference formulas
    ImportData, ImportHtml, ImportFeed, or ImportXml formulas: 50 functions for external data.

Spreadsheets also have overall storage limits. Some spreadsheets may reach these before hitting the 400,000 cell limit, particularly when individual cells have large amounts of text. In such cases, the spreadsheet will go into read-only mode to prevent data loss."

"Tips & Tricks

    Try breaking up a large spreadsheet into multiple smaller spreadsheets.
    Minimize the number of complex formulas. See the list above for examples of functions that increase complexity.
    After getting data points through any complex formula, try to copy-and-paste them as "values only."
        Select the values and copy them.
        Go to the Edit menu.
        Point your mouse to "Paste special."
        Select "Paste values only."
    Minimize the number of cells that depend on:
        A large number of other cells.
        Cells with complex formulas.
        Cells that are changed frequently."

"All spreadsheet limits mentioned above have been removed in the new version of Google Sheets. The new version of Google Sheets should support 2 million cells of data, though please note that extremely large spreadsheets may have slower performance. Learn more about switching to the new version of Google Sheets."

If you follow the link about switching to the new version of Google Sheets it takes you here: https://support.google.com/docs/answer/3544847 and says:

"Moving spreadsheets to the new Google Sheets

Starting in March 2014, the new version of Google Sheets will become the default for all users when creating a new spreadsheet. Older spreadsheets will remain in the old version of Sheets for now*, but you can manually move spreadsheet contents into the new version of Sheets to take advantage of new functionality, following any of these steps:

    Copy and paste content from a spreadsheet created in the old version to a spreadsheet created in the new version.
    In a spreadsheet created in the old version, click the down arrow next to a sheet tab and click Copy to…, and copy the sheet (and its contents) to a spreadsheet created in the new version.
    Export the contents from the old version and import them into a spreadsheet created in the new version.

*Spreadsheets created in the old version of Sheets will eventually be converted to the new Sheets, which won’t require any action on your part. More details coming soon."

I would try moving to the new version first if possible.  If not, then you can try the suggestions in the "Tips and Tricks" section.
0
 
LVL 1

Author Closing Comment

by:gregholl
Comment Utility
thanks
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

You can provide a virtual interface for remote stakeholders in a SWOT analysis through a Google Drawing template. By making real time viewing and collaboration possible, your team can build a stronger product.
If your app took Google’s lash recently, here are the 5 most likely reasons.
This Micro Tutorial demonstrates in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how marketers can use the Mobile Emulation Tool in Chrome Developer Tool. This will let you preview your site on any mobile device.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now