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.
Formula to calculate age in Excel
In case you copy the formula, paste in Excel and it doesn’t work, you need to enter the formula manually.
=INT((TODAY()-A1)/365.25)
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
=SUBSTITUTE(PROPER(SUBSTITUTE(A1,”`”,”Q”)),”q”,”`”)
Count occurrence of a value
=COUNTIF(A1:A7,”String”)
Count nonblank Cells
=COUNTA(A1:A10)
IF ELSE
If A1 is 1 then 1 else if A1 is greater than 1 then 0
=IF(A1=”1″,1,IF(A1>”1″,0))
Formula Always Referring To Cell Above Without A Cell Reference
=INDIRECT(ADDRESS(ROW()-1,COLUMN()))
Sum all cells above which doesn’t get disturbed if you insert a row above the sum row
=SUM(Range Start:OFFSET(Reference, Rows, Cols))
E.g.
=SUM(A1:OFFSET(A10, -1, 0))
A10 is the cell for sum.
Refer to Absolute cell
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.
Hyperlink
=HYPERLINK(B2)
Date to Day of the Week
=TEXT(A1,”ddd”)
Cell A1 will have the date in it.
Leave a Reply