Create a Named Range that Always References the Cell Above

Did you know you can create a named range that always references the cell above it?

In other words, you can create a situation where all you have to do is enter =CellAbove and the Excel will return a reference to cell above the cell you’re in.

.

.

First, I’ll explain how to do it, then why you would want to.

.



How to do it:

First, place your cursor in cell A2.

Next, click on the Formulas tab, then choose Name Manager, and then click the New button.

The New Name dialog box will open.

Enter the CellAbove in the Name input box

Enter =A1 in the Refers to input box. Be sure to not include any Dollar symbols ($). This will ensure that wherever you use the named range, it will always refer to the cell above, not just A1.

.

After you confirm your new named range by clicking OK, you will be able to use it anywhere on the sheet simply by entering =CellAbove.

.

.

Why you would want to do this:

You may be a bit underwhelmed at the power of this technique initially. But it does come in handy in certain situations. Here are a couple of scenarios where I use this.

.

Give your formulas some stability

You can use the CellAbove named range as a reference in any formula.

For example, I sometimes add it to SUM formulas to create a kind of expanding range that ensures all the data is captured; even if I add rows to my table of numbers. In the screenshot below, the sum formula calculates for Cell D20 to the CellAbove. This ensures that if I add a row in between row 22 and 23, the Sum formula continues to get all the data.

.

.

Give your tables some dynamic formatting

When building a report, sometimes I like to format tables so that redundant data is not shown. In this example, I only want to show each region name one time.

.

So I highlight the region column, and start a new Conditional Formatting rule (click on Home ->Conditional Formatting -> New Rule).

In the dialog box, I choose the “Format only cells that contain” option, then specify that I want the rule to apply when the cell value is equal to =CellAbove.

.

.

Then I click the Format button to apply a custom Number format. Here, I set the applicable cells to ;;; (this basically blanks out the cells). You could also set the font and fill to white, but the data would show when highlighting the range.

.

.

After pressing OK, I get this effect.

Neato!

.

.

I’m sure you can think of other ways to use this technique.

.

Note:

Using this technique, the CellAbove named range will only apply to the sheet it was created on.

That is to say, it won’t work on other sheets. For each sheet you need to use this technique on, you’ll need to create its own named range.

11 thoughts on “Create a Named Range that Always References the Cell Above

  1. Haffy

    Mike, if you make it a workbook-level Name and include an exclamation mark in the formula, eg =!A1, you can use it on any sheet. Just the exclamation mark, mind – not the sheet name.

  2. Big Robert

    Do you have a sample file I can look at.
    I have been unable to duplicate the last example.

  3. Oleksiy

    Haffy, nice tip!
    @airplayne – Yes, it works with any Excel
    @Big Robert – What error do you get?

  4. Vernon Wankerl

    I had an occasion to try this and it works. I also needed to do a CellBelow Defined Name and it works too!

  5. JohnC

    Since no one else has said it, this is a great tip. I wondered if there was a better way to add a total that didn’t have to be fixed after every row insert. Thx much!

  6. Stefan

    @Haffy: apparently using a named range with a refersto that starts with “!” leads to bugs. I can confirm this as I recently experienced an issue where cells using such a named range were not recalculating properly. A great workaround suggested to me by Charles Williams on stackoverflow is to use a refersto like this:

    =INDIRECT(“R[-1]C”,FALSE)

  7. ScottK

    Dude, this is remarkable. Solves so many problems. I can’t believe I never thought of it! Great work!

  8. WDM

    This has to be one of the best tips I’ve ever found!!!! Thank you for sharing it. I’ve found this very handy for using on a form where the user wants to show a row numbers (but not an Excel Row number) and needs to delete empty rows. By using cellabove+1, I can do that. It also works great on filtered lists as well.

  9. CJ

    I forgot to mention my favourite use-case for this trick:

    Its a relative cell reference that doesn’t change when you insert, cut or copy (much like OFFSET and INDIRECT).

    Imagine a chain of events, a chronological or derivative list of items or a linked “tree”. The current row always needs to act on the row just above it. When you insert or move a row, normal references will adjust to keep referring to the same content. Using OFFSET will work but is slow. Relative named ranges FTW.

Leave a Reply

Your email address will not be published. Required fields are marked *