05 November, 2012

Get the last value in an Excel Range

I frequently copy formulas from one months data to the next, but I run into trouble when I want to get a total off the last row, when the last row is different each month. I discovered a formula that will read the last value (before blanks) in a range.

  1. Enter the formula =INDEX($E$1:$E$5100,LARGE(IF($E$1:$E$5100<>"",ROW($E$1:$E$5100)),1),1)
  2. Press Ctrl-Shft-Enter
The key here is you must use Ctrl-Shft-Enter. It will insert curly brackets, which wont work if you manually enter them. The curly brackets are needed as this is an array formula. See Introducing array formulas in Excel from Microsoft or search for other websites for further information.