What is MsExcel?
Ms Excel is an Accounting software based on electronic
spreadsheet developed by Microsoft it is also known as excel in excel we can
store, manipulate, share and protect data as form of row and column.
Element of Excel
a.
Title bar – This area found top most side of
window in which Excel file name and some extra button are found such as
minimize, maximize, close etc.
b.
Minimize → by this option we can hide the opened
window in the task bar.
c.
Maximize/Restore Down → by this option we can
decrease or increase the window size on the screen.
d.
Close =alt+F4 → By this option we can current
opened window
e.
Name Box → In this area we can check the current selected
cell address.
f.
Formula Bar → when we write any content in any
cell the all document can be seen in the formula bar.
g.
Column Header → There are 16384 column found are in one
excel sheet these are as a form or a,b,c,d etc in the worksheet.
h.
Row header → The number series such as 1,2,3,4,5 are call
row header there are 1048576 row in one excel sheet.
i.
Cell → the one single box in the worksheet is
called cell there made from gridline
j.
Grid line → the lines which is form as row and
column is call gridline
k.
Worksheet → The area where we write our content is
called worksheet.
l.
Workbook → The Group or all worksheet is called
workbook.
Home Tab
1.
Clip board Group
a.
Cut → By this tool we can change any selected
cell position.
b.
Copy → By this option we can make duplicate for
any selected cell or an object
c.
Paste → This option display out the copied or
cut object or cell.
d.
Format Painter→ by this option we copy any cell
format such as(Color, Size, Style Etc.) and apply them at another cell.
2.
Font Group
a.
Font → By
this option we can change writing style.
b.
Font size → By this option
we can grow or shrink the font size.
c.
Increase font → this option grow the font size
for the selected cell.
d.
Decrease Font → this option shrink the font size
for the selected cell.
e.
Bold →
This option make thicker text for the selected cell’s text.
f.
Italic →
This option make curved text for the selected cell’s text.
g.
Underline → This option draw line under a
selected text.
h.
Border → This option add outline border effect
for the selected cell.
i.
Fill color → This option colored the selected
cell’s text.
j.
Font Color → by this option we can change color
for selected text.
3.
Alignment Group
a.
Align Top → This option move text at the top
side of the cell.
b.
Align Middle → This option move text at the
middle side of the cell.
c.
Align Bottom → This option move text at the
bottom side of the cell.
d.
Align Left →
By this option we can move cursor at the left side of the cell.
e.
Align Center → By this option we can move the
cursor at the center of the cell.
f.
Align Right
→ By this option we can move the cursor ate right side of the cell.
g.
Orientation → By this option we can rotate the
text written in the cell.
h.
Decrease Indent → This option Decrease the line
label.
i.
Increase Indent → This option Increase the line
label.
j.
Wrap Text → This option break the long line and
adjust the line in short cell.
k.
Merge & Center → This option Merge ( Joint )
the selected cell to each other.
4.
Number Group
a.
Number Format → By this option we can change
Cell Format such as ( Number,Text, Date, Short Date, Long Date, Percentage
etc.)
b.
Accounting Number Format → By this option we can
add some Currency Symbol in selected Cell.
c.
Percent Style → This option add Percentage
symbol in selected cell.
d.
Comma Style → By this option we can change the number
writing style into the comma style ( In this method the written number value
Separate with comma)
e.
Increase Decimal → This option add Zero value
after decimal places.
f.
Decrease Decimal → This option Remove Zero value
after Decimal Places.
5.
Style Group
1.
Conditional Formatting
a.
Highlight cells rules
i.
Greater Than →This option indicate the Larger
number comparing other number.
ii.
Less Than → This option indicate the smaller
number comparing the others number
iii.
Between → This option indicate the number
between two different numbers.
iv.
Equal To → This option indicate the equal value
written in the sheet.
v.
Text That Contains → This option indicate the
special character or word which are specify by users.
vi.
A Date Occuring. → This option indicate
duplicate date written in the cell.
vii.
Duplicate Values → This option indicate the
duplicate Value such as number or text written in the sheet.
b.
Top Bottom Rules
i.
Top 10 Item → by this option we can get top 10
greater number from selected cells area.
ii.
Top 10 Percentage → this option indicate largest
number from top 10 item.
iii.
Bottom 10 Items → By this option we can get
below 10 smaller number from selected cells area.
iv.
Bottom 10% → This option indicate smallest
number from bottom 10%
v.
Above Average → This option indicate smaller
number average from selected number.
vi.
Below Average → This option indicate higher
number average from selected number.
c.
Data Bars → This option add progressing value
according to their smaller or greater number.
d.
Color Scale → This option fill different type of
color according to their smaller or greater number.
e.
Icon Sets → This option add different type of
icon (symbol) according to their smaller or greater number.
2.
Format As Table → This option change normal
selected cell into the table format in which we can easily filter them.
3.
Cell Styles → This option change selected cell
appearances.
6.
Cell Group
a.
Insert →
by this option we can Add new column, cell or Row in the worksheet.
b.
Delete → By this option we Delete column, cell
or row in the worksheet.
c.
Format
i.
Row height → This option set selected row height
ii.
Auto fit Row Height → This option adjust auto
row height according to their Text Height.
iii.
Column width → This option set Column Width only.
iv.
Auto fit column Width → This option adjust Column width according to
their to written text.
v.
Default Width → This option show old Column
width size.
vi.
Hide & Unhide → By this option we can hide or unhide any
selected sheet.
vii.
Rename Sheet → by this option we can change any
sheet name.
viii.
Move or Copy sheet → By this option we change sheet position or
make their copy.
ix.
Tab Color →
This option change sheet tab(this are display lower most area) color.
x.
Protect sheet →
By this option we protect our worksheet by having a password number.
7.
Editing Group
a.
Auto sum → By this option we can add select
number to each other.
b.
Fill → This option fill forward number.
c.
Clear → This option delete all format, Content, etc.
d.
Sort & Filter → By
this option we can sort select data as ascending order or descending order.
e.
Find And select → By this option we can search any content
written in the worksheet.
Insert Tab
1.
Table Group
a.
Pivot Table →
By pivot table we can summarize complex data in a specific area.
b.
Table → This option change selected data as
table format in which we can easily sort and filter them.
2.
Illustration Group
a.
Picture → By this option we can insert any
picture from our compute in the worksheet.
b.
Online Picture → By this option we can insert
any picture from internet.
c.
Shape → This option add different type of Shapes
in the worksheet.
d.
Smart Art → This option insert relation figure
in the worksheet.
e.
Screenshot → By this option we can take a
screenshot from our computer’s Desktop.
3.
Chart – (note ) Chart show our data value a
different type of graph format such (Column, pie , Scatter etc.)
4.
Sparkline Group
a.
Line → By this option we can draw a graph line in
a cell according to another cell’s value.
b.
Column →
By this option we can draw a column in a cell according to another cell’s
Values.
c.
Win → This option indicate the empty cell using
column graph in a cell.
5.
Filters Group
a.
Slicer →
By slice we can create a floating filter button over worksheet to the selected
table.
b.
Timeline
→ By time line we can merge different type of pivot chart or table data
to each other.
6.
Links Group ( Hyper links Group) By Hyper link we can link any sheet to
another sheet or insert a web link to any selected cell.
7.
Text Group
a.
Text Box →
By this option we can insert a text holder box (Text holder means area
where Any text are Written) .
b.
Header & Footer → By this option can activate or deactivate
Header and Footer.
c.
Word Art → By this option we can Insert Stylish
Text holder Floating Box.
d.
Signature Line
→ This option insert a digital signature line the worksheet.
e.
Object. → This option insert another app data in
excel worksheet without closing the excel window.
8.
Symbol Group
a.
Equation → By this option we can insert
Mathematical formula in worksheet.
b.
Symbol → By this option we can insert Different
type of symbol in worksheet.
Page Layout Tab
1.
Themes Group
a.
Themes →
By this option we can change overall look of worksheet such color, font, and
object effect etc.
b.
Color → By this option we change inserted themes
color.
c.
Font → By this option we can change inserted
themes font.
d.
Effect → By this option we can change inserted
object effect such darkness or lightness.
2.
Page Setup Group
a.
Margins → This option set printing page corners.
b.
Orientation → By this option we can Rotate
worksheet as portrait or landscape.
c.
Size → this option set Printing page size.
d.
Print Area → If we want that only select area
should be print, we can use Print are option to set printing area.
e.
Breaks → This option break the worksheet and
make a new page.
f.
Background
→ By this option we can insert a Image behind the sheet.
g.
Print Titles → By this option we can set printing page title.
3.
Scale To Fit – (Note) By Scale to fit we can Set
default width and height of our worksheet.
4.
Sheet option Group
a.
Gird line → By this option we can Show or hide
Grid while working the worksheet or Printing our document.
b.
Heading →
By this option we can show or hide Heading while working the worksheet or
printing our document.
5.
Arrange Group
a.
Bring To Front →By this option we bring the
selected shapes or image from another image or shapes.
b.
Send Backward by this option we send back the selected
shapes or image to another shapes or image.
c.
Selection Pane → By this option we can show
details or inserted shape and hide or unhide them.
d.
Align →
This option can make perfect alignment of selected shapes such as center,
middle, left or right.
e.
Group → This option can joint shape to each
other.
f.
Rotate. → By this option we rotate the selected
shapes.
Formula Tab
Important formula List
1. Sum → By
this formula we can add selected number with each other.
Syntax → =Sum (Number1, Number2)
2. Subtract - → If we
need to minus one number to another than we can use subtract Formula.
Syntax →= Number1, - Number2
3. Divide - →
This formula Divide one number to another number.
Syntax → =Number1, / Number2
4. Product → By this formula we
can multiply with select numbers.
Syntax → =Product (Number1, Number2)
5. Count - → This
formula basically used to count the number written in the cell ( its count only
number written in the cell )
Syntax → =Count (Value1, Value2)
6. Count Blank → By this
formula we can count blank value in selected area.
Syntax → =Count blank(Range)
7. Count If – This is
logical function this formula count only specific text or number which we are
mentioned.
Syntax → =Count if (Range, Criteria)
8. Count Ifs → This
formula also work like count if formula but it have more than two condition for
counting the value.
Syntax
→ =Countifs (Criteria_Range1,)
9. If - This is a logical
function based on two logic true and false its show the result according the
true and false condition.
Syntax → =If (Logical_Test, [Value_If_True],
[Value_If_False])
10. Max -This formula is used to find the largest number among the digits written in the selected area.
Syntax → =Max (Number1, [Number2])
11. Min -This formula is used to find the smallest number among the digits written in the selected area.
Syntax → =Min (Number1, [Number2],)
12. Average -This formula is used to find the average value of selected numbers.
Syntax → =Average (Number1, [Number2])
13. Average if -This formula is used to find the average value of the numbers written in the selected area according to a selected argument.
Syntax → =Averageif (Range,Criteria,
[Average_Range])
14. Averageifs -This formula is used to find the average value of the numbers written in the selected area according to more than one selected argument.
Syntax → =Averageifs (Average_Range,
Criteria_Range1,Criteria1, [Criteria_Range2])
15. Date- This formula is used to write date in a cell.
Syntax → =Date (Year, Month, Day)
16. Day - This formula is used to write the day from the date cell to another cell.
Syntax → =Day (Serial_Number)
17. Month -This formula is used to write the month from the date cell to another cell.
Syntax → =Month(Serial_Number)
18. Year -This formula is used to write the year from the date cell to another cell.
Syntax → =Year(Serial_Number)
19. Days360 - This formula is used to find the time between any two dates.
Syntax → =Days360 (Start_Date, End_Date, [Method])
20. Today - Today formula is used to write the current date in a cell.
Syntax → =Today()
21. Time - This formula is used to write time in a cell.
Syntax → =Time(Hour, Minute, Second)
22. Hour - This formula is used to write hour from time cell to another cell.
Syntax → =Hour(Serial_Number)
23. Minute -This formula is used to write minutes from time cell to another cell.
Syntax → =Minute(Serial_Number)
24. Second -This formula is used to write seconds from time cell to another cell.
Syntax → =Second(Serial_Number)
25. Now -This formula is used to write the current time and date in a cell.
Syntax → =Now()
26. Upper - Upper formula is used to write the English letters written in a cell in capital letters.
Syntax → =Upper(Text)
27. Lower - Lower formula is used to write English letters written in a cell in small letters.
Syntax → =Lower(Text)
28. Proper - Through this formula, the first letter of a word or sentence is written in capital and other small letters.
Syntax → =Proper(Text)
29. Datedif - This formula is mainly used to find the date of birth.
Syntax → =Datedif()
30. Roman - To write a decimal number in the Roman system, the Roman formula is used.
Syntax → =Roman(Number, [Form])
31. Power - Power formula is used to find the value of a number by raising it to the power.
Syntax → =Power(Number, Power)
32. Abs - Abs formula is used to convert negative number into positive number.
Syntax → =Abs(Number)
33. Lcm - This formula is used to find the Lowest Common Multiple value of the number written in the selected cell.
Syntax → =Lcm(Number1 [Number2])
34. Len - Len formula is used to count the letters written in a cell.
Syntax → =Len(Text)
35. Dollar - This formula is used to put dollar sign in the cell.
Syntax → =Dollar(Number, [Decimals])
36.Sumif - Through this formula we can create and join any type of data group.
Syntax → =Sumif(Range, Criteria, [Sum_Range])
37. Sumifs - Using this formula, we can group and connect more than one type of data.
Syntax → =Sumifs(Sum_Range, Criteria_Range1,
Criteria1, [Criteria_Range2, Criteria2])
38. Lookup - This formula is used to find all the information related to any data written in the sheet.
Syntax → =Lookup(Lookup_Value, Lookup_Vector, [Result_Ve4ctor])
39. Vlookup - This formula works like the Lookup formula but it searches the data vertically.
Syntax → =Vlookup(Lookup_Value, Table_Array,
Col_Index_Num, [Range_Lookup])
40. Hlookup- This formula also works like the Lookup formula but it searches the data horizontally.
Syntax → =Hlookup(Lookup_Value, Table_Array,
Row_Index_Num, [Range_Lookup])
41.Index-
42. Match-
43.Subtotal - This is a formula through which more than one formula can be applied in a cell at a time. It includes many formulas like (Sum, Divide, Subtract etc.)
Syntax → =Subtotal(Function_Num, Ref1,
[Ref2])
44.. Sum Product - Through this formula, numbers written in a column are added and multiplied.
Syntax → =Sumproduct(Array1, [Array2])
45. Repeat - This formula is used to repeat the letters written in a cell in another cell.
Syntax → = Rept(b3)
46. Left - This formula is used to write some letters to the left of the words written in a cell in another cell.
Syntax → =Left(Text, [Num_Chars])
47. Right - This formula is used to write some letters on the right side of the words written in a cell in another cell.
Syntax → =Right(Text, [Num_Chars])
48. Row - This formula is used to find row number
Syntax → =Row(Reference])
49. Column -This formula is used to find the column number.
Syntax → =Column(Reference])
50. Rows -If we want to know how many rows are selected in the selected area, then we use the Rows formula.
Syntax → =Rows(Array)
51. Columns - If we want to know how many columns are selected in the selected area, then we use the Columns formula.
Syntax → =Columns(Array)
52.Mid
53.Concanate.
54.Substitute
55.Hyperlink
56.Int
57.Frequency -
Define Name Group
a.
Name Manager
→ By this option we can modify or delete Defined name area.
b.
Define Name → This function set a value name for
the selected area.
c.
Use in Formula → By this option we can use
defined name area in any formula.
d.
Create from Selection → This function can Define
a name value for the selected area.
Formula auditing Group
a.
Trace Precedents → By this option We can
indicate relational formula cells value by arrow symbol.
b.
Trace Dependents → This function indicate Which
are related any formula applied cell this is also indicate by arrow symbol.
c.
Remove Arrow → By this function we can Remove
Arrow of Trace Dependents and Trace Precedents Arrow.
d.
Show Formula → This function show formula syntax applied in
the cells.
e.
Error Checking → This function Check Formula
Error.
f.
Evaluate Formula → This function open a formula
dialogue box in which we can organize our formula and understand them
accurately.
g.
Watch Window → This function can Create as
floating window for any selected area over excel worksheet.
Data Tab
1.
Get External Data Group
Note –By This Group We can Import External
from other source such Tex, Web, etc to the Excel Sheet.
2.
Connection Group
Note – by connection Group We can Refresh,
Edit, and Delete Linked data to the Excel Sheet.
3.
Sort & Filter Group
Note – by sort & Filter Group We can
Add Table, And Add The Advance filter Effect.
4.
Data Tools Group
Note- By Data Tools Group We can Create
Drop Down list, Add Text Data to Excel Sheet, And Merge two Different Sheet
data to each other.
5.
Outline Group
Note- By Outline Group We can hide or unhide
Columns or row by adding subtotal effect.
Review Group
1.
Proofing Group
a.
Spelling
- By this option we can check spelling mistake
b.
Thesaurus → By this option we can Find synonyms
and antonyms for the selected Text.
2.
Language Group
Note – → This group can Translate any
language to another language.
3.
Comment Group
a.
New Comment (Shift + F2) → When we need to add
additional Note in our Document. We can use New comment option.
b.
Delete Comment → This option delete Inserted Comment in the
worksheet.
c.
Previous Comment → This option Move cursor
position to the Previous comments.
d.
Next Comment → This option Move the Cursor
Position to the Next Comment.
4.
Changes Group
Note - → By Changes Group we can protect our
workbook, worksheet with password and share with connected network.
View Tab
1.
Workbook View Group
a.
Normal → This is the default view of the
worksheet.
b.
Page Layout → This View display the Sheet Data
as the Printing page view.
c.
Page Break Preview → This view show only Written
Content in the Sheet.
d.
Customs View → By this option we can set the
different type of Display view and can show them by clicking area.
e.
Full Screen View → This View display the Data as Full width mode.
2.
Show/ Hide Group
a.
Zoom → By
this option we can Zoom out or Zoom in Window size.
b.
100% Zoom → This Button fixed the Window Zooming
point at 100% This is best view of the Window Appearance.
c.
Zoom to Selection → This option Zoom only
selected area.
3.
Window Group
a.
New Window → By this option we can Make a new
window as current window.
b.
Arrange All → It arrange all opened window in the screen
so why we can easily see all window.
c.
Freeze Pane → By this option we can fixed the
First Row and First Column Position in the worksheet.
d.
Split Window → By this option we can Break the
Window sheet from selected area and make the new window with new scroll bar.
e.
Hide window → This option can hide Opened window
in the task bar.
f.
Unhide Window → This option display out the
hidden window.
g.
View Side By Side → By this option we can Watch
two window on the desktop screen at a time.
h.
Synchronous Scrolling → By this option we can
Move two window scroll bar at the same time.
i.
Reset Window Position → This option restore all
window position and make them default position.
j.
Switch Window → This option can change the
Window number position.
k.
Macros → By this option we can Record all
activity such as insertion, deletion, formatting etc and Apply them for our
further requirement.