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.
Post a Comment
0 Comments