Excel formulas are also commonly known as worksheet functions. Worksheet functions are used to calculate numbers captured in one or more worksheets. The primary worksheet functions are used for addition (positive and negative numbers), multiplication, counting, finding maximum and minimum values in a range. Each worksheet function must start with an equal sign.
The primary calculation in Excel falls into 11 basic categories as shown below. They can be found in the Subtotal function and also in Pivot Tables.
Beside the above basic calculations, Excel formulas can also be used calculate days and time. This can only be achieved if you understand how Excel stores dates and time.
Generates the serial number of a date based the input year, month and day. Very useful if you need to insert a date into another formula.
Determine the number from 0 to 7 repday of the week for a date.
Display the date that is x number of months after or before a given date.
To determine the number of completed months or years between 2 dates. It is very useful for calculating age or the anniversaries of employees.
The logical formulas do only one thing, to return the result as TRUE or FALSE. They are very powerful and are commonly used to evalation data within a range.
Determine the value or text to display under a specific condition.
Lookup formulas are very good for cleaning up and managing data. They are very efficient when it comes to finding data in a sea of information.
VLOOKUP or Vertical Lookup
Display the value on the right of the lookup value within a range.
HLOOKUP or Horizontal Lookup
Display the value below the lookup value within a range.
Display the value that correspond to the position of lookup value.
Return the range address, calculated based on a reference cell
Returns the position of the lookup value found in a range.
CHOOSE(2,"a","b","c") Returns the value in a given position.
The special formulas below can perform multiple calculation and is reserved for advanced users of Excel.
Find the total sum from two sets of values multipled by each other.
Can perform 11 different calculations and works hand in hand with auto-filter function.
Text formulas as the name imples is used to manage text in Excel. Some of the formulas could also be used to manage values.
Extract text within a cell. You can start from any character in the cell.
Extract part of the text within a cell from the extreme right to the left.
Extract part of the text within a cell, from the left to the right.
Merge texts from different cells into one.
To find out how many characters (including spaces) there are in a cell. For example, if cell A1 contains the word "excel courses", entering the LEN formula (without quotes) "=LEN(A1)" into cell B1 will return the number 13, because there are 13 characters in the word including the space between the word excel and courses.
To present text or value from another cell with certain text or values 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 SUBSTITUTE formula without quotes
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 date 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 :
The following formulas are reserved for those who have some basic foundation in finance.
Identifies the scenario that will make zero losses in a project.
Calculates the number of periods required to recover an investment.
Present Value (PV)
How much is a sum of money in the future worth now.
Net Present Value (NPV)
Calculates the net returns of an investment, calculated based on today's value.
Internal Rate Of Return (IRR)
Determines the discounted rate that causes an investment to breakeven.