Excel Formulas
|
| Text |
|
Output |
|
Formula |
Description |
|
|
|
|
|
|
| i am indian |
|
I Am Indian |
|
PROPER |
Capitalize first letter of each word in phrase |
| excel data |
|
EXCEL DATA |
|
UPPER |
Uppercase all letters |
| EXCEL DATA |
|
excel data |
|
LOWER |
lower case all letters |
| excel data entry |
|
excel data entry |
|
TRIM |
removes blank space around text |
| 12 |
|
12 |
|
VALUE |
Checks the number only |
| Excel Data |
|
10 |
|
LEN |
counts number of char present in the cell |
| Excel |
|
1 |
|
COUNTIF |
checks the particular word present in the range |
| refer sheet2 and 4 |
|
24 |
|
SUM |
also called 3d sum, calculates sum of values from multiple sheets |
| MBA Crystal Clear |
|
MBA |
|
LEFT |
Which finds leftside characters |
| MBA Crystal Clear |
|
Crystal |
|
|
Which finds middle characters |
|
|
Equal |
|
|
Checks equal valued cells |
| VLOOKUP(F6, $A$2:$B$6,2,0) |
|
|
|
VLOOKUP(F6, $A$2:$B$6,2,0) |
VLOOKUP |
| HLOOKUP(“*”, A2:D2,1,0) |
|
|
|
HLOOKUP(“*”, A2:D2,1,0) |
HLOOKUP |
| CountA |
|
9 |
|
COUNTA |
counts number of cells |
| Concatenate |
|
i am indianexcel data |
|
CONCATENATE |
Joins 2 or more cells values |
|
|
|
|
|
|
| if statements |
|
Result varies |
|
if(b4<35, “Fail”, if(b4<45, “Pass”, if(b4<60, “Second”, if(b4<80, “First”, if(b4<100, “Distinction”))))) |
multiple if statements |
|
|
|
|
|
|
| Future value of savings |
|
Result varies |
|
FV(H6,H5,H4,0,1) |
Best for calculating FV / RD |
|
|
|
|
|
|
| Payment calc / EMI |
|
Result varies |
|
PMT(H14,H13,H12,0,1) |
Best for calculating PMT or EMI |
Microsoft Excel is most powerful software application for office use. And it’s strength lies in the use of advanced formulas for calculating any sort of given data. Here we have listed some of the most common and advanced formulas for MIS. Have a look!