msomibora.
com
Microcomputer Applications
Spreadsheet with Microsoft Excel
Module 4
Training Department
[email protected]
1
msomibora.com
Course Objectives
Understand and appreciate the spreadsheet programs
Understand the main features of Spreadsheet
Know how to start and close Microsoft Excel
Describe Microsoft Excel Terminologies
Understand data types and associated entry techniques
Understand and use Data editing and Manipulation
features
Understand how to format data and worksheets
Understand how to create and modify charts
Understanding and Using Formulas and functions
Understand how to Print a worksheet
msomibora.com Microsoft Excel 2
Getting Started with Excel
There are several ways which you can use to
Start Microsoft Excel
1st Option
Click Start Button
Highlight All Programs
Highlight Microsoft Office
Click Microsoft Excel 2003
msomibora.com Microsoft Excel 3
Getting Started with Excel
2nd Option
If there is a shortcut to Microsoft Excel on
the Desktop
Double Click this Icon
msomibora.com
msomibora.com Microsoft Excel 4
Getting Started with Excel
3rd Option
Click Start Button
Click Run Command,Run box will open
Type excel in this box
excel
Click OK Button
msomibora.com Microsoft Excel 5
Microsoft Excel Screen
msomibora.com Microsoft Excel 6
Spreadsheet Program
spreadsheet
A type of application program which manipulates
numerical and string data in rows and columns
of cells.
The value in a cell can be calculated from a
formula which can involve other cells. A value is
recalculated automatically whenever a value on
which it depends changes. Different cells may
be displayed with different formats.
msomibora.com Microsoft Excel 7
Spreadsheet Program
Accounting field
Even we
Statisticians
we enjoy the
Academics
capabilities of
functions
Microsoft Excel
msomibora.com Microsoft Excel 8
Microsoft Excel Terminologies
Workbook - A file in a spreadsheet program.
Worksheet - A smaller section of a workbook
file designated by sheet tabs. Worksheets can
have multiple pages. A worksheet has 65,536
Rows and 256 Columns.
By default if you open a workbook for the first
time it consist of only Worksheets
But it is also possible to insert more worksheet
msomibora.com Microsoft Excel 9
Microsoft Excel Terminologies
Row The name assigned to each
line/grouping of horizontal cells in a
spreadsheet. Excel labels the rows with
numbers.
Column
The name assigned to each line/grouping of
vertical cells in a spreadsheet. Excel labels the
columns with letters. There are 256 columns
in an Excel worksheet.
msomibora.com Microsoft Excel 10
Microsoft Excel Terminologies
Active Cell
The active cell is the cell
that is currently
selected. The selected
cell will have an outline
surrounding the cell
and in the lower right
corner there will be a
small box (fill handle).
The active cell is where
data will be inputted
when a user is typing.
msomibora.com Microsoft Excel 11
Microsoft Excel Terminologies
Name Box
The name box shows the cell address for the
active cell. As a user selects areas of the
workbook the quantity of rows and columns
will be displayed in the name box. A user may
decide to name areas of the spreadsheet. The
name box can be used to quickly name and
then select areas of a worksheet.
msomibora.com Microsoft Excel 12
Microsoft Excel Terminologies
Formula Bar
The formula bar shows what is contained
within the active cell. Sometimes you may
not be able to see everything in the active
cell; however the formula bar will display the
entire contents of the cell.
Formulas are also displayed in the Formula Bar.
Formulas are mathematical equations used to
compute something within the worksheet.
Formulas will display in the formula bar, while
the answer to the formula will display in the cell.
msomibora.com Microsoft Excel 13
Microsoft Excel Terminologies
msomibora.com Microsoft Excel 14
Microsoft Excel Terminologies
Sheet Tabs
Labels at the bottom of the workbook to
designate the worksheets contained within
the workbook. The sheet tabs look similar to
file folder tabs. The worksheets can also be
moved , renamed and deleted
msomibora.com Microsoft Excel 15
msomibora.com Microsoft Excel 16
Entering Data in a Worksheet
Before you start to enter data you need to
create a new workbook.
Steps to Create a New Workbook
Click on the File menu and then select New.
Click on Blank Workbook to create a new blank
workbook.
OR
Click on the New button
on the Standard toolbar.
msomibora.com Microsoft Excel 17
Entering Data in a Worksheet
Types of data that can be entered in an Excel
Worksheet
1.Label
Any text is considered as label
2.Constant
These are all numerical values and
symbols
3.Formula
These include all formula which can be
entered and the formula starts with equal
msomibora.com Microsoft Excel 18
Entering Data in a worksheet
Steps to enter data
1.Select a cell in which you want to enter
data.
2.Type a data in cell
3.Press Enter key OR Return key
msomibora.com Microsoft Excel 19
Entering Data in a worksheet
Once you have entered data in a cell use the
following keys to move from one cell to another
cell.
Shift Key +Enter Key
Shift +Tab key Tab key
Enter key
msomibora.com Microsoft Excel 20
Entering Data in a worksheet
You can notice something when you enter
data in a cell Automatically numbers are
aligned to the Right Side of the cell and Text
to the Left of the Cell
Number
Text
msomibora.com Microsoft Excel 21
Hands on Practice
Enter the data as shown below and answer the
questions which are in the next slide.
msomibora.com Microsoft Excel 22
Saving a Workbook
After entering data then you need to save it
,and whatever you do either you modify or
change ,make sure you save the changes
.and have trend of saving your work every 5
to 10 minutes depending on your typing
speed. There are two options which you can
use to save
Save and Save As
msomibora.com Microsoft Excel 23
Saving a workbook
Click File Menu
Select Save from the drop down list
Save dialog box will open
Select a location where you to save
Type a file name in the Filename Text Box
Select a format under which you want to
save from the Save as Type List box
Click Save Button.
msomibora.com Microsoft Excel 24
Saving a Workbook
Use the Save In list box
to specify the folder/disk
the file will be saved in
Type the new file
Select a file format
name
you want to save
msomibora.com Microsoft Excel 25
Closing a Workbook
Click File Menu
Click Close from the drop down list
OR
Quitting Microsoft Excel
Click File Menu
Select Exit from the drop down menu
msomibora.com Microsoft Excel 26
Editing data in a worksheet
Data in a worksheet can be edited using a formula
bar or by editing it directly in the cell.
Editing Directly in the cell
Double click the cell with data you want to
edit.
Position the insertion by using arrow keys on the
keyboard
Use Delete key OR Backspace to erase the characters
you don’t want
Type other characters
Press Enter key
msomibora.com Microsoft Excel 27
Editing data in a worksheet
The Text in the cell was supposed to be
Chuo Kikuu cha Dar ,So move the
Insertion point as seen above and
delete the Character “a” and type
Character “u”
msomibora.com Microsoft Excel 28
Editing data in a worksheet
Using a Formula bar to Edit
Select a cell with data you want to edit
Click on the Formula bar
Use arrow keys to move the insertion
close to where you want to edit
Use Delete key and Backspace to erase
characters which you don’t want.
Insert other character(s) if there is a
need.
Click a green Tick
msomibora.com Microsoft Excel 29
Editing data in a worksheet
Move the insertion point so that it will be
between letter “t” and “e” then press
delete key to delete “e” then type letter
“a” and the word will be Mtanzania
Click this button to
Accept changes after
editing
msomibora.com Microsoft Excel 30
Deleting a data in a worksheet
Select a data you want to delete
Press delete key on the keyboard
OR
Select a cell with a data you wish to delete
Click Edit Menu
Select Clear
Click Contents
msomibora.com Microsoft Excel 31
Hands on Practice
Editing a data in a worksheet
In the above sheet there are some words especially
those with a shaded cell have spelling mistakes so
edit those which are not correct
Kikweete to be Kikwete, Konya to be Kenya
Neirobi to be Nairobi, Melawi to be Malawi
msomibora.com Microsoft Excel 32
msomibora.com Microsoft Excel 33
Selecting Cells
Number of Cells How to Select
A single cell Single click it
More than one cell Click a first cell you wish
adjacent to select ,then hold down
SHIFT key then click the
last cell you want to
select.
More than one cell non- Hold Down CTRL key then
adjacent continue to click the cells
you wish to select
Entire column Click the column heading
msomibora.com Microsoft Excel 34
Selecting Cells
Number of Cells How to Select
Entire Row Click a row heading
Entire Worksheet Click a blank cell before
the Column and row
heading
msomibora.com Microsoft Excel 35
Inserting a Worksheet
Click Insert Menu
Click Worksheet
OR
Point a sheet tab
Right Click it ,a pop-menu appear
Select Insert……
A box will appear
msomibora.com Microsoft Excel 36
Inserting a Worksheet
Select General Tab
Highlight Blank Sheet
Click OK Button
msomibora.com Microsoft Excel 37
Inserting Column & Rows
Select a cell close to where you want to insert a
new Column.
Click Insert Menu
Click Column(Note that the column will be inserted
on the left of the selected cell or column)
OR
Select a cell close to where you want to insert a
new Row.
Click Insert menu
Click Rows (Note that the row will be inserted on
the top of the selected cell or row)
msomibora.com Microsoft Excel 38
Copying Cells
Select cells you wish to copy
Click Edit Menu
Click Copy (or Click a copy Icon on the
standard toolbar
Click a cell where you want to paste
Click Edit menu
Click Paste (or click a Paste icon on the
Standard toolbar
msomibora.com Microsoft Excel 39
Moving Cells
Select cells you wish to move
Click Edit Menu
Click Cut (or Click a Cut Icon on the
standard toolbar
Click a cell where you want to paste
Click Edit menu
Click Paste (or click a Paste icon on the
Standard toolbar
msomibora.com Microsoft Excel 40
Finding and Replacing Text
Position an insertion point where you want to insert.
Click Edit Menu
Click Find
Click Find All/Find Next Type a text you wish
to find here
msomibora.com Microsoft Excel 41
Finding and Replacing Text
Click Replace tab in the Find and Replace
dialog box
Type a text you wish to Type a text to replace
find with
Click Replace All /Replace button
msomibora.com Microsoft Excel 42
Hands on Practice
Copy the data as shown on the worksheet above and answer
all the question on the next slide
msomibora.com Microsoft Excel 43
Hands on Practice cont…
Insert a new Column between Name and
Residence Column and name it Age type
any numbers
Insert a new row between Maimuna and
Zakaria ,and type any entries as per
columns headings
Copy all the contents and paste in the
sheet number 2
msomibora.com Microsoft Excel 44
msomibora.com Microsoft Excel 45
Formatting Cells
Data in the worksheet can be formatted
using the
Formatting toolbar
or
Format menu
msomibora.com Microsoft Excel 46
Formatting Text
Select a cell (s) you wish to format
Click a command buttons on the formatting
toolbar in order to change the Font size,
Color, align text and font style.
msomibora.com Microsoft Excel 47
Number Formatting
Select the numbers you want to format
Click Format menu
Click Cells, a dialog box will open
Select a Number tab
Select a category you want
on the category List
Apply the formats you wish
Click OK button to apply
msomibora.com Microsoft Excel 48
Number Formatting
msomibora.com Microsoft Excel 49
Formatting Cells
Format Cells command – controls the
formatting for numbers, alignment, fonts,
borders, and patterns (color)
Select the cells to which the formatting will apply
Execute the Format Cells command
msomibora.com Microsoft Excel 50
Number Formatting
Choose Cells from
the Format menu
Click on a tab (for
example, Number)
Select format (for
example, choose
currency, 2 decimal
places)
Click OK
msomibora.com Microsoft Excel 51
Hands on Practice 2
Copy the data below and answer all the
questions written on the next slide
msomibora.com Microsoft Excel 52
Hands on Practice 2
Insert a new row above the headings Row, merge
cells A1 to D1 and type”Payment Collection 2007”
as your heading
Format the figures in the Paid Fee column to be in
Tanzanian currency
Format the score numbers for each student to be
in percentages
Change the font size of the heading to be 18 and
apply any light color for shading.
Format the date of admission to display the Month
as a text
Rotate the word score at an angle of negative 15
degrees.
msomibora.com Microsoft Excel 53
msomibora.com Microsoft Excel 54
The Chart Wizard(1)–chart Type
Chart wizard
Select or click dialog box
within the data
Click on the Chart
Wizard icon
Chart Wizard box
will appear, which
will guide you
through the chart
creation process
Chart
wizard
icon
msomibora.com Microsoft Excel 55
The Chart Wizard (2) – Data Source
Allows you to define
the data that will be
used to create the
chart
msomibora.com Microsoft Excel 56
Chart Wizard (3) – Chart Options
Allows you to
define the options
you wish to include
e.g.
- Titles
- Axes
- Gridlines
- Legend
- Data Labels
- Data Table
msomibora.com Microsoft Excel 57
Chart Wizard (4) – Chart Location
Allows you to choose where to insert the chart
e.g As an Object in the current worksheet or As
a new worksheet which will be added
automatically into the current workbook
msomibora.com Microsoft Excel 58
Chart Formatting – Chart Area
Double click on the chart area to display
the relevant dialog boxes
The chart area is the “empty” chart
background
msomibora.com Microsoft Excel 59
Chart Formatting - Legends
Double click on the Legend to display
the relevant dialog boxes
msomibora.com Microsoft Excel 60
Chart formatting - Axis
Double click on an Axis to
display the relevant dialog box
msomibora.com Microsoft Excel 61
Chart Formatting – The Plot Area
Double click on the Plot area to
display the relevant dialog box
– The plot area is the “empty “ area
of the actual graph
msomibora.com Microsoft Excel 62
Chart Formatting - Gridlines
Double click on a gridline to display
the relevant dialog box
msomibora.com Microsoft Excel 63
Chart Formatting – The Data Series
Double click on a data series to display
the relevant dialog box
msomibora.com Microsoft Excel 64
Creating Chart
Hands on Practice
Use the data above to create a 3D bar
chart type
Format the chart as much as you can
msomibora.com Microsoft Excel 65
Chart Editing
To edit a chart like to change the chart type,
chart options or simply to access the 4 steps
of chart wizard in order to modify the chart.
Point within the chart plot area
Right click ,a pop-up menu appears
Select an option you want in order to access
one of the Four steps of the Chart Wizard
A Chart wizard step box will appear
Apply changes you want then click Ok
Button
msomibora.com Microsoft Excel 66
Chart Editing
1st Chart Wizard Step
2nd Chart Wizard Step
3rd Chart Wizard Step
4th Chart Wizard Step
msomibora.com Microsoft Excel 67
Hands on Practice
Copy the data as shown below and create a
pie chart then answer the questions on the
next slide
msomibora.com Microsoft Excel 68
Hands on Practice
Using the data and the chart above do the
following.
Change the chart above to Cylinder chart
type.
Remove the data for UCC-Dodoma. Does
the data in the chart changes?
Change the data label of pie chart to
percentage values
msomibora.com Microsoft Excel 69
msomibora.com Microsoft Excel 70
About Formula
As good as Excel is, it cannot guess or read
your mind. You must know which math
operation(s) you need to use
Arithmetic Operators :
Addition + (plus)
Subtraction - (minus)
Multiplication * (asterisk)
Division / (forward slash)
msomibora.com Microsoft Excel 71
Creating a formula
Steps to create a formula
When you have decided where you want the result of the
math operation, and you have clicked on that cell …
Start by entering “=“
Enter the formula
Press Enter key to continue
msomibora.com Microsoft Excel 72
Creating a formula
Addition
Select a cell where
to create a formula
Start with equal sign
type the cell addresses of
what you want to add
Press the enter key
msomibora.com Microsoft Excel 73
Creating a formula
Multiplication
Type this formula and press Enter key
to get the Product
msomibora.com Microsoft Excel 74
Creating Formula
Division and Subtraction
Type this formula
Type this in order to
divide numbers
msomibora.com Microsoft Excel 75
Functions
Instead of using long formulas is better to
use Functions
=A1+A2+A3+A4+A5+A6+A7
=sum(A1:A7)
Function name
=sum(1st Cell address:Last Cell address)
msomibora.com Microsoft Excel 76
Using IF Function
IF function is used for logical testing ,the
structure for the IF function looks as below
=IF( Logical test," value if true”,”value if false”)
msomibora.com Microsoft Excel 77
Functions
The above examples have used only two
condition that if score is greater than 50 the
remark is Pass otherwise is Fail
Other functions can be accessed through
msomibora.com Microsoft Excel 78
Functions
There are functions like
Multiplication
=Product(1st cell address :Last cell address )
Average
=Average(1st cell address: Last cell address)
Maximum
=Max(1st cell address: last cell address)
Minimum
=Min(1st cell address: last cell address)
msomibora.com Microsoft Excel 79
Creating formula
Copying a formula
Click on the cell that has the formula you
want to copy (E2
small square in
That cell will be selected the lower, right
corner(Fill
handle)
msomibora.com Microsoft Excel 80
Creating formula
There will also be a small square in the lower,
right corner (as shown on the diagram on the
previous slide)
If you hover the mouse pointer over that cell, it
will show up as a white plus ( ) sign.
As you hover over the small square, the pointer
will turn into a black plus (+)
msomibora.com Microsoft Excel 81
Creating formula
Once the plus sign turns black (+), click and
drag the cell down one cell.
Once you release the “clicker” the formula
will be in cell “E3”.
The software makes adjustments in the
formula so that it operates on the correct
cells.
msomibora.com Microsoft Excel 82
Hand on Practice
Copy the data below and Create the formula to
find VAT of 20% on cell C2
Create the formula to calculate the new price
including the VAT on cell D2
msomibora.com Microsoft Excel 83
Hand on Practice
Copy the data as shown and answer the
questions below
Create a formula to find total and average score for
each student
Using IF Function create a formula on the remark
column that if average score is greater than 70 remark
“REGISTER “ otherwise “DON’T REGISTER”
msomibora.com Microsoft Excel 84
msomibora.com Microsoft Excel 85
Printing a worksheet
Before you print a worksheet you need to
perform page set-up
Click File menu
Click Page set up
A box will appear
msomibora.com Microsoft Excel 86
Printing a worksheet
Use this Page Tab
for setting
orientation ,paper
size ,and print
quality
msomibora.com Microsoft Excel 87
Printing a worksheet
Use this Margin tab for setting the margins
Of left,right,top and Bottom
msomibora.com Microsoft Excel 88
Printing a worksheet
Use this tab for creating a header and
footer on the worksheet
msomibora.com Microsoft Excel 89
Printing a worksheet
Use this tab if the you want to print Gridlines
,row and column heading by just checking
the corresponding Check Boxes
msomibora.com Microsoft Excel 90
Printing a worksheet
After page set-up completion the last step is to print
a worksheet
Click File menu
Click Print
Select a printer
Select/Type number of
copies you want to print
Click Print Button
msomibora.com Microsoft Excel 91
Hand on Practice
Do a page set-up of the worksheet on Hands
on practice.
Preview your work to see if there are
Headers and footers ,Gridlines.
msomibora.com Microsoft Excel 92
Thank you for listening
93
msomibora.com
