Using names and the Name Manager in Grid by Zaweba

A formula that reads =B2*$H$1 tells you very little. One that reads =Price*VATRate tells you exactly what it does. Names are how you get from the first to the second, and Grid by Zaweba has three tools for them: Define Name, Create from Selection and the Name Manager.

Giving something a name

Define Name gives a name to a cell, a range or a formula. Select the cell holding your VAT rate, choose Define Name, type VATRate, and from then on any formula can use that name instead of the cell address. If the rate changes you edit the cell, and every formula follows. If the cell moves because you insert rows above it, the name moves with it.

Create from Selection is the quick way to name many things at once. Select a block that includes labels, say a column of setting names with their values beside them, and Grid uses the labels as names for the neighbouring cells. A settings panel with a dozen rates and limits can be named in one step.

Pick names that are short, clear and without spaces. A consistent style, such as capitalising each word, makes them easy to spot in formulas.

Workbook scope and sheet scope

A name can belong to the whole workbook or to a single sheet. Workbook scope is right for things that are the same everywhere, such as a tax rate or a company name. Sheet scope lets the same name mean different things on different sheets, which suits a workbook with one sheet per month or per branch, where each sheet has its own "Target" cell and a formula on that sheet should pick up its own.

Managing them

The Name Manager lists every name in the workbook with what it refers to and its scope. Use it to check a name points where you think, to change a reference, or to delete names that are no longer needed. Old workbooks often collect stray names over the years, and a quick tidy here makes autocomplete far less cluttered.

Where names help most

In formulas, names make long calculations readable, and autocomplete offers them as you type, so they're quick to use. In data validation, a named list of options is the cleanest source for an in-cell drop-down: keep the list on a settings sheet, name it, and point the validation rule at the name. Add an item to the list and every drop-down that uses it gains the new choice, as long as the name covers the new row.

With LAMBDA, names go a step further. Write a LAMBDA, define a name for it in the Name Manager, and it becomes a function of your own that you can call anywhere in the workbook, like =DeliveryCharge(weight). Grid supports named LAMBDAs with recursion, so a function can even call itself.

Mistakes to avoid

Don't give a name to a cell and then type numbers into formulas that should have used it; the point is to have one source. Avoid names that look like cell addresses, because they're confusing to read. And when you delete a sheet or a range a name relies on, check the Name Manager afterwards so no formulas are left pointing at nothing.

Names take a minute to set up and save hours of puzzling later. In Grid by Zaweba they also travel with the workbook when you save as .xlsx, so the person you send it to sees the same readable formulas.