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.
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.
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.
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!
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.
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)).
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.