MICROSOFT EXCEL

MICROSOFT EXCEL
Module 03
Spreadsheet Program
A type of application program which manipulates numerical and string data in rows and columns of
cells.
Spreadsheets are frequently used to record information and perform calculations on the results of
investigations and experiments. In mathematics, spreadsheets are particularly useful when a number
of repetitive calculations need to be performed
Getting Started with Excel
There are several ways which you can use to Start Microsoft Excel
1st Option
i.Click Start Button
ii.Highlight All Programs
iii.Highlight Microsoft Office
iv.Click Microsoft Excel 2003
2nd Option
If there is a shortcut to Microsoft Excel on the Desktop
Double Click this Icon
3rd Option
i. Click Start Button
ii. Click Run Command,Run box will
open
iii. Type excel in this box
iv. Click OK Button
PCCD Vocational Training Project
2
Microsoft Excel Screen
Terminologies Used
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 3 Worksheets. But it is also
possible to insert more worksheet
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.
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.
PCCD Vocational Training Project
3
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.
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.
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
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
Steps to enter data
i. Select a cell in which you want to enter data.
ii. Type a data in cell
iii. Press Enter key OR click anywhere in the workspace
iv. Once you have entered data in a cell use the following keys to move from one cell to another
cell.
PCCD Vocational Training Project
4
Notice: When you enter data in a cell automatically (by default) numbers are aligned to the
Right Side of the cell and Text to the Left of the Cell
Exercise
Enter the data as shown below and answer the questions which are in the next slide.
Saving, Opening an existing
workbook, Opening new
blank workbook
Editing data in a worksheet
Data in a worksheet can be edited using a formula bar or by editing it directly in the cell or using a
shortcut.
Editing Directly in the cell
i. Double click the cell with data you want to edit.
ii. Position the insertion by using arrow keys on the keyboard
iii. Use Delete key OR Backspace to erase the characters you don’t want
iv. Type other characters
v. Press Enter key
Using a Formula bar to Edit
i. Select a cell with data you want to edit
ii. Click on the Formula bar
iii. Use arrow keys to move the insertion close to where you want to edit
iv. Use Delete key and Backspace to erase characters which you don’t want.
PCCD Vocational Training Project
5
v. Insert other character(s) if there is a need.
vi. Click a green Tick
Using a Shortcut
i. Select the cell to be edited
ii. Press F2 on the keyboard
iii. Start editing
iv. Press Enter key from the keyboard
Deleting a data in a worksheet
i. Select a data you want to delete
ii. Press delete key on the keyboard
OR
i.Select a cell with a data you wish to delete
ii.Click Edit Menu
iii.Select Clear
iv.Click Contents
Exercise
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
Working with Worksheets
Selecting Cells
Number of cells How to select
A single cell Single click it
More than one celladjacent
Click a first cell you wish to select ,then hold down SHIFT
key then click the last cell you want to select.
More than one cell
non-adjacent
Hold Down CTRL key then continue to click the cells you wish to
select
Entire column Click the column heading
Entire Row Click a row heading
Entire Worksheet Click a blank cell before the Column and row heading
PCCD Vocational Training Project
6
Inserting a Worksheet
i. Click Insert Menu
ii. Click Worksheet
OR
i. Point a sheet tab
ii. Right Click it ,a pop-menu appear
iii. Select Insert……
iv. A box will appear
v. Select General Tab
vi. Highlight Blank Sheet
vii. Click OK Button
Inserting Column & Rows
Column
i. Select a cell close to where you want to insert a new Column.
ii. Click Insert Menu
iii. Click Column(Note that the column will be inserted on the left of the selected cell or column)
Row
i. Select a cell close to where you want to insert a new Row.
ii. Click Insert menu
iii. Click Rows (Note that the row will be inserted on the top of the selected cell or row)
Copying Cells
i. Select cells you wish to copy
ii. Click Edit Menu
iii. Click Copy (or Click a copy Icon on the standard toolbar
iv. Click a cell where you want to paste
v. Click Edit menu
vi. Click Paste (or click a Paste icon on the Standard toolbar
Moving Cells
i. Select cells you wish to move
ii. Click Edit Menu
iii. Click Cut (or Click a Cut Icon on the standard toolbar
iv. Click a cell where you want to paste
v. Click Edit menu
vi. Click Paste (or click a Paste icon on the Standard toolbar
PCCD Vocational Training Project
7
Exercise
Copy the data as shown on the worksheet above and answer all the question on the next slide
1. Insert a new Column between Name and Residence Column and name it Age type any
numbers
2. Insert a new row between Maimuna and Zakaria ,and type any entries as per columns
headings
3. Copy all the contents and paste in the sheet number 2
Formatting
Formatting Cells
Data in the worksheet can be formatted using the
Formatting toolbar
OR
Format menu
Number Formatting
i. Select the numbers you want to format
ii. Click Format menu
iii. Click Cells, a dialog box will open
iv. Select a Number tab
v. Select a category you want on the
category List
vi. Apply the formats you wish
vii. Click OK button to apply
PCCD Vocational Training Project
8
Wrap Text
Wrap text allows you to display multiple lines
of text in a cell.
Type some text into the cell that will overlap
into multiple cells. Highlight the first cell with
one click. From the Menu Bar, click
Format>Cells>Alignment Tab.
Under text control click the check box in front
of Wrap text.
Merging Cells
Merging cells creates one cell.
i. Type the text into the cell that will need to be
merged. Click and hold the mouse button
over the first cell that you typed in and
highlight all of the cells to be merged
together.
ii. Click Format>Cells>Alignment Tab
iii. Click the check box in front of Merge Cells.
Text Orientation
Text Orientation allows you to rotate the text inside a cell.
i. Type the text into the cell that you want to rotate.
ii. Click Format>Cells>Alignment Tab.
iii. Under Orientation, drag the line to the desired degree of rotation or use the up and down arrows
next to degrees to change the orientation.
Creating Borders
Using borders can help to separate information in a spreadsheet.
i. Select the range of cells that require a border.
ii. Click on Format>Cells>Border Tab.
iii. Select the preset border style, or select the individual border style by clicking the lines desired
for the border.
iv. Change the style of the border lines by clicking the desired line style.
v. Change the color of the border by clicking the arrow.
Exercise
Copy the data below and answer all the questions written on the next slide
PCCD Vocational Training Project
9
1.Insert a new row above the headings Row, merge cells A1 to D1 and type ”Payment
Collection 2007” as your heading
2.Format the figures in the Paid Fee column to be in Tanzanian currency
3.Format the score numbers for each student to be in percentages
4.Change the font size of the heading to be 18 and apply any light color for shading.
5.Format the date of admission to display the Month as a text
6.Rotate the word score at an angle of negative 15 degrees.
Sort Data
This means to arrange data in a specific order; you can arrange data in ascending (1-10) or
descending (10-1)
Steps
i. Select the range of cells you want to order
ii. Click on the Data menu
iii. Click Sort
iv. Sort warning dialog box will appear
v. Select expand the selection if you want to sort all data in the worksheet or continue with the
current selection if you want to sort the current column
vi. Click Sort
vii. Select the option you want either ascending or descending
viii. Click Ok
Header and Footer
Header: Is the area between the top margin of the worksheet and the edge of the page/worksheet
Footer: Is the area between the bottom margin of the worksheet and the edge of the page/worksheet.
In these areas you can insert graphics, text and page numbers.
Steps
i.Click on View menu
ii.Click on header and footer
iii.Click on Custom header or custom footer
iv.Type in either left section, center section or right section
v.On the row of button click on what you want to insert
vi.Click Ok.
Hide Column, Row and Worksheet
This is used to hide some column and rows or worksheet you do not want to include when you print
the document
i.Select what you want to hide (either column or row or worksheet)
PCCD Vocational Training Project
10
ii.Click on the Format menu
iii.Point on what you selected (either column or row or worksheet)
iv.Click on Hide
Unhide Column Row or Worksheet
i.Click on Format menu
ii.Point on what you want to unhide
iii.Click on Unhide
Maintaining Security
Security entails preventing unauthorized users from editing or opening you workbook by setting a
password of access. Password is the secret key that the user uses to have the right of access and
modification. This can be numeric or alphabet or alphanumeric
Note: A password has to be easy for you to remember but difficult for others to guess.
Protecting Worksheet from Editing
Steps
i. Click on Tools menu
ii. Point on Protection
iii. Click on Protect Sheet
iv. Type the password to protect in the Password to unprotect sheet text box
v. Check what users are allowed to do on the respective worksheet.
vi. Click Ok
vii. Retype your password to confirm the password
viii. Click Ok
Protecting a Worksheet for Editing
i. Click on the Tools menu
ii. Point on Protection
iii. Click Unprotect Sheet
iv. Specify the password correct password used to protect sheet
v. Click Ok.
Protecting Worksheet from Opening
i. Click on Tools menu
ii. Click on Option
iii. Click on Security tab
iv. Type your password in the Password to open text box
v. Click Ok
vi. Retype your password to confirm the password and the click Ok.
Formula & Functions
Formula
A spreadsheet needs some commands from the user. You must specify what you want the application
do for you. This is done through formulae and functions.
PCCD Vocational Training Project
11
Creating a formula
Steps
i. Decide where to place the result and select the cell
ii. Start by entering the equal sign (=)
iii. Enter the formula
iv. Press Enter key on the keyboard
Examples of Formula
Addition
=300+40 (using constants)
=D3+A6+B2 (cell values)
=300+F3 (numbers and cell values)
=AutoSum
Multiplication
=300*40 (using constants)
=D3*A6*B2 (cell values)
=300*F3 (numbers and cell values)
= AutoSum>Product
Subtraction
=300-40 (using constants)
=D3-A6 (cell values)
=300-F3 (numbers and cell values)
Division
=300/40 (using constants)
=D3/A6 (cell values)
=300/F3 (numbers and cell values)
PCCD Vocational Training Project
12
Functions
Instead of using long formulas is better to use functions. Also functions are easy
Formula Functions
=300+40 =SUM(300,40)
=D2+D3+D4+D5+D6+D7+D8 =SUM(D2:D8)
=300+D4 =SUM(300,D4)
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”)
Nested IF Functions
Is an IF function where one IF function is
within another. This function is used to decide from more than one option. This is the one used to
find grades in results
Example
Given:
Colum name=F
Row name=4
Marks Ranges
81-100=A
61-80=B
41-60=C
21-49=D
0-20=F
A function to define this could be
=IF(F4>80,”A”,IF(F4>60,”B”,IF(F4>40,”C”,IF(F4>20,”D”,IF(F4>0,”F”)))))
OR
=IF(F4>80,”A”,IF(F4>60,”B”,IF(F4>40,”C”,IF(F4>20,”D”,”F”))))
Other function
Maximum
This formula returns the maximum number in a range of selected cells.
=MAX(300,40,65,78,90)
=MAX(D2:D8)
=MAX(300,D4,67,G6,89) and =AutoSum>Max
Minimum
=MIN(300,40,65,78,90)
=MIN(D2:D8)
=MIN(300,D4,67,G6,89)
=AutoSum>Min
Count
The Count Function returns the number of values in a range and can quickly determine how many
cells contain numeric information.
=COUNT(300,40,65,78,90)
=COUNT(D2:D8)
=COUNT(300,D4,67,G6,89) and =AutoSum>COUNT
PCCD Vocational Training Project
13
Copying Formula or Function
i. Click on the cell that has the formula you want to copy (E2)
ii. There will also be a small square in the lower, right corner (as shown on the diagram on the
previous slide)
iii. If you hover the mouse pointer over that cell, it will show up as a white plus ( ) sign.
iv. As you hover over the small square, the pointer will turn into a black plus (+)
v. Once the plus sign turns black (+), click and drag the cell down one cell.
vi. Once you release the “clicker” the formula will be in cell “E3”.
vii. The software makes adjustments in the formula so that it operates on the correct cells.
Exercise
i. Copy the data below and Create the formula to find VAT of 20% on cell C2
ii. Create the formula to calculate the new price including the VAT on cell D2
Copy the data as shown and answer the questions below
i. Create a formula to find total and average score for each student
ii. Using IF Function create a formula on the remark column that if average score is greater
than 70 remark “REGISTER “ otherwise “DON’T REGISTER”
PCCD Vocational Training Project
14
CHARTS
Types of chart and their uses
1. Area - An Area chart emphasizes the magnitude of change, rather than time and the rate of
change. It also shows the relationship of a part to a whole by displaying the sum of the plotted
values.
2. Bar - A Bar chart shows individual figures at a specific time or shows variations between
components but not in relationship to the whole.
3. Bubble - A Bubble chart compares sets of three values in a manner similar to a scatter chart with
the third value displayed as the size of the bubble marker.
4. Column - A Column chart compares separate (non-continuous) items as they vary over time. 5.
Cone - A Cone chart displays columns with a conical shape.
6. Cylinder - A Cylinder chart displays columns with a cylindrical shape.
7. Doughnut - A Doughnut chart shows the relationship of parts to the whole.
8. Line - A Line chart shows trends and change over time at even intervals. It emphasizes the rate
of change over time rather than the magnitude of change.
9. Pie - A Pie chart shows proportions and relationships of parts to the whole.
10. Pyramid - A Pyramid chart displays columns with a pyramid shape.
11. Radar - A Radar chart emphasizes differences and amounts of change over time and variations
and trends. Each category has its own value axis radiating from the center point. Lines connect all
values in the same series.
12. Stock - A Stock chart shows four values for a stock - open, high, low, and close.
13. Surface - A Surface chart shows trends in values across two dimensions in a continuous curve.
14. XY (Scatter) - A Scatter chart shows either the relationships among numeric values in several
data series or plots the interception points between x and y values. It shows uneven intervals of data
and is commonly used in scientific data.
Creating Charts
The Chart Wizard(1)–chart Type
i. Select or click within the data
ii. Click on Insert menu
iii. Click Chart
iv. The Chart Wizard box will appear, which
will guide you through the chart creation
process
Creating a chart allows you to represent your data in a
visual format.
OR
Click on the Chart icon from the
standard bar
PCCD Vocational Training Project
15
The Chart Wizard (2) – Data Source
Allows you to select the chart source data that will be
used to construct your chart.
With the Data range tab selected.
i. Click the Data range box to select the
information to be used in the chart.
ii. Select Cells A1 through C4 and return to the
Chart Wizard.
iii. To change the way the rows and columns
appear, click the appropriate radio button.
iv. Click the Series Tab. The Series box shows the column headings that were selected from
the spreadsheet.
v. Across from the Series are the Name box and the Values box for the highlighted series. The
boxes show the location of the information on the spreadsheet.
vi. The Category (X) axis labels can be a category or value labels from the spreadsheet. Click
the box to the right to see where the information came from.
vii. Click Next.
Chart Wizard (3) – Chart Options
Step 3 allows you to add titles to your chart, arrange the
legend, add Data labels, and work with the data table.
i. Chart title allows you to put a title at the top of the
chart. Add a title to your chart.
ii. Category (X) axis allows you to put a title
underneath the X axis or along the bottom of the
chart.
iii. Value (Z) axis allows you to put a title to the left
side of the chart.
iv. Click the Legends tab to adjust the placement of the
legend. Place the legend at the bottom of the chart.
v. Click Data labels. This allows you to add labels on the chart depending on what information
is needed.
vi. Click on all tabs and do as required
vii. Click Next.
Chart Wizard (4) – Chart Location
Step 4 allows you insert the chart either as a new sheet or as an object in the current sheet. This is
the final step
i. Select the chart location of
your choice: inserted into the
workbook as a new sheet or as
an object in the current sheet.
ii. Click Finish.
PCCD Vocational Training Project
16
Chart Formatting
To format any part of the chart double click on it. Items that can be formatted are:
Chart Area- The chart area is the “empty” chart background
Legends
Axis
The Plot Area- The plot area is the “empty “ area of the actual graph
Gridlines
The Data Series
Exercise
i. Use the data above to create a 3D bar chart type
ii. Format the chart as much as you can
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.
i. Point within the chart plot area
ii. Right click ,a pop-up menu appears
iii. Select an option you want in order to access one of the Four steps of the Chart Wizard
iv. A Chart wizard step box will appear
v. Apply changes you want then click Ok Button
PCCD Vocational Training Project
17
Printing a worksheet
Before you print a worksheet you need to perform:
Page Setup
i. Click File menu
ii. Click Page setup
iii. A box will appear
iv. Click Page tab to set orientation ,paper
size ,and print quality
v. Click Margin tab to set the margins of
left, right, top and bottom
vi. Click Header/Footer tab to create a
header and footer on the worksheet
vii. Click Sheet tab if then you want to print
Gridlines ,row and column heading by
just checking the corresponding Check
Boxes
Printing
After page set-up completion the last step is to print a
worksheet
i. Click File menu
ii. Click Print
iii. Select a printer
iv. Select/Type number of copies you want to print
v. Click Print Button
Setting Print Area
Sometimes you need to specify what you need to print from the whole of the worksheet.
Steps
i. Select what you want to print
ii. Click on File menu
iii. Point on Print Area
iv. Click on Set Print Area
v. Setup the page to choose Scaling
vi. During printing under Print what option select Selection