Basic Concepts of Microsoft Excel
Microsoft Excel
Ar. Manish Kumar
Ms
Excel
Meaning:-
It
is
basically
a
spreadsheet
program
formed
by
Microsoft,
in
which
there
are
rows
and
column
which
used
to
organize
the
data
with
function
and
formula.
Basic
Terms:-
WORKSHEET
:-
It
is
a
grid
of
horizontal
rows
and
vertical
column.
•
Rows:-
Rows
are
horizontal
staring
from
1
and
go
as
2,3,4....
Maximum
row
in ms
excel
worksheet
is
1,048,576(2019).
•
Column:-
Column
are
vertical
staring
from
A and
go
as
B,
C. Colum
are
lettered
as A-Z, AA-AZ,
BA-BZ,
AAA-AAZ,
ABA-ABZ
and
go on... Maximum
column
in
excel
worksheet
is
16384
(2019).
WORKBOOK
:
-
A
spreadsheet
allows
you
to
combine
more
than
one
worksheet
in
a
file.
Such
a
file
having
multiple
worksheets
is
know
as a
workbook.
DIFFERNCE
BETWEEN
WOEKBOOK
AND
WORKSHEET
A
WORKSHEET
is a grid
of cells
made
up of
rows
and
columns. Multiple
worksheets
can
be
combined
under
a
file
know
as
WORKBOOK
.
CELLS:-
Cell
is
a basic
unit
of
worksheet
where
numbers,
texts,
formulas
etc.
Can be
placed.
Cell
is
formed
by
intersection
of
rows
and
column
and
this
intersection
gives
a cell
a unique
address
i.e. the
combination
of
the
column
letter
and
row
number.
FOR
EX
:- If a
row
4
intersected
by
a
column
E
,
then the cell
formed
out of
it
gets
an
address
E4.
•
Cell
Pointer:-
It
is a
cell
boundary
that
specifies
which cell
is
active
at
that
moment
•
Current
cell
:-
It is
a
cell
which is
active.
This
is
the cell
where
cell
pointer
point to.
And
it
is
the
cell
where
the
next
entry
would
take
place.
•
Range
of
cells:-
A
range
of cells
is
a
group
of contiguous
cells
that
form
rectangular
area
in
shape.
For ex:-
a
range
starting
from
F7
till
G14
would
be
written
as F7:G14
in
ms
excel.
RA
N
G
E
CELL
CELL
Important
terms
Merging
and
unmerging
of
cells
:-
Combining
multiple
cells
(two
or
more)
that
are
in
the
same
row
and/or
in
the
same
column
to
a
single
cell
is
known
as
merging
cells
.
Separating
the
cells
that
are
merged
is
known
as
unmerging cells
Inserting
a
cell:-
1.
Select
the
cells,
rows,
or columns where
you
want
the
new,
blank cells
to
appear.
2.
Click
the
drop-down
arrow
attached
to
the
Insert
button
in the
Cells
group
of
the
Home
tab.
3.
Click
Insert
Cells
on
the
drop-down
menu
.
Shift
Cells
Right
shifts
existing
cells
to
the
right
to
make
room
for
the blank
cells
you
want
to
insert.
Shift
Cells
Down
instructs
Excel
to
shift
existing
cells
down.
This
is
the
default
option.
Entire
Row
inserts
complete
rows
in
the
cell
range.
You
can
also
select
the
row
number
on
the
frame
before
you
choose
the
Insert
command.
Entire
Column
inserts
complete
columns
in the cell
range.
You
can
also
select
the
column
letter
on
the
frame
before
you
choose
the Insert
command.
Then
click
Ok
MS
EXCEL
FUNCTION
BASIC
TERMS
ARGUMENTS
:-
Arguments
are
the
values
passed
to
the functions,
using
which
the
function
carries
out
some
task.
Syntax:-
The structure of
a function begins
being with
function
name,
followed
by
an
opening parenthesis,
the
arguments
for
the function
are
separated
by
comas,
and
a closing
parenthesis.
1.
SUM
Meaning:-
This
function
is
used
for
adding
number
in
excel
Syntax:-
Sum
function
=sum(number
1,
number
2...)
Arguments
:-
•
number1
-
The
first
value
to
sum.
•
number2
-
[optional]
The
second
value
to
sum.
E
x
ampl
e
:
-
1.
Enter
the
function
2.
Select
the
column
3.
press
enter
Marks1
total
is
i.e.
79
same
apply
to
marks
2
column
i.e.
73
2.IF
Meaning:-
Function
is
used
for
whether condition
is
true
or
false
Syntax:-
=IF
(logical
test,
[value
if
true],
[value
if
false])
Arguments:-
•
logical
test
-
A
value
or
logical
expression
that
can
be
evaluated
as
TRUE
or
FALSE.
•
Value
if
true
- [optional]
The
value
to
return
when
logical
test
evaluates
to
TRUE.
•
Value
if
false
- [optional]
The
value
to
return
when
logical
test
evaluates
to
FALSE.
Example:-
After
applying
the
function
of
IF:-
=
if(B2:B6>35,“pass",“fail")
This
means
marks
greater
than
35
true
and
less
than
35
false
F
F
LR
G
T
FUNCTION
IS
PLACED
CLICK
ON
CELL
THEN
GO
RIGHT CORNER OF
CELL
THAN
SCROLL
DOWN
FOR
GETTING
REMAING
RESULT
2A.
IF
OR
Meaning:-
One
condition
to
be true
or
false
Syntax:-
=IF(OR(logical
test
1,logical
test
2)
,
[value
if
true],
[value
if
false])
Arguments:-
•
Logical
test
1
-
The
first
condition
or
logical
value
to
evaluate.
•
Logical
test
2
- [optional]
The second condition or logical value
to
evaluate.
•
Value
if
true
- [optional]
The
value
to
return
when
logical
test
evaluates
to
TRUE.
•
Value
if
false
- [optional]
The
value
to
return
when
logical
test
evaluates
to
FALSE.
Example:-
FUNCION
IS
APPLIED
CLICK
ON
CELL
THEN
GO
RIGHT
CORNER
OF
CELL
THEN
SROLL
DOWN
After
applying
the
function:-
=if(or(warehouse1>0,warehouse2>0),"instock","outstock")
2B.
IF
AND
Meaning:-
All
condition
need
to
be
true
Syntax:-
=IF(AND(logical
test
1,logical
test
2) ,
[value
if
true],
[value
if
false])
Arguments:-
•
Logical
test
1
-
The
first
condition
or
logical
value
to
evaluate.
•
Logical
test
2
-
The
second
condition
or
logical
value
to
evaluate.
•
Value
if
true
-
The
value
to
return
when
logical
test
evaluates
to
TRUE.
•
Value
if
false
-
The
value
to
return
when logical
test
evaluates
to
FALSE.
Example:-
FUNCTION
IS
APPLIED
After
applying
the
function:-
=if(and(marks1>40,marks2>40),“pass",“fail")
3.
SUM
IF
Meaning:-
The
powerful
SUMIF
function
in
Excel
sums
cells
based
on certain
criteria
Syntax:-
•
=SUMIFS
(sum
range,
range1,
criteria1,
[range2],
[criteria2],
...)
Arguments;-
•
Sum
range
-
The
range
to
be
summed.
•
range1
-
The
first
range
to
evaluate.
•
criteria1
-
The
criteria
to
use
on range1.
•
range2
-
[optional]
The
second
range
to
evaluate.
•
criteria2
-
[optional]
The
criteria
to
use
on
range2.
Example:-
FUNCTION
APPLIED
After
applying
the
function:-
=SUMIF(A1:A6,"=A",B1:B6)
ONLY
“A”
alphabet
amount
added
that
is
1500
Summary
•
Sum:-
=sum(number
1,
number
2...)
•
If:-
=IF (logical
test,
[value
if true],
[value
if
false])
•
If
Or:-
=IF(OR(logical
test
1,logical
test
2) ,
[value
if
true],
[value
if
false])
•
If
And:-
=IF(AND(logical
test
1,logical
test
2) ,
[value
if true],
[value
if
false])
•
Sum
If:-
=SUMIFS
(sum
range,
range1,
criteria1,
[range2],
[criteria2],
...)
Microsoft Excel is a powerful spreadsheet program with features like worksheets, workbooks, cells, ranges, and functions. Learn about terms like merging, unmerging, inserting cells, and using arguments in functions to enhance your Excel skills.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
Ms Excel Meaning:- It is basically a spreadsheet program formed by Microsoft, in which there are rows and column which used to organizethe data with function and formula. Basic Terms:- WORKSHEET:- It is a grid of horizontal rows and vertical column. Rows:- Rowsare horizontal staring from 1 and goas 2,3,4....Maximum row in ms excel worksheet is 1,048,576(2019). Column:- Column are vertical staring from A and go as B, C. Colum are lettered as A-Z, AA-AZ, BA-BZ, AAA-AAZ, ABA-ABZ and go on... Maximum columnin excel worksheet is 16384(2019).
WORKBOOK:- A spreadsheet allows youto combinemore than one worksheet in a file. Such a file having multiple worksheets is know as a workbook. DIFFERNCEBETWEEN WOEKBOOK AND WORKSHEET A WORKSHEET is a grid of cells made up of rows and columns. Multiple worksheets can be combinedundera file know as WORKBOOK. CELLS:- Cell is a basic unit of worksheet where numbers, texts, formulas etc. Can be placed. Cell is formed by intersection of rows and column and this intersection gives a cell a unique address i.e. the combinationof the column letter and row number. FOR EX:- If a row 4 intersected by a column E, then the cell formed out of it gets an address E4. Cell Pointer:- It is a cell boundarythat specifies which cell is active at that moment Current cell:-It is a cell which is active. This is the cell where cell pointer point to. And it is the cell where the next entry would take place. Range of cells:-A range of cells is a group of contiguouscells that form rectangulararea in shape. For ex:- a range starting from F7 till G14 would be written as F7:G14 in ms excel.
RANG E CELL CELL
Important terms Merging and unmerging of cells:- Combiningmultiple cells (two or more) that are in the same row and/orin the same columnto a single cell is known as merging cells. Separating the cells that are merged is known as unmerging cells
Inserting a cell:- 1.Select the cells, rows, or columns where youwant the new, blank cells to appear. 2.Click the drop-down arrow attached to the Insert button in the Cells group of the Home tab. 3. Click Insert Cells on the drop-downmenu. Shift Cells Right shifts existing cells to the right to make room forthe blank cells youwant to insert. Shift Cells Down instructs Excel to shift existing cells down.This is the default option. Entire Row inserts complete rows in the cell range. Youcan also select the row number on the frame beforeyouchoose the Insert command. Entire Column inserts complete columns in the cell range.Youcan also select the column letter on the frame beforeyouchoose the Insert command. Thenclick Ok
MS EXCEL FUNCTION BASIC TERMS ARGUMENTS:-Arguments are the values passed to the functions, using which the function carries out some task. Syntax:- The structure of a function begins being with function name, followed by an opening parenthesis, the arguments for the function are separated by comas, and a closing parenthesis.
1. SUM Meaning:- This function is used for adding number in excel Syntax:- Sum function=sum(number1, number 2...) Arguments :- number1 - The first value to sum. number2 - [optional] The secondvalue to sum.
Exampl e:- 1. Enter the function A B C 2. Select the column 3. press enter 1 NAME MARKS1(20) MARKS2(20) 2 RITA 12 14 3 SEEMA 14 16 4 SONIA 16 18 5 TARUN 18 10 6 TINA 19 17 7 TOTAL =sum(B2:B6) =sum(C2:C6) Marks1 total is i.e. 79 same apply to marks 2 columni.e. 73
2.IF Meaning:- Function is used for whether conditionis true or false Syntax:- =IF (logicaltest, [value if true], [value if false]) Arguments:- logicaltest - A value or logical expression that can be evaluated as TRUE or FALSE. Value if true - [optional] The value to return when logical test evaluates to TRUE. Value if false - [optional] The value to return when logical test evaluates to FALSE.
Example:- A B After applying the function of IF:- 1 NAME MARKS = if(B2:B6>35, pass", fail") This means marks greater than 35 true and less than 35 false 2 MEENA 75 3 SURAJ 60 4 SONALI 35 5 REKHA 54 6 GEETA 30 FUNCTIONIS PLACED FFLRGT A B C 1 NAME MARKS RESULT CLICK ON CELL THEN GO RIGHT CORNER OF CELL THAN SCROLL DOWN FOR GETTING REMAINGRESULT 2 MEENA 75 pass 3 SURAJ 60 pass 4 SONALI 35 fail 5 REKHA 54 pass 6 GEETA 30 fail
2A. IF OR Meaning:- One condition to be true or false Syntax:- =IF(OR(logical test 1,logical test 2) , [value if true], [value if false]) Arguments:- Logicaltest 1 - The first conditionor logical value to evaluate. Logical test 2 - [optional] The second condition or logical value to evaluate. Value if true - [optional] The value to return when logical test evaluates to TRUE. Value if false - [optional] The value to return when logical test evaluates to FALSE.
Example:- A B C D FUNCIONIS 1 PRODUCT WAREHOUSE1 WAREHOUSE2 RESULT APPLIED 2 TABLE 0 10 INSTOCK 3 CHAIR 0 0 OUTSTOCK CLICKONCELL THENGO RIGHT CORNEROF CELL 4 BED 5 1 INSTOCK 5 CABINET 0 0 OUTSTOCK THENSROLLDOWN After applyingthe function:- =if(or(warehouse1>0,warehouse2>0),"instock","outstock")
2B. IF AND Meaning:- All conditionneed to be true Syntax:- =IF(AND(logicaltest1,logical test2) , [value if true],[value if false]) Arguments:- Logicaltest 1 - The first conditionor logical value to evaluate. Logicaltest 2 - The second conditionor logicalvalue to evaluate. Value if true - The value to return when logical test evaluates to TRUE. Value if false - The value to return when logical test evaluates to FALSE.
Example:- A B C D FUNCTIONIS 1 STUDENT MARKS1 MARKS2 RESULT 2 STUDENT 1 30 60 FAIL APPLIED 3 STUDENT 2 50 40 FAIL 4 STUDENT 3 75 64 PASS 5 STUDENT 4 66 35 FAIL 6 STUDENT 5 80 77 PASS After applying the function:- =if(and(marks1>40,marks2>40), pass", fail")
3. SUM IF Meaning:- The powerfulSUMIF function in Excel sums cells based on certain criteria Syntax:- =SUMIFS(sum range, range1, criteria1, [range2], [criteria2],...) Arguments;- Sum range - The range to be summed. range1 - The first range to evaluate. criteria1 - The criteria to use on range1. range2 - [optional] The secondrange to evaluate. criteria2 - [optional]The criteria to use on range2.
Example:- FUNCTION A B APPLIED 1 A 1000 2 B 1300 3 C 900 4 A 500 5 B 1500 6 C 800 After applyingthe function:- =SUMIF(A1:A6,"=A",B1:B6) ONLY A alphabet amount added that is 1500
Summary Sum:- =sum(number1, number 2...) If:- =IF (logicaltest, [value if true], [value if false]) If Or:- =IF(OR(logical test 1,logicaltest 2) , [value if true], [value if false]) If And:- =IF(AND(logical test 1,logical test 2) , [value if true], [value if false]) Sum If:- =SUMIFS (sum range, range1, criteria1, [range2], [criteria2], ...)
