Excel formulas

Excel formulas, also known as worksheet functions, is one of the key features in Microsoft Excel. They are not defined as excel functions in our resource center because we feel that another group of tools in Excel are more qualified to be called excel functions.

Unknown to the novice Excel users, Excel has one of the most comprehensive set of formulas, not only to perform calculation but also to manage data records. Unlike most programs where the results are displayed on request, Excel has the ability to instantaneously re-calculates the results as the raw data changes. It is so powerful that it can perform the calculation faster than the user making those changes.

For example, Excel is able to find out the month of a particular date through the use of excel functions. By itself, it does not seem to offer any advantage. But if you are required to find out the month of a hundred dates, a thousand dates or even ten thousand dates, the month function would help you get those results out in minutes.

I have classified the formulas into different categories so that it is easier for you to refer to them.

Date and time worksheet formulas

About Excel date and time
Basic understanding of how date and time are stored in Excel.

DATE Formula
Converts a specific date to serial number.

DAY Formula
To determine the day of the month for a date.

NETWORKDAYS Formula
To calculate the number of working days between 2 dates.

WEEKDAY Formula
To determine the day of the week for a date.

EDATE Formula
To determine the date that is x number of months after the input date.

DATEDIF Formula
To determine the number of complete months, years between 2 dates. It is very useful for calculating age or the anniversaries of employees.

Lookup and Reference worksheet formulas

VLOOKUP Formula
To display the value in a cell that is on the right of the lookup value.

HLOOKUP Formula
To display the value in a cell that is below the lookup value.

LOOKUP Formula
An alternative to IF formula

OFFSET Formula
Could be used to determine the range based on pre-set criteria.

Special Formulas

SUMPRODUCT
Find the sum in a range if they meet certain condition(s)

Text and data worksheet formulas

MID Formula
picking up text within a cell

CONCATENATE Formula
Merge texts from different cells into one.

LEN Formula
To find out how many characters (including spaces) there are in a cell. FOr example, if cell A1 contains the word "excel course", entering the LEN formula (without quotes) "=LEN(A1)" into cell B1 will return the numeber 12, because there are 12 characters in the word including space between excel and course.

SUBSTITUE Formula
To present a word from another cell with certain alphabet(s) replaced. For example, you can replace the word "course" in "excel course" displayed in Cell A1 with the word "training" and present it in cell B5 by entering the SUBSTITUE formula without quotes

"=SUBSTITUTE(A1,"course","training")"

in cell B5. Cell B5 will display the word "excel training".

TEXT Formula
To present a number as text and in a pre-defined format. This formula is very useful when you want to present excel dates (they are actually numbers) in some other date format.
For example, you can convert the day 16-Mar-09 displayed in cell A1 to 03/09 by making reference to cell A1 and giving the format code (including the quotes) "MM/YY" in the TEXT formula as shown :

=TEXT(A1,"MM/YY")