# How do I get the last value of a column in an Excel spreadsheet?

In column A, I have dates. In column B, I have my body weight logged for the day. I add one row to each every day when I weigh myself, so this means the data is sorted by date ascending. The weights, of course, fluctuate (though it would be nice if they would go down every day for my own personal benefit).

For a couple of calculations, I want to get the latest (or last) weight entered in column B. Not the max or the min, but the last one entered in the column. I want it to work no many how many rows I enter.

I use Excel 2007, if that ends up mattering.

-

## 3 Answers

You can put in another cell (supposing there is no blank cell in the column B):

``````=INDEX(B:B,COUNT(B:B))
``````

Note that you could also try (if your weight is not recognized as numbers and supposing you have titles on the first row):

``````=INDEX(B:B,COUNTA(B:B) + 1)
``````
-
That was it! As soon as Stackexchange allows me, I'll mark as answered (apparently there's a time limit on that). – Chris Feb 8 '12 at 15:10
The first was the one I used since I don't have titles, and the weights are marked as numbers. – Chris Feb 8 '12 at 15:14
@Chris: glad it helped :) – JMax Feb 8 '12 at 15:15

This version will work even with blanks in the column (assumes data is numeric)

`=LOOKUP(9.99E+307,B:B)`

-
Good call on the blanks. – Jesse Feb 8 '12 at 17:21

There is a trick with `=CELL("CONTENTS")`

If you leave the cell reference blank, just like in the formula above, then the cell will display the last entered cell for the whole workbook.

It might not be useful, but in case if your weight is the last information you enter then it will be displayed.

Hope this helps.

-