How can we improve Excel for Windows (Desktop Application)?

Ability to reference the end of the column or row regardless of the starting position

I can enter A:A to reference the entire column or 1:1 for the whole row, but if I want to go from A2:A, I have to enter a number for the end of the range.

What if I don't know where it ends? Or where it may end someday? If I type A2:A10000 I'm safe only as long as my data doesn't exceed 9,999 rows, which is hard to determine. I want to reference the whole rest of the column regardless of how much it expands in the future.

And because of this, sometimes a reference like A:A in conditional formatting gets translated later to $A$1:$A$134050, and that can be confusing because if I didn't write the original CF, I would be guessing at the significance of stopping at 134050 and not before or after.

So whole-column and whole-row references are really just a way to 1) make universal formulas easier to write and 2) keeping the workbook references tidy.

5 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)

    We’ll send you updates on this idea

    DtorkDtork shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    5 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...
      • Kenneth BarberKenneth Barber commented  ·   ·  Flag as inappropriate

        In that case, while you wait for your suggestion to be implemented, you could use the Name Manager to get the syntax that you've suggested.

        If you want the end of column A, define a name "A" whose formula is =INDEX(A:A,ROWS(A:A)). Then you can literally just write A2:A like in your suggestion, and it will work.

        Rows are a bit different, since you can't have a name that is a number, but the same concept applies.

        The disadvantage is that you would need 1 name per column whose end that you want.

      • DtorkDtork commented  ·   ·  Flag as inappropriate

        Kenneth, I think you miss the point of this site. I know tables. I use tables. And I'm well aware of their limitations when it comes to using their references. But when tables are not an option--or when I just want my the intent of my spreadsheets to be clearer and more precise for other users--this is my suggestion to MSFT.

        See that little grey note below the post? The one that says "Dtork shared this idea"? I'm not asking for help, I'm sharing an idea. Cheers.

      • Kenneth BarberKenneth Barber commented  ·   ·  Flag as inappropriate

        I still say that you should put your data into a table (select your data, and then Insert tab → Table or Ctrl + T). Tables extend only as far down as you let them, and as the number of rows in the tables changes, the references to the table columns do not change.

        Conditional formatting doesn't support the table style of reference (e.g. Table[Column]), so you will still have to use the A1:A2 style of reference for your columns. However, the conditional formatting is smart enough to change the column reference as the number of rows changes.

        Give it a try!

      • DtorkDtork commented  ·   ·  Flag as inappropriate

        True, I can write out those functions (and then conditional formatting immediately translates it to 1048576), but it'd be a whole lot cleaner to use the column letter as a universal reference to the end of the column, in the same way it is when referencing the first and last rows continuously.

        I'm just asking devs to extend the usability a bit. If I needed a workaround, I'd just add ROW()>1 as a condition in my CF formula. Typing INDEX(AA:ROWS(A:A)) for the range application is just as annoying as simply typing the number 1048576 each time and keeping it on a sticky until I have it memorized.

      • Kenneth BarberKenneth Barber commented  ·   ·  Flag as inappropriate

        You can already do this. Use Insert tab → Table or Ctrl + T to transform your data into a table. Then you can reference the table column rather than the sheet column.

        If you cannot use a table, use a reference like A2:INDEX(A:A,ROWS(A:A)).

        Explanation:
        It's not just cell references that are valid in the A1:B2 syntax. Anything that returns a reference can be used. In this case, we have A2:INDEX(). INDEX returns a reference to the cell at a given position (ROWS(A:A)) in the given array (A:A). ROWS(A:A) is the number of rows in column A. Using it in INDEX gives us a reference to the last cell in column A.

      Feedback and Knowledge Base