MS Excel 2016
Introduction to Excel-2016
Microsoft Excel is an electronic
spreadsheet program which is developed by Microsoft Corporation. This program
is specially designed to calculate mathematical problems such as salary sheets,
electricity bills, student mark sheet ledgers, etc.
How to start Microsoft Excel 2016?
·
Type excel in the search box.
·
Click on Excel 2016.
OR
·
Press <Windows> + <R> Key.
·
Type "excel"
·
Click on Ok. Or Press Enter Key
The layout of Microsoft
Excel 2016
1. Quick
Access Toolbar
2.
File Menu
3. Tabs
4. Ribbon
5.
Name box
6. Formula Bar
7.
Zoom
8.
View
9. Sheets
Important Terms:
Wordbook - Microsoft Excel file is called a workbook. In another
word, a collection of worksheets is called a workbook.
Worksheet- Actual data is stored in the worksheet.
Row- Horizontal Line. Row Number is appeared as
1,2,3,.....,..,1048576 (Total number of Rows= 10,48,576).
Column - Vertical line. Column numbers is appeared as
A,B,C, D,AA,AB, AZ,BA, BB....XFD (Total Number of Column=16,384).
Cell- Intersection point of row and column. Cell address
appears as A1,A2,A5, etc.
Reference
Reference
means cell address which is changeable. There are three types of references.
1. Relative
reference- No dollar sign before column name and row number.
A1,B5,A1:A3
2. Absolute
Reference- Dollar sign before column name and row number.
&A$5,$B$5,$A$1:$A$2
3. Mixed
Reference- the combined features of absolute and relative references.
$A5 or A$5,
$B5 or B$5, $A1:$A3 or A$1:A$3
Operator:
Operator is
a sign or symbol which is used for calculation. There are different types of
operator which can be explained below.
Arithmetic
Operator
-Addition
(+)® 3+2=5
-Subtraction
(-)® 3-2=1
-Multiplication
(*)® 3*2=6
-Division (/)®
9/2=4.5
-Exponent
(^)® 2^3=8
-Percentage
(%)® 20%=0.2
Main Point to Remember
1. Microsoft
excel is an electronic spreadsheet program.
2. Microsoft excel
file is called workbook.
3. Vertical
line is called column.
4. Horizontal
line is called row.
5. Total
number of rows=10,48,576.
6. Total
number of columns= 16,384.
7. Keyboard
shut cut to fill down= Ctrl+D
8. Keyboard
shut cut to fill right=Ctrl+R
9. For select
of cell = Shift+Arrows Key
10. To select
rows= Shift+Space key
11. To select
Columns= Ctrl+Space Key
12. To open
Format cell =Ctrl+1
13. Absolute
reference means dollar sign before row and column.
14. Relative
reference means no dollar sign before row and column.
15. To show
date in Sheet =today()¿
16. To show
time in sheet=now()¿
17. To find
out column=columns(1:1)¿
18. To find
out Row=rows(A:A)¿
19. To find
out cell =Rows×Columns¿
20. To addition Number =Sum(range)¿
21. To multiply Number=product(range)¿
Text Operator
-Ampersand (&) ®[1&2=12]
["A"&"B"]
Reference
Operator
-Colon
(;) Range Operator [A1:A5] ®(SUM)
-Comma
(,) Union Operator [A1, A2, A3]
-Space ( ) Intersection Operator
[A1:A3 A2:A4]
Relation
Operator
Relational
Operator return TRUE or FALSE value, if the condition is true, it shows TRUE.
If the condition is false, it shows FALSE.
-Equal to
(=) 5=5 ®TRUE, 6=5 ®FALSE
-Greater
than (>) 5>10 ®FALSE 5>2® TRUE
-Less than
(<) 5<10 ®TRUE 5<2® FALSE
-Greater
than or equal to (>=)
5>=5 ®TRUE
5=5 ®TRUE
OR
5>5® FALSE
-Less than
equal to (<=)
5>=10®TRUE
5=10 ®FALSE
OR
5>10® TRUE
-Not equal
to (<>)
5<>10® TRUE,
5<> ®FALSE
1. Char Function
Syntax:
=Char(number)¿ (1-255)
2. Code Function (“,#,&)
Syntax:
=code(text) ¿ (“,#,&)
3. Trim Function (remove
unnecessary spaces)
Syntax:
=trim(text) ¿
4. Left Function
Syntax:
=left(text,[num_chars])
¿
=left(“text”,2)
¿
5. Right Function
Syntax:
=right(text,[num_chars]
¿
=right(“text”,3)
¿
6. Mid Function
Syntax:
=mid(text,start,start_num,num_chars)
¿
=mid(range,2,3)
¿
7. Lower Function
Syntax:
=lower(text) ¿
8. Upper Function
Syntax:
=upper(text) ¿
9. Proper Function
Syntax:
=proper(text) ¿
10.
Trim Proper Function
Syntax:
=Trim(proper(“Text”)
Use: Proper ("it computer") [large space between
text]
11.
Counta Function
Syntax:
=counta(select text range)¿
12.
Countif
Function
Syntax:
=countif(select text range,”value”)¿
Essential Math Function
1. Abs Function
Syntax:
=ABS(num)¿
2. Sum Function
Syntax
=Sum(Num1,num2,num3)
¿
3. Min Function
Syntax
min(num1,num2,num3..)
¿
4. Max Function
Syntax
Average(num,num2,num3,...)
¿
5. Average Function
Syntax:
Average(num1,num2,num3,...)
¿
6. Count Function
Syntax:
=Count(value1,value2,value3,....)
¿
7. SQRT Function
Syntax:
=Sqrt(number) ¿
8. SUMIF Function
Syntax:
Sumif(range,criteria,[sum_range])
¿
9. Rank Function
Syntax:
=Rank(num,referance,[order])
¿