Basics of Ms Excel Office Tool

Indian Institute of Cost & Management Studies &
Research, Pune
24-06-2014
MS EXCEL 1
SUBMITTED BY
Subodh D Shahare

INDEX
 INTRODUCTION TO
EXCEL…………………………………...
 OVERVIEW OF
EXCEL…………………………………………...
 OFFICE
BUTTON………………………………………..........

RIBBONS……………………………………………………
………….
 WORKING WITH
CELLS……………………………………....
 FORMATTING
TEXT……………………………………………...
 CONDITIONAL
FORMATTING…………………………….....
3
4
5
6
7-8
9-11
12-13
14
15
16
17-19
20-26
27
28-30
24-06-2014MS EXCEL
2

INTRODUCTION TO MS-
EXCEL
 Excel is a computer program used to create electronic
spreadsheets.
 Within excel user can organize data ,create chart and
perform calculations.
 Excel is a convenient program because it allow user to
create large spreadsheets, reference information, and it
allows for better storage of information.
 Excels operates like other Microsoft(MS) office programs
and has many of the same functions and shortcuts of
other MS programs.
24-06-2014MS EXCEL
3

OVERVIEW OF EXCEL
 Microsoft excel consists of workbooks.
Within each workbook, there is an
infinite number of worksheets.
 Each worksheet contains Columns
and Rows.
 Where a column and a row intersect is
called a cell. For e.g. cell D5 is located
where column D and row 5 meet.
 The tabs at the bottom of the screen
represent different worksheets within a
workbook. You can use the scrolling
buttons on the left to bring other
worksheets into view.
24-06-2014MS EXCEL
4

OFFICE BUTTON
CONTAINS..
NEW-TO OPEN NEW WORKBOOK.
(CTRL+N)
OPEN-TO OPEN EXISTING
DOCUMENT (CTRL+O)
SAVE-TO SAVE A DOCUMENT.
(CTRL+S)
SAVE AS-TO SAVE COPY
DOCUMENT. (F12)
PRINT-TO PRINT A DOCUMENT.
(CTRL+P)
PREPARE-TO PREPARE DOCUMENT FOR DISTRIBUTION.
SEND-TO SEND A COPY OF DOCUMENT TO OTHER PEOPLE.
PUBLISH-TO DISTRIBUTE DOCUMENT TO OTHER PEOPLE.
CLOSE-TO CLOSE A DOCUMENT (CTRL+W).
24-06-2014MS EXCEL
5

TABS:THERE ARE SEVEN TABS ACROSS THE TOP OF
THE EXCEL WINDOW.
GROUPS: GROUPS ARE SETS OF RELATED
COMMANDS,DISPLAYED ON TABS.
COMMANDS: A COMMAND IS A BUTTON,A MENU OR
A BOX WHERE YOU ENTER INFORMATION.
1
2
3
THE THREE PARTS
OF THE RIBBON
ARE
R I B B O N S
TABS
GROUPS
COMMANDS
24-06-2014MS EXCEL
6

WORKING WITH CELLS
TO COPY AND PASTE CONTENTS:
Select the cell or cells you wish to copy.
Click the Copy command in the Clipboard group on the Home tab.
Select the cell or cells where you want to paste the information.
Click the Paste command.
The copied information will now appear in the new cells.
24-06-2014MS EXCEL
7

WORKING WITH CELLS
To Cut and Paste Cell Contents:
Select the cell or cells you wish to cut.
Click the Cut command in the Clipboard group on the Home tab.
Select the cell or cells where you want to paste the information.
Click the Paste command.
The cut information will be removed and now appear in the new cells.
24-06-2014MS EXCEL
8

FORMATTING TEXT
TO FORMAT TEXT IN BOLD,
ITALICS OR UNDERLINE:
Left-click a cell to select it or drag your
cursor over the text in the formula bar
to select it.
Click the Bold, Italics or underline
command.
TO CHANGE THE FONT STYLE:
Select the cell or cells you want to format.
Left-click the drop-down arrow next to the
Font Style box on the Home tab.
Select a font style from the list.
24-06-2014MS EXCEL
9

FORMATTING TEXT
TO CHANGE THE FONT SIZE:
Select the cell or cells you want to
format.
Left-click the drop-down arrow next to
the Font Size box on the Home tab.
Select a font size from the list.
TO ADD A BORDER:
format.
Click the drop-down arrow next to the
Borders command on the Home tab. A
menu will appear with border options.
24-06-2014MS EXCEL
10

FORMATTING TEXT
TO CHANGE THE TEXT COLOUR:
Select the cell or cells you want to format.
Left-click the drop-down arrow next to the Text
Color command. A color palette will appear.
Select a color from the palette.
TO ADD A FILL COLOUR:
format.
Click the Fill command. A color palette
will appear.
Select a color from the palette.
24-06-2014MS EXCEL
11

CONDITIONAL FORMATTING
TO APPLY CONDITIONAL FORMATTING:
Select the cells you would like to format.
Select the Home tab.
Locate the Styles group.
Click the Conditional Formatting command. A menu will
appear with your formatting options.
TO REMOVE CONDITIONAL FORMATTING:
Click the Conditional Formatting command.
Select Clear Rules.
Choose to clear rules from the entire worksheet or the
selected cells.
24-06-2014MS EXCEL
12

CONDITIONAL FORMATTING
TO MANAGE CONDITIONAL FORMATTING:
Click the Conditional Formatting command.
Select Manage Rules from the menu. The
Conditional Formatting Rules Manager dialog box
will appear. From here you can edit a rule, delete
a rule, or change the order of rules.
TO APPLY NEW
FORMATTING:
Click the Conditional Formatting
command. Select New Rules from
the menu. There are different rules,
you can apply these rules to
differentiate particular cell.
24-06-2014MS EXCEL
13

TO INSERT ROWS & COLOUMS
TO INSERT ROWS:
Select the row below where you want the new row to appear.
Click the Insert command in the Cells group on the Home tab. The row will
appear.
To Insert Columns:
Select the column to the right of where you want the column to appear.
Click the Insert command in the Cells group on the Home tab. The column will
appear.
NOTE:
1. The new row always
appears above the
selected row.
2. The new column
always appears to the
left of the selected
column.
24-06-2014MS EXCEL
14

24-06-2014MS EXCEL
15
EDITING- FILL
 IN THE LOWER RIGHT HAND CORNER OF THE ACTIVE
CELL IS EXCEL’S “FILL HANDLE”.WHEN YOU HOLD
YOUR MOUSE OVER THE TOP OF IT, YOUR CURSOR WILL
TURN TO A CROSSHAIR.
 IF YOU HAVE JUST ONE CELL SELECTED, IF YOU CLICK
AND DRAG TO FILL DOWN A COLUMN OR ACROSS A
ROW, IT WILL COPY THAT NUMBER OR TEXT TO EACH
OF THE OTHER CELLS.
 IF YOU HAVE TWO CELLS SELECTED, EXCEL WILL FILL
IN A SERIES. IT WILL COMPLETE THE PATTERN.FOR
EXAMPLE,IF YOU PUT 4 AND 8 IN TWO CELLS SELECT
THEM,CLICK AND DRAG THE FILL HANDLE ,EXCEL WILL
CONTINUE THE PATTERN WITH 12,16,20.ETC.
 EXCEL CAN ALSO AUTO- FILL SERIES OF DATES, TIMES,
DAYS OF THE WEEK, MONTHS.

SORTING
TO SORT IN ALPHABETICAL ORDER:
Select a cell in the column you want to sort
(In this example, we choose a cell in column
Q).
Click the Sort & Filter command in the
Editing group on the Home tab.
Select Sort A to Z. Now the information in
the Category column is organized in
alphabetical order.
TO SORT FROM SMALLEST TO LARGEST:
Select a cell in the column you want to sort
(In this example, we choose a cell in column
Q).
Click the Sort & Filter command in the
Editing group on the Home tab.
Select From Smallest to Largest. Now the
information is organized from the smallest
to largest amount.
24-06-2014MS EXCEL
16

CELL REFERENCING
A RELATIVE
CELL
REFERENCE
AS (A1) IS
BASED ON
THE
RELATIVE
POSITION OF
THE CELL. IF
THE
POSITION OF
THE CELL
THAT
CONTAINS
THE
REFERENCE
CHANGES,
THE
IN CELL (C1) SUM FUNCTION IS USED.
THEN FUNCTION FROM CELL (C1) IS COPY TO
CELL (D3).
WHEN THE POSITION OF THE CELL IS CHANGED
FROM (C1) TO (D3),THEN THE REFERENCE IS ALSO
CHANGED FROM (A1,B1) TO (B3,C3).
24-06-2014MS EXCEL
17

CELL REFERENCING
AN ABSOLUTE
CELL
REFERENCE
AS ($A$1)
ALWAYS
REFERS TO A
CELL IN A
SPECIFIC
LOCATION. IF
THE POSITION
OF THE CELL
THAT
CONTAINS THE
FORMULA
CHANGES, THE
ABSOLUTE
REFERENCE
REMAINS THE
THEN FUNCTION FROM CELL (C1) IS COPY TO CELL
(D3).
FROM (C1) TO (D3),THEN THE ABSOLUTE REFERENCE
REMAINS THE SAME(A1,B1).$ IS USED FOR CONSTANT
ROW OR COLUMN.
24-06-2014MS EXCEL
18

CELL REFERENCING
THEN FUNCTION FROM CELL (C1) IS COPY TO CELL
(D3).
FROM (C1) TO (D3),THEN ROW REFERENCE IS
CHANGED(FROM 1 TO 3) BUT COLUMN REFERENCE
REMAINS SAME(A,B).
A MIXED
REFERENCE
HAS EITHER AN
ABSOLUTE
COLUMN AND
RELATIVE ROW
OR ABSOLUTE
ROW AND
RELATIVE
COLUMN. AN
ABSOLUTE
COLUMN
REFERENCE
TAKES THE
FORM $A1,
$B1.AN
ABSOLUTE ROW
REFERENCE
24-06-2014MS EXCEL
19

=
=
=
=
=
=
FUNCTIONS
SYNTAX OF DATEDIF
=DATEDIF(START_DATE,END_DATE,”INTERVAL”)
START DATE-
Date from which u want to
calculate difference.
END DATE-
Date up to which u want to
INTERVAL-
Form in which u want to
This says that I
am 19 years 6
months & 18
days old
“ D ” - D AY S
“ M ” - M O N T H S
“ Y ” - Y E A R S
“ Y M ” - M O N T H S O V E R Y E A R
“ M D ” - D AY S O V E R M O N T H
24-06-2014MS EXCEL
20

FUNCTIONS
SYNTAX OF SUMIF
=SUMIF(RANGE,CRITERIA,SUM_RANGE)
RANGE-
Range of cells on which conditions
are applied.
CRITERIA-
Condition that defines which cell or
cells will be added.
SUM RANGE-
Actual cells to sum.
NOTE:-
If sum range is not used then range
is used for sum.
WITHOUT
SUM_RANGE
=
=
24-06-2014MS EXCEL
21

FUNCTIONS
SYNTAX OF IF
=IF(LOGICAL TEXT, VALUE IF TRUE, VALUE IF FALSE)
LOGICAL TEXT-
Any value or expression that can be
evaluated to TRUE or FALSE.
VALUE IF TRUE-
Value that is returned if logical text
is TRUE.
VALUE IF FALSE-
Value that is returned if logical text
is FALSE.
IN COLUMN B DIFFERENT CONDITIONS ARE
USED AND BASED ON THIS, IN COLUMN C
DIFFERENT RESULTS ARE SHOWN.
=
=
=
=
=
=
=
24-06-2014MS EXCEL
22

COUNT FUNCTIONS
SYNTAX OF
FUNCTIONS
1. COUNT
=COUNT(VALUE1,VALUE2,…)
2. COUNTA
=COUNTA(VALUE1,VALUE2,…)
3. COUNTBLANK
=COUNTBLANK(RANGE)
4. COUNTIF
=COUNTIF(RANGE,CRITERIA)
COUNT ONLY
CELLS THAT
CONTAINS
NUMBER.
COUNT
CELLS THAT
ARE NOT
EMPTY.
COUNT
CELLS THAT
ARE BLANK.
COUNT NO. OF
CELLS THAT
MEET GIVEN
CONDITION.
1. 2. 3. 4.
=
=
=
=
24-06-2014MS EXCEL
23

TEXT FUNCTIONS
SYNTAX OF
FUNCTIONS
1. LOWER FUNCTION
=LOWER(TEXT)
2. UPPER FUNCTION
=UPPER(TEXT)
3. PROPER FUNCTION
=PROPER(TEXT)
TO CONVERT
TEXT FROM
CAPITAL TO
SMALL.
TO CAPITALISED
EACH WORD OF
TEXT.
TO CONVERT
TEXT FROM
SMALL TO
CAPITAL.
1. 2. 3.
24-06-2014MS EXCEL
24

TEXT FUNCTIONS
=LEFT(An ,3) =RIGHT(An ,3) =MID(An ,2,3)
SYNTAX OF FUNCTIONS
1. LEFT FUNCTION
=LEFT(TEXT,NUM_CHARS)
2. RIGHT FUNCTION
=RIGHT(TEXT,NUM_CHARS)
3. MID FUNCTION
=MID(TEXT,STARTNUM,NUM_
CHAR)
RETURN
SPECIFIED NO.
OF CHARACTER
FROM START OF
TEXT.
RETURN
SPECIFIED NO.
OF CHRACTER
FROM END OF
TEXT.
RETURN
CHARACTER
FROM MIDDLE OF
TEXT,GIVEN A
STARTING
POSITION.
1. 2. 3.
24-06-2014MS EXCEL
25

24-06-2014MS EXCEL
26
OTHER FUNCTIONS
=
=
NOW RETURNS CURRENT DATE AND
TIME.
TODAY RETURNS CURRENT DATE ONLY.
MOD RETURNS THE REMAINDER AFTER
A NO.
IS DIVIDED BY A DIVISOR.
LEN RETURNS THE NO. OF
CHARACTERS IN A
TEXT STRING.
USES OF FUNCTIONS
=
=
=

24-06-2014MS EXCEL
27
SHOW ARROW THAT INDICATE
WHAT CELLS AFFECT THE VALUE
OF THE CURRENTLY SELECTED
CELL.
IN THIS EXAMPLE CELLS A1 & A3
AFFECT THE VALUE OF CELL C2 &
CELLS A1 & A4 AFFECT THE
VALUE OF CELL C6.
SHOW ARROW THAT INDICATE
WHAT CELLS ARE AFFECTED BY
THE VALUE OF THE CURRENTLY
SELECTED CELL.
IN THIS EXAMPLE CELL C2 & C6
ARE AFFECTED BY THE VALUE
OF CELL A2 & CELL C6 IS ALSO
AFFECTED BY THE CELL A4.
TRACE
PRECEDENTS
TRACE
DEPENDENTS
FUNCTION AUDITING

SHORTCUTS
PARTICULARS
 EDIT THE ACTIVE CELL
 CREATE A CHART
 INSERT CELL COMMENT
 FUNCTION DIALOGUE BOX
 INSERT A NEW WORKSHEET
 NAME MANAGER DIALOGUE
BOX
 VISUAL BASIC EDITOR
 MACRO DIALOGUE BOX
 HIDE THE SELECTED COLUMNS
 UNHIDE THE COLUMNS
 HIDE THE SELECTED ROWS
 UNHIDE THE ROWS
KEYS
F2
F11
SHIFT + F2
SHIFT + F3
SHIFT + F11
CTRL + F3
ALT + F11
ALT + F8
CTRL + 0
CTRL + SHIFT +
0
CTRL + 9
CTRL + SHIFT +
24-06-2014MS EXCEL
28

SHORTCUTS
PARTICULARS
 DOWN FILL
 RIGHT FILL
 ENTER SUM FUNCTION IN CELL
 EURO SYMBOL
 CENT SYMBOL
 POUND SYMBOL
 YEN SYMBOL
 ENTER NEW LINE IN ACTIVE
CELL
 CURRENT DATE
 CURRENT TIME
 SHOW FORMULA
 SELECT ENTIRE COLUMN
KEYS
CTRL + D
CTRL + R
ALT + =
ALT + 0128
ALT + 0162
ALT + 0163
ALT + 0165
ALT + ENTER
CTRL + ;
CTRL + SHIFT + ;
CTRL + `
CTRL +
SPACEBAR
24-06-2014MS EXCEL
29

SHORTCUTS
PARTICULARS
 APPLIES NUMBER FORMAT
 APPLIES CURRENCY FORMAT
 APPLIES PERCENTAGE FORMAT
 APPLIES EXPONENTIAL
FORMAT
 APPLIES GENERAL NO. FORMAT
 APPLIES TIME FORMAT
 APPLIES DATE FORMAT
 APPLIES OUTLINE BORDER
 REMOVE OUTLINE BORDER
KEYS
CTRL + SHIFT + !
CTRL + SHIFT + $
CTRL + SHIFT +
%
CTRL + SHIFT + ^
CTRL + SHIFT + ~
CTRL + SHIFT +
@
CTRL + SHIFT + #
CTRL + SHIFT + &
CTRL + SHIFT + _
24-06-2014MS EXCEL
30

Basics of Ms Excel Office Tool

Recommended

More Related Content

What's hot (20)

Viewers also liked (20)

Similar to Basics of Ms Excel Office Tool (20)

More from Subodh Shahare(सुबोध शहारे) (11)

Recently uploaded (20)

Basics of Ms Excel Office Tool