Locking Cells in Numbers

You can lock a whole table by selecting it and going to Arrange > Lock.

But you can't lock individual cells.

If you need to lock certain cells then you could consider moving them to a separate table and locking that.

Another user, of course, could easily unlock what you have locked, so it's mostly just modest protection against overwriting a cell by accident.

SG

Expanding on SGIII's answer, the locked separate table could be placed behind its identical copied and pasted twin (size-wise, not content-wise). This way the cell that appears to be locked is showing at the correct location through the table(s) above it but cannot be accessed. Use Format / Arrange in the toolbar to reorganize the tables.

Make sure that any formula elsewhere that refers to the cell to be protected would point to the version in the locked table in the back, not to the see-through cell above it. To this respect it's better to complete all your formulas before stacking the tables.

Thank you Ian

Here are a few more clever uses of stacked tables:

1- Temporary value override.

Let's say 'Table 1 - Back' contains a set of default values (fixed or computed). 'Table 1 - Front' is an overlay that gives the user the possibility to selectively override the back values for further use. An overriding front cell value triggers its own conditional formatting with a non-transparent fill to cover the cell in the back.

You can chain many constructions like this to keep default values and formulas safe.

There are two ways to approach this situation.

A- 'Table 2' chooses the correct value to be used in other formulas, either the Front one if the cell is not empty, otherwise the back one.

Example formula in Table 2:

IFERROR(TEXTBEFORE(IF(ISEMPTY('Table 1 - Front'::A1), 'Table 1 - Back'::A1, 'Table 1 - Front'::A1),"/"),"")

B- At other times I will make the value selection directly in 'Table 1 - Back' to trigger its own conditional formatting and to avoid repetitive IFs in multiple dependent cells.

Example formulas in 'Table 1 - Back'::A1

IF(ISEMPTY('Table 1 - Front'::A1), 'Source Table'::C5, 'Table 1 - Front'::A1)

or

IF('Table 1 - Front'::A1="-", "", 'Source Table'::C5)

Then the formula in the first method would simply become

IFERROR(TEXTBEFORE('Table 1 - Back'::A1,"/"),"")

2- Complex conditional formatting.

Numbers's conditional formatting is very basic and must always compare simple values (fixed or from a single cell) to the exact content of the cell itself. But what if you want to format based on the square root of the content? Or if you want to format a cell solely based on the content of other cells? Then you put a formatting table behind the main one where cell formulas give exactly the results that you need for the conditional formatting, and you format that cell. You can even put an infinite number of tables on top of each other to get formatting based on various conditions. Example: Content of 'Table 1'::A1 is irrelevant, but you want to conditionally format it if the value of B1 + 3 * C1 is larger than the value of D1. 'Table 1 - Format' placed just behind comes to the rescue. In cell 'Table 1 - Format'::A1 you enter formula

 'Table 1'::B1+3*'Table 1'::C1>'Table 1'::D1

which gives a TRUE or FALSE result, and you format that cell accordingly. Numerical or text results are fine too, of course. Just make sure conditional formatting gives cell background and font the same color to avoid obstruction with front cell content.

The possibilities are endless and if you only want to identically colorize a small set of cells you can actually create a formatting table smaller than the main table and locate it judiciously. In a 20 x 20 table you may want to only conditionally format block B5:C8 based on a unique complex condition. Nothing stops you from putting behind a 1 x 1 table with a single extra big cell as big as the block of 8 cells in front.

Some cells are inputs to a model, others are outputs. Spreadsheets make things weird by mixing up the inputs, the model, and the outputs. The calculations are your model.

You don’t want people messing with your model. So put the inputs into a separate table, and lock the model.

You might not want to expose the model, so put that table in a separate tab, then make an outputs table (locked) in the first tab. Maybe with tables, notes, etc.

Hi Recycleur,

A clever idea! I like it.

Regards,

Ian.