FuTuRe VaLuE

When we make any investment or do fixed deposit or any recurring deposit (RD) or take an insurance policy. We want to know/calculate how much amount we get after maturity/due date.

• It is very easy to calculate the total amount after maturity by applying a simple formula. Below you can see a table in which the amount, rate % and the time period is given.

• By applying formula =FV(Rate,Nper,Pmt,,1) here one stand for beginning of the period. After you type =FV you can see a description.

• Now we select all the figures according to the formula. As you can see below.

• Now a question arises here why the amount appear which minus sign and red colour. This is because your bank is receiving your money on your behalf and it will pay out this money at the end. And the accountant denotes this amount by red colour or negative sign.

SpLiT nAmE

This formula is related to the separation of the first name and the last name in different cells. We generally write the name with its surname in the same cell.

To make data in single cell is not a issue we can change it according to our requirements. By applying this simple formula

• Below you can see a name in a cell. Now select the data tab. In the data tab select text to columns.

• Now select fixed width. And press next. 

• Then press next again.

• Then press finish.
• Now you can see the first and last name in different cell.

• And our next formula is related to change the name in single cell. 

• For this we have to apply

          =CONCATENATE (Select First Name , last name).

• And from two different cell the name is converted into single cell. And you can drag the whole column.

ChAnGe CaSe

When it come to excel one option named change case which we use in MS WORD not present. It became difficult to change the type the whole data again but with the help of change case we can capitalize, lower or write the word in sentence case. To do the work of change case in excel there are some formulas.

• First formula is related to capitalize each word. If we made our data in small alphabets and now we need each word in capital letter .In this data you can each word in lower case.

• By applying formula =UPPER(Select Cell ) . Then press Enter . This is the easy and quick way to convert words from lower to upper.

• Now drag the cell up to the point our name are mention 

• Next formula is to convert the data in sentence case where the first letter of the name will be capital. Formula is =PROPER(Select Range ) . Then Press Enter . 

• And our last case is when our letters are in capital word or sentence case and we want each word in lower case formula to convert in the lower case =LOWER(Select Cell ). Then press Enter 

There is only one exception with this formula you have to apply it in every row or column separately.

If you apply the formula in one column then you can make capital , small words of that particular column.

mR. & mRs.

When we have completed our data and submit that data to our authority and they say to add salutation MR. or MRS.  In front of the name or Kumar , Singh and the end of the name. A blunder take place because you have completed the data . You don’t need to make that data again . By applying a simple formula you can resolve this .

• There are some names in which we add salutation MR. and MRS.

• Now we apply the formula = “MR “ &Select Name . then press enter . And for female write =”MRS. “&Select Name . then press enter . After MR. / MRS. You can see there is a space this is because to separate the salutation from name.

• No need to apply formula again and again just simply drag it and paste in the desired cell . You can see the formula in the formula bar

• As salutation MR. and MRS. You can add KUMAR and SINGH at the end of the name as suffix .
• By applying =Select Name “ Kumar “ . Then press ENTER 

MaX & MiN

How to know the maximum or minimum marks or any other value. If you have a very large data and you want to see which is the maximum or minimum value / marks. Then by using a simple formula we know this .

•  Here is  data of students who scored marks in different subjects . and you can see we have done the sum total of the subjects . 

• Now by applying formula which you can see below in the image =max(range) .Then press enter . you can see which is the highest marks and if you want to know which person has secured highest marks in the HOME tab

  First click on conditional formatting

  Then highlight cell rules

  You can see greater than , more than , between , equal to by which you can see which students secured more or least marks

• Similarly by using formula =min(range). Then press enter . you get the minimum marks of the student. 

DiViDe

This formula is to calculate per unit or per month salary . If you know the annual salary and want to know the per month salary then by applying this formula you can calculate it .

• Below you can see a data related to the salary structure. In this data you have given annual salary of the employees and you can know the per month salary 

• By applying formula =ANNUAL INCOME/MONTHS . Then press enter. In place of annual income and months you can have total cost of the product and per unit cost or any other data .

• Now you can see your per month salary.

• Now simply drag the column by putting cursor on it. And you get the per month salary of every employee. 

MuLtIpLy

The next formula is basically for calculating total amount of per unit price of the product  or salary of the employees per day etc 

• We have a data related to the computer parts , their quantity ,and price per unit  

• By applying formula  = QUANTITY*PER PIECE  . Then press ENTER . In place of quantity and per piece you can have some other data like no. Of days and per day

             

• Now you get your total amount 

• You need not to apply this formula again and again . Just simply drag the cell by putting the cursor on it ..

                      

SuBtRaCt

So today we will learn how to subtract the values .

For ex. – If we want to calculate the balance from the total amount to the amount paid we can know it by subtracting .

• There is a small data in which we can calculate the balance 

•  By applying formula =AMOUNT – PAID . Then press enter .in place of amount and paid you can also put any other transaction like income and expenses

•  So you can now see we get our balance .

• You have not to apply this formula again and again you can simply drag the cell by putting cursor on it ..

 

SUM

Its time to boost up your skills to survive in this competitive world .

Some basic formulas of excel which can make your work easier . If you are working or want to do a job

As a Clerk or an accountant..

Lets start with the formula of sum

•     Here is a small data which we add today

             

•           Now apply the formula SUM(RANGE). Then press ENTER
•      There Is another option select the range and press Alt =

             

• Or you can use auto sum in HOME tab

           

             

•         Now we get the sum….