Ms Excel

 Ms-Excel is a Software and is a part of Ms-Office. It is used for calculation and logical operation. In excel we make Data entry, calculation for Block and panchyet label even Banking system, Like- Government may be privet label.

How to Start Microsoft Excel?

1. Click on Start button.
2. Click on All program.
3. Click on Microsoft office.
4. Click on Microsoft Excel.

What we find in Ms Excel environment?

1)      Title bar.(Book 1-Microsoft Excel)

2)      Menu bar(File symbol ,Home , Insert, Page Layout)

3)      Submenu (Symbols Under Menu bar)

4)      Formula bar (White area under sub menu)

5)      Name box (Left side of Formula bar)

6)      Spread sheet (Total working area)

7)      Sheet button (Sheet1, sheet2, sheet3)

8)      Scroll bar (Up page up down symbol)

What is Word Book ?

When you open Ms-Excel program then will be find minimize and maximize Close(x) symbol appears under Right corner side the title bar, there if you click on restore button then will be found a deferent page came down in the screen it is called Word book.

What is Spread Sheet ?

The spread sheet is the area of  Ms-Excel in which all the works are done. It is an Electronic Worksheet or Electronic spread sheet. It is made with 16384 Columns. (A,B,C.....Z,AA,AB....AZ,BA,BB.....BZ,-XFD)

and 1048576  rows (1,2,3-1048576) in a spread sheet. Ms-Excel is also known as spread sheet analysis program.

What is Cell?

The rectangular intermediate region between a column and a row is known as a cell.

So there are total cell 16384 * 1048576=17179869184

What is Cell Pointer?

When we open Excel program then we find a rectangular box always remain highlighted in the spread sheet. This highlighted box is called cell pointer. If you wish to enter data in cell then the cell pointer move to that particular cell.

Formula Bar:

Formula bar shows the  contents of the selected cell. If any result is derived by a formula or function  then the formula bar shows the formula which was use to find that result in the particular Cell.

How to move in Excel spread sheet?

To input or edit data in Excel Spreadsheet we need to move from one cell to another cell. With the help of the following commands we can easily move to desired cell.

            Key                                         Result

1)         ®                                            1)To move one cell Right.

2)         ¬                                            2)To move one cell  Left.

3)         ­                                              3)To move one row Up.

4)         ¯                                              4)To move one row Down.

5)         Ctrl+®                                    5)To move to the right most column of a filled data area.

6)         Ctrl+¬                                    6)To move to the Left most column of a filled data area.

7)         Ctrl+­                                     7)To move to the top most row of a field data area.

8)         Ctrl+¯                                     8)To move to the left most column of  the current row .

9)         Home                                      9) To move to the left most column of the topmost row.

10)       End                                         10)To set the end mode.                                            

11)       Ctrl+Home                              11)To move to the left most column of the topmost row

12)       Ctrl+End                                 12)To move to the last filled cell of the spreadsheet.

13)       Page Up                                  13To  move one screen Up.

14)       Page Down                             14)To move one screen Down.

15)       Ctrl+Page Down                     15)To move one screen Right.

16)       Ctrl+Page Up                          16To move one screen Left.

How to move one Worksheet to another Worksheet?

Two system of move one worksheet another sheet

      a)Click on Sheet button.(Like Sheet1,Sheet2,Sheet3)

      b) Press Ctrl+Page Up Key /Press Ctrl+Page Down Key.

Type of Data:

There are two types of data that we can insert in the worksheet:

1)      Number :Data like all the numbers created on the basis of the digits 0 to 9, decimals,#,%,+,etc.fallow under this category.

2)      Text: All the word and sentence composed by character as well as alphabets from A to  Z fallow under the category.

3)       

Formula:

All the formula used for calculation based on math starts with ‘=’(is equal symbol).For Example, to add the number of the cell C1+C2+C3,in formula would be written as =C1+C2+C3.

Changing Cell Width:

1)      Place the mouse pointer between two column headings.

                        Immediately , it will be turned to a double arrow shape.

2)      Press the left mouse button and drag up to the satisfactory width.

                                    Or

1)      Place the cell pointer at any cell of the column whose width you want to change.

2)      Click on Home Menu.

3)      Click on Format option(in right side)

4)      Click on column width option.

5)      Set the column width option.

6)      Click on Ok.

Changing the Height of Row:

1)      Place the mouse pointer between two Row headings.

            Immediately ,it will be turned to a double arrow shape i.e. resize  arrow.

     2) Press the left mouse button and drag up to the satisfactory height.

                                    Or

1)  Place the cell pointer at any cell in the row whose height you want to change.

2)  Click on Home menu.

3)  Click on format option.

4)  Row Height option.

5)  Set the number for Row Height.

6)  Click on Ok.

Find the Total through Auto sum option:

1)Select the all the cells in a particular column or a row to add.

2)Click on ‘Auto Sum’ button    å   in the standard Toolbar.

            Immediately , the result of the summation will appears at the end  of the selected cells.

 

Formula:-

1)SUM().

With the function you can find of some numbers. Suppose ,we want to find the total of numbers of the cells

 of B1,B2,B3,B4,B5 and B6.Now ,place the cell pointer in that cell where you want to find the result. Now Type –

                        =SUM(B1:B6) after press Enter key.

We can also find the result of summation by =B1+B2+B3+B4+B5+B6.

2)AVERAGE( ).

With this function used to find the average of some numbers.

EXAMPLE:-=AVERAGE(B1:B6)

3)MAX( ).

This function is use to find the maximum or greatest number among few numbers in a range.

EXAMPLE: =MAX(B1:B2)

4)MIN( ).

This function is use to find the minimum or smallest number among few numbers in a range.

EXAMPLE: =MIN(B1:B2).

5)COUNT( ).

With this function we can find how many numbers are there in a range.

EXAMPLE: =COUNT(B1:B6).

6)INT(x).

Here ‘x’ is a number with decimal part i.e. it is a number with fractional part.

Example: =INT(25.654) equals to 25.

7)DAY( ).

This function is used to find the day in a particular date.

=DAY(“08/22/2014”)

8)MONTH( ).

    This function is used find the month in a particular date

=MONTH(“12/04/2014”)

9)YEAR ( ).

This function is used to find the tear in a the year in a particular date.

=YEAR(“05/04/2014”)

10)LEFT( ).

This function is used to return a particular number of characters from the left  hand side of a text .

EXAMPLE:- = LEFT(“SUBASH”,3) Returns the value ‘SUB’.

11)RIGHT( ).

This function is used to return  a particular number of characters from the  Right  hand side of a text .

EXAMPLE:- = Right(“SUBASH”,3) Returns the value ‘HAS’.

12)MID( ).

MID returns a specific number of characters from a text starting at the position you specify, based on the number of characters you specify.

=MID(“SUBASH BOSH”,1,4).

13)LEN( ).

LEN returns the number of character in the a text string.

 =LEN(“ MELAGHARSBS”).

14)UPPER ( ).

Converts text to  Uppercase .

Text is the text you want converted to uppercase. Text can be a reference or text string.

Example:-  =UPPER(“training”)

 

15)LOWER ( ).

Converts text to  Lower case .

Text is the text you want converted to Lowercase. Text can be a reference or text string.

Example:-  =LOWER(“MELAGHAR”)

16)SIGN( ).

This function is used to find the positive or negative of a number. If a number has positive sign or no sign

 then it is a positive number and if a number has negative sign then it is a negative number.

Example:-

            =SIGN(53.5)

            =SIGN(-55)

17)Calculation operators in Formula:

Operators specify the type of  calculation that you want to perform on the elements of a formula. Microsoft Excel includes four different types of calculation operators, arithmetic, comparison, text and reference.

a)Arithmetic Operator: To types basis mathematical operation such as addition , subtraction, or multiplication, combine numbers and produce numeric result , use the following arithmetic operators  

Arithmetic operators               Meaning                      Example

+ (plus sign)                            Addition                     =5+5

-(minus sign)                           Subtraction                  =5-3

                                                Negation                     =3-8

(asterisk)                                  Multiplication              =5*5

/(forward slash)                       Division                       =6/3

%(percent sign)                       Percent                                    =5%*500

b)Comparison Operators: You can compare two values with the followings operators-

Comparison operators :                       Meaning                      Example

 = (equal)                                             Equal to                       =a1=b1

c)Text concatenation operation:  Use the ampersand ( &) to join, or concatenate, one or  more text strings

To produce a single place of text.

Example:- Ram krishna  to Ram & Krishna.

d)Reference operators :Combine ranges  of cells for calculations with the following operators .

Example:- A1:A5 (Like- Sum, average)

f)Comma ,

Example:- A1:a5,B1:B5

 

How to Hide two or more Cell?

1. Select two more cell.
2. Right click on the selected cell.
3. Click on Format cells..
4. Click on Alignment option.
5. Click on Merge cells.
6. Click on ok

 

How make hide to unhide  two more Cell?

1. Right click on Hide cell.
2. Click on format cells
3. Click on alignment option.
4. Click on shrink to fit
5. Bring out tick mark of Merge cells.
6. Click on ok.

If function.

 It is use such as condition when you remain unsure, means may be or not  may be.

Example:-

  =IF(A10<=100,”SBS”,”MELAGHAR”)

                        OR

=IF(A10=100,SUM(B1:B5),”Not”)

                        OR

=IF(C6>=70,”CPU”,”MONITOR”)

                        OR

=IF(COUNT(B1:F15)=20,”OK”,”NOT”)

OR

=IF(SUM(C1:G2)>200,MAX(C1:G2),”NOT”)

                       

How to make a Mark sheet design?

At first you create one more cells hide (Merge) for type your school name.

Follow the system.

	Tripura Board of secondary education
SL	NAME	BENG	ENGL	MATH	TOTAL	AVERAGE	DIVISION
1	RABI	45	85	55	=sum(D4:F4)	=Average(D4:F4)

Divission =IF(B6>=60, ”1^ST  division” ,(if(B6>=45, ”2^nd division” ,(if(B6>=30, ”pass” , ”fail”)))))

How to create a  Graph?

1. Select the data range that you want to represent through the graph.
2. Insert Menu.
3. Click on Column button or any chart style.
4. Chose any column style.

 

How to insert Worksheet?

1. Click on Home Menu.
2. Click on Insert option.
3. Click on insert Sheet button.

How to change the name of Worksheet?

1. Right click on the sheet button.
2. Click on Rename option.
3. Type the name for the sheet.

 

How to delete Worksheet?

1. Right click on the worksheet.
2. Click on delete option

How to break a Page?

We can break a page vertically or Horizontally. Follow the steps:

1. Select the last column heading in your spread sheet.
2. Click on Page Layout Menu.
3. Click on Breaks option.
4. Click on Insert Page Break. 

 

How to Insert a Comment?

1. Select a name.
2. Click on Review Menu.
3. Click on New comment.
4. Type the text in the comment box
5. Place the cursor outside of comment box.

 

How to Edit Comment?

1. Select the comment which you want edit text.
2. Click on Review Menu.
3. Click on Edit comment option.
4. Type any thing in comment box.

 

How to Delete Comment?

1. Select the comment which you want to delete.
2. Click on Review Menu.
3. Click on Delete option.

 

How to Short Data?

1. Select the data in a column.
2. Click on Home Menu.
3. Click on ‘Sort Filter’ button.(In the Right side)
4. Click on Sort Smallest to Largest option .              

   Or

      5.   Click on Sort Largest to Smallest option.

 

How to Protect Sheet?

After type in the spreadsheet.

1. Click on Review Menu.
2. Click on protect Sheet.
3. Type the password  in password to unprotect sheet.
4. Click on ok.
5. Type the Reenter password to proceed.
6. Click on ok option.

 

How to Insert Row in the Spreadsheet?

1. Place the cell pointer in the row which place you want to insert row.
2. Click on Home Menu.
3. Click on insert option.(In the right side)
4. Click on insert Sheet Rows.

 

How to insert Column in the Spreadsheet?

1. Place the cell pointer in the column which place you want to insert column.
2. Click on Home Menu.
3. Click on insert option.(In the right side)
4. Click on insert Sheet Columns.

 

How to use Filter?

1. Select the number in the column.
2. Click on Data menu.
3. Click on Filter option.

                Immediately a black button appears in the column heading button.

4. Click on this button.
5. Click on number Filters option.
6. Click on Custom Filter
7. Set is greater then or is less than in the left side box.
8. Set the number in the right side box which number you want to compare.
9. Click on Ok.

How to show column and row in the Print Preview.

After typing

1. Select the cell which you want show in the print preview.
2. Right click on the selected cell.
3. Click on Format Cell.
4. Click on Border  option.
5. Click on Out line and inside button.
6. Click on Ok.
7. Click on File symbol.
8. Click on Print preview of print option.

            Then show the column and row in page.

 

How to  use Wrap text?

Some times we find when a word or text typed then the word arrived one cell to another cell, if you want take the word one cell, so follow the steps-

1. Right click on the Word or text.
2. Click on Format cell.
3. Click on Alignment option.
4. Click on Wrap text option.
5. Click on Ok.

How to use Page Range ?

1. Select one more cell which you want to create Page Range.
2. Right click on the selected cell.
3. Click on Name a Range..
4. Type the name for the Page Range.
5. Click on Ok.
6. Click on name box button .
7. Chose the name that you typed behind.

Immediately you will be find selected area appears in the spread sheet.

 

How to create text Orientation?

1. Select the text.
2. Right click on the text.
3. Click on Format cells.
4. Click on Alignment option.
5. Set the Orientation pointer in right side.
6. Click on Ok.

 

How to send a picture behind column and row border area?

1. Click on Page layout Menu.
2. Click on Back ground option.
3. Click on My Computer option.
4. Click on picture drive option.
5. Chose a picture.

How to create Data entry in Excel?

Data entry have varieties system may be office label or privet label but its procedure same system.

Like Panchyet label :-

		Chandrapur Grampanchyet
Sl	Name	M/F	Word No	House No	Ration Card No	Apl/Bpl	Age	Qualification	Occupation
1	Subir	M	03	77	55	APL	25	Madhaymik	Business
2	Sumitra	F	01	55	78	BPL	19	H.S(+2 Stage)	Student

Like- Market label :-

		Loknath Electrics Enterprise
Sl	Name	Date	Item	Quantity	Per Rate	Total	Clear	Balance
1	Suman	1/4/2014	T.V	5	5000	=5000X5	20000	=25000-20000
2	Kings	2/4/2014	C.P.U	5	15000	=15000X5	50000	=75000-50000

Click Here For:>> Download