Protect Formula Tables While Keeping Inputs Editable in Numbers

Quick answer: Numbers’ documented locking control applies to an entire table, not a selection of formula cells within a mixed input/calculation table. Put the inputs in one table and formulas in another, then lock the formula table with Arrange → Lock. This helps prevent accidental changes. It does not create a secure boundary against someone who can edit and unlock the spreadsheet. Apple’s table guide documents whole-table locking.

What this workaround can and cannot do

The workflow is for Numbers for Mac, based on Apple’s 15.4 guide. Check your installed version with Numbers → About Numbers. It is not an Excel Protect Sheet workflow, and it does not promise equivalent protection after conversion to XLSX or when opened in another client. The current Mac guide identifies the documentation version.

Use the split-table approach for a personal calculator, a team template used cooperatively, or a model where accidental typing is the main concern. If the requirement is “other people must be able to change these two cells but must be unable to alter my formulas,” this arrangement does not meet that access-control requirement. A visible label saying “Do not edit” and an object lock are useful safeguards, but neither establishes a permission boundary.

Goal	Relevant Numbers feature	Important limit
Avoid overwriting a calculation table	Arrange → Lock on that table	Applies to the whole table, including any inputs left inside it.
Prevent casual changes to the whole file	Locked in the document-title controls	Apple explicitly says anyone can unlock the spreadsheet.
Require a password to open the file	File → Set Password	A file-opening password does not define editable cell ranges.
Let a collaborator read without editing	Sharing permission Can view only	This is spreadsheet access, not permission to edit selected input cells.

These are distinct controls in Apple’s table locking, document locking, password protection, and sharing permissions documentation.

Build a two-table order calculator

Start with a new practice spreadsheet rather than rearranging a production model. Use two tables on the same sheet, each with one header row.

Table 1: Inputs

Rename the first table Inputs and enter A1:B3:

Item	Quantity
Notebook	2
Pen	3

Keep this table unlocked. Give the quantity cells a distinctive fill and add a nearby instruction such as “Enter quantities in Inputs.” Use real numbers, not text such as 2 notebooks. For the initial test, allow only the two listed products; this small design is not a general inventory database.

Table 2: Calculations

Add a second table named Calculations, with columns A:D and four rows including the header. Enter the following cells. Formulas begin with =; do not paste the code formatting marks.

Row	A: Item	B: Unit price	C: Quantity	D: Line total
2	=Inputs::A2	4.50	=Inputs::B2	=B2*C2
3	=Inputs::A3	1.25	=Inputs::B3	=B3*C3
4	Order total			=SUM(D2:D3)

The double colon in Inputs::B2 separates the table name from the cell address. To avoid mistyping a reference, start a formula with =, then click the appropriate input cell. Numbers supplies the table reference. Apple explains references to other tables and sheets.

Format the price and total cells to show two decimal places. The expected initial results are:

• Notebook: 2 × 4.50 = 9.00
• Pen: 3 × 1.25 = 3.75
• Order total: 12.75

These are plain sample amounts without tax, discounts, shipping, or a currency-specific accounting rule. See SUM if you want to understand the total formula.

Lock only the calculation table

1. Check all formulas before locking anything. Confirm the initial total is 12.75.
2. Click Calculations to select that table.
3. Choose Arrange → Lock from the menu bar.
4. Leave Inputs unlocked. Do not enable the document’s Locked checkbox for this working calculator.
5. Try selecting and changing a calculation cell. The table should require unlocking before it can be edited.
6. Change the Notebook quantity in Inputs::B2 from 2 to 4. The intended results are 18.00 for Notebook, 3.75 for Pen, and 21.75 for the order.

Apple describes a locked table as unavailable for editing, moving, or deletion until unlocked. Arrange → Unlock is also the documented way to maintain it. The object lock is intended to protect the table from editing, not to freeze an approved numerical result. Complete the input-change check in your installed version before distributing the template.

Verify both sides of the design

Testing only whether a formula is blocked misses half the requirement. Check that intended inputs remain usable too.

Test	Expected outcome
Change Notebook quantity to 4	Input accepts 4; calculated order target is 21.75.
Try to type 999 into the locked line total	Calculation table must be unlocked before editing.
Try to change the unit price	Also blocked by the table lock; the price is in Calculations.
Unlock Calculations deliberately	Formula and price cells become editable for maintenance.
Relock, save, close, and reopen	Repeat the editable-input and blocked-edit checks in the reopened file.

This checklist is a test to perform in your own file. The arithmetic was calculated independently; the locking and recalculation checks were not performed in Apple Numbers.

Make maintenance deliberate

To change a price, select Calculations, choose Arrange → Unlock, update the price, check the formulas, and lock it again. Keep a dated backup before changing a template used by others.

The sample pairs products by row. Both item and quantity are read from the same input row, but unit prices are fixed beside them. Do not independently sort Inputs: swapping Notebook and Pen would pair those quantities with the wrong fixed prices. For a larger product catalog, redesign around a stable product identifier and a verified lookup instead of extending this two-row demonstration by assumption.

Adding another product also needs work in both tables: add the input row, add its matching calculation row, and verify the total includes it. Table locking does not build missing formulas. If empty quantities need a special display, IF can be used in a deliberate blank-input rule; decide first whether a blank means zero or “not answered.”

When another control is needed

For a read-only audience, use the spreadsheet’s sharing permissions rather than relying on an object lock. A view-only collaborator cannot also fill in the input table. If different people must have different cell-edit rights, use a workflow or tool that actually supports that requirement; do not label this template “secure input-only access.”

For file-opening privacy, Apple’s password feature is separate. Apple warns that a forgotten spreadsheet password cannot be recovered. Keep it safely and understand that sharing the password allows opening the file; it is not a formula-cell permission system.

For a finished report whose displayed numbers must remain fixed, keep an appropriately saved output copy. Locking a live calculation table is not an approval history or a substitute for retaining the version that was reviewed.

Verification and official sources

The two-table design and arithmetic are independently worked examples. Apple documentation was checked on October 15–16, 2026. This tutorial was not tested in Apple Numbers, and no application screenshots were captured. Collaborator permissions and protection after export/import were not tested.

• Resize, move, or lock a table
• Layer, group, and lock objects
• Lock a spreadsheet
• Password-protect a spreadsheet
• Change a shared spreadsheet’s settings
• Calculate using table-cell data