presented by
s.kowsar
M.C.A
24/06/2025 MS EXCEL 1
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
24/06/2025 of other MS programs.
MS EXCEL 2
Introduction of ms excel
Entering data is easier and faster
Making changes is simpler
It helps you organize data , perform
calculations and analyze the result.
It can faster, more efficient and produce less
errors than manual worksheets.
If a particular value changed, its effect on
other values is automatically displayed
24/06/2025 MS EXCEL 3
Applications of excel
Forecasting
Banking
Budgets
Sales
Income tax statements
Annual reports of business firms
Marketing
Accounting
Hotel management
Inventory Control
Budgets
Sales
Income tax statements
Payroll
Invoice
Financial analysis
24/06/2025 MS EXCEL 4
Statrting a excel
Maximize Button
24/06/2025 MS EXCEL 5
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.
24/06/2025 MS EXCEL You can use the scrolling buttons
6
OFFICE BUTTON 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/2025 MS EXCEL 7
Types of data
In each cell there may be the following data types:
Labels -- (text with no numerical value) 32,000
characters can used to enter
Number data (constant values)
Formulas (mathematical equation used to calculate)
Data Types Examples Descriptions
Name or Wage anything that is
LABEL
or Days just text
5 or 3.75 or -
CONSTANT any number
7.4
=5+3 or =
FORMULA math equation
8*5+3
Constants
Constants are
FIXED number data
Constants may refer
to dollars,
percentages, or
number of items (in
this case number of
hours worked within
a certain pay
period).
Formulas
Formulas are math
equations that CALCULATE
a value to be displayed.
DO NOT type in the
numbers; type in the
equation.
It is BEST to Reference as
much data as possible as
opposed to typing data into
equations.
That way when OTHER
information changes, we
DO-NOT have to change
the equations or type in
information again.
THE THREE
RIBBON S PARTS OF THE
TABS RIBBON ARE
GROUPS
COMMANDS
1 TABS:THERE ARE SEVEN TABS ACROSS THE TOP OF THE
EXCEL WINDOW.
GROUPS: GROUPS ARE SETS OF RELATED
2 COMMANDS,DISPLAYED ON TABS.
COMMANDS: A COMMAND IS A BUTTON,A MENU
3 OR A BOX WHERE YOU ENTER INFORMATION.
24/06/2025 MS EXCEL 11
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/2025 MS EXCEL 12
FORMATTING TEXT
TO FORMAT TEXT IN BOLD, TO CHANGE THE FONT STYLE:
ITALICS OR UNDERLINE: Select the cell or cells you want to format.
Left-click a cell to select it or drag your Left-click the drop-down arrow next to the
cursor over the text in the formula bar to Font Style box on the Home tab.
select it. Select a font style from the list.
Click the Bold, Italics or underline
command.
24/06/2025 MS EXCEL 13
FORMATTING TEXT
TO CHANGE THE FONT SIZE: TO ADD A BORDER:
Select the cell or cells you want to format. Select the cell or cells you want to format.
Left-click the drop-down arrow next to the Click the drop-down arrow next to the
Font Size box on the Home tab. Borders command on the Home tab. A
Select a font size from the list. menu will appear with border options.
24/06/2025 MS EXCEL 14
FORMATTING TEXT
TO CHANGE THE TEXT COLOUR: TO ADD A FILL COLOUR:
Select the cell or cells you want to format. Select the cell or cells you want to
Left-click the drop-down arrow next to the Text format.
Color command. A color palette will appear. Click the Fill command. A color palette
Select a color from the palette. will appear.
Select a color from the palette.
24/06/2025 MS EXCEL 15
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/2025 MS EXCEL 16
CONDITIONAL FORMATTING
TO APPLY NEW TO MANAGE CONDITIONAL FORMATTING:
FORMATTING: Click the Conditional Formatting command.
Click the Conditional Select Manage Rules from the menu. The
Formatting command. Select Conditional Formatting Rules Manager dialog box
New Rules from the menu. will appear. From here you can edit a rule, delete a
There are different rules, you rule, or change the order of rules.
can apply these rules to
differentiate
24/06/2025 particular cell.
MS EXCEL 17
TO INSERT ROWS & COLOUMS
NOTE:
1. The new row always
appears above the selected
row.
2. The new column
always appears to the
left of the selected
column.
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.
24/06/2025 MS EXCEL 18
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,
24/06/2025 TIMES, DAYS OF THE WEEK, MONTHS.
MS EXCEL 19
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/2025 MS EXCEL 20
Pivot Table
A Pivot table lets your arrange, sort, and filter a set of
data on the fly so you can analyze it from different
perspectives with minimum effort.
Start with a data list with a few columns
Make sure each of the rows have a value of each one of the
columns
CELL REFERENCING
RELATIVE REFERENCE A RELATIVE
CELL
REFERENCE
AS (A1) IS
BASED ON
THE
RELATIVE
POSITION OF
IN CELL (C1) SUM FUNCTION IS USED.
THEN FUNCTION FROM CELL (C1) IS COPY TO CELL
THE CELL. IF
(D3). THE POSITION
WHEN THE POSITION OF THE CELL IS CHANGED OF THE CELL
FROM (C1) TO (D3),THEN THE REFERENCE IS ALSO THAT
CHANGED FROM (A1,B1) TO (B3,C3). CONTAINS
THE
REFERENCE
CHANGES,
THE
REFERENCE
ITSELF IS
24/06/2025 MS EXCEL CHANGED. 22
CELL REFERENCING
ABSOLUTE REFERENCE AN
ABSOLUTE
CELL
REFERENCE
AS ($A$1)
ALWAYS
REFERS TO A
IN CELL (C1) SUM FUNCTION IS USED.
THEN FUNCTION FROM CELL (C1) IS COPY TO CELL CELL IN A
(D3). SPECIFIC
WHEN THE POSITION OF THE CELL IS CHANGED LOCATION. IF
FROM (C1) TO (D3),THEN THE ABSOLUTE REFERENCE
THE
REMAINS THE SAME(A1,B1).$ IS USED FOR
CONSTANT ROW OR COLUMN. POSITION OF
THE CELL
THAT
CONTAINS
THE
FORMULA
24/06/2025 MS EXCEL CHANGES, 23
CELL REFERENCING
MIXED REFERENCE A MIXED
REFERENCE
HAS EITHER AN
ABSOLUTE
COLUMN AND
RELATIVE ROW
OR ABSOLUTE
IN CELL (C1) SUM FUNCTION IS USED. ROW AND
THEN FUNCTION FROM CELL (C1) IS COPY TO CELL RELATIVE
(D3). COLUMN. AN
WHEN THE POSITION OF THE CELL IS CHANGED ABSOLUTE
FROM (C1) TO (D3),THEN ROW REFERENCE IS
COLUMN
CHANGED(FROM 1 TO 3) BUT COLUMN REFERENCE
REMAINS SAME(A,B). REFERENCE
TAKES THE
FORM $A1,
$B1.AN
ABSOLUTE ROW
REFERENCE
TAKES THE
24/06/2025 MS EXCEL 24
FORM A$1, B$1.
FUNCTIONS
DATEDIF FUNCTION 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
= calculate difference.
=
INTERVAL-
=
Form in which u want to
=
calculate difference.
“ D ” - D AY S This says
“M”- MONTHS that I am 19
“Y”- YEARS years 6
“YM”- MONTHS OVER YEAR months & 18
“ M D ” - D AY S OV E R M O N T H days old
24/06/2025 MS EXCEL 25
Basic Math Functions
Math functions built into them. Of the most
basic operations are the standard multiply,
divide, add and subtract.
SUM Function
Definition: Tips:
Probably the most Blank cells will return a
popular function in any value of zero to be added
spreadsheet is the SUM to the total.
function. The Sum Text cells can not be
function takes all of the added to a number and
values in each of the will produce an error.
specified cells and totals
their values.
The syntax is:
=SUM(first value,
second value, etc)
Sum function
IF Function
Definition: Tips:
The IF function will Until you are used to
check the logical writing them, test them
condition of a statement out on multiple cells.
and return one value if There are multiple ways
true and a different to write an IF statement
value if false. to get the same result
The syntax is:
=IF (condition, value-if-
true, value-if-false)
IF Function
IF Functions are like
programing - they
provide multiple answers
based on certain
conditions.
FUNCTIONS
IF FUNCTION 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/2025 MS EXCEL 31
FUNCTIONS
SUMIF FUNCTION 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
WITHOUT WITH range is used for sum.
SUM_RANG SUM_RANG
E E
24/06/2025 MS EXCEL 32
COUNT FUNCTIONS
SYNTAX OF
FUNCTIONS
1. COUNT
=
= =COUNT(VALUE1,VALUE2,
= …)
=
2. COUNTA
=COUNTA(VALUE1,VALUE2,
…)
1. 2. 3. 3.
COUNTBLANK
4. =COUNTBLANK(RANGE)
COUNT COUNT NO.
COUNT ONLY COUNT
CELLS OF CELLS
CELLS THAT 4. CELLS COUNTIF
THAT ARE THAT MEET
CONTAINS THAT ARE
NOT GIVEN
NUMBER. BLANK.
=COUNTIF(RANGE,CRITERI
EMPTY. CONDITION.
24/06/2025 MS EXCEL A) 33
TEXT FUNCTIONS
SYNTAX OF
FUNCTIONS
1. LOWER
FUNCTION
=LOWER(TEXT)
2. UPPER
FUNCTION
=UPPER(TEXT)
1. 2. 3. PROPER
3. FUNCTION
TO CONVERT =PROPER(TEXT)
TO CONVERT TO CAPITALISED
TEXT FROM TEXT FROM
EACH WORD OF
CAPITAL TO SMALL TO
TEXT.
SMALL. CAPITAL.
24/06/2025 MS EXCEL 34
TEXT FUNCTIONS
SYNTAX OF
FUNCTIONS
=LEFT(An ,3) =RIGHT(An ,3) =MID(An ,2,3)
1. LEFT FUNCTION
=LEFT(TEXT,NUM_CHARS)
2. RIGHT FUNCTION
=RIGHT(TEXT,NUM_CHAR
S)
1. 2. 3. MID FUNCTION
3. RETURN
RETURN RETURN =MID(TEXT,STARTNUM,N
CHARACTER
SPECIFIED NO. SPECIFIED NO. UM_CHAR)
FROM MIDDLE
OF CHARACTER OF CHRACTER
OF TEXT,GIVEN
FROM START OF FROM END OF
A STARTING
TEXT. TEXT.
24/06/2025 MS EXCEL
POSITION. 35
OTHER FUNCTIONS
USES OF
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.
24/06/2025 MS EXCEL 36
FUNCTION AUDITING
TRACE SHOW ARROW THAT INDICATE
PRECEDENTS 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
TRACE C6.
SHOW ARROW THAT
INDICATE WHAT CELLS ARE
DEPENDENTS AFFECTED BY THE VALUE OF
THE CURRENTLY SELECTED
CELL.
IN THIS EXAMPLE CELL C2 &
C6 ARE AFFECTED BY THE
24/06/2025 MS EXCEL VALUE OF CELL A2 & CELL37
Create a chart
Series 1 Series 2 Series 3
Category 1 4.3 2.4 2
Category 2 2.5 4.4 2
Category 3 3.5 1.8 3
Category 4 4.5 2.8 5
select the cell containing the data you want to
display in a chart ,including the row and
column labels.
Click the insert menu and go to the chart option
then select a chart type and select type ok.
24/06/2025 MS EXCEL 38
Create a chart
Category 4
Category 3
Series 3
Series 2
Category 2 Series 1
Category 1
0 0.5 1 1.5 2 2.5 3 3.5 4 4.5 5
24/06/2025 MS EXCEL 39
SHORTCUT KEYS
PARTICULARS KEYS
F2
EDIT THE ACTIVE CELL F11
SHIFT + F2
CREATE A CHART SHIFT + F3
INSERT CELL COMMENT SHIFT + F11
CTRL + F3
FUNCTION DIALOGUE BOX ALT + F11
INSERT A NEW WORKSHEET ALT + F8
CTRL + 0
NAME MANAGER DIALOGUE CTRL + SHIFT
BOX
24/06/2025 MS EXCEL 40
SHORTCUT KEYS
PARTICULARS KEYS
CTRL + D
DOWN FILL CTRL + R
RIGHT FILL ALT + =
ENTER SUM FUNCTION IN ALT + 0128
CELL ALT + 0162
EURO SYMBOL ALT + 0163
CENT SYMBOL ALT + 0165
POUND SYMBOL ALT + ENTER
YEN SYMBOL CTRL + ;
ENTER NEW LINE IN CTRL + SHIFT
ACTIVE CELL +;
CURRENT DATE CTRL + `
CTRL +
24/06/2025 CURRENT TIME
MS EXCEL 41
SHORTCUT KEYS
PARTICULARS KEYS
CTRL + SHIFT
APPLIES NUMBER FORMAT +!
APPLIES CURRENCY CTRL + SHIFT
FORMAT +$
APPLIES PERCENTAGE CTRL + SHIFT
FORMAT +%
APPLIES EXPONENTIAL CTRL + SHIFT
FORMAT +^
APPLIES GENERAL NO. CTRL + SHIFT
FORMAT +~
APPLIES TIME FORMAT CTRL + SHIFT
APPLIES DATE FORMAT +@
CTRL + SHIFT
24/06/2025 APPLIESMS
OUTLINE
EXCEL BORDER 42
Thank you
24/06/2025 MS EXCEL 43
