Microsoft Excel Formulas

Microsoft Excel
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.


Comments

One response to “Microsoft Excel Formulas”

  1. Dennypeters@hotmail.com Peters Avatar
    Dennypeters@hotmail.com Peters

    List is too short for this Excel Junky. These formulas can not be found in a help search because one does not know how to say it. Thanks. Love ’em. More. And I love your Bing Images program. Today trying to find an iPhone version.

Leave a Reply to Dennypeters@hotmail.com Peters Cancel reply

Your email address will not be published. Required fields are marked *