Nice answer, but I found that =IFERROR(SMALL(A:A,ROW()-1),"") works equally well. What's the advantage of adding MATCH() and INDEX()? And ROW($1:$1) always returns 1, so why not use the literal, which can be incremented if there are more header lines?

@AFH Good catch!. There's no need for the INDEX()/MATCH() at all and there's no advantage to having them. (I'm so used to more complex formula where they are required, that I forgot that for this simple case they weren't needed.) The reason for using ROW($1:$1) is precisely so that if more header rows are added (or rows inserted above the Table) after the formula has been entered, the Table won't break and you don't need to adjust a literal value to fix it. It makes the Table pretty much bullet-proof. I've updated the answer with an even simpler version. I hope you don't mind.

I've tried this, but ROW($1:$1) still returns 1 when an extra header line is added. What sort of works is =SMALL(A:A,ROW(1:1)), but only the row below the inserted line(s) is maintained with ROW(1:1). The rows below are unchanged, so the formula has to be copied again down the rest of the column. If a row is inserted above the existing header, none of the formula cells is maintained. What works a bit better is to set a work cell (I chose G1) to =ROW(A2)-1 and use =SMALL(A:A,ROW()-G$1) for the sorted column, but even this fails if a row is inserted above the header (G1 moves).

@AFH Oops. I shouldn't have said "if more header rows are added" in my previous comment. The Table is not a table but a Table (Insert > Tables > Table) and you can't have more than one header row for a Table. I assume that when talking about Tables in Excel, and the screenshot shows a Table, not a table, we are talking about Tables ;-) When I was mentioned inserting rows, I was actually talking about inserting entire rows, not just the cells above the Table. Luckily for me, with a Table starting on the first row, you can only insert entire rows, and the formula still works. …

… You are right, however, that for a normal table adding extra header rows breaks the formula. You are also right that for a normal table, and a Table not on the first row, inserting anything other than an entire row above the table will break the formula. The solution that is actually bulletproof requires a Structured Table Reference, and is =SMALL(A:A,ROW()-ROW(AutoSort[#Headers])).

I've found a formula that seems to work: =SMALL(A:A,ROW()-MATCH("^-*[0-9][0-9]*$",A:A,0)+1). The MATCH() finds the first numeric value in the data column and uses this to offset the ROW() in the sort column.

@AFH That looks hacky, but nice! Will only work for integers as it currently is, though ;-)

We should probably clean up the comments. I suggest just leaving the first two.

There may be better ways of finding the first number in a column. I use regular expressions quite a lot, so their use comes fairly naturally to me, but I'm the first to admit that they look geeky, and beyond the needs of the average Excel user. I use a lot of the Excel functions, but I've never explored more advanced data structures and features, and I don't use VBA, since I mostly use LibreOffice on Linux.

I still haven't found a circumstance when ROW($1,$1) returns anything other than 1 or any cell where it gets incremented/decremented when a row is inserted/deleted.

Must be late here as I completely forgot - in Excel, MATCH() can only use the wildcards * and ?, and only if the third parameter is 0. Your formula won't work.

The IFERROR() is not required when using a Table in Excel as the column doesn't need to be pre-filled since the Table auto-extends, including the formulas, when data is entered in row after the last row.

As for the ROW($1,$1), in Excel create the Table shown in my answer, and enter the formula; then insert an entire row above the first row. You will see that it changes to ROW($2,$2). Don't know what happens in LibreOffice ;-)

I leave it to you to decide which comments are worth leaving in.

Yeah. If you are testing in LO (or Google Sheets) and the question is about Excel, you really should preface your comments with "tested in LO" ;-)

I suggest you leave your first comment and delete your other two. I've cleaned up mine. @AFH

@robinCTS Done.