A named range gives a cell or block of cells a label, so that instead of Log!$C$2:$C$500 in your formulas you type Exp_Amount. Select the cells, click the Name Box left of the formula bar, type the name and press Enter. Formulas become shorter and readable, but a fixed named range still stops at the last row you selected, so know when to move to a table.
How do you create a name?
Two quick ways. Select the range, click the Name Box, type a name and press Enter. Or choose Formulas, Define Name for more options. Suppose your log is on a sheet called Log with Date in A, Category in B and Amount in C, rows 2 to 500. Create these three names:
- Exp_Date for Log!$A$2:$A$500
- Exp_Category for Log!$B$2:$B$500
- Exp_Amount for Log!$C$2:$C$500
You can also select the whole block with headers and use Formulas, Create from Selection, tick Top row, and Excel names each column after its header.
What are the naming rules?
- Names must start with a letter, underscore or backslash, and cannot contain spaces. Use underscores, as in Start_Date.
- A name cannot look like a cell address. FY24 is rejected because FY is a column and 24 is a row, and so is Q1. Use FY_2026 instead.
- Avoid names that are also function names, such as Date or Sum, to prevent confusion.
- Names are not case sensitive. Exp_amount and EXP_AMOUNT are the same name.
How do formulas change?
Put the first day of your month in a cell named Month_Start. Before and after:
| Without names | With names |
|---|---|
| =SUM(Log!$C$2:$C$500) | =SUM(Exp_Amount) |
| =SUMIFS(Log!$C$2:$C$500, Log!$B$2:$B$500, "Rent") | =SUMIFS(Exp_Amount, Exp_Category, "Rent") |
| =SUMIFS(Log!$C$2:$C$500, Log!$B$2:$B$500, $A2, Log!$A$2:$A$500, ">="&$B$1, Log!$A$2:$A$500, "<"&EDATE($B$1,1)) | =SUMIFS(Exp_Amount, Exp_Category, $A2, Exp_Date, ">="&Month_Start, Exp_Date, "<"&EDATE(Month_Start,1)) |
The third row is where the benefit lands. The version with names reads almost as a sentence, which helps a month later when you open the file and cannot remember what the formula does.
How do you keep names tidy in the Name Manager?
Open Formulas, Name Manager, or press Ctrl+F3. Each name shows what it refers to and its scope. Check three things:
- Delete names whose Refers To shows #REF!. They appear when you delete the cells that a name pointed at, and they break any formula that uses them.
- Look at the scope column. A name scoped to one sheet will not work from another sheet without the sheet prefix, while Workbook scope works everywhere.
- Edit a name's range in the Refers To box if your data has grown, for example from row 500 to row 1000.
What happens when the data grows?
This is the weakness. A fixed name points at rows 2 to 500. Add a transaction in row 501 and SUMIFS with that name ignores it, silently. You have three choices. The easiest is to select far more rows than you need. The next is a name that stretches with the data, using Refers To: =Log!$C$2:INDEX(Log!$C:$C, COUNTA(Log!$A:$A)), which counts the filled cells in column A and ends the range at the last one. The best, in most cases, is to convert the log to a table with Ctrl+T. A name can then simply refer to Expenses[Amount], and the table does the growing for you.
Does this work in Google Sheets?
Yes, with a different menu. Choose Data, Named ranges, type a name and the range. Named ranges in Sheets use the same style inside formulas, so =SUMIFS(Exp_Amount, Exp_Category, "Rent") works in both applications. The Excel dynamic INDEX trick works in Sheets as well, but it is rarely needed because you can reference whole columns such as C2:C.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

