Excel Guides

Relative references within named ranges in Excel

When working with named ranges in Excel, you may sometimes want to use a relative reference within the named range. For example, let's say you have a named range called "Data" that refers to cells A1:A10. If you want to add a new row of data to the end of the named range, you could use a relative reference like this:

=Data!$A$1:$A$11

The $ signs in front of the row and column references make them absolute, so the reference will always point to cells A1:A11 no matter where it's used. But what if you want the reference to be relative? In other words, you want it to always refer to the last row of data in the named range, even if more rows are added later.

To do this, you can use a relative reference in your named range. For example, let's say your named range "Data" refers to cells A1:A10, and you want to add a new row of data to the end of the named range. You could use a relative reference like this:

=Data!$A$1:$A$10

Now, if you add another row of data below row 10, the named range will automatically include that new row. The reference is still pointing to cells A1:A10, but because row 10 is now the last row of data, the reference will include the new row as well.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.