Naming Constants and Formulas

So far a name pointed at a block of cells. But a name can also store a fixed value or even a whole formula, no cells required.

This lets you keep important constants in one place and give complex calculations a short, meaningful label.

• TaxRate could equal the number 0.18.
• GrandTotal could equal a SUM formula you reuse everywhere.

To store a value under a name in Excel, open the Name Manager on the Formulas tab and click New.

• Type a name such as TaxRate.
• In the Refers to box, enter =0.18.
• Click OK.

Notice you type the value with a leading equals sign. Now TaxRate behaves like a cell holding 0.18, but it lives in the workbook's definitions instead of a visible cell.

Google Sheets named ranges always point to cells, so the simplest approach is to put the value in a cell and name that cell.

• Type 0.18 in a spare cell, say Z1.
• Select Z1 and open Data > Named ranges.
• Name it TaxRate.

The result feels the same: =Price*TaxRate works everywhere, and you can update the rate by editing one cell.

Once TaxRate is defined, formulas become self-explaining. To add 18% tax to a price in A2:

=A2*(1+TaxRate)

The big win comes when the rate changes. Update TaxRate in one spot and every formula across the workbook recalculates instantly.

• No hunting for hard-coded 0.18 values.
• No risk of missing one and creating inconsistent numbers.

Hard-coding a value like 0.18 directly into dozens of formulas creates a maintenance trap.

• If the rate changes to 20%, you must find and edit every formula.
• Miss one and your totals quietly disagree.
• Readers cannot tell whether 0.18 is a tax rate, a discount, or a typo.

A named constant such as TaxRate fixes all three: one source of truth, clearly labeled.

A name can also hold an entire formula. In Excel's Name Manager, create a name like SalesTotal with Refers to set to:

=SUM(Sheet1!$B$2:$B$13)

Now typing =SalesTotal in any cell returns that sum. You have wrapped a calculation in a single readable label.

Use absolute references (the dollar signs) so the named formula always points at the same cells.

Suppose you frequently compute profit margin. Define two named formulas:

• TotalRevenue → =SUM(Revenue)
• TotalCost → =SUM(Costs)

Then your margin formula reads beautifully:

=(TotalRevenue-TotalCost)/TotalRevenue

Each named formula encapsulates its own logic, so the final calculation focuses on the business meaning rather than cell math.

An advanced trick: if you leave references relative in a named formula, the name behaves differently depending on where you use it.

For example a name PrevRow defined while cell A2 is active as =A1 will always refer to the cell directly above wherever you type =PrevRow.

• Powerful for repeating patterns down a column.
• Tricky to debug — use only when you understand the active-cell rule.

As your workbook grows, a list of named constants becomes a tidy settings panel. Common candidates to name include:

• TaxRate, DiscountRate, ShippingFee
• CompanyName as a text constant like ="Acme Inc"
• StartDate holding a fixed date

Grouping these in the Name Manager gives you a single dashboard for every assumption in your model.

To change a named constant in Excel, open the Name Manager, select the name, and edit the Refers to box. Press Enter and every dependent formula recalculates.

In Sheets, just edit the underlying cell you named.

• One edit ripples through the whole workbook.
• This is the core benefit: a single source of truth.

Always change the definition rather than scattering new numbers into formulas.

It helps to keep the two ideas straight:

• A named constant refers to a fixed value, like =0.18 or ="Acme Inc". It never changes unless you edit the definition.
• A named formula refers to a calculation, like =SUM(Revenue). It recalculates whenever its inputs change.

Both are created the same way in the Name Manager — the difference is simply what you put in the Refers to box.

You defined a name TaxRate that refers to =0.18. What is the main advantage over typing 0.18 in each formula?

You learned that names can store more than ranges:

• A named constant like TaxRate=0.18 centralizes an assumption.
• A named formula like SalesTotal=SUM($B$2:$B$13) wraps a calculation in a label.
• Update the definition once and all dependent formulas refresh.
• Use absolute references in named formulas so they stay anchored.

Next you will control what users can type using data validation.