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)

3 Comments

  • Hi, My son dropped the Ipad-2 and the screen shattered (a big crack in the center) with tiny bits of glass on the floor. Needless to say that it does not work anymore. Googled and found ur blog with info about where u repaired it. Looks like the “Apple authorized service centers” will charge something equal to the cost of the ipad itself. I’m taking it to one such center for a quote. COULD YOU TELL ME if your Ipad STILL works or you had any problem with the quality of the screen or the Ipad itself after it came back from the local service center??????? PLEASE, any help in this matter is appreciated.