MS Office Excel

Ø  The extension name of MS office excel:  .xlsx

Ø  to go to last column:     ctrl+→

Ø  to come back first column:    ctrl+← 

Ø   to go to last row:    Ctrl+↓

Ø  to come back first row:    Ctrl+↑

Ø  how many rows and column in a sheet?                         

Rows:    1048576

Column:    16384(XFD)

Row/column adjustment: double click when mouse curser will convert to below sign

Ø  Merge and center: select your cells→go to home tab→ click merge and center

Total: SUM(select first cell address to last cell address) ↵

Average: AVERAGE(select first cell address to last cell address) ↵

Max: MAX(select total number) ↵

Min: MIN(select total number) ↵

Ø  Wrap text: select your cell→go to home tab →click wrap text

Ø  File menu: info, new, open, save, save as, print, share, export, close, account, options

 Paste link:

select you data→copy→select cell→go to home tab→click paste option→select paste link

Redo/Undo: redo: Ctrl+Z;      undo: ctrl+Y(it will not work while editing cell,to editing cell double click on cell, otherwise cell data will replaced)

Ø  Orientation: select cell →go to home tab→ click orientation→select any orientation

Border: Select Your Table→ Go To Home Tab→ Click border → select any Border Style

Ø  How to format cell alignment?             Select cell  →go to home tab→click conditional formatting → new rule →click format only cells that content→Cell Value→write condition(between 60-90)→ format→select font style and color→OK→OK

Ø  Format as table: select your data table→go to home tab→click format as table→select any table style→OK

Ø  Cell  styles: select cell→go to home tab→click cell styles→select any cell style

Ø  How to create own cell style?                    Go to home tab →click cell styles→ new cell style→write style name→format →provide instruction→OK→OK

Ø  How to insert cell?                 Go to home tab →click insert →insert cells…

Ø  How to insert row/column?                      Go to home tab →click insert →insert sheet  rows…/ insert sheet columns…

Ø  How to insert sheet?                 Go to home tab →click insert →insert sheet…

Ø  How to rename sheet?                           Select sheet→Go to home tab→click  format→rename sheet→write sheet name

Ø  tab color:  select sheet →go to home tab→click format→tab color→select any color

 

Ø  series: at first write a number in a cell→go to home tab→click fill→series→write step value→write stop value→select row/column→OK

at first write week day name→write date of day→select the date →go to home tab→fill→series→write step value(1/7)→write stop value(28/30/31)→select row/ column→OK

Ø  How to delete cell?                              Select cell→go to home tab→click delete under cells group→delete cell→OK

Ø  How to delete row /column?                 Select row/column→go to home tab→click delete→ click delete sheet rows/delete sheet columns

Ø  How to delete sheet?                 Select sheet→go to home tab→click delete→

  click delete sheet rows/delete sheet columns

Ø  IF function: =IF(logic test," true value"," false value")

Ø  AND function: =IF(AND(logic1,logic2,logic3,……………..)," true value"," false value")

Ø  OR function: =IF(OR(logic1,logic2,logic3,……………..),” true value"," false value")

Ø  Pass/fail:

=IF(click total>=272,"pass","fail" ) ↵

Total: =SUM(select first subject to 8th subject) ↵

Average: =click total number/total sub(8) ↵

                Division: =IF(MIN(select first subject to 8th subject)<34,"Fail",IF(click total>=600,"star",IF(click total>=480,"1^st ",IF(click total>=360,"2^nd ",IF(click total>=272,"3^rd ","Fail"))))) ↵

                Grade: =IF(MIN(select first subject to 8th subject)<34,"D",IF(click average>=90,"AA",IF(click average>=80,"A+",IF(click average>=70,"A", IF(click average>=60,"B+" IF(click average>=45,"B", IF(click average>=34,"C","D" ))))))) ↵

Pass/Fail:=IF(MIN(select first subject to 8th subject)<34,"Fail","Pass")↵

Ø  Leap year or not: =IF(MOD(C2,400)=0,"leap year",IF(MOD(C2,100)=0,"not leap year",IF(MOD(C2,4)=0," leap year","not leap yaer")))

Ø  Insert picture: go to  insert tab →click picture→select any picture from file→insert

Ø  Chart:

Select your table→go to insert tab→select any chart style under chart group

 

Ø  Insert word-art: go to insert tab →click insert word-art→select any word art style→write your text

Ø  Hyper-link: go to insert tab →click hyper link→select any file→ok

 

Salary Sheet:

Part-1

Ø  DA: =Click basic*18%↵

Ø  TA: =Click basic*15%↵

Ø  HRA: =Click basic*10%↵

Ø  Gross: =Click basic+DA+TA+HRA↵

Ø  PF: =Click basic*2%↵

Ø  Tax: =Click basic*1%↵

Ø Net Salary: =Click gross-PF-Tax↵

Part-2

Ø  calculate DA 21% of basic whose basic more than 8000, others 18%: =IF(click basic>8000,click basic*21%,click basic*18%)↵

Ø  calculate TA 19% of basic whose address azimganj, others 15%: =IF(click address=”azimganj”, click basic*19%,click basic*15%)↵

Ø  calculate HRA 15% of basic whose department ACC, others 10%: =IF(click department=”ACC”,click basic*15%,click basic*10%)↵

Ø  calculate gross: =click basic+DA+TA+HRA↵

Ø  calculate PF 1% of gross whose basic less than 5000, others 2%: =IF(click basic<5000,click gross*1%,click gross*2%)↵

Ø  calculate tax 3% of gross whose gross more than 12000, others 2%: =IF(click gross>12000,click gross*3%,click gross*2%)↵

Ø Net Salary: =Click gross-PF-Tax↵

Part-3

Ø  calculate DA 21% of basic whose department ACC or address azimganj, others18%: =IF(OR(click address="azimganj",click department="ACC"),click basic*21%,click basic*18%)↵

Ø  calculate TA 19% of basic whose department ACC and DA>2000, others 15%: =IF(AND(click department="ACC",click DA>2000),click basic*19%,click basic*15%)↵

Ø  calculate HRA 15% of basic whose department ACC and address azimganj, others 10%: =IF(AND(click department="ACC",click address="azimganj"),click basic*15%,click basic*10%)↵

Ø  calculate gross: =basic+DA+TA+HRA↵

Ø  calculate PF 1% of gross whose basic less than 5000 and E-ID 1003, others 2%: =IF(AND(click basic<5000,click E_ID="1003"),click gross*1%,click gross*2%)↵

Ø  calculate tax 3% of gross whose gross more than 12000 and department ACC, others 2%: =IF(AND(click gross>12000,click department="ACC"),click gross*3%,click gross*2%)↵

Ø Net Salary: =Click gross-PF-Tax↵

Ø  Filter: select column heading→go to home tab→click short & filter→filter

Ø  sort: select your data→go to home tab→click sort & filter→ select short A to Z/ Z to A

Ø  how to protect your sheet? Go to review tab→protect sheet→write your password→reenter password to protect→OK

Ø  How to remove password? Go to review tab→unprotect→write your password→enter password →OK

 

Ø 

Ø  Trans pose(row to column and column to row):

select you data→copy→select cell→go to home tab→click paste option→select trans pose

Ø  Advanced filter:

 

Go to data tab→advanced→select list range→select criteria range→click copy to another location→select list range, criteria range,copy to→OK

Get External Data: at first fill up data in notepad, separate by tab→ save→open excel→ go to data tab→ click on get external data→ from text→select saved text document→ click delimited→ next→ check tab→ next→ finish→ OK

Ø  Goal seek:

Total Amount: =click labour * click payment

go to data tab→click what if analysis→select goal seek→select set cell (total amount)→write to value→select changing cell(labour/payment)→OK→OK

Ø  Validation rule: at first select your cell→go to data tab→click data validation→click settings→click allow drop down box→select date→write start date, end  date→ click input→write title& message→click error alert→write title & message

 

Ø  Consolidate:

Total: =click quantity * click rate

at first create this table with different data in sheet1 and sheet2→go to sheet3→go to data tab→click consolidate→Function:Average→ click reference box→go to sheet1 and select range with heading→add→go to sheet2→select table range with heading→add→go to sheet2 →select table range with heading→add→go to sheet3→check top row and left column→OK

Ø  freeze panes: go to view tab→click freeze panes→select freeze top row/ left column(first column)

Ø  Operators:

1.       Operators are symbols

2.       Four types of operator in MS Excel

i.                     Arithmetic operator:      +,-,*,/,%,^

ii.                   Comparison operator: =,>,<,>=,<=,<>(not equal to)

iii.                 Reference operator: <space>,<coma>

iv.                 Text operator: " " (double quotation)

Ø  Cell reference: two types of cell reference: 1)relative    2)absolute (absolute are three types

           i. Absolute Cell, ii.Absolute Row, iii.Absolute Column)

i.                     Relative: =A1*B1↵

ii.                   Absolute Cell:

=click absolute cell(100)→press F4 one time→ *→ click changing cell→↵

         iii.            Absolute Row:    B$1

               iv.             Absolute Column:    $A2

            Example:

=click column wise→click F4 three times→*→ click row wise→click F4 two times↵

Ø  function:

i.                     logical: IF,AND,OR

ii.                   text functions:

a.       =CHAR(65) ↵ → A

b.       =CODE("A") ↵→65

c.       =MID("computer",4,3) ↵→put

d.       =LEFT("computer",3) ↵→com

e.       =RIGHT(("computer",5) ↵→puter

f.        =REPT("Bappa ",3) ↵→Bappa Bappa Bappa

g.       =LEN("ram babu") ↵→8

h.       =CONCATENATE("ram"," ","babu") ↵→ram babu

i.         =TRIM("            ram babu           ")↵→ram babu

j.         =UPPER("ram") ↵→RAM

k.       =LOWER("RAM") ↵→ram

l.         =PROPER("ram babu") ↵→Ram Babu

...more

iii.                 Mathematical functions:

a.       =ABS(-6)↵→  6

b.       =MOD(15,2)↵→  1

c.       =SQRT(81)↵→ 9

d.       =INT(125.63)↵→ 125

e.       =ROUND(127.652,2)↵→ 127.65

f.        =ODD(22)↵→ 23

g.       =EVEN(23)↵→ 24

h.       =MAX(43,4)↵→ 43

i.         =MIN(3,5,43,44,45,46,47,30,34)↵→ 3

j.         =SUMIF(B2:B10,"ACC",C2:C10)↵

k.       =COUNTIF(B2:B10,"ACC")↵

...more

iv.                 Date and time function:

a.       =TODAY()↵

b.       =NOW()↵

c.       =YEAR(TODAY())↵

d.       =MONTH(“9/13/2018)↵

e.       =DAY("9/13/2018")↵

f.        =WEEKDAY(TODAY())↵

g.       =DATE(2011,9,3)↵

h.       =WEEKNUM("9/13/2018")↵

i.         =TIME(20,5,55)↵

j.         =HOUR(NOW())↵

k.       =MINUTE(NOW())↵

l.         =SECOND(NOW())↵

m.     =DAYS360("9/6/2011","10/6/2011")↵

                            ...more