Microsoft

MS Excel Tips

I use MS Excel a lot in a day for reviewing financial reports, client proposals, analysis of market data and so on. Beyond simple calculations, charts I find working with Excel especially formulas to be difficult. Most of the time when that happens Google or Bing comes to rescue. In this post I plan to write down the tips that I used which I hope will be useful to others and to myself in future.

When we want to do a summary sheet in a Excel file, bringing the last value in a particular range of cells (say a column).

For picking the last non empty number from a range of cells, 9.999* is the largest number in Excel

 =VLOOKUP(9.99999999999999E+307,A:A,1) 

For picking the last value from a range of cells, works for me for Date values as well

=OFFSET($A$1,COUNT(A:A),0)

For picking the last value from a range of cells, in the below case the range is from another sheet

=OFFSET('Sheet1'!$A$1,COUNT('Sheet1'!A:A),0)