Thanks for downloading this Excel Formulas 140 Excel Formulas and Functions with usage and examples book. Excel is part of the Microsoft Office suite and the world’s most widely used spreadsheet application used by Millions.
Compared to other Spreadsheet programs Excel is very feature rich and popular and incorporated with so many built in Formulas and Functions.
Even if you don’t know the function name you can simply search the word you think related to a formula in the insert function box and you will get back the Formulas related to that word.
This Excel Formulas 140 Excel Formulas and Functions with usage and examples book provides more than 140 Formulas and there use with examples for you to understand and use it in your day to day work.
Please keep in mind some of the new functions will not work in your Computer if you are using previous versions of Excel.
Formula can be entered in Excel by first entering the equal sign (=) followed by the formula name like this =SUM(A1:A100), opening and closing brackets are compulsory.
Or else you can enter a plus sign and type the formula and the formula will be automatically converted to =+SUM(A2:A4).
This is used for compatibility reason as when Excel was introduced the leading spreadsheet program was using the plus sign.
A reference means a cell (A1) or a range of cells (A1:A8) on a worksheet, and tells Excel where to look for the values or data you want to use in a formula. By default Excel follows relative reference.
Say for example if you are adding two cells A1 and B1 in the cell C1 using the formula =SUM(A1+B1) and if you copy down the formula to C2 the Sum formula will automatically change the cell reference relatively to the next row to =SUM(A2+B2).
If you copy the formula across the column to D1 the formula will change to SUM(B1+C1). Absolute reference means the cell reference will always refer to the same cell and for that you have to put a dollar sign before the column name and row number.
As in the above example if you rewrite the formula to =SUM($A$1+$B$1) and if you copy down the formula the cell reference will not change relatively it will always get the sum of A1 and B1 wherever you paste the formula in the sheet.
By pressing the F4 key you can easily insert the dollar sign and if you keep on hitting the F4 key the reference will change to mixed and to relative.
A mixed reference has either an absolute column and relative row, or absolute row and relative column in a formula.
For example =$A1 will always refer to the A column, since there is no dollar sign before the row number if you copy across the formula the column name will not change but the row number will change.
Likewise if there is dollar sign before the row number (A$1) then the column number will change if you copy the formula but the row number will not change.
You can use the auto complete feature of the Excel while entering the formula to speed up the Formula capturing.