Feb 7, 2020
To reduce lag time, is it better to spread out data over different sheets or same sheet within wrkbk
1. Have one large spreadsheet and within same sheet spread out all data/tables ie. from A1:BZ1200 as a range, functions within the sheet refer to each other
or
2. Have a spreadsheet with 16 or so sheets where the formulas are more localized ie. from A1:Z300, but the cells within those sheets use =indirect function to pull data from other sheets.
or
3. Have various spreadsheets with fewer sheets within it (quarterly reports) but information is obtained from various spreadsheets with =importrange formula.
Thank you!
Informational notification.
This question is locked and replying has been disabled.
Community content may not be verified or up-to-date. Learn more.
All Replies
Feb 7, 2020
Hello José Antonio Flores,
A1:BZ1200 is around 93K cells - not a problem, but not a small sheet either.
It's not going to be a question of splitting the data into multiple tabs or multiple sheets, and things like Indirect() will only slow things down.
More important are the things your doing with your data and how you're doing it.
I have a notepad page with some comments that address performance issues. I'll paste them below without editing - some may not be relevant, but it's everything in my notes.
In the end, you may want to make a clone of your sheet that you can share here. i'll close this post with guidance for doing that safely.
Ok here goes:
-------------------------
Ben Collins article: https://www.benlcollins.com/spreadsheets/slow-google-sheets/?utm_source=Ben+Collins+Newsletter&utm_campaign=2d8a37a761-EMAIL_CAMPAIGN_2018_03_14&utm_medium=email&utm_term=0_77e612d207-2d8a37a761-283308257 My sheet is very slow. Progress bar. Canned response: we can't be specific without being able to examine what you've got going on. But here are a few things that I see on the forum that lead to slow performance.
1 - Excessive use of Conditional Formatting.
2 - Use of Copy-Down formulas repeated in every row rather than use of Arrayformulas to process the entire range.
3 - Repeated calls to ImportRange, rather than a single call to import the data, which can then serve your local formulas.
4 - large numbers of empty rows and columns, particularly if those empty rows are processed by formulas.
5 - unnecessarily complex formulas, formulas that run the same calculation repeatedly, complex formulas that could be replaced with built in functions.
6 - Approaching the cell-count limit of 5 millions cells. As the sheet gets up there, it inevitably slows down. I don't know exactly what the limit for tab count is, but if you've got large numbers of tabs, you could probably improve on that organization. Cells themselves have a 50K character limit. Certain types of formulas can challenge that limit during internal processing.
Addendum - I've seen use of IMPORTRANGE() to access data on the same Sheets document. This is unnecessary as local data can be referenced directly. Using ImportRange forces the sheet to go back to the server to fetch data it already has.
As a sheet grows larger, it becomes more important that you strategize the structure of your data and efficiency of your formulas. I would look for ways to improve those areas.
Also, if your sheet is doing more than one job, consider splitting it into separate files and use ImportRange to share data between the files.
Stock paragraph:
If you'd like help entered directly on a Google Sheet, from me or dozens of other forum contributors, please share an editable copy we can all work on. Please take a look at: Google Product Experts' TIPS FOR POSTING
(Have sensitive info in your sheet? Read the tips!)
Best
Lance
Last edited Feb 7, 2020