Spreadsheets

Settings tab and named ranges: make your budget sheet easy to change

Move rates, limits and category lists to a Settings tab, name the cells, and change one value to update every formula in your budget or EMI sheet.

Editorial illustration for Settings tab and named ranges: make your budget sheet easy to change

Put every number that might change, such as a loan amount, an interest rate or a monthly limit, in one labelled cell on a Settings tab. Give each cell a short name, then write formulas that use the name instead of a typed-in figure. When the value changes, you edit one cell and every formula follows.

What goes wrong with numbers typed inside formulas?

Say you built an EMI sheet and typed the loan details straight into fourteen formulas, like =PMT(0.09/12, 240, -4000000). It works until the lender revises the rate or you decide to prepay a chunk. Now you must find and edit every copy, and you will miss one. The sheet then shows two different EMIs and you cannot tell which is right.

How do I set up the Settings tab?

CellLabelValueName
B2Loan amount (₹)40,00,000LoanAmount
B3Annual interest rate9%AnnualRate
B4Tenure in years20TenureYears
B5Monthly household limit (₹)60,000MonthlyLimit
B6Category listA10:A25CategoryList

These numbers are only an illustration; use your own. Keep the label in column A and a short note about the unit in column C, such as per year or in rupees.

Naming a cell

In Excel, click the cell, type the name in the Name Box left of the formula bar and press Enter, or use Formulas, Define Name. In Google Sheets, choose Data, Named ranges, then Add a range. Names cannot contain spaces and should not look like a cell address, so use LoanAmount rather than Loan1 or EMI1.

What do before and after look like?

VersionFormula
Before=PMT(0.09/12, 240, -4000000)
After=PMT(AnnualRate/12, TenureYears*12, -LoanAmount)
Total interest=PMT(AnnualRate/12, TenureYears*12, -LoanAmount)*TenureYears*12-LoanAmount

PMT returns a positive EMI because the loan amount is entered as a negative. Now try it: change AnnualRate from 9% to 9.5% and every sheet that uses it moves together. You can also compare tenures by changing TenureYears from 20 to 15 and noting the new EMI. Nothing else needs touching.

The first argument of PMT is the rate for one period, which is why the annual rate is divided by 12. The second is the number of monthly instalments, and the third is the amount borrowed. Your lender's own schedule can differ by a little because of rounding, the date of the first instalment or a rate reset, so use the sheet for planning and compare it with the schedule the lender gives you.

Can names also drive checks and warnings?

Yes. A budget alert can read =IF(SUM(C2:C40)>MonthlyLimit, "Over limit", "OK"), which reads like a sentence. A dropdown can use =CategoryList as its source, so a new category added on the Settings tab appears in every dropdown at once.

Which numbers deserve a place on Settings?

  • Anything you would otherwise type into more than one formula.
  • Rates and tenures you expect to revisit, such as an EMI interest rate.
  • Limits and thresholds, such as a monthly household limit or an amber trigger.
  • Lists used by dropdowns, such as categories and payment modes.
  • Dates that start or end a period, such as the first day of the financial year in April.

Leave true constants alone. 12 months in a year, or 100 for a percentage, does not need a name.

A five-minute tidy-up

Hunt for typed numbers with Find in formulas, for example search for 12 or 0.09. Move each to Settings, name it, then replace it in the formula. Colour the Settings input cells pale yellow so family members know which cells are safe to edit. Last, if you ever move from a sheet to an app such as SpendixAI, the same habit of labelled categories carries across, because categories there also have their own settings.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports