Each week I add a new column to a spreadsheet and then need to update several formulas to calculate the delta between the last column and the column to the left of it.

I haven't figured out a way to get the value from that column so i can do math on it. The other solutions i found would let me reference that column...

enter image description here


To determine the last column, one should decide in what row to measure it. Suppose it's the 2nd row; then the last column number is

=max(filter(column(K2:2), len(K2:2)))

where I put K to avoid circular dependency, since this formula will also affect some cells in row 2, to the left of column K. I'd put the above formula in some cell, e.g., E1 (or on another sheet), and then use it in computations like this:

=arrayformula(filter(K2:27, column(K2:2)=E1) - filter(K2:27, column(K2:2)=E1-1))

This takes the difference between the last and second-to-last columns.

Instead of filter, one could incorporate column number in formulas by using indirect with R1C1 notation, but that seems more complicated to me.

| improve this answer | |
  • Thanks! Works great. RE: 'K' as the column... you just needed any column so you could get the size of a range right? Like you could replace K with L or M and it'd work the same. – blak3r Jul 14 '16 at 19:25
  • 1
    Right, it can be any column that's to the right of where your formula is. Easiest to make it the next column to the right – user79865 Jul 14 '16 at 19:26

I'm not entirely sure if the original poster was originally asking for the value of the last column (in letter notation) or the value from the last column, but I specifically needed the letter value of the last column in a sheet, in order to then use it in a QUERY formula.

The previous respondent's answer provides a nice way of getting the numeric value of the last column.

Wrapping this formula with ADDRESS() and then SUBSTITUTE() will return just the column letter value that I (at least) was looking for:

=SUBSTITUTE(ADDRESS(1, max(filter(column(K1:1), len(K1:1))),4), "1", "")

(In my case, I am primarily concerned with headers, so my formula works on row 1).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.