Master essential Mac skills!

Expand or refresh your Mac know-how with
Take Control of Mac Basics by Tonya Engst!

Force a Table Recalculation

Longtime Excel users have another reason to need it: some multi-worksheet files are so complexly intertwined that the automatic recalculation of connected formulas every time you enter new data can slow things down. So, they turn off the automatic calculation feature and trigger it when they want the sheet refreshed.

But, back to Numbers. It’s unlikely your spreadsheets will be so complex that recalculation will slow things down; and, in any case, there’s no way to turn off the automatic recalculation that occurs when you enter data. But you can’t specifically trigger a recalc of cells, so those containing random numbers (from the RAND or RANDBETWEEN function) are quite static. Whether you’ve set up a bunch of random-number cells to test what will happen when real data is entered, or intend to keep the random-number formula in place to interact with data you input later, it’s important to test the results of various possibilities in your random range.

Why? What would happen if, coincidentally, all the randomly generated numbers are even, or there’s nothing under 10 or over 99, or perhaps there are no multiples of 5, and you’ve set up something where an odd number, a low or high number, or a multiple of 5 will result in flawed output? (Even if “flawed output” is nothing more than a column that’s not wide enough to display three digits.) You won’t realize there’s a problem because the random numbers are static until something else you enter forces a recalculation.

The solution I proposed in the book was to go to any cell adjacent a block of cells that include RAND or RANDBETWEEN cells and use Command-X to force Numbers to recalculate the entire table, resulting in newly generated random numbers. If there was nothing in the cell, nothing gets cut, and if there was, you can paste it right back.

Well, as of Numbers 3.6.1—and perhaps before, because this was only recently brought to my attention—this no longer works. Command-V in any cell works, but that runs the risk of accidentally pasting something that could overwrite nearby cells and really make a mess of your table. So, let me recommend the trick that everybody in the know has used all along for recalculating Numbers spreadsheets; I had ignored it because it requires using a cell specifically for the recalc trigger.

Simply format any cell to hold a checkbox: select the cell and go to the Format Inspector’s Cell tab, and then choose Checkbox from the Data Format menu. When you check or uncheck the checkbox, every formula in the document—in every cell of every table on every sheet—will be recalculated. The fact that the recalc is global in the document, really takes care of my objection to setting aside a cell in one of your tables for this purpose. You could, of course, stick it in the usually unused cell A1 where it’s easy to get at, and you could easily just delete it and set it up again if you don’t need it in the interim. But since the recalc works globally, you don’t have to keep the checkbox in any of your working tables. Make a table specifically for the checkbox—it can even be alone on its own tab. And, you can delete all but its first row and first column, making it a single-cell table (yes, an amoeba table!) and duplicate it on every tab for convenience.

Posted by Sharon Zardetto (Permalink)