Programmatically convert formulas to their values in Numbers
I have a couple of Numbers documents with multiple sheets each which containi quite a lot of formulas (enough to slow down most typical actions on the spreadsheets such as text input and, most disturbing, scrolling).
Every row on these tables corresponds to one day. Most formulas on any given row only operate on that row, menaing that when I've inserterd all the data for the corresponding day they become useless (they output will not change anymore). I will never need to change the input data for days in the past.
So, I was wondering whether it was possible to replace the formulas with their results in all rows belonging to a date in the past, maybe with some kind of macro which I could execute once in a while, or in general any way different than manually copying and pasting.
My goal with this hypothetic macro would be to reduce the "processing weight" of this spreadsheets and at the same time fix the results of formulas regarding past days, protecting them from accidental modifications of the corresponding input.
You probably can automate the conversions you have described to some extent with AppleScript but sorry that I don't know how.
Until you figure this or another automated solution you might use the following keyboard shortcuts in the given order to select multiple rows and copy & paste them in-place as values. These are likely to reduce the time you spend on such conversions if you are not already using them.
- Go to the first cell in the last row you wish to convert to values,
- Press Shift ⇧+Command ⌘+Right Arrow → to select all of the row,
- Select as many rows as you like by pressing Shift ⇧+Up Arrow ↑ ,
- When finished, copy all the rows by pressing Command ⌘+C,
- Paste all the rows in-place as values by pressing Shift ⇧+Command ⌘+V.