Numbers: Clean up multiple tables

[curmudgeonette]

I have a Numbers spreadsheet that seems to have gotten too big for Numbers. It is about 5000 rows and 100 columns. This isn't anywhere near the published limits, but I seem to have hit some internal limit.

Every month I need to add a row in about ten places. Each Add Row operation takes about a minute (i7-7700 w/ 32GB). I believe this is because the spreadsheet is full of references which are and must be absolute. Add Row must update tens of thousands of cells with the new row number - and then Numbers insists on recalculating.

Recently, Numbers has been saying that it can't Auto Save the file after adding a row. This also means I can't save the file. This further means I can't quit Numbers until I revert changes. Luckily I found that I can Save Duplicate. After that I can Revert, Close, go to the Finder, rename the duplicate as the original, Open it, and resume work. Everything works fine until the next time I need to Add or Delete rows. I believe the issue is that I've hit some sort of internal limit of an Undo / Revert change log.

A solution which I think will work is to turn the 5000 rows into 50 tables with 100 rows each. When adding a row to one table, references to and within the other 49 won't need to change. (Of course, the next time I need to add a column, I'll be screaming as I edit all 50 tables.)

The problem with this solution is that I have not found a way to keep all 50 tables arranged neatly one after the other on a single sheet. There's some sequence of actions that will create tables that magically stay cleaned up. However it is easy to break the magic. Is there some solution?

 

[MauiPa]

I have never had a Numbers spreadsheet be too big for Numbers, and I am on a MBP 2014 with about half your Geekbench 5 score. For a project I imported large amounts of data on power plant simulations which would crash Excel, but still worked fine in Numbers.

So first of all, your statement "the spreadsheet is full of references which are and must be absolute" sounds more like bad spreadsheet design than anything else. When I have situations like that (if a spreadsheet is in fact the best solution and not a database to begin with), I use a table of raw data and only basic computations. I augment this database by computing summaries based on "keys" in rows and columns and use functions such as Sumif and sumifs in one or more separate tables. Using this technique, I have successfully processed tables with 64000 rows and monthly data for power plants and operating criteria.

As a point of reference, Excel - when it didn't crash, produced much larger file sizes than Numbers, up to a point, and then the Numbers file sizes were slightly larger (I never bothered to figure out why). There were no performance issues in either Excel or in Numbers.

So give it a try, you will be amazed. I'd be happy to provide a small example, if you provide a small subset of your data

 

[curmudgeonette]

So first of all, your statement "the spreadsheet is full of references which are and must be absolute" sounds more like bad spreadsheet design than anything else. When I have situations like that (if a spreadsheet is in fact the best solution and not a database to begin with), I use a table of raw data and only basic computations. I augment this database by computing summaries based on "keys" in rows and columns and use functions such as Sumif and sumifs in one or more separate tables.

So give it a try, you will be amazed. I'd be happy to provide a small example, if you provide a small subset of your data

The spreadsheet calculates the dividends paid to me by ETF's. In the body of the sheet, it is one row per dividend date. Each ETF's data block is preceded with three rows with share count, buy date, and sell date. The spreadsheet calculates how many shares I have on each dividend date (and then calculates the dividend payment.) Thus each dividend date row needs to refer back to the same count/buy/sell rows, i.e. absolute references. At the top of the sheet there are manually created pivot tables summarizing a year's payments from each ETF. Each annual pivot table needs to look down at the same data block, so more absolute references. The pivot table area is about 8500 cells, each with two SUMIF involving eight absolute row references.