How to use relative references in Excel named ranges

By default, Excel automatically assigns absolute address references to cell range names. Such names are most commonly used in work processes. But sometimes you need to assign a relative address reference to a name.



Examples of Names with Relative References

Example Quarter sum

Having profits in individual months, let's calculate the total profit for a quarter. For this task, it is much more convenient to use a name with relative addressing.

  1. Create a report as shown below and go to cell C4.
  2. Choose the "Formulas" - "Defined Names" - "Define Name" tool. In the "Refers to" input field, enter only relative references (without the $ symbol):
    3. Profit for the Quarter
    =Sheet1!B2:B4
  3. In the "Create Name" dialog, enter the name "quarter" in the "Name" field, and enter the relative range address that covers the profits of the first quarter in the "Refers to" field. Set the "Scope" option in the dropdown list as desired. You are familiar with this parameter from the previous lesson: Scope of Name on an Excel Sheet.
  4. In front of the last month of the first quarter (cell C4), enter the formula: =SUM(Quarter). Copy it to all the cells in front of the last months of each quarter: C7, C10, and C13.
    5. Example Quarter sum

Note: To quickly and conveniently change the address type in references (from absolute to relative), use the F4 key.

Assigning a Name to an Excel Function

In Excel, names can be assigned not only to cell ranges and values but also to formulas. A name assigned to a formula with relative address references can be used as a function.

  1. Create an Excel workbook with reports as shown below:
    2. 2023 report
    =SUM('2023'!B2:B5)
  2. Go to cell B6 and choose the "Formulas" - "Define Name" tool. In the "Refers to" input field, enter only relative references.
  3. Fill in the dialog as shown in the image. Pay attention to the fact that the "Refers to" field contains a function with relative references to the range. The "Scope" parameter must specify the current sheet, "2023".
  4. Enter the value =TOTAL in cells B6, C6, and D6.
  5. Copy the "2023" sheet, naming the new copies "2024" and "2025". Open "Formulas" - "Name Manager."
Name Manager.

download file Download

As you can see in the Name Manager, when copying sheets, names "TOTAL" are automatically created for them.