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.
A.C. WILSON commented
OssieMac's suggested implementation is a good one.
The method that I would suggest is for a character to represent the last used row or last used column. I would suggest the # character like the following examples.
A2:A# would be from A2 to last used row.
A2:#200 would be the last used column.
A2:## last used column and last used row.
A fantastic idea that would help many novice to intermediate users.
Yep, cleaner indeed. And therefore easier to use and easier to understand, especially for others.
Always nice to see someone offer up workarounds to help in the meantime, but of course, since the whole point of this site is to suggest things it'd be nice to see in the (hopefully near) future, no workaround can substitute for the desire to see Excel offer something directly and cleanly.
On Excel's side, acting on this suggestion would be one way to "future-ize" the program. Using this feature, a formula works when the number of rows or columns, or even pages, is increased. It also makes it work cross platform if Excel wrote a version for, say, phones, that differed from desktop or tablets, and did not offer the same maximum number of rows or columns. Instead of being broken, it would work on and on.
And if Excel would like to be a familiar and useful interface to everything (toasters, refrigerators, table saws, hot tubs, kids' toys, etc.), it would be extremely likely it would need to offer limited row/column extent versions. So...
But again, the whole point of the site is to not have to give in to three decades of workarounds piled on workarounds, all archaic and none transparent, but rather to express present/future needs and desires to do things directly.
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.