ms excel notes

Add This To Del.icio.us Share on Facebook StumbleUpon This Add to Technorati Digg This

EXCEL

HOW OPEN EXCEL?

· Go to start

· Choose program

· Choose Microsoft excel

Or

Go to start

Choose run

And type excel in run box > ok

How to insert / add new work sheet?

By default we have 3 work sheet but if we want at that time we can add the new sheet also.

-Right click on any worksheet tab

-then choose insert

-Now choose worksheet option

-Then click on insert tab

-At last click on ok

To change color of work sheet tab

-Right click on work sheet

-Choose tab color

-Now choose any one color

-Ok

How to rename for the worksheet?

-Right click on the worksheet

-Choose rename

-Now give the name for that worksheet

Or

Format menu> sheet > rename

Or

Double click on sheet

Type name

How to copy a worksheet?

-Click on required sheet

-Drag it withholding ctrl key

-At last drop it at required place

Cell addressing

It is name of cell. For that we say/write first column and then second row.

For eg A5

How to define the name of cell/cells?

ü Select the cell or cells

ü Go to insert menu > name > define

ü Now type the required name

ü And click on ok

Navigation through worksheet

v Edit menu > go to option > give the reference name of cell / cells > ok

Or

Direct press ctrl + g on key board and > give name > ok

Or

Double click on address bar

Then give the name of cell/cells

In this way we can immediate move anywhere from current place

Note :

1> to move last row :- ctrl + down arrow

2> to move first row:- ctrl + up arrow

3> to move last column:- ctrl + right arrow

4> to move first column :- ctrl + left arrow

How to insert the row?

Select any row > insert menu > row

Or

Select any row > right click there > choose insert

How delete row?

Select the row > edit menu > delete

Or

Select the row > right click > delete

How to insert new column?

Select required column > insert menu > column

Or

Select required column > right click > insert

How to make series?

v At first give the 1^st and 2^nd no in two cell in required series ( eg 1 , 2 or 1 , 3 etc)

v Select both cells

v Put cursor on the bottom corner of right side

v Then drag it up to your requirement

To insert series:-

By using this option we can insert the required series by computer.

v Write any one number in a cell (eg 5 )

v Go to edit menu

v Choose fill option

v Choose series

v Now define the series in row or column

v Then define a type :

o A> linear , it gives series in 'sum' basis

o B> growth , it gives series in 'multiple' basis

v Now give step value eg 3

v And define the stop value(max value) eg 100

v At last click on ok

How to edit the wrong entry into the cells?

Double click in that cell and edit it

Next method: go to that cell and press f2 key then edit the entry

Merge and center tool:

We can see this tool in the formatting toolbar of excel. It is used to merged more than 2 cells into one cell whenever necessary.

Process:

v Select the required cells

v Click on merge and center tool of formatting toolbar.

Process to insert the current date in cell:-

-press ctrl + ;

Process to insert the current time in cell:-

-press ctrl+shift+;

Hiding all content

Select all > format > choose cell > number tab > custom > type ';;;' > ok

Show the hiding content

Select all >Format menu > choose cell > number tab > custom > type '000' > ok

Auto format:-

Using this we can format the table with the prebuilt table format.

a) Select the required cells

b) go to format menu.

c) Click on auto format

d) Select the format and click on ok.

Merge and center tool:

We can see this tool in the formatting toolbar of excel. It is used to merged more than 2 cells into one cell whenever necessary.

Process:

v Select the required cells

v Click on merge and center tool of formatting toolbar.

How to write more than 1 line into the merged cell?

Normally the sentences appears in to one line at merged cell but if necessary to write multiple line there follow the following process:-

1. at first merge the required cells and write the required text normally
2. go to format menu > select cells
3. click on alignment tab
4. select center for horizontal and vertical box
5. check on wrap text and merge cells both check box.
6. and select context to text direction
7. at last ok
8. now fixed the cursor there from where you want to break line.
9. and maintain it by giving required spaces.

Working with function

1> AGGREGATE FUNCTION

there are pre built function available in excel.

a> sum

b> min

c> max

d> average

e> count

a> SUM

using this function we can get the total sum result of given values. By two ways we can calculate the sum.

i> cell by cell

=cell1+cell2+cell_n

=sum(cell1,cell2,……cell_n)

ii> range of cell

=sum(cell1:cell_n)

Sum function with criteria

=sumif(cell1:celln,give the criteria)

Eg sumif(a5:a10,15)

b>MIN

Using this function we can find the minimum value in the specified cells. By two ways we can use this function.

i> cell by cell

=min(cell1,cell2,……celln)

ii> range of cell

=min(cell1:celln)

c>MAX

to find out the maximum value we use this function.

i> cell by cell

=max(cell1,cell2,……celln)

ii> range of cell

=max(cell1:celln)

d>AVERAGE

We use this function, whenever is necessary to find out the average value among the given values.

i>cell by cell

=average(cell1,cell2,……celln)

ii> range of cell

=average(cell1:celln)

e>COUNT

To get the total number of given value this function is used.

i> cell by cell

=count(cell1,cell2,……celln)

ii> range of cell

=count(cell1:celln)

Count with criteria

If the requirementss is to count any specified value only among the given value. Then we use count function with criteria.

=countif(cell1:celln,give required criteria)

Example

For the case of number

=countif(a5:a10,12)

For the case of text

=countif(a5:a10,"shari")

Salary sheet

Monthly salary EPF net salary

Where EPF = 10% of monthly salary

Net salary = monthly salary - EPF

price list

here ,

VAT = 13% of purchase price

cost price = purchase price + VAT

profit / loss = sales price – cost price

LOGICAL FUNCTION

Her we can logically test the different kind of condition. We have 3 types of condition as follows:-

a> condition 1 – 'if'

b> condition 2 – 'if and'

c> condition 3 – 'if or'

a> if condition

here we can check only one condition among the given conditions. To get the true result that condition should be satisfy otherwise result will be false.

Formula

=if(condition,"true result","false result")

Example:

We have a data of many actors then we want to define that they are actor or actress according to their gender. Here we can use 'if condition'

=if(gender="male","actor","actress")

Or

=if(gender<>"female","actor","actress")

Note:

Here gender means that cell's name where we have defined the gender. Eg e5

To find balance amount

=if(withdraw>0,balance-witthdraw,if(deposite>0,balance+deposite-withdraw))

B> "IF AND" CONDITION

In this condition we can check the multiple (2 or more than 2) conditions. Where to get true result all the conditions should be satisfied among the given conditions otherwise it gives false result.

Formula:

=If(and(condition1,condition2,……condition _n),"true result","false result")

Example

To pass the any exam we should obtain the minimum pass mark in all subjects , other wise the result will be fail. in such type of case we can use the if and condition.

Like this

=if(and(nepali>=35,English>=35,math>=35,science>=35),"pass","fail")

Note:

Here the name of subject means that cell's address where is the marks of defined subject.

Eg k4

C> "if or" condition

In this function also we can check the multiple conditions alternatively. Here to get true result minimum one condition is to be sufficient among the given conditions.

means if all conditions are not satisfied at that time only it gives the false result.

Formula:

=if(or(condition1,condition2,……condition _n),"true result","false result")

Example

Bhairahawa multiple college makes to give bus facility for its student who lives in to following places: lumbini, butwal, bank road.

In this case we can use if or condition

=if(or(address="lumbini", address="butwal", address="bank road"),"yes","no")

Here the address means name of that cell's where we have defined the address.

How to make a complete mark ledger?

How to find total marks?

Use sum function

Or =sum(nepali:English)

How to find result

Use if and condition

=if(and(nepali>=35,math>=35,science>=35,English>=35),"pass","fail")

How find percent

=if(result="pass", obtain marks/full marks*100,"no")

But if you want to round up the decimal places then;

=if(and(result="pass"),round(obtain marks/full marks*100,2),"no")

No of decimal place.

To find out division

Use if and condition

=if(and(%>=80,%<>"no"),"distinction",if(and(%<80,%>=60),"first",if(and(%<60,%>=45),"second",if(and(%<45,%>=35),"third","no"))))

Note:

Here the % means name of that cell where is defined the %

Eg d3

To give remarks

=if(division="distinction","excellent keep it up",if(division="first","well done", if(division="second","good try go ahead", if(division="third","plz be studies","try again))))

Note:

Here division means name of that cell where is defined the division.

Conditional formatting

How to give different colour for pass and fail

- select result column

- go to format menu

- choose conditional formatting

- give information:- cell value is equal to pass

- now click on format tab

- then click on patterns tab

- now choose one color

- then click on ok

now for fail

-click on add>> tab

Other process same as above

To see top three

If you want to see top Student according to obtain marks

- select the column of total

- go to data menu

- select filter

- then select auto filter

- now a small arrow will appears on your document

- now click on that arrow

- choose sort descending

- now again clink on that arrow

- then choose top ten option

- next give the necessary info on dialog box eg top 3 items

- at last click on ok

Vlookup

How to look the required information according to sn.?

>select the mark ledger

>define the name for that mark ledger (insert menu > name > define)

>then go to next place where do you want to look the required information.

>make a format as per your requirement

>give formula:-

=vlookup(cell no of sn,table name,column no of required field)

• Draw table:-

• Click on the draw border tools in the formatting tools.

• Click on draw border tools.

• Now, draw the table, give the boarder color, size etc.

• Then draw grid draws the line in all the cells border.

Sub total

By using this formula we can find out the total value of same items

• Select the item name
• Then sort them on ascending basis
• Check on expand the selection
• Then click on sort
• Now select all table
• Then go to data menu
• Choose subtotal
• Give item name on at each change box
• Then give sum on use function box
• Next qty on add sub total box
• Then check on : replace current subtotal

: Page break between groups

: Summary below data

At last click on ok

Make graph

In excel we can make the graph for our document when we have need

1. make a data
2. select all data
3. go to insert menu and choose chart (or direct click on chart wizard tool of standard toolbar)
4. now choose a style of chart eg column
5. then choose chart type
6. click on next
7. chose rows or column on series in option
8. again click on next
9. then a chart wizard box will open where you can see the different tabs click on any tab and give required information
10. then click on next
11. then click on finish

Age

=int((now()-date of birth)/365)

remarks

=IF(age<=12,"child",IF(age<>0,IF(age<=19,"teenage",IF(age<=45,"young",IF(age<=65,"adult",IF(age>=66,"old")))))

Find out the allowance with following conditions:-

-if age is more than or equal to 60 years, assume widow and give allowance of rs 150

- if age is more than or equal to 76 years, assume old give allowance of rs 200

here ,

- to find the age use =INT((NOW()-DOB)/365)

- to find the rank =IF(AND(AGE>=60,AGE<=75),"widow",IF(AGE>=76,"old","no"))

- to find the allowacnce amount =IF(RANK="old",200,IF(RANK="widow",150,"no"))

For Further Reading,

Computer Basic Notes