EXCEL
Ms excel:-
Excel is an electronic spreadsheet program which is used to input, calculate and manipulate data , stored in form rows and columns ,generation of charts, perform mathematical ,logical, financial and text functions etc.
What is extension name of excel?
Dot XLS
How to open ms excel?
Go to start menu → all program → ms office → ms excel.
press window + r → type me excel→ ok
What is worksheet?
Worksheet is a collection of gridlines called horizontal rows and vertical columns .
What is workbook ?
A collection of one or more worksheets is called workbook.
How many columns or rows in one worksheet in MS Excel 2003?
Rows = 65536, Columns =256
How to go to last column & last row?
Last column: – ctrl + right arrow
Last row: – ctrl + down arrow
How can editing in a cell or see formula ?
Press F2
How can wrap text in a cell?
Alt + Enter
What is name box ?
It displays the address of cell at the top of sheet in left side.
What is cell?
Cell is intersection of row and column.
How to go to option use?
Ctrl + G, F5
How to go to on next sheet?
Ctrl + page down
How to go to on previous sheet?
Ctrl + page up
How to go next sheet on a fixed address?
Press f5 → type sheet + no sheet+ 1 key with shift & give address column → row → ok.
How to write a text in all cells?
Select all sheet → give text name → ctrl + enter.
How to change direction of enter key?
Go to tools menu →option → edit → select direction from move selection after enters → ok.
How to use custom list?
Select the list → go to tools menu→ option → custom list → import → add → ok→ write any name & draw it.
How to give color for gridlines?
Go to tools menu → option → view → click on gridlines color → choose color → ok.
How to hide gridlines?
Go to tools option → view → click on gridlines → ok
How to insert a new column?
Right click on mouse → insert → click on entire row & entire column → ok
How to insert a new worksheet?
Right click on sheet → insert → ok OR Shift + alt +f1
How to delete a sheet?
Right click on sheet → delete
How to rename a sheet?
Right click on sheet → rename → give any name → ok
How to copy a sheet?
Right click on sheet → move or copy → click on a create a copy → ok
How to select a single row & column?
Row: – shift + space
Column: – ctrl + space
How to prepare table for number?
Create a series row & column → keep the curser near no of 2 = select no2 from row → press → f4 for three times → again select no of 2 from column → press f4 for two times → enter → draw the number of row & column
How can transpose the table .
Select the table → copy → click another location → paste special → transpose → ok
How to create a series row & column?
Go to edit menu → fill → series → select row and column → give step value → give stop value → ok
How to insert date for row and column?
Give date → go to edit menu →fill → series → select row and column → click on date → select date unit → give step value → stop value → ok
How to insert header & footer in excel sheet?
Insert the text in excel sheet → go to view menu → header and footer → click on custom header → ok → click on custom footer → ok → click on print preview → ok
How to use comments?
Go to insert menu → give comment→ go to view menu → comment
What is custom view option?
Custom view option is by which we can give any name a text and see the text its name.
How to use custom view?
Insert the text → go to view menu → custom view → give name → ok
How to see custom view text?
Go to view menu → custom view → select view name → show
How to insert a new worksheet?
Right click on sheet → insert → ok OR shift + alt + f1, f4
How to prepare chart?
Select the table → go to insert menu → chart → select chart type → next → next → finish.
How to give color in chart?
Right click on chart → format → chart area → chose → ok
How to symbol?
Go to insert menu → symbol → select font style → select subject → chose symbol → ok
How to total by keyboard?
Alt + (+) key →enter →shift +down arrow → ctrl +d
To Make Relative reference formula: See the example which is given below.
Example:
|
Product |
Quantity |
Rate |
Total |
|
Close-Up |
10 |
25 |
250 |
|
Colegate |
15 |
30 |
450 |
In Relative reference we use the formula Cell* Cell
(QUANTITY* RATE).
To Make Absolute reference formula:
Example:
|
Product |
Quantity |
Rate |
Total |
|
Close-Up |
10 |
25 |
250 |
|
Colegate |
15 |
375 |
In Absolute reference we fixed the cell with Dollar Sign ($) for result (F4)
QUANTITY CELLS* RATE CELL ($Column $Row) F4.
To Make Mixed references formula:
Example:
|
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
|
1 |
|
|
|
|
|
|
|
|
|
|
|
2 |
|
|
|
|
|
|
|
|
|
|
|
3 |
|
|
|
|
|
|
|
|
|
|
|
4 |
|
|
|
|
|
|
|
|
|
|
|
5 |
|
|
|
|
|
|
|
|
|
|
Apply formula on blank cell: = above one* beside one.
Select the Table> Data (Menu Bar)> Table> Row input cell (Above one cell)> Column Input cell (Beside one cell)> Ok.
Financial Function
Loan Project
Principal value = 10000
Rate = 4%
Year = 4
How to calculate pmt?
= pmt → ctrl + a →
Select rate → press f4→ /12
N per (in select year) → press f4 → *12
PV in (press (–) key select principal value) → f4 →ok.
How to calculate PPMT?
= PPMT → ctrl + a
Rate: select rate → press f4 / 12
Per: select S.No 1st
N per: – select year → press f4 → type * 12
PV: – press (-) key →select principal value
How to calculate ipmt?
= ipmt → ctrl + a
Rate: – select rate → press f4 → / 12
Per: – select s.no 1st
N per: – select year → press f4 → type * 12
PV: – press (-) key →select principal value
Full form in financial function:-
PMT: – Per Month Total
PPMT: – Principal Per Month Total
IPMT: – Interest Per Month Total
PV: – Present Value
NPER: – Number of Period
FV: – Future Value
Logical Function
|
s.no |
Name |
Dept |
Grade |
House |
Basic |
Da |
Ta |
Hra |
Total |
|
1 |
A |
|
|
|
|
|
|
|
|
|
2 |
B |
Acct |
|
|
|
|
|
|
|
|
3 |
C |
Pur |
|
|
|
|
|
|
|
|
4 |
D |
|
|
|
|
|
|
|
|
|
5 |
E |
Acct |
|
|
|
|
|
|
|
|
6 |
f |
market |
|
|
|
|
|
|
|
How to calculate grade, house, basic, da, ta, hra, & total?
Grade: – type = if (select + dept in sale = “sale”,”senior”,”junior”) →enter
House: – type =if (and (select dept in sale =”sale”, again select grade =”senior”),”yes”,”no”) →enter
Basic: – type = if (and (select dept in sale =”sale”, again select grade =”senior”), 15000, 12000) →enter
Da: – type = if (and (select dept in sale =”sale”, again select grade =”senior”), select basic *10%, again select basic *6%) → enter
Ta: – type =if (and (select dept in sale =”sale”, again select grade =”senior”), select basic *9%, again select basic *7%) → enter
Hra: – type =if (and (select dept in sale =”sale”, again select =”senior”), select basic *9%, again select basic *7%) → enter
Full form in logical functions:-
S.No 1, 2,3,4,5, etc
Name A, B, C, D, E, etc
Dept
Grade Senior, Junior
House Yes, No
Basic Basic Salary Give
Da Dearness Allowances
Ta Traveling Allowances
Hra House Rent Allowances
Total Do the All Total
How to use number?
Select the column → go to format menu → cell → number → select category→ ok OR Ctrl +I
How to use text alignment?
Go to format menu → cell→ alignment → select the position → ok
What is wrap text?
Wrap text is command by which use can give text in a particular cell.
How to use wrap text?
Go to format menu → cell → alignment → click on wrap text → ok.
Enter text then Alt+Enter
How to use shrink to fit?
Press ctrl + 1→ alignment → shrink to fit → ok.
How to use merge cell?
Select the cell → go to format menu → cell → alignment → click on merge cell → ok.
How to use orientation?
Press ctrl + 1 → alignment → give text direction → ok.
How to use font?
Go to format menu → cell → font → give font style → give size → give color → ok.
How to use border / apply the border?
Press ctrl + 1 → border → choose patterns → chose alignment style → choose color → ok.
How to give password whole cell?
Go to tools menu → protection → protect sheet → give password two times → ok.
How to give password to a limit range?
Select the all sheet → format menu → cell → protection → lock → ok → again select the limit range → again go to format menu → cell protection → locked → ok → go to tools menu protection → protect sheet → give password two times → ok.
How to use unprotect sheet or password remove in sheet?
Go to tools menu → protection → unprotect sheet → give password → ok.
How to use auto format?
Select the row / column → go to format menu → auto format.
How to use conditional formatting?
Select any column → go to format menu → conditional formatting → give condition → font → give color → add→ give condition → again number → give color → ok.
How to create a new style?
Go to format menu → style name → modify → alignment → give text alignment → orientation degrees → go to font → give font style → size → color → ok → ok.
What is normal value of row / column?
Row (width) 12.75
Column (width) 8.43
What is goal seek?
Goal seek is an option by which we can change value of one cell.
How to use goal seek?
Type number→ total all →give cell value→ select a change cell → ok
How to use install solver?
Go to tools menu → add –in → solver add – in → ok → yes → again go tools menu → select target cell→ guess → add→ cell reference → constraint → ok → solver
How to use scenarios?
Select a single row → go to tools menu → scenarios →add → give scenarios name →ok→ add other for change the values → repeat the process for scenarios →ok→ click on scenarios show
There are two types of lookup function:-
Vlookup
Hlookup
How to use v lookup function?
Draw a table → copy the heading → paste it another location → go to data menu → validation → setting → list → source → = name column list → ok→ put in type =vlookup →ctrl +a
Lookup value: – select list cell
Table area: – all table select
Column & row: – 2/3
Range lookup: – false → ok
How to use hlookup?
Draw a table → select all table → copy of all table →click another location → paste special →transpose → again copy heading paste it other location → go to data menu → validation → setting → source= → ok → type =h lookup → ctrl +a
Lookup value: – select list cell
Table area: – select the all table (horizontal)
Column &row 2/3
Range lookup: – false → ok
How to set the direction of right to left of sheet?
Go to tools menu → option → international → click on default direction to left → click on view current sheet right to left → ok.
How to use sort option?
Go to data menu → sort → change position → ok
How to use auto filter?
Make a table → select it → go to data menu → filter → auto filter → see the report on your according
How to use advance filter?
Make a table → copy the heading → paste it → give the condition → select all previous table → go to data menu → filter → advance filter → select criteria range → click on copy to select any cell → ok
How to use form?
Create a table → select it → go to data menu → form → fill the name →criteria → new press your need → close
How to use subtotals?
Click in table → do the dept column ascending or descending order → go to data menu → subtotals → add each change in → dept → subtotal to → you want to total of those → choose formula → ok
How to use validation?
Select any column → go to data menu →validation → give condition with number →ok
How to use custom in validation?
How can we can put validation that column not accept a duplicate value ?
Select entire column(such as P) then we can put condition → validation → =COUNTIF(P:P,P24)=1
How to use consolidate?
Prepare the table for two month jan/feb → give the heading for month at march → select the blank cell → go to data menu → consolidate →select the value of Jan table in a reference → add → again → select the value of Feb. in reference → add → ok
How to create group &outline?
Select the range → go to data menu → group &outline →group →select row /column →ok
How to create pivot table /pivot chart report?
Select the table →go to data menu →next →finish →add to range the table.