FrEeZe PaNe

In a company or a school we have a number of students and employees and we maintain the data of all of them but sub times a hindrance came when the data is very big and we scroll the page and we don’t see the heading or if we scroll right we don’t see the serial number or the name of that person. But for this we have an option in the excel name freeze pane which help us to fix the heading, serial number or the name or any other headings which we want to see after we scroll.

The freeze pane option is mention in the view tab.

• If we only want to freeze the top cell/row then we have to click on freeze top row. So that the top row should be visible even after scrolling.
• If we want to see the left side of the page where the names are written then click freeze first column
• And if you want to fix both the top row and the first column then click on the freeze pane.

This option make the work easier so that we can see the headings. The option looks like given image.

DaTa VaLiDaTiOn

So here is one more feature of excel by which we write only text or numbers in the cell/column/row. We can do this by data validation in the data tab. And for entering the mobile number we can format the columns so that it can accept only 10 digits. After formatting according to text or numbers it can accept only that if we format according to number the cell will accept only numbers if we try to write any text it will reject it. Similarly in case of text it will reject numbers.

Text  

If we want to write only text in the columns there are some steps:

• Select the area in which you want only text.
• Then click data validation option in data tab.

• In data validation click on settings and you will see the validation criteria .
• From validation criteria allow custom.

• Then type formula =ISTEXT(Range in which we want text). Then press enter.

Now you can write only text in it if you try to write number it will reject it. If you want to clear this format then you can see in the above picture the option clear all. Click on it and press enter/ok. And all the format you apply with the help of data validation should be cleared.

NUMBERS

In case of numbers there are two cases whether to fix the limit of number or write only numbers in columns. Let’s start with first one the limit of numbers. Limit should be fixed mainly for phone numbers. So that we don’t enter the wrong mobile number. All we need to do is:

• Select the column in which we write mobile number
• Click on data validation in data tab.

• Now in settings select text length in validation criteria, and in data select equal to and in length type 10.
• Then press enter.

By mistake if you write less or more than 10 numbers it will reject it. So it helps to lower your mistakes.

Now the second aspect is to write only number in the column. for this we have to follow the below steps:

• Select the column in which you want to write only numbers
• Go on data tab and click data validation

• In the validation criteria select custom and in the formula type =ISNUMBER(Range of the column). And then press enter. 

PrOtEcT sHeEt AnD wOrKbOoK

How to protect our sheet or workbook?

If we want that nobody can make changes in our sheet than we need to protect our sheet. And we can easily do this by clicking on REVIEW tab.

Sometimes it became very important to protect our sheet because the data inside is really important for us or the organization in which we work. At that time we don’t want to take any risk so we need to protect it. But if we want nobody can see our data also what is written we need to protect our whole workbook. Now in this we learn how to protect our sheet and workbook both. And also if we want some cells to work.

Protect sheet

To protect our sheet we have to follow the below steps:

• In excel click on review tab.
• There you see option protect sheet. You have to click on that option.

• Now they ask for a password to unprotect that sheet then you have to re-enter that password. And then click ok.

If anyone tries to write on this he/she will be unable to write anything. Only you can work on the sheet after entering the password.

Protect workbook

Workbook is protected so that nobody can check the matter written inside the sheet. It was the protection of the whole book. Which can seen only by you. The following steps are given below.

• For protecting workbook you have to click on review tab.
• After that click on protect workbook as you can see in the above picture the icon near protect sheet.
• Then enter and re-enter the password and click ok.

How to unlock some cells?

Apart from protecting sheet and workbook there is an another thing unlocking cells. By this we can unlock some cells from the protected sheet. Like if we want to fill a form. For this you have to check below the following steps:

We have made a small data related to the person detail.

Now select all the cells in which you want the person to fill the details. You can select the cells by CTRL+LEFT click of mouse.

Now right click on the mouse you will see various options click format. After clicking format you see a screen like below.

Now click on protection and un tick the locked button. Then press ok.

After that protect the whole sheet by applying the above steps. Now the person can’t make any changes he/she will only fill the blanks which you unlock.

This are some simple things by which you can safe /protect your data for others. And make your work easier.

HoW tO mAkE aTtEnDaNcE ShEeT iN tHe ExCeL

If you are working as an accountant or a clerk you have to make monthly attendance sheet of the employees/staff/students. Monthly attendance sheet is mainly used for calculating the salary paid to the employees. Because in this we check the total working days and the employee present/absent in that particular month.

• Given below is the weekly attendance sheet of the employees. We have to fill /mark the attendance manually. It consume some time but after that with the help of formula we calculate the no. of working /present or absent days of the employee.

• First step is to calculate the working days. Total working days of that particular company/office/ school or any other institution. So, we apply =COUNTA(Range of day of the particular employee).

• As you can see below the formula applied. By this formula we know the total number of working days.

• After typing formula press enter and you see the number of working days and simply drag the column up to where the list of the employees. You can see the formula in the formula bar.

• Now we calculate the number of days the employees are present =COUNTIF(Select Range of days , " P") . then press enter. You can see the days on which the employee is present . For calculating the number of days the employee absent we apply the similar formula as the formula applied in the calculation of number of present days. =COUNTIF(Select Range of days , " A"). Then press enter and drag the column till the list of employees.

• And to calculate the total amount paid we multiply the no. of present days and daily wages. The multiply formula which I mentioned in the previous blog. 

• Press enter and the total amount paid to the employee will be shown then drag the column.

In above picture you will see how the whole attendance should be maintained. By this particular formula you can work anywhere as a clerk or accountant.

lOoKuP

In offices / factories/schools we have a large number of data. And to find one name from the whole data become difficult .So to make our work easier a LOOKUP function is in built in excel. This function is performed in a row or column. In this we select a range from which we have want to find the name.

• The data below is of the employees with their residence and salary. As you can see the data is very big we can’t see the names below. So here is the quick and  easy way.

• By applying formula =LOOKUP (First Cell, whole range, the range of content we want in that cell).And then press enter. We not only apply this for office use but also in school for student’s fees or marks.

• This is how you apply it in all cells in a row.

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.