The following will work nicely and has the feature that it only returns rows with something in column A:
Since you ask for only column A... Edit the A31 as needed to suit the rows you need and also, if you really need more columns than just A, edit the column label part as well.
The problem is in the end point. Let's say the last used row is (presently) row 20,000. You can simply use that for the row number edit, if you like, and it will handle row insertions and deletions just fine as Excel does the keeping track of those effects. But it won't handle someone simple typing the next entry in row 20,001, and so on.
One can get past that by specifying some suitably larger value, say A25000, so as to allow for a lot of that before it is an issue again. Of course, the farther down one goes like that, the longer it will be till reaching it and the likelier one is to forget about that... or remember to pass it on to the person taking it over when one gets promoted.
Before the next part, I should mention that the function mentioned a lot can be used in two ways in this kind of problem. One is to use it to build a string that is the range address you need, then give that to
INDIRECT() to make it a real boy. This is not an issue nowadays given memory and speed in modern machines, but pile it up in a HUGE number of cells, and the fact it recalcs every time anything changes and you could see some actual slowdown, even today. 100,000 rows and 30 columns bogs me down to taking a noticeable second or so lag, but at 20 columns, nothing apparent. Still, a consideration. The other way is to use it in a function like
INDEX() is not volatile so it only recalcs when Excel thinks it needs to.
FILTER() is happy to have either present it with the range you require. So probably you'd prefer
Or, avoid either way by using the approach I recommend at the end.
The way of choosing an end point for the range COULD seem to be to use something like
COUNTA() to give you a dynamic and accurate number, even adding some amount to it to accommodate any Excel "funny business" on the matter. But... if blank cells are possible, you immediately start seeing lines from the bottom not being presented on your output sheet. So, rows 2-2,001 have entries and
COUNTA() gives 2,000. And immediately your formula is only returning things from the range A2:A2000, not A2:A2001. Again, adding some suitably (seemingly anyway) high value to that would cover you for a time, but eventually, the problem would occur.
(Naturally, if blanks are not possible, it is not a problem.)
There is no function in Excel to return the last used cell in a row or column. There are plenty of workaround approaches, but nothing is surefire. Adding columns complicates that issue. Even "polling" the columns involved with
MAX(COUNTA(one column after another, then MAX choosing the highest value returned)) does not assure success if blanks are involved: The blanks do not all have to be in the same rows. So A1:B5 can have in column A
a, blank, d, blank, g and give 3 while column B might have
blank, a, blank, blank, blank and give 1. Neither is the right value as 5 was needed. Approaches like "textjoining" all the cells in a row and presenting that to
COUNTA() run into problems with the limits on internal array length in Excel's formulas (not the row textjoining, probably, but when the array of all those rows is put together for
COUNTA() to count from).
Personally, I would just give the range used by
FILTER() a suitably higher number, maybe even get a little fancy and just multiply its result by some factor like 1.25, or whatever seems suitable to your needs for the project in hand. (
ROUND(xxx,0) of course, to get an integer!)
To really solve your problem, you can use the gold standard: VBA. Either a full-blown, do much/all kind of macro, or just a UDF that uses VBA to find that true LastCell, make a range of it and the known starting cell, and give that to the formula. VBA is almost always appreciably faster than Excel formulas and if you fashion the whole thing in VBA, you just need the UDF. Though it'd have to be volatile to update as changes occur, so the VBA code would run lots more than the formula calculates... might be very "pound foolish"...