ICDL Module 4 – Spreadsheet
Using Microsoft Office Excel 2007
Aston Swann - Syllabus Five
A First Look At Excel
Starting Excel
ICDL Syllabus 5 ModuleThree– Slide No. 3
What is the Active Cell?
• Excel identifies the active cell with a bold
outline around the cell and highlighting the
column heading letter and row heading
number of the cell.
– In the example, B2 is the active cell:
ICDL Syllabus 5 ModuleThree– Slide No. 4
The Excel Cell Referencing System
• An Excel worksheet is made up of individual
cells, each of which had a unique reference.
Look at the illustration below. We have
clicked on cell B3, which means that the cell is
in column B, row 3.
ICDL Syllabus 5 ModuleThree– Slide No. 5
Entering Numbers and Text
ICDL Syllabus 5 ModuleThree– Slide No. 6
Default Text and Number Alignment
• If you look carefully at what you have typed in
you will see that by default text is aligned within
a cell to the left, while numbers are aligned
within the cell to the right.
– This makes sense, as normally text starts from the left
of a page and it is the same within a cell.
– Numbers on the other hand normally align to the
right.
– Think how you would write down a column of
numbers on a page that you want to add up.
Numbers align to the right.
ICDL Syllabus 5 ModuleThree– Slide No. 7
Worksheets and Workbooks
• Look at the bottom-left of your screen and
you will see the worksheet tabs displayed.
• By default each workbook contains three
worksheets.
– This is similar to a notebook that contains
separate pages.
ICDL Syllabus 5 ModuleThree– Slide No. 8
Saving a Workbook
ICDL Syllabus 5 ModuleThree– Slide No. 9
Closing a Workbook
• To close the workbook, click on
the Microsoft Office Button
(top-left of your screen), from
the drop down options
displayed, click on the Close
command.
ICDL Syllabus 5 ModuleThree– Slide No. 10
Creating a New Workbook
• To create a new workbook, press Ctrl+N.
• This is the keyboard shortcut for creating a
new file.
– A new workbook will be created containing three
worksheets.
ICDL Syllabus 5 ModuleThree– Slide No. 11
Opening a Workbook
• Press Ctrl+O the keyboard shortcut to open an
existing file.
• Or click on the Microsoft Office Button (top-
left) and then click on the Open command.
ICDL Syllabus 5 ModuleThree– Slide No. 12
Switching Between Workbooks
• To switch to a particular Excel workbook, click
on the relevant Excel workbook icon displayed
within the Windows Taskbar (across the
bottom of the screen).
TIP: You can use the Alt+Tab keyboard
shortcut to switch between open programs.
ICDL Syllabus 5 ModuleThree– Slide No. 13
Saving a Workbook
Using Another Name
ICDL Syllabus 5 ModuleThree– Slide No. 14
Saving a Workbook Using
a Different File Type
ICDL Syllabus 5 ModuleThree– Slide No. 15
HELP
Getting Help
• Click on the Microsoft Office Excel Help icon
(towards the top-right of the screen).
Or press the F1 help key.
ICDL Syllabus 5 ModuleThree– Slide No. 17
Searching for Help
• You can search for help on a topic of particular
interest. Press F1 to display the Excel Help
window. Within the text box near the top of
the Excel Help window, type in a word or
words relating to the help you need.
ICDL Syllabus 5 ModuleThree– Slide No. 18
The Help 'Table of Contents'
• Press F1 to display the Excel Help window.
Click on the Table of Contents icon
ICDL Syllabus 5 ModuleThree– Slide No. 19
Printing a Help Topic
• Display an item of interest within the Excel
Help window. Click on the Print icon
ICDL Syllabus 5 ModuleThree– Slide No. 20
Alt Key Help
• Press the Alt key and you will see numbers
and letters displayed over icons, tabs or
commands
ICDL Syllabus 5 ModuleThree– Slide No. 21
Customizing Excel
Modifying Basic Options
ICDL Syllabus 5 ModuleThree– Slide No. 23
Using Excel
Selection Techniques
Why Are Selection Techniques
Important?
• Often when you want to do something within
Excel you need to select an item first.
– This could involve selecting a cell or multiple cells.
– It many need you to select a row, a column or
even the entire table.
ICDL Syllabus 5 ModuleThree– Slide No. 26
Selecting a Cell
• Open a workbook called Selection techniques.
• To select a cell simply click on that cell.
• Thus to select cell B3, click on cell B3.
ICDL Syllabus 5 ModuleThree– Slide No. 27
Selecting a Range of Connecting Cells
• We want to select the cells from C3 to G3.
– To do this click on the first cell within the range,
i.e. C3. Then press down the Shirt key (and keep it
held down). Click on cell G3. When you release
the Shift key the cell range will remain selected, as
illustrated.
ICDL Syllabus 5 ModuleThree– Slide No. 28
Selecting a Range of
Non-connecting Cells
• Click on the first cell, i.e. C3. Then while
keeping the Ctrl key pressed click on the cells
E3 and G3. When you release the Ctrl key the
cells will remain selected.
ICDL Syllabus 5 ModuleThree– Slide No. 29
Selecting the Entire Worksheet
• To select the entire worksheet, click on the
intersection between the column and row
referencing numbers.
ICDL Syllabus 5 ModuleThree– Slide No. 30
Selecting a Row
• To select a row, say the row relating to
Canada, click on the relevant row number
displayed down the left side of the worksheet.
ICDL Syllabus 5 ModuleThree– Slide No. 31
Selecting a Range of Connecting Rows
– First click on the row number next to Canada.
Press down the Shift key and keep it pressed.
Click on the row number relating to Australia.
When you release the Shift key the multiple rows
remain selected.
ICDL Syllabus 5 ModuleThree– Slide No. 32
Selecting a Range of
Non-connected Rows
• Click on the row number 3 and press down
the Ctrl key. Click on row number 5, then row
number 7 and finally number 9. Release the
Ctrl key and the rows will remain selected.
ICDL Syllabus 5 ModuleThree– Slide No. 33
Selecting a Column
• To select the column containing data relating
to 2003, click on the column header C, as
illustrated.
ICDL Syllabus 5 ModuleThree– Slide No. 34
Selecting a Range
of Connecting Columns
• First select column C. Press the Shift key and
while keeping it pressed select column F.
When you release the Shift key the columns
will remain selected.
ICDL Syllabus 5 ModuleThree– Slide No. 35
Selecting a Range of
Non-connecting Columns
• First select the column C. Press the Ctrl key
and keep it pressed. Select column E and then
select column G. Release the Ctrl key and the
columns remain selected.
ICDL Syllabus 5 ModuleThree– Slide No. 36
Manipulating Rows And
Columns
Inserting Rows
• Right click over the selected row and from the
popup menu displayed select the Insert
command.
ICDL Syllabus 5 ModuleThree– Slide No. 38
Inserting Columns
• Right click over the selected column and from
the popup menu displayed select the Insert
command.
ICDL Syllabus 5 ModuleThree– Slide No. 39
Deleting Rows
• Select the row you want to delete and right
click over the selected row. From the popup
menu displayed select the Delete command.
ICDL Syllabus 5 ModuleThree– Slide No. 40
Deleting Columns
• Right click over the selected column and from
the popup menu displayed select the Delete
command. The column is deleted without any
additional warning.
ICDL Syllabus 5 ModuleThree– Slide No. 41
Modifying Column Widths
• Right click over the selected column and from
the popup menu displayed select the Column
Width command.
ICDL Syllabus 5 ModuleThree– Slide No. 42
Modifying Column Widths
Using ‘Drag and Drop'
• Move the mouse pointer to the line between
the header for column B and column C, as
illustrated below.
– Press the mouse button and keep it pressed.
Move the mouse pointer left or right to make the
column narrower or wider. Release the mouse
button and the column width will change as
required.
ICDL Syllabus 5 ModuleThree– Slide No. 43
Automatically Resizing
Column Width to Fit Contents
• To automatically resize each column width to
fit the contents, select all the columns
containing data. Double click on the junction
between one of the column header headers
within the selected columns.
ICDL Syllabus 5 ModuleThree– Slide No. 44
Modifying Row Heights
• Select one or more rows and then right click
over the selected row(s). From the popup
menu displayed select the Row Height
command.
ICDL Syllabus 5 ModuleThree– Slide No. 45
Copying, Moving And Deleting
Copying the Cell
or Range Contents
• Select a cell, range, row or column to copy.
• Press Ctrl+C to copy the selected range to the
Clipboard
• Click at the location you wish to paste the data
to and press the Ctrl+V keys to paste the data
from the Clipboard.
ICDL Syllabus 5 ModuleThree– Slide No. 47
Deleting Cell Content
• Select the range that you wish to delete the
contents of. Press the Del key and the cell
contents will be deleted.
– TIP: You can use the same technique to delete
entire rows or columns.
– Pressing Ctrl+A will select everything within a
worksheet will allow you to delete the entire
worksheet contents when you press the Del key.
ICDL Syllabus 5 ModuleThree– Slide No. 48
Moving the Contents of a Cell
or Range
• Select the range to wish to move and then cut
it to the Clipboard. In this case select the
data, as illustrated. Press the Ctrl+X keys to cut
the selected data to the Clipboard.
• Click at the location you wish to move the
selected data to and press Ctrl+V, to paste the
data.
ICDL Syllabus 5 ModuleThree– Slide No. 49
Editing Cell Content
• It is easy to edit existing data within a cell or
to replace existing data within a cell.
ICDL Syllabus 5 ModuleThree– Slide No. 50
Undo and Redo
• Click on the Undo icon (top-left of your
screen) to reverse the last action. Try it now.
• Click on the Redo icon (top-left of your screen)
to reapply the last action.
ICDL Syllabus 5 ModuleThree– Slide No. 51
AutoFill
• Use AutoFill to extend a series across a range
ICDL Syllabus 5 ModuleThree– Slide No. 52
Sorting a Cell Range
• Click on the Data tab, and from within the Sort
& Filter section, click on Sort A to Z icon.
ICDL Syllabus 5 ModuleThree– Slide No. 53
Searching and Replacing Data
• Press Ctrl+F to start the Search utility
ICDL Syllabus 5 ModuleThree– Slide No. 54
Worksheets
Manipulating Worksheets
Switching Between Worksheets
• To switch to another worksheet click on either
the sheet tab.
ICDL Syllabus 5 ModuleThree– Slide No. 57
Renaming a Worksheet
• Double click on the Sheet tab and you will be
able to type in a new name.
ICDL Syllabus 5 ModuleThree– Slide No. 58
Inserting a New Worksheet
• Click on a worksheet tab to select it. Right
click over the tab and from the popup menu
displayed, click on the Insert command.
ICDL Syllabus 5 ModuleThree– Slide No. 59
Deleting a Worksheet
• Right click on the tab and from the popup
menu displayed select the Delete command.
ICDL Syllabus 5 ModuleThree– Slide No. 60
Copying a Worksheet
Within a Workbook
• Right click on the tab and
from the popup menu
displayed select the Move
or Copy command. The
Move or Copy dialog box is
displayed. As we want to
copy rather than move,
click on the ‘Create a copy’
check box.
ICDL Syllabus 5 ModuleThree– Slide No. 61
Moving a Worksheet
Within a Workbook
• Right click on the tab and from the popup
menu displayed select the Move or Copy
command. The Move or Copy dialog box is
displayed. As we want to move rather than
copy, do NOT click on the ‘Create a copy’
check box.
ICDL Syllabus 5 ModuleThree– Slide No. 62
Moving a Worksheet
Within a Workbook
• In the Before sheet section of the dialog box,
select which worksheet you wish to insert the
moved worksheet in front of.
ICDL Syllabus 5 ModuleThree– Slide No. 63
Formatting
Font Formatting
Font Type
• Click on the down
arrow within the
Font section and
select a different
font type, such as
Arial.
ICDL Syllabus 5 ModuleThree– Slide No. 66
Font Size
• Click on the down arrow within the Font Size
section and select a different font size.
ICDL Syllabus 5 ModuleThree– Slide No. 67
Bold, Italic
& Underline Formatting
ICDL Syllabus 5 ModuleThree– Slide No. 68
Cell Border Formatting
• Click on the
down arrow
next to the
Border icon. A
drop down is
displayed from
which you can
select the
required border.
ICDL Syllabus 5 ModuleThree– Slide No. 69
Formatting the Background Colour
• Click on the Fill Color icon. Move the mouse
over a colour and you will see the colour
formatting previewed within your data. Click
on a colour to apply it.
ICDL Syllabus 5 ModuleThree– Slide No. 70
Formatting the Font Colour
• Click on the down arrow next to the Font
Colour icon. This will display a drop down
from which you can select the required colour.
ICDL Syllabus 5 ModuleThree– Slide No. 71
ALIGNMENT FORMATTING
Aligning Contents in a Cell Range
• Select a range. Use the alignment icons
ICDL Syllabus 5 ModuleThree– Slide No. 73
Centering a Title Over a Cell Range
ICDL Syllabus 5 ModuleThree– Slide No. 74
Cell Orientation
• Click on the Orientation icon. You will see a
drop down menu allowing you to format the
cell orientation.
ICDL Syllabus 5 ModuleThree– Slide No. 75
Text Wrapping
ICDL Syllabus 5 ModuleThree– Slide No. 76
Format Painter
• Paint formatting from one place to another
ICDL Syllabus 5 ModuleThree– Slide No. 77
NUMBER FORMATTING
Number Formatting
• Click on the down arrow next
to the Number Format
control. You will see a drop
down menu from which you
can select the format.
ICDL Syllabus 5 ModuleThree– Slide No. 79
Decimal Point Display
• Click on the Decrease Decimal icon or the
Increase Decimal icon
ICDL Syllabus 5 ModuleThree– Slide No. 80
Comma Formatting
• Click on the Comma Style icon to format the
number using commas.
ICDL Syllabus 5 ModuleThree– Slide No. 81
Currency Symbol
• Click on the down arrow next to the Currency
icon and select the required option.
ICDL Syllabus 5 ModuleThree– Slide No. 82
Date Styles
ICDL Syllabus 5 ModuleThree– Slide No. 83
Percentages
ICDL Syllabus 5 ModuleThree– Slide No. 84
Freezing Row And Column Titles
Freezing Row and Column Titles
ICDL Syllabus 5 ModuleThree– Slide No. 86
Formulas And Functions
Formulas
Creating Formulas
• All formulas within Excel start with the
'equals' symbol. Type in the following
formula.
=C3*D3
• Press the Enter key and you will see the result
of the calculation in the cell.
ICDL Syllabus 5 ModuleThree– Slide No. 89
Easy Way to Create Formulas
• You can use the mouse to help create formula
references
ICDL Syllabus 5 ModuleThree– Slide No. 90
Copying Formulas
– Click on cell E4. Move the mouse pointer to the
bottom-right border of this cell and you will notice
that the mouse pointer changes to the shape of a
small, solid black cross. Press the mouse button and
while keeping the mouse button depressed, drag
down to cell E9. Release the mouse button and you
will see the formula copied down this range.
ICDL Syllabus 5 ModuleThree– Slide No. 91
Operators
• Operators sound complicated. In reality they
are items such as:
+ (plus)
- (minus)
/ (divide)
* (multiply)
ICDL Syllabus 5 ModuleThree– Slide No. 92
Formula Error Messages
– The contents of the cell cannot be displayed correctly
as the column is too narrow.
– Indicates that a cell reference is invalid. This is often
displayed when you delete cells which are involved in
a formula
– Excel does not recognise text contained within a
formula.
ICDL Syllabus 5 ModuleThree– Slide No. 93
Relative, Mixed And Absolute
Cell Referencing
Relative Cell Referencing
Within Formulas
• Relative addressing means that a cell or range
is references using a coordinate system, not an
absolute system
ICDL Syllabus 5 ModuleThree– Slide No. 95
Absolute Cell Referencing
Within Formulas
• An absolute address is an address using an
specified column and row address, such as:
$A$8
• The $ indicates an absolute address
ICDL Syllabus 5 ModuleThree– Slide No. 96
Functions
What is a Function?
• A function allows you to calculate a result
such as adding numbers together, or finding
the average of a range of numbers.
ICDL Syllabus 5 ModuleThree– Slide No. 98
Common Functions
• AVERAGE: Used to determine the average value of the selected cells
contents.
• COLUMNS: Used to return the number of columns within a reference.
• COUNT: Used to count how many numbers are in the list.
• MAX: Used to return the maximum number from a list.
• MIN: Used to return the minimum number from a list.
• ROUND: Used to round off numbers to a specified number of decimal
points.
• SUM: Used to add the contents of selected cells.
ICDL Syllabus 5 ModuleThree– Slide No. 99
Sum Function
• Used to sum a range
ICDL Syllabus 5 ModuleThree– Slide No.100
Average Function
• Used to average a range
ICDL Syllabus 5 ModuleThree– Slide No.101
Max Function
• Used to find the maximum number within a
range.
ICDL Syllabus 5 ModuleThree– Slide No.102
Min Function
• Used to find the minimum number within a
range.
ICDL Syllabus 5 ModuleThree– Slide No.103
Count Function
• The Count function will count up the number
of cells which contain numbers.
ICDL Syllabus 5 ModuleThree– Slide No.104
What Are 'IF Functions'?
• Excel has a number of functions which allow
us to evaluate values and make decisions
based on the result of the evaluation. The IF( )
FUNCTION is one of these.
IF(LOGICAL_TEST, ACTION_IF_TRUE,
ACTION_IF_FALSE)
ICDL Syllabus 5 ModuleThree– Slide No.105
Using the IF Function
ICDL Syllabus 5 ModuleThree– Slide No.106
Charts
Using Charts
Inserting a Column Chart
ICDL Syllabus 5 ModuleThree– Slide No.109
Inserting a Line Chart
ICDL Syllabus 5 ModuleThree– Slide No.110
Inserting a Bar Chart
ICDL Syllabus 5 ModuleThree– Slide No.111
Inserting a Pie Chart
ICDL Syllabus 5 ModuleThree– Slide No.112
Resizing a Chart
• To resize a chart click on it to select it.
• Move the mouse pointer to one of the four corners of the
chart. You will notice that the mouse pointer changes to
the shape of a diagonal line with an arrow at each end.
• When you see the mouse pointer change, press the mouse
button and while keeping the button pressed move
diagonally across the screen.
• Move away from the centre of the chart to make the chart
larger and toward the centre of the chart to make the chart
smaller.
• When you release the mouse button the chart will be
resized.
ICDL Syllabus 5 ModuleThree– Slide No.113
Deleting a Chart
• Select the chart and press the Del key.
ICDL Syllabus 5 ModuleThree– Slide No.114
Chart Title or Labels
ICDL Syllabus 5 ModuleThree– Slide No.115
Chart Background Colour
ICDL Syllabus 5 ModuleThree– Slide No.116
Changing Column, Bar,
Line or Pie Slice Colours
ICDL Syllabus 5 ModuleThree– Slide No.117
Changing the Chart Type
ICDL Syllabus 5 ModuleThree– Slide No.118
Modifying Charts
Using the Layout Tab
ICDL Syllabus 5 ModuleThree– Slide No.119
Copying and Moving Charts
Within a Worksheet
• Use the Windows Clipboard
ICDL Syllabus 5 ModuleThree– Slide No.120
Copying and Moving
Charts Between Worksheets
• Use the Windows Clipboard
ICDL Syllabus 5 ModuleThree– Slide No.121
Printing
Worksheet Setup
Worksheet Margins
ICDL Syllabus 5 ModuleThree– Slide No.124
Worksheet Orientation
ICDL Syllabus 5 ModuleThree– Slide No.125
Worksheet Page Size
ICDL Syllabus 5 ModuleThree– Slide No.126
Headers and Footers
ICDL Syllabus 5 ModuleThree– Slide No.127
Header and Footer Fields
ICDL Syllabus 5 ModuleThree– Slide No.128
Scaling a Worksheet to Fit a Page(s)
ICDL Syllabus 5 ModuleThree– Slide No.129
Preparing To Print A Worksheet
Visually Check Your Calculations
• Always visually check over worksheet data and
try to look for formula calculation results that
do not make sense.
ICDL Syllabus 5 ModuleThree– Slide No.131
Gridline Display When Printing
• To see how the worksheet will look when
printed view the worksheet in Print Preview
view. To do this click press Ctrl+F2.
ICDL Syllabus 5 ModuleThree– Slide No.132
Printing Titles
On Every Page when Printing
• You can force titles to print on every page
when printing.
ICDL Syllabus 5 ModuleThree– Slide No.133
Printing the Excel Row and
Column Headings
ICDL Syllabus 5 ModuleThree– Slide No.134
Spell Checking
• Press F7 to start the spell checker (or click on
the Review tab and click the Spelling icon).
ICDL Syllabus 5 ModuleThree– Slide No.135
Previewing a Worksheet
• Always view a workbook in Print Preview
mode prior to printing it. It allows you a quick
visual check over how the worksheet will look
when printed.
• Click on the Microsoft Office Button and then
click on the arrow next to the Print command.
From the submenu, select Print Preview.
ICDL Syllabus 5 ModuleThree– Slide No.136
Comparing Workbooks Side by Side
• This feature allows you to compare two
versions of a workbook, side by side.
ICDL Syllabus 5 ModuleThree– Slide No.137
Zooming the View
ICDL Syllabus 5 ModuleThree– Slide No.138
Printing Options
ICDL Syllabus 5 ModuleThree– Slide No.139