On this page I’ve collected some of the most commonly used Excel formulas, tips and tricks. Some of these might be cryptic as rest of the background stuff behind these formulas remain in my brain. I’m sure you will figure them out when you use them.
I keep updating this page quite regularly as I come across any new formulas or tips.
I still use Microsoft Excel 2003 and most of these formulas are tested on Excel 2003 only. These may or may not work on any other Excel version.
In case you copy the formula, paste in Excel and it doesn’t work, you need to enter the formula manually.
Cell A1 contains the date of birth.
Change the case to be sentence case (first letter of each word to be capital)
E.g. Change This is my name to This Is My Name
Count nonblank Cells
If A1 is 1 then 1 else if A1 is greater than 1 then 0
=SUM(Range Start:OFFSET(Reference, Rows, Cols))
=SUM(A1:OFFSET(A10, -1, 0))
A10 is the cell for sum.
By default all formulas in Excel refer to relative cell. Use $ sign to convert a formula to absolute.
E.g. $A$1 will always refer to cell A1.
Cell A1 will have the date in it.