Improving Performance Example

Google Sheets
, ,
1

Overview

I drastically improved spreadsheet performance by deleting all blank rows past row 100 (5,824 blank rows deleted), in the Spending Trends sheet.

:star: Hot Tip - delete any blank rows in the Transactions sheet :star:

I figured I’d share my experience with trouble shooting frustratingly slow performance. I wanted to see what I could do without doing the commonly recommended remove transactions (5,423) and Tiller solution tabs (9).

Google Sheet Size Audit Tool - Slow Performance Results

I discovered this problem spot by running the Google Sheet Size Audit Tool, which is one of the ideas listed in the Slow Google Sheets? Here are 27 Ideas to Try Today article I’ve seen referenced multiple times in this community.

Here are the results for my Tiller spreadsheet, which includes some commonly used Tiller solutions:

image
image1421×743 95.1 KB

My spreadsheet is nowhere near Google sheets size limits (11% and 1%).

Spending Trends

Spending Trends stood out in the audit results as easily the largest number of cells in a sheet, plus the stark difference vs number of cells with data (503,540 vs 611). It was easy at that point to know I needed to delete all the extra blanks rows (and I knew Spending Trends installs using 100 rows). Sheet performance became enjoyably snappy after that change - I was surprised this one change caused such a massive performance improvement.

I believe I introduced all the extra blank rows by playing with array type formulas, that combined with some Spending Trends helper formulas executing on all rows causing the slow down.

Net Worth

The other data that jumps out from the report is the high TODAY() count of 648 in the Net Worth solution tab. So, I followed another idea from the article to remove volatile functions by replacing multiple TODAY() function calls with cell references to one function call.

I actually found/replaced 1477 TODAY() function calls and I wasn’t able to determine why the audit tool only reported 648. I investigated the AppsScript and it just looks like a basic count loop.
Maybe @benlcollins could provide a possible reason for the difference?

Here is a snapshot of the one occurrence I added in cell T5. I replaced all other TODAY() with $T$5.
@heather I would think a similar change should be made to the official version of Net Worth.
Although, I didn’t notice a significant change in performance after this change, I imagine it can all add up.
image

Monthly Analysis

This might be the next one I investigate, since it has the highest count of ArrayFormula (71). While I don’t currently have a performance reason to do that at the moment, I might check it out at some point to see what I can find.

Google Sheet Size Audit Tool - Fast Performance Results

For reference, these are the results after my two changes:

audit2
audit21418×740 94.9 KB

Conclusion

Lots of blank rows cause performance issues - delete them :slight_smile:

9 Likes
2

@Mark.S this is great real-world information. In my experience, you’re right to point to blank rows as the cause of many slowdowns. (These rows are often created by a mismatch between the cell an array formula appears in and the start of the array – a map() formula in B2, for example, that has an array input of A1:A. Sheets will keep adding rows until the sheet limit is reached.)

Another major contributor to slowdowns is conditional formatting. I’ve seen significant improvements in performance after deleting some useful but not absolutely necessary conditional formatting rules. I haven’t experimented enough to be able to say if it’s the number of rules or the complexity of the rules that causes problems.

2 Likes
3

Yeah, I’ve been avoiding conditional formatting on the Transactions tab for that reason, thanks for highlighting it.

According to Ben’s 27 tips, it’s the dataset size, but I haven’t tested it either:

22. Use Conditional Formatting sparingly
…
it can be very slow on large datasets because it’s implemented on a cell-by-cell basis.

So it’s best used for small data tables and in your presentation tables and dashboard reports.

2 Likes
4

I’ve found the same thing. I change the conditional formatting to only apply to the top 200 rows on my Transactions sheet and it’s made a noticeable difference (as opposed to all 5000+ rows that used to have it)

3 Likes
5

Ok. Ok. I implemented your helpful little edit on that uncategorized transaction utility to limit it to the first hundred rows. Thanks for these tips.

3 Likes
6

Can confirm deleting blank rows vastly improves performance. I think I pointed this out on a random thread in past few days.

My sheets were steadily bogging down and in my attempt to clean things up, I stumbled across 50K empty rows at the bottom of my Transactions sheet. I deleted all of them and performance drastically improved and immediately.

3 Likes
7

Thanks for diving into this with some hard data, @Mark.S, and tips.

We often are trying to make a simple one-size-fits-all default template. These typically work great for new and typical users, but advanced users with long-lived datasets and many accounts can sometimes bump into performance issues.

There are definitely some opportunities to fine tune performance particularly if you understand how everything ties together in the template(s) architecture.

1 Like
8

Found a bunch of unnecessary blank rows on various sheets and can notice the improvement after removing them. Great idea.

2 Likes
9

Can you share if the performance improvements are “by feel” or if you have a solution to empirically measuring performance, @mu3484343? This has been a riddle.

10

It’s feel, but I suppose a way to measure would be to duplicate documents and then apply changes in one and then run the same task across both and time the difference. Possibly a plugin could measure this as well but I’m unaware of that.

11

I occasionally hover over the Chrome browser tab of my Tiller spreadsheet to get a feel for typical memory usage and it tends to be around 250-450 MB. I haven’t specifically correlated this to performance, yet, just trying to roughly establish an anecdotal baseline.

I briefly explored 4. Measure a Google Sheet’s calculation speed, which uses Chrome Developer Tools to measure speed, but I probably needed to spend more time with it to get something useful.