MICROSOFT EXCEL

PRESENTER:
FRELYN MAE D. CASINAY

MS EXCEL 1/19/2013 1
 INTRODUCTION TO MS-EXCEL

 Excel is a spreadsheet program that allows you

to store, organize, and analyze information.

 Excels operates like other Microsoft(MS) office programs and

has many of the same functions and shortcuts of other MS
programs.

2
The Excel Start Screen
From the Excel Start Screen, locate and select Blank workbook to access
the Excel interface.
 The parts of the Excel window
Some parts of the Excel window (like the Ribbon and scroll bars) are standard in most other
Microsoft programs. However, there are other features that are more specific to spreadsheets, such
as the formula bar, name box, and worksheet tabs.
 Excel uses a tabbed Ribbon system instead of traditional
The Ribbon menus. The Ribbon contains multiple tabs, each with
several groups of commands. You will use these tabs to perform
the most common tasks in Excel.

• Each tab will have

one or more groups.

• Some groups will have

an arrow you can click
for more options.

• Click a tab to see more

commands.

• You can adjust how

the Ribbon is
displayed with the
Ribbon Display
Options.
 Description Excel Shortcuts 

Workbook Close a workbook. Ctrl+W

Open a workbook. Ctrl+O
Shortcut
Go to the Home tab. Alt+H
Keys Save a workbook. Ctrl+S
Copy selection. Ctrl+C
Paste selection. Ctrl+V
Undo recent action. Ctrl+Z
Remove cell contents. Delete
Choose a fill color. Alt+H, H
Cut selection. Ctrl+X
 Description Excel Shortcuts 

Workbook Apply bold formatting. Ctrl+B

Center align cell contents. Alt+H, A, C
Shortcut
Go to the Page Layout tab. Alt+P
Keys Go to the Data tab. Alt+A
Go to the View tab. Alt+W
Open the context menu. Shift+F10 or
Windows Menu key
Add borders. Alt+H, B
Delete column. Alt+H, D, C
Go to the Formula tab. Alt+M
Hide the selected rows. Ctrl+9
 Description Excel Shortcuts 
Use the Move to the Tell me or Search field on the ribbon and type a Alt+Q, then enter the
Access search term for assistance or Help content. search term.
keys for Open the File menu. Alt+F
ribbon Open the Home tab and format text and numbers and use the Alt+H
tabs Find tool.

Open the Insert tab and insert PivotTables, charts, add-ins, Alt+N

Sparklines, pictures, shapes, headers, or text boxes.

Open the Page Layout tab and work with themes, page setup, Alt+P
scale, and alignment.

Open the Formulas tab and insert, trace, and customize Alt+M

functions and calculations.

Open the Data tab and connect to, sort, filter, analyze, and work Alt+A
with data.

Open the Review tab and check spelling, add notes and Alt+R

threaded comments, and protect sheets and workbooks.

Open the View tab and preview page breaks and layouts, show Alt+W
and hide gridlines and headings, set zoom magnification,
manage windows and panes, and view macros.

Move to the Tell me or Search field on the ribbon and type a Alt+Q, then enter the
search term for assistance or Help content. search term.
 Description Excel Shortcuts 
Work in Select the active tab on the ribbon and activate the Alt or F10. To move to a different
access keys. tab, use access keys or the
the arrow keys.
ribbon Move the focus to commands on the ribbon. Tab key or Shift+Tab
with the Move down, up, left, or right, respectively, among the Arrow keys
keyboard items on the ribbon.

Show the tooltip for the ribbon element currently in Ctrl+Shift+F10

focus.
Activate a selected button. Spacebar or Enter

Open the list for a selected command. Down arrow key

Open the menu for a selected button. Alt+Down arrow key

When a menu or submenu is open, move to the next Down arrow key
command.

Expand or collapse the ribbon. Ctrl+F1

Open a context menu. Shift+F10
Or, on a Windows keyboard, the
Windows Menu key (usually
between the Alt Gr and right Ctrl
keys)
Keybo Description Excel Shortcuts 
ard Move to the previous cell in a worksheet or the previous
option in a dialog box.
Shift+Tab

shortc Move one cell up in a worksheet. Up arrow key

uts for Move one cell down in a worksheet. Down arrow key

naviga
ting in Move one cell left in a worksheet. Left arrow key

cells Move one cell right in a worksheet. Right arrow key

Move to the edge of the current data region in a worksheet. Ctrl+Arrow key

Enter the End mode, move to the next nonblank cell in the End, Arrow key
same column or row as the active cell, and turn off End mode.
If the cells are blank, move to the last cell in the row or
column.
Move to the last cell on a worksheet, to the lowest used row of Ctrl+End
the rightmost used column.

Extend the selection of cells to the last used cell on the Ctrl+Shift+End
worksheet (lower-right corner).
Move to the cell in the upper-left corner of the window when Home+Scroll lock
Scroll lock is turned on.
Keybo Description Excel Shortcuts 
Move to the beginning of a worksheet. Ctrl+Home
ard
shortc Move one screen down in a worksheet. Page down
uts for Move to the next sheet in a workbook. Ctrl+Page down
naviga
ting in Move one screen to the right in a worksheet. Alt+Page down

cells Move one screen up in a worksheet. Page up

Move one screen to the left in a worksheet. Alt+Page up

Move to the previous sheet in a workbook. Ctrl+Page up

Move one cell to the right in a worksheet. Or, in a Tab key

protected worksheet, move between unlocked cells.

Open the list of validation choices on a cell that has Alt+Down arrow key
data validation option applied to it.
Cycle through floating shapes, such as text boxes or Ctrl+Alt+5, then the Tab key
images. repeatedly
Keybo Description Excel Shortcuts 
ard Exit the floating shape navigation and Esc
return to the normal navigation.
shortc Scroll horizontally. Ctrl+Shift, then scroll your mouse
uts for wheel up to go left, down to go
right
naviga Zoom in. Ctrl+Alt+Equal sign ( = )
ting in
cells  Zoom out. Ctrl+Alt+Minus sign (-)
 Description Excel Shortcuts 
Cell 11. Hide the selected rows. Ctrl+9

Formatting 12. To copy and paste cells Ctrl + C, Ctrl + V

Shortcut
13. To italicize and make the font bold Ctrl + I, Ctrl + B
Keys
14. To center align cell contents Alt + H + A + C

15. To fill color Alt + H + H

16. To add a border Alt + H + B

17. To remove outline border Ctrl + Shift + _

18. To add an outline to the select cells Ctrl + Shift + &

19. To move to the next cell Tab

20. To move to the previous cell Shift + Tab

 Description Excel Shortcuts 
Cell 21. To select all the cells on the right Ctrl + Shift + Right arrow
Formatting 22. To select all the cells on the left Ctrl + Shift + Left Arrow
Shortcut
23. To select the column from the
Keys selected cell to the end of the table
Ctrl + Shift + Down Arrow

24. To select all the cells above the

Ctrl + Shift + Up Arrow
selected cell

25. To select all the cells below the

Ctrl + Shift + Down Arrow
selected cell

26. Hide the selected columns. Ctrl+0

27. To delete a cell comment Shift + F10 + D

28. To display find and replace Ctrl + H

Ctrl + Shift + L
29. To activate the filter
Alt + Down Arrow

30. To insert the current date Ctrl + ;

Cell Formatting Shortcut Keys

Description Excel Shortcuts 

31. To insert current time Ctrl + Shift + :

32. To insert a hyperlink Ctrl + k

33. To apply the currency format Ctrl + Shift + $

34. To apply the percent format Ctrl + Shift + %

35. To go to the “Tell me what you want to do” box Alt + Q

 Description Excel Shortcuts 
Row and
36. To select the entire row Shift + Space
Column
Formatting
37. To select the entire column Ctrl + Space

Shortcut 38. To delete a column Alt+H+D+C

Keys 39. To delete a row Shift + Space, Ctrl + - 

40. To hide selected row Ctrl + 9

41. To unhide selected row Ctrl + Shift + 9

42. To hide a selected column Ctrl + 0

43. To unhide a selected column/ To undo Ctrl + Z

44. To group rows or columns Alt + Shift + Right arrow

45. To ungroup rows or columns Alt + Shift + Left arrow

 Description Excel Shortcuts 
Keyboard shortcuts Open the Format Cells dialog box. Ctrl+1
for formatting cells
Format fonts in the Format Cells dialog box. Ctrl+Shift+F or Ctrl+Shift+P

Edit the active cell and put the insertion point at the F2
end of its contents. Or, if editing is turned off for the
cell, move the insertion point into the formula bar. If
editing a formula, toggle Point mode off or on so
you can use the arrow keys to create a reference.
Insert a note. Shift+F2
Open and edit a cell note. Shift+F2

Insert a threaded comment. Ctrl+Shift+F2

Open and reply to a threaded comment. Ctrl+Shift+F2

Open the Insert dialog box to insert blank cells. Ctrl+Shift+Plus sign (+)

Open the Delete dialog box to delete selected cells. Ctrl+Minus sign (-)

Enter the current time. Ctrl+Shift+Colon (:)

Open the Format Cells dialog box. Ctrl+1

Format fonts in the Format Cells dialog box. Ctrl+Shift+F or Ctrl+Shift+P

 Description Excel Shortcuts 
Keyboard shortcuts Open the Format Cells dialog box. Ctrl+1
for formatting cells
Format fonts in the Format Cells dialog box. Ctrl+Shift+F or Ctrl+Shift+P

Edit the active cell and put the insertion point at the F2
end of its contents. Or, if editing is turned off for the
cell, move the insertion point into the formula bar. If
editing a formula, toggle Point mode off or on so
you can use the arrow keys to create a reference.
Insert a note. Shift+F2
Open and edit a cell note. Shift+F2

Insert a threaded comment. Ctrl+Shift+F2

Open and reply to a threaded comment. Ctrl+Shift+F2

Open the Insert dialog box to insert blank cells. Ctrl+Shift+Plus sign (+)

Open the Delete dialog box to delete selected cells. Ctrl+Minus sign (-)

Enter the current time. Ctrl+Shift+Colon (:)

Enter the current date. Ctrl+Semicolon (;)

Switch between displaying cell values or Ctrl+Grave accent (`)

formulas in the worksheet.
 Description Excel Shortcuts 
Keyboard shortcuts
Copy a formula from the cell above the active cell Ctrl+Apostrophe (')
for formatting cells into the cell or the formula bar.

Move the selected cells. Ctrl+X

Copy the selected cells. Ctrl+C

Paste content at the insertion point, replacing any Ctrl+V

selection.

Open the Paste Special dialog box. Ctrl+Alt+V

Italicize text or remove italic formatting. Ctrl+I or Ctrl+3

Bold text or remove bold formatting. Ctrl+B or Ctrl+2

Underline text or remove underline. Ctrl+U or Ctrl+4

Apply or remove strikethrough formatting. Ctrl+5

 Description Excel Shortcuts 
Keyboard shortcuts
Remove the outline border from the Ctrl+Shift+Underscor
for formatting cells
selected cells. e (_)
Display or hide the outline symbols. Ctrl+8
Use the Fill Down command to copy Ctrl+D
the contents and format of the
topmost cell of a selected range into
the cells below.
Apply the General number format. Ctrl+Shift+Tilde sign
(~)
Apply the Currency format with two Ctrl+Shift+Dollar
decimal places (negative numbers in sign ($)
parentheses).
Apply the Percentage format with no Ctrl+Shift+Percent
decimal places. sign (%)
Apply the Scientific number format Ctrl+Shift+Caret sign
with two decimal places. (^)
Apply the Date format with the day, Ctrl+Shift+Number
month, and year. sign (#)
Apply the Time format with the hour Ctrl+Shift+At sign
and minute, and AM or PM. (@)
Keyboard shortcuts Description Excel Shortcuts 
in the Paste Paste all cell contents and formatting. A
Special 
Paste only the formulas as entered in the F
formula bar.
Paste only the values (not the formulas). V

Paste only the copied formatting. T

Paste only comments and notes attached to C

the cell.
Paste only the data validation settings from N
copied cells.
Paste all cell contents and formatting from H
copied cells.
Paste all cell contents without borders. X

Paste only column widths from copied cells. W

Paste only formulas and number formats R

from copied cells.
Paste only the values (not formulas) and U
number formats from copied cells.
 Description Excel Shortcuts 
Keyboard shortcuts
for making Select the entire worksheet. Ctrl+A or
Ctrl+Shift+Spacebar
selections and
Select the current and next sheet in a Ctrl+Shift+Page down
performing actions workbook.
Select the current and previous sheet in a Ctrl+Shift+Page up
workbook.

Extend the selection of cells by one cell. Shift+Arrow key

Extend the selection of cells to the last Ctrl+Shift+Arrow key

nonblank cell in the same column or row as
the active cell, or if the next cell is blank, to
the next nonblank cell.
Turn extend mode on and use the arrow F8
keys to extend a selection. Press again to
turn off.
Add a non-adjacent cell or range to a Shift+F8
selection of cells by using the arrow keys.
Start a new line in the same cell. Alt+Enter

Fill the selected cell range with the current Ctrl+Enter

entry.
 Description Excel Shortcuts 
Keyboard shortcuts
for making Complete a cell entry and select the cell Shift+Enter
above.
selections and
Select an entire column in a worksheet. Ctrl+Spacebar
performing actions
Select an entire row in a worksheet. Shift+Spacebar

Select all objects on a worksheet when an Ctrl+Shift+Spacebar

object is selected.
Extend the selection of cells to the beginning Ctrl+Shift+Home
of the worksheet.
Select the current region if the worksheet Ctrl+A or
contains data. Press a second time to select Ctrl+Shift+Spacebar
the current region and its summary rows.
Press a third time to select the entire
worksheet.
Select the current region around the active Ctrl+Shift+Asterisk sign
cell. (*)
Select the first command on the menu when Home
a menu or submenu is visible.
Repeat the last command or action, if Ctrl+Y
possible.
Keyboard shortcuts
for making
selections and Description Excel Shortcuts 
performing actions Undo the last action. Ctrl+Z

Expand grouped rows or columns. While hovering over the

collapsed items, press
and hold the Shift key
and scroll down.
Collapse grouped rows or columns. While hovering over the
expanded items, press
and hold the Shift key
and scroll up.
 Description Excel Shortcuts 
Keyboard shortcuts
for working with Turn on or off tooltips for checking formulas Ctrl+Alt+P
directly in the formula bar or in the cell
data, functions, and you’re editing.
the formula bar Edit the active cell and put the insertion F2
point at the end of its contents. Or, if editing
is turned off for the cell, move the insertion
point into the formula bar. If editing a
formula, toggle Point mode off or on so you
can use the arrow keys to create a
reference.
Expand or collapse the formula bar. Ctrl+Shift+U

Cancel an entry in the cell or formula bar. Esc

Complete an entry in the formula bar and Enter

select the cell below.
Move the cursor to the end of the text when Ctrl+End
in the formula bar.
Select all text in the formula bar from the Ctrl+Shift+End
cursor position to the end.
Calculate all worksheets in all open F9
workbooks.
Calculate the active worksheet. Shift+F9
 Description Excel Shortcuts 
Keyboard shortcuts
for working with Calculate all worksheets in all open Ctrl+Alt+F9
workbooks, regardless of whether they have
data, functions, and changed since the last calculation.
the formula bar Check dependent formulas, and then Ctrl+Alt+Shift+F9
calculate all cells in all open workbooks,
including cells not marked as needing to be
calculated.
Display the menu or message for an Error Alt+Shift+F10
Checking button.

Display the Function Arguments dialog box Ctrl+A

when the insertion point is to the right of a
function name in a formula.
Insert argument names and parentheses Ctrl+Shift+A
when the insertion point is to the right of a
function name in a formula.
Insert the AutoSum formula Alt+Equal sign ( = )

Invoke Flash Fill to automatically recognize Ctrl+E

patterns in adjacent columns and fill the
current column
Cycle through all combinations of absolute F4
and relative references in a formula if a cell
reference or range is selected.
Insert a function. Shift+F3
 Description Excel Shortcuts 
Keyboard shortcuts
for working with Copy the value from the cell above the Ctrl+Shift+Straight
active cell into the cell or the formula bar. quotation mark (")
data, functions, and
Create an embedded chart of the data in the Alt+F1
the formula bar current range.
Create a chart of the data in the current F11
range in a separate Chart sheet.

Define a name to use in references. Alt+M, M, D

Paste a name from the Paste Name dialog F3

box (if names have been defined in the
workbook).
Move to the first field in the next record of a Enter
data form.
Create, run, edit, or delete a macro. Alt+F8

Open the Microsoft Visual Basic For Alt+F11 

Applications Editor.
Open the Power Query Editor Alt+F12
 Description Excel Shortcuts 
Keyboard shortcuts
for working with Copy the value from the cell above the Ctrl+Shift+Straight
active cell into the cell or the formula bar. quotation mark (")
data, functions, and
Create an embedded chart of the data in the Alt+F1
the formula bar current range.
Create a chart of the data in the current F11
range in a separate Chart sheet.

Define a name to use in references. Alt+M, M, D

Paste a name from the Paste Name dialog F3

box (if names have been defined in the
workbook).
Move to the first field in the next record of a Enter
data form.
Create, run, edit, or delete a macro. Alt+F8

Open the Microsoft Visual Basic For Alt+F11 

Applications Editor.
Open the Power Query Editor Alt+F12
 Description Excel Shortcuts 
Keyboard shortcuts
Stop a refresh operation. Esc
for refreshing
external data
Refresh data in the current Ctrl+F5
worksheet.
Refresh all data in the workbook. Ctrl+Alt+F5
 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
= calculate difference.
=
INTERVAL-
=
Form in which u want to
=
calculate difference.
“ D ” - D AY S
“M”-MONTH This says that I
S am 19 years 6
“ YY ”M- ”Y- E
M AORNST H S OVE months & 18
R YEAR days old
“ M D ” - D AY S O V E R M O N T H 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

32
 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.
33
 COUNT FUNCTIONS
SYNTAX OF FUNCTIONS
1. COUNT
=COUNT(VALUE1,VALUE2,…)
=
= 2. COUNTA
= =COUNTA(VALUE1,VALUE2,…)
=
3. COUNTBLANK
=COUNTBLANK(RANGE)

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.
34
 TEXT FUNCTIONS
SYNTAX OF FUNCTIONS
1. LOWER FUNCTION
=LOWER(TEXT)

2. UPPER FUNCTION
=UPPER(TEXT)

3. PROPER FUNCTION
=PROPER(TEXT)

1. 2. 3.

TO CONVERT TO CONVERT TO CAPITALISED

TEXT FROM TEXT FROM EACH WORD
CAPITAL TO SMALL TO OF TEXT.
SMALL. CAPITAL.
35
 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.

RETURN SPECIFIED RETURN CHARACTER

RETURN SPECIFIED
NO. OF CHARACTER FROM MIDDLE OF
NO. OF CHRACTER
FROM START OF TEXT,GIVEN A
FROM END OF TEXT.
TEXT. STARTING POSITION.
36
 OTHER FUNCTIONS

USES OF FUNCTIONS

= NOW RETURNS CURRENT DATE AND

TIME.

= TODA RETURNS CURRENT DATE ONLY.

Y
= MOD RETURNS THE REMAINDER AFTER A
NO.
IS DIVIDED BY A DIVISOR.
= LEN RETURNS THE NO. OF CHARACTERS IN A
TEXT STRING.

= SUM ADD ALL THE

NUMBERS.
37
 FUNCTION AUDITING
TRACE PRECEDENTS
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

TRACE DEPENDENTS 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.