Ms Excel
Ms Excel
Ms Excel
MS EXCEL 1/19/2013 1
INDEX
INTRODUCTION TO EXCEL…………………………………...
OVERVIEW OF EXCEL…………………………………………...
OFFICE BUTTON………………………………………..........
RIBBONS……………………………………………………………….
WORKING WITH CELLS……………………………………....
FORMATTING TEXT……………………………………………...
CONDITIONAL FORMATTING…………………………….....
TO INSERT ROWS & COLUMNS…………………………....
EDITING – FILL……………………………………………………….
SORTING………………………………………………………….....
CELL REFERENCING……………………………………........
FUNCTIONS……………………………………………………..….
FUNCTION AUDITING…………………………………………...
SHORTCUT KEYS………………………………………..........
MS EXCEL 1/19/2013 2
INTRODUCTION TO
MS-EXCEL
Excel is a computer program used to create electronic
spreadsheets.
SAVE-TO SAVE A
DOCUMENT. (CTRL+S)
MS EXCEL 1/19/2013 5
THE THREE PARTS
RIBBONS OF THE RIBBON
TABS ARE
GROUPS
COMMANDS
MS EXCEL 1/19/2013 9
FORMATTING TEXT
MS EXCEL 1/19/2013 10
FORMATTING TEXT
MS EXCEL 1/19/2013 11
CONDITIONAL FORMATTING
MS EXCEL 1/19/2013 12
CONDITIONAL FORMATTING
MS EXCEL 1/19/2013 13
TO INSERT ROWS & COLOUMS
NOTE:
1. The new row always
appears above the
selected row.
MS EXCEL 1/19/2013 14
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.
MS EXCEL 1/19/2013 16
Relative Cell References
• Calling cells by just their column and row labels (such as
’A1’) is called a relative cell reference. When formulas or
functions contain relative cell referencing and they are
copied from one cell to another, Excel does not create an
exact copy of the formula or function. Instead, Excel
changes the cell addresses relative to the row and column
they are moved to.
ˆEXAMPLE: If a simple addition formula of ’=A1+B1’ in cell
C1 is copied to cell C2, the formula would change to
’=A2+B2’ to reflect the new row.
ˆEXAMPLE: If a simple average function of
’=AVERAGE(B2:B5)’ in cell B6 is copied to cell C6, the
function would change to ’=AVERAGE(C2:C5)’ to reflect
the new column.
CELL REFERENCING
A RELATIVE
CELL
REFERENCE AS
(A1) IS BASED
ON THE
RELATIVE
POSITION OF
IN CELL (C1) SUM FUNCTION IS USED. THE CELL. IF
THEN FUNCTION FROM CELL (C1) IS COPY TO CELL THE POSITION
(D3). WHEN THE POSITION OF THE CELL IS CHANGED
OF THE CELL
FROM (C1) TO (D3),THEN THE REFERENCE IS ALSO
CHANGED FROM (A1,B1) TO (B3,C3). THAT
CONTAINS THE
REFERENCE
CHANGES, THE
REFERENCE
ITSELF IS
CHANGED.
MS EXCEL 1/19/2013 18
Absolute Cell References
A MIXED
REFERENCE HAS
EITHER AN
ABSOLUTE
COLUMN AND
RELATIVE ROW OR
IN CELL (C1) SUM FUNCTION IS USED. ABSOLUTE ROW
THEN FUNCTION FROM CELL (C1) IS COPY TO CELL (D3). AND RELATIVE
WHEN THE POSITION OF THE CELL IS CHANGED FROM COLUMN. AN
(C1) TO (D3),THEN ROW REFERENCE IS CHANGED(FROM 1 ABSOLUTE
TO 3) BUT COLUMN REFERENCE REMAINS SAME(A,B). COLUMN
REFERENCE TAKES
THE FORM $A1,
$B1.AN
ABSOLUTE ROW
REFERENCE
A$1,
TAKES THEB$1.
FORM
MS EXCEL 1/19/2013 23
Functions are pre
-defined formulas that
come with Excel.
Entering Functions
Functions differ from regular formulas in that, after the
• equal sign, you supply the cell addresses but not
• the arithmetic operators. Functions perform calculations
by using specific values, called arguments, in a
• particular order called syntax. When using a
function, remember the following:
2. Union:
ˆ Includes two or more references.
ˆ Consists of two or more cell addresses separated by a comma.
ˆ EXAMPLE: ’A7, B8, C9’ refers to cells A7, B8, and C9.
ˆ EXAMPLE: ’A7, B8:D9, E4’ refers to cells A7, B8, B9, C8, C9, D8, D9 and E4.
Autosum
You can use the Autosum icon on the standard toolbar, which
automatically adds the contents of a cluster
of adjacent cells.
ˆ Select the cell that the sum will appear in that is outside
the cluster of
cells whose values will be added.
ˆ Click the Autosum button (Greek letter sigma, Σ).
ˆ Highlight the group of cells that will be summed.
ˆ Press the Enter key on the keyboard or click the green
check mark on the
formula bar.
Function Wizard
You can access all of the available functions in Excel using the Function
Wizard.
ˆSelect the cell where the function will be placed and click the Function
Wizard button on the standard
toolbar.
ˆ Other ways of starting the Function Wizard are:
– Select Function from the Insert drop menu.
– Click on the drop down arrow next to the Autosum icon button.
*You will first see the commonly used functions in Excel, and at the
bottom of the menu, the
More Functions option.
* Clicking on More Functions will give you an alphabetical and
categorical
listing of all available
functions in Excel.
List of functions
1. IF Functions
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
“ D ” - D AY S
calculate difference.
“M”- MONTH
S
“Y” - Y E A R S This says that I
“YM”- MONT OVER YEAR am 19 years 6
HS months & 18
“ M D ” - D AY S O E R M O N T H MS EXCEL
days old
1/19/2013 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
MS EXCEL 1/19/2013 40
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.
4. COUNTIF
=COUNTIF(RANGE,CRITERIA)
1. 2. 3. 4.
COUNT COUNT NO. OF
COUNT CELLS COUNT CELLS
ONLY CELLS CELLS THAT
THAT ARE THAT ARE
THAT MEET GIVEN
NOT EMPTY. BLANK.
CONTAINS CONDITION.
NUMBER.
MS EXCEL 1/19/2013 42
TEXT FUNCTIONS
SYNTAX OF FUNCTIONS
1. LOWER FUNCTION
=LOWER(TEXT)
2. UPPER FUNCTION
=UPPER(TEXT)
3. PROPER FUNCTION
=PROPER(TEXT)
1. 2. 3.
MS EXCEL 1/19/2013 43
TEXT FUNCTIONS
SYNTAX OF FUNCTIONS
1. LEFT FUNCTION
=LEFT(An ,3) =RIGHT(An ,3) =MID(An ,2,3) =LEFT(TEXT,NUM_CHARS)
2. RIGHT FUNCTION
=RIGHT(TEXT,NUM_CHARS)
3. MID FUNCTION
=MID(TEXT,STARTNUM,NUM_CHAR)
1. 2. 3.
USES OF FUNCTIONS
Specifically , in
many cases, a function will simplify formulas that you can type in manually, such as average or sum.
ˆ EXAMPLE: If you wanted to add the values of cells D1 through D10, you could type the formula
The following table contains the definitions and examples of several other available functions:
Functio
Example Description
n
SUM =SUM(A1:A100) Finds the sum of cells A1 through A100.
AVERAG =AVERAGE(B1:B10) Finds the average of the cells B1 through B10.
E MAX =MAX(C1:C100) Returns the highest number from cells C1 through
C100.
MIN =MIN(D1:D100) Returns the lowest number from cells D1 through D100.
SQRT =SQRT(D10) Finds the square root of the value in D10.
TODAY =TODAY() Returns the current date (leave the parentheses empty).
There are several different ways
exist to copy and paste a formula or
a function:
MS EXCEL 1/19/2013 52
Introduction to Formulas
Formulas in Microsoft Excel begin with an
equal sign. The equal sign tells Excel
that the succeeding characters constitute
a formula. If you don't enter the equal
sign, Excel will treat your entry as text
and the calculation will fail.
Entering
Formulas
After the equal sign, a formula includes the addresses of the cells whose values will
be manipulated with appropriate operands placed in between. The operands are the
standard arithmetic operators:
A7/A9
(ˆ ) Exponents =A7ˆA9
You can also enter formulas by using the point mode, where you either
click on a cell with your left mouse button or you use the arrow keys.
When you are working with a large spreadsheet you can name a
range of cells and use the name in formulas
or functions.
ˆTo name a range, select the cells to be included in the range, and
then type a name in the Name Box located above the worksheet.
ˆRecall, the Name Box normally contains the address if the current
cell.
Linking Worksheets