A while back I posted a formula to find the row number of the last used cell in a column. I revisit the solution to provide the last used column number in a row.

Now, I must admit I am not sure if this is new and caused by a recent update.

The AGGREGATE function is not supposed to work across rows, but it does in this case.

The formula from the old blog post that works out the last used row number in a column is.

=AGGREGATE(14,6,ROW(A:A)/(NOT(ISBLANK(A:A))),1)

The old blog post explained how this formula works.

To work out the last used column number in a row we can use.

=AGGREGATE(14,6,COLUMN(2:2)/(NOT(ISBLANK(2:2))),1)

This uses the same technique as before. I am surprised this works across the row as AGGREGATE is only supposed to work on columns.

Cell P2 has an IF function that displays a blank cell, so it has an entry and is the last used cell in the row.

Note both solutions ignore cells with only formatting and only pick up cells that have entries in them.

In the above picture cell R2 has a blue format applied, but there is no entry in the cell and so it is ignored.

The formula handles errors.

If I enter an error in the cell R2 the formula updates correctly.