LESSON 1

TOPIC: INTRODUCTION TO MICROSOFT EXCEL

OBJECTIVES
Students should:
● know the functions of a spreadsheet program
● be able to label the parts of the Microsoft Excel workbook
● be able to explain each part of the Microsoft Excel workbook
● be able to label the parts of the Standard and Formatting toolbars
● know the function of each part of the Standard and Formatting toolbars

MICROSOFT EXCEL

Microsoft Excel is a spreadsheet program that is used for:

● Organizing data
● Completing calculations (using formula and/or functions)
● Making decisions
● Graphing data

1
Complied from: CXC Information Technology, Microsoft Excel 2000/2002 Made Simple,
Information Technology for CXC, Microsoft Office 2000 Introduction to Concepts and Techniques
 PARTS OF THE MICROSOFT EXCEL WORKBOOK

Workbook
This is an excel file that stores information. Title Bar
By default, workbooks contain three The title bar is the first bar from the top of
worksheets (Sheet1 – Sheet3). A workbook the screen that displays the control icon and
can have up to 255 sheets. three buttons in the right-hand corner.

Worksheet File name

The worksheet is organized into a The title bar at the top of the screen will be
rectangular grid containing columns reading Book1 whenever you start up the
(vertical) and rows (horizontal). There are spreadsheet program. Book1 is the default
sheet tabs at the bottom of the workbook file name that remains there until you, the
window that separate each sheet. Each tab user, save and name the file with which you
can be renamed. are working or until you open a file with
another name.
Name Box
The name box displays the cell Menu Bar
reference/address of the active cell. The menu bar displays menu options which,
when clicked, displays drop-down listings of
Cell reference/address associated actions. It is identified by the
This is a combination of column letters and options File, Edit, View, etc.
row numbers that uniquely identifies a cell.
The column letter must be first followed by Standard Toolbar
the row number e.g. A1, B7. The standard toolbar contains buttons
representing features that are often used.
Cell For example the save button, print button
A cell is the intersection of a row and a etc.
column. A cell can contain a label, a
numeric value, or a formula. It is the basic Formatting Toolbar
unit of a worksheet. The formatting toolbar contains buttons
representing the most recently used
Active Cell text-editing and text-layout features. For
An active cell is identified by a dark border example the bold button (represented by a
around the cell. The column and row B), the cut button (represented by a scissors)
headings also light up. etc.

Formula Bar Rows

The formula bar is located next to the Name Rows are the horizontal lines on the
box. It provides a space for typing or worksheet identified by numbers. A
editing cell data. Data is displayed in it as worksheet has 65,536 rows.
you type.
Columns
Status Bar Columns are the vertical lines on the
The status bar is located at the bottom of the worksheet identified by letters. A worksheet
Excel window. This bar displays a brief has 256 columns.
description of the command selected in a
menu, the function of the button the mouse Gridlines
pointer is pointing to, or the current activity
(mode – ready and enter) in progress.
2
Complied from: CXC Information Technology, Microsoft Excel 2000/2002 Made Simple,
Information Technology for CXC, Microsoft Office 2000 Introduction to Concepts and Techniques
The horizontal and vertical lines on the
worksheet itself are called gridlines.

PARTS OF THE STANDARD TOOLBAR

STANDARD TOOLBAR
No. Name of Icon Purpose
1 New Workbook Creates a new workbook
2 Open Opens a workbook
3 Save Saves a workbook
4 E-mail Sends a file to an e-mail address
5 Print Prints a workbook
6 Print Preview Previews workbook data
7 Spelling Checks the spelling in a worksheet
8 Research Researches data in the worksheet
9 Cut Moves data in a worksheet
10 Copy Copies data in a worksheet
11 Paste Pastes data in the worksheet
12 Format Painter Copies formats
13 Undo Undoes the preceding action
14 Repeat Repeats the preceding action
15 Insert Hyperlink Inserts or edits hyperlink
16 AutoSum Inserts a SUM function in the worksheet
17 Sort Ascending Sorts data in ascending order
18 Sort Descending Sorts data in descending order
19 ChartWizard Accesses the ChartWizard
20 Drawing Displays the drawing toolbar
21 Zoom Control Controls the display of a worksheet
22 Help Provides on-screen Help on Excel

3
Complied from: CXC Information Technology, Microsoft Excel 2000/2002 Made Simple,
Information Technology for CXC, Microsoft Office 2000 Introduction to Concepts and Techniques
 PARTS OF THE FORMATTING TOOLBAR

FORMATTING TOOLBAR
No. Name of Icon Purpose
1 Font Changes the font style
2 Font size Changes the font size
3 Bold Makes cell contents bold
4 Italic Makes cell contents italic
5 Underline Underlines cell contents
6 Align Left Aligns cell entries to the left
7 Centre Aligns cell entries to the centre
8 Align Right Aligns cell entries to the right
9 Centre Across Aligns text on the left and right margins
Column
10 Currency Style Formats a number to show dollar ($) sign, a comma as the 1000’s
separator and 2 decimal places e.g. $1,205.36
11 Percentage Style Multiplies a number by 100 and adds percentage (%) sign and 2
decimal places, e.g. 95.35%
12 Comma Style Formats a number to show a comma as the 1000’s separator and 2
decimal places e.g. 1,205.36
13 Increase Decimal Adds one decimal place to number
14 Decrease Decimal Removes one decimal place from number
15 Decrease Indent Moves information to the left
16 Increase Indent Moves information to the right
17 Borders Adds a border to a selected range

4
Complied from: CXC Information Technology, Microsoft Excel 2000/2002 Made Simple,
Information Technology for CXC, Microsoft Office 2000 Introduction to Concepts and Techniques
18 Fill Colour Adds background colour to cell(s)
19 Font Colour Adds colour to the characters in cell(s)

LESSON 2
TOPIC: MOVING AROUND MICROSOFT EXCEL AND
ENTERING DATA IN MICROSOFT EXCEL
OBJECTIVES
Students should:
● know the different ways to move around in Microsoft Excel
● know the various types of data that can be entered into Microsoft Excel
● be able to identify/label the types of data that can be entered into Microsoft Excel
● be able to enter data into Microsoft Excel

MOVING AROUND THE Types of Data that can be entered

WORKSHEET
Text
To move from cell to cell, you may use the These are letters and numbers added to the
arrow keys, the tab key or simply click in work sheet cell (can contain 1 - 32,767) that
the cell required. If the spreadsheet data will not be used as a value or a label.
spreads to cells that cannot be seen on the
screen, you may use your scroll bars to shift Value
the screen before clicking, or click on the These are numbers that are entered in a cell
arrow in the name box, type in the cell to be used in calculations. By default,
reference/address and press the ENTER key. values align to the right. Values may later be
From any point of the spreadsheet, you may aligned to suit the user’s wishes.
return to the beginning cell A1 by pressing
Ctrl and Home. Label
This is a text entry into the cell that is used
Other methods of moving around to describe the data contained in a row or a
● Select Edit from the menu. column. By default, labels align to the left.
● Click Go To. Labels may later be aligned to suit the user’s
● Type the cell reference/address wishes.
● Click OK or press ENTER
Two types of Labels
ENTERING DATA IN MICROSOFT Text labels
EXCEL These may be made up of letters only or
may be a combination of letters, numbers
To enter data in a cell, position the cursor in and/or symbols.
the cell and type the data. As you type, the
data is displayed in the chosen cell, as well Numeric labels
as on the formula bar. To indicate These are numbers that have an apostrophe
completion, do one of the following: (‘) placed at the beginning of them, to make
● Press the ENTER key or them appear as labels.
● Press an arrow key or
● Click another cell

5
Complied from: CXC Information Technology, Microsoft Excel 2000/2002 Made Simple,
Information Technology for CXC, Microsoft Office 2000 Introduction to Concepts and Techniques
 Formula
This is a sequence of values, cell addresses
and/or operators that:
• Begin with an equal sign
• Produces a new value
For example = B4 + C4
Function
This is a preset/predefined formula that
performs calculations by using specific
values called arguments in a particular order
or structure. For example = Sum(A1: A6)

● Click on AutoFit Selection

Column Widths
All worksheets in a workbook are set for a
standard column width (8.43). Sometimes it
is necessary to adjust the column width
when entering data that is too long for the Using the Width option
column. Steps
● Position the cursor in the column you
Using the AutoFit Selection want to change the width of
Steps ● Click on Format
● Position the cursor in the cell containing ● Go to Column
the longest item of data ● Click on Width
● Click on Format ● Enter the exact column width you desire
● Go to Column
6
Complied from: CXC Information Technology, Microsoft Excel 2000/2002 Made Simple,
Information Technology for CXC, Microsoft Office 2000 Introduction to Concepts and Techniques
● Click OK

LESSON 3
TOPIC: FORMATTING THE WORKSHEET
AND ITS DATA
OBJECTIVES

Students should:
● know the various formatting features for text that can be used in Microsoft Excel
● know the various formatting features that can be used make changes to the worksheet

FORMATTING THE WORKSHEET Renaming a sheet using the left mouse

button
There are many ways to format the Steps
worksheet to suit your needs. Formatting ● Double-click on the current sheet name
emphasizes certain entries and makes the ● Type the new name
worksheet easier to read and understand. ● Press ENTER

Inserting a sheet
By default, workbooks contain three
worksheets (Sheet1 – Sheet3). Sometimes it
is necessary to insert other sheets into the Renaming a sheet using the right
workbook. A workbook can have up to 255 mouse button
sheets. Steps
● Right-click on the current sheet name
Steps ● Click rename
● Click on Insert ● Type the new name
● Click on Worksheet ● Press ENTER
This places the new sheet to the left of the Renaming a sheet using the Menu bar
active sheet. The worksheet can then be
Steps
moved by placing the cursor over the sheet,
● Click on Format
clicking, and dragging it to the required
● Go to Sheet
position.
● Click on rename
● Type the new name
Renaming a sheet ● Press ENTER
It is good to rename sheets to easily identify
what the type of information that the sheet
Inserting columns/rows
contains. Worksheet names can be up to 31
When a column is added to a spreadsheet, it
characters. There are several ways to
will appear to the left of the column where
rename a worksheet.
the cursor was positioned. When a row is
added to a spreadsheet, it will appear above
the row where the cursor was positioned.
7
Complied from: CXC Information Technology, Microsoft Excel 2000/2002 Made Simple,
Information Technology for CXC, Microsoft Office 2000 Introduction to Concepts and Techniques
 ● Go to Row
Steps ● Click Unhide
● Position the cursor in the appropriate
column/row Unhiding columns in the spreadsheet
● Click on Insert Steps
● Click on Rows/Columns ● Highlight the cells in the column to the
left and in the column to the right of the
Deleting columns/rows hidden column(s)
Steps ● Click on Format
● Position the cursor in the column or row ● Go to Column
to be deleted ● Click Unhide
● Click on Edit
● Click on Delete Adjusting Row Height
● Choose Entire row or Entire column Steps
● Click OK ● Position the cursor in the row you want
to change the height of
Freezing panes ● Click on Format
This option is useful when working with ● Go to Row
large spreadsheets. In the process, the cells ● Click on Height
to the left of and above the active cell ● Enter the exact row height you desire
position are frozen. This means that they do ● Click OK
not move from the screen no matter how the
data is shifted. FORMATTING WORKSHEET DATA
(VALUES AND LABELS)
Steps
● Position the cursor in the area where you Formatting is changing the appearance of
want the panes to be frozen data entered into the spreadsheet.
● Click on Window
● Click Freeze Panes Examples of formatting for values
● Shift the screen to view the required ● General, Number, Currency,
data Accounting, Date, Time, Percentage etc.

Unfreezing panes Examples of formatting for labels

Steps ● Bold, Underline, Italics
● Click on Window ● Changing font size, etc.
● Click Unfreeze Panes
When formatting a value or a label it is
Hiding rows/columns in the spreadsheet necessary to highlight the area in the
You can hide data that may not be relevant spreadsheet where the data is found. This
to a spreadsheet or data that you do not want area is called a range.
anyone to see.
Steps A range is a block of cells that can be
● Highlight the columns/rows to be hidden highlighted, manipulated, named or
● Click on Format formatted as a group.
● Go to Column or Row
Example 1
● Click on Hide
If you highlight the cells F4, F5 and
Unhiding rows in the spreadsheet F6 this range of cells can be indicated as
Steps F4:F6.
● Highlight the cells in the row above and
Example 2
in the row below the hidden row(s)
A1:G2 includes all the cells in columns A
● Click on Format
through G in rows one and two.
8
Complied from: CXC Information Technology, Microsoft Excel 2000/2002 Made Simple,
Information Technology for CXC, Microsoft Office 2000 Introduction to Concepts and Techniques
 Highlighting a non-adjacent cell range
Highlighting an adjacent cell range Steps
Steps ● Click the first cell and drag in the
● Click on the first cell you want to direction of the cells to be selected
highlight ● Press and hold down the Ctrl button
● Drag the mouse in the direction of the while dragging through additional
cells to be included in the selection ranges to include in your selection

Formatting figures Centering data across columns

Steps To take off formats
● Highlight the cell(s) to be formatted After you select a number format, it can be
● Click on Format taken off by carrying out the following steps.
● Click on Cells
● Select the Number tab Steps
● Choose the category required ● Click on Format
● Click OK ● Click on Cells
● Select the Number tab
● Choose the General category
● Click OK

This function may be used for centering

main and sub-headings/titles. This feature
can only be done on one row at a time

9
Complied from: CXC Information Technology, Microsoft Excel 2000/2002 Made Simple,
Information Technology for CXC, Microsoft Office 2000 Introduction to Concepts and Techniques
 LESSON 4
TOPIC: INSERTING FORMULAS, COPYING FORMULAS
AND USING REFERENCES

OBJECTIVES

Students should:
● be able to insert appropriate formulas to be used in calculations
● be able to copy formulas that have been inserted in a cell
● know the difference between relative addressing and absolute addressing

USING SIMPLE FORMULAS

A formula allows you to calculate and analyze data in a spreadsheet. A formula must begin with
an equal sign (=) and can contain values, cell addresses/references and operators. The operators
that can be used in a formula fall into two categories, Arithmetic and Comparison.

Examples of formula

Formula Explanation
= B4*C4 To multiply the value in B4 and C4
= D3/D10 To divide the value in D3 by the value
in D10
= C20 – D20 To subtract the value in D20 from the value
in C20
= F15 + E15 To add the values in F15 and E15 together

10
Complied from: CXC Information Technology, Microsoft Excel 2000/2002 Made Simple,
Information Technology for CXC, Microsoft Office 2000 Introduction to Concepts and Techniques
REFERENCES

Relative Cell Addressing

A relative address is a standard cell reference
that appears like this: A1, E15, and M42. A
relative address changes if you copy a
formula that contains it to a new location on
the worksheet.

Example of relative cell addressing

When to use Absolute Cell Addressing

A cell is usually fixed in a formula when the
contents of that one specific cell must be
used repeatedly to achieve accuracy in the
formula as it is copied. In some cases, users
may place the value at the top of the
spreadsheet in a cell by itself (indicating that
it is a key value) and label it appropriately.
Popular examples of values used as absolute
values are discounts being offered to all
clients/customers or for all items in a
spreadsheet, interest rates, salary increase
percentages and so on.

Absolute Cell Addressing

An absolute address in a formula refers to a
specific cell location or range. It always
points to the location of a specific cell, even Mixed Cell Addressing
if you copy it. Absolute addresses are created Mixed references are a combination of
by adding a dollar ($) sign in front of each relative and absolute references. While
character in the cell address. For example, ‘absolute’ fixes the entire cell (both row and
$C$8 always refers to cell C8. column), ‘mixed’ fixes either the column or a
row.

Example of Absolute Cell Addressing

11
Complied from: CXC Information Technology, Microsoft Excel 2000/2002 Made Simple,
Information Technology for CXC, Microsoft Office 2000 Introduction to Concepts and Techniques
Example of Mixed Cell Addressing A$1 The column letter will
change, but not the row
Cell Address Explanation number
$A1 The row number will
change, but not the
column letter
LESSON 5
TOPIC: FUNCTIONS

OBJECTIVES

Students should:
● be able to insert appropriate functions to be used in calculations

FUNCTION
A function is a built-in formula that can:
● automatically perform calculations
● assist in decision making based on information provided

There are approximately 400 functions that can be performed in Excel.

Ways to enter functions

● Using the keyboard or mouse
● Using the Insert option on the Menu bar

Function Checklist
● It must begin with an equal (=) sign
● There must be a function name
● There must be arguments enclosed in parentheses (brackets). An argument is the information
required to calculate the function (usually a range). A function can have a maximum of 30
arguments and a minimum of 1.

Layout

= Function Name(Range of cells)

Example 1
Function used to add consecutive ranges

= Sum(B2:B6)

Explanation of Example 1
The contents of cells B2, B3, B4, B5 and B6 will be added together. One argument is used in this
example.

Example 2
Function used to add non-consecutive ranges

= Sum(B2:B4,B6)
12
Complied from: CXC Information Technology, Microsoft Excel 2000/2002 Made Simple,
Information Technology for CXC, Microsoft Office 2000 Introduction to Concepts and Techniques
Explanation of Example 2
Only the contents of cells B2, B5 and B6 will be added. Three arguments are used in this
example.

Below is a list of commonly used functions and their purpose

Ways to enter functions

Using the keyboard or mouse

Steps
● Position the cursor in the cell where the
function is to be displayed
● Type in the function needed paying
attention to the function checklist Using the Insert option on the Menu bar
● Press ENTER Steps
13
Complied from: CXC Information Technology, Microsoft Excel 2000/2002 Made Simple,
Information Technology for CXC, Microsoft Office 2000 Introduction to Concepts and Techniques
● Position the cursor in the cell where the ● Choose the required function
function is to be displayed ● Type in the function arguments in the
● Click Insert appropriate area
● Click Function ● Click OK
● Go to Select a category and choose All

LESSON 6
TOPIC: COPYING AND MOVING DATA
OBJECTIVES

Students should:
● be able to copy data from one sheet to another
● be able to move data from one sheet to another
● be able to copy data obtained from formulae
● be able to link data
● be able to link worksheets

COPYING OR MOVING DATA FROM ONE SHEET TO ANOTHER

Sometimes it is necessary to copy data from one sheet to another and make changes to it in order
to perform new calculations.

Steps
● Highlight the required data
● Click on the Cut or Copy icon
● Select the sheet where the data will be copied or moved
● Select the specific cell position where the data is to be copied or moved
● Click the Paste icon

COPYING DATA OBTAINED FROM FORMULAE

If you are copying only data that is based on a formula to another location, you may be surprised
to see a #REF! error or zero or a dash displayed after pasting.

This occurs because the data being copied depends on cell addresses. The Paste Special feature is
used to eliminate this problem, allowing the user to paste the data as Values. This means that in
the pasted data, the Values will appear without the formula.

Copying with Paste Special

Steps
● Highlight the data being copied
● Click the Copy icon
● Move the cursor to the starting position
for the copied data
● Click on Edit
● Click Paste Special
● Select Values
● Click OK

14
Complied from: CXC Information Technology, Microsoft Excel 2000/2002 Made Simple,
Information Technology for CXC, Microsoft Office 2000 Introduction to Concepts and Techniques
Linking data
A ‘link’ provides an automatic update of copied data as long as the original data is changed.
Links may be done when copying:
● To another part of the spreadsheet
● To another sheet within the same workbook
● To another file

Steps
● Highlight the data to be copied
● Click the Copy icon
● Move the cursor to the starting position for the
copied data
● Click on Edit
● Click Paste Special
● Select the Paste Link button

Linking worksheets
Sometimes in a worksheet, you may be required to calculate cells which are in different sheets.
For example, in a workbook you may want to add the value of cell A15 in the current worksheet
and cell A17 in a second worksheet. These two cells can be added by using the format
“sheetname!celladdress”. The formula for this example would be “=A15+Sheet2!A17”. this
formula is indicating that the value of cell A15 in the current worksheet is added to the value of
Excel A17 in the worksheet named “Sheet2”.

Steps
● Move the cursor to the sheet and to the position where you want to perform the formula
● Type the equal (=) sign
● Type or select with the mouse, the cell(s) in the current sheet e.g. =A17
● Type the calculation sign e.g. +
● Select the sheet which contains the next value and click on the cell
● Press ENTER

15
Complied from: CXC Information Technology, Microsoft Excel 2000/2002 Made Simple,
Information Technology for CXC, Microsoft Office 2000 Introduction to Concepts and Techniques
 LESSON 7
TOPIC: SORTING DATA
OBJECTIVES

Students should: ● be able to sort by more than one

● be able to sort data in ascending order column
● be able to sort data in descending order ● be able to insert a header and a footer
● be able to sort by one column

SORTING DATA
Data in a spreadsheet may be arranged in order
based on one or more column. During sorting
all data is re-arranged based on the sorted
column.

Data may be sorted in:

● Ascending Order (A – Z or 1 – 20)
● Descending Order (Z – A or 20 – 1)

Example of sorting by one column

(the Fruits column) in Ascending order
A common mistake when sorting is to select
only the column of data that is being sorted.
When this is done, movement of data would be
restricted to the selected column. The data in
the other columns would remain in place
causing a loss of data integrity.

Sorting by one column

Steps
● Highlight all the data
● Click on Data
● Click on Sort
● In the Sort by section, choose the column
being sorted and the sort order required
● Click OK

16
Complied from: CXC Information Technology, Microsoft Excel 2000/2002 Made Simple,
Information Technology for CXC, Microsoft Office 2000 Introduction to Concepts and Techniques
 column (secondary key)) in Descending
order

Sorting by more than one column

In cases where data is to be sorted by more
than one column, the user must determine
which column should come first, second, etc.
Three columns may be sorted at the same time.

Steps
● Highlight all the data
● Click on Data
● Click on Sort
● Choose the appropriate column for the
Sort By section and the required sort order
● Repeat for the first Then By section
● Repeat for the second Then By section if
necessary
● Click OK

Example of Sorting by more than one

column (by the Surname column
(primary key) then by the First Name
17
Complied from: CXC Information Technology, Microsoft Excel 2000/2002 Made Simple,
Information Technology for CXC, Microsoft Office 2000 Introduction to Concepts and Techniques
 LESSON 8
TOPIC: FILTERING
OBJECTIVES ● Click OK
● Highlight the results and click on Copy
Students should: ● Paste in a new area of the same worksheet
● be able to use the AutoFilter feature
● be able to use the Advanced Filter
feature

FILTERING
This is finding a subset of records in a list that
matches the criteria (data used to search for
records) you specify for a column.

Ways to Filter
● Using the AutoFilter feature
● Using the Advanced Filter feature
or in a new sheet
Using the AutoFilter feature ● Click on Data, Go to Filter, Click on
Steps AutoFilter
● Position the cursor in the cell containing
the information to be filtered
● Click on Data
● Go to Filter
● Click on AutoFilter
● Click on the drop down arrow of the
column that you want to filter
● Click Custom
● Click the first drop down arrow and select Example of Using AutoFilter feature to
the type of information you want to find show a list of the BOOK IDs that begin
out with the letter ‘B’
● Click in the second box in the first row on
the right and type in the information that
you want
Using the Advanced Filter feature
Steps

18
Complied from: CXC Information Technology, Microsoft Excel 2000/2002 Made Simple,
Information Technology for CXC, Microsoft Office 2000 Introduction to Concepts and Techniques
● Copy column heading(s) containing information you are looking for.
● Paste the column heading(s) in a blank area at the bottom of the spreadsheet data
● Enter the criteria in the row below the column heading.
● Click Data
● Click Filter
● Click Advanced Filter
● Click on the collapse button to the right of List Range. Select the cells that contain the data
to be searched. When the selection is over click the collapse button to return to the Advanced
Filter dialog box.
● Click on the collapse button to the right of Criteria Range. Select the cells that contain the
criteria. When selection is over click the collapse button to return to the Advanced Filter
dialog box.
● Check the Copy to Another Location option. Click on the collapse button to the right of
Copy to. Choose an area on the spreadsheet (usually at the bottom of the spreadsheet data) to
place the copied records. When the selection is over click the collapse button to return to the
Advanced Filter dialog box.

Example of Using Advanced Filter feature to show a list of all the records where
amount sold is greater than 200.

EXAMPLE USING ONE CONDITION

19
Complied from: CXC Information Technology, Microsoft Excel 2000/2002 Made Simple,
Information Technology for CXC, Microsoft Office 2000 Introduction to Concepts and Techniques
 1. List all the records where the Net Due is more than $600.00.

NET DUE
> 600

EXAMPLES OF WORKING WITH TWO CONDITIONS

When you are working with two or more conditions, the AND or OR operators must be used.
AND is used when both conditions must be true. When using AND, the information is entered
next to each other.

Criteria from different columns

1. List all the records where the room length is greater than 5 and the room width is less
than 7.

ROOM ROOM
LENGTH WIDTH
>5 <7

Criteria from the same column

2. List all the records where the discount is between $40.00 and $60.00.

DISCOUNT DISCOUNT

> 40 < 60

OR is used when either condition is true. When using the OR operator, the information is entered
below each other.

Criteria from the same column

3. List all the records where the total cost is greater than $1000.00 or less than $400.00.

COST OF
CARPETING
> 1000
< 400

Criteria from different columns

4. List all the records where the floor area is greater than 30 or the total cost is more than
$600.00.

20
Complied from: CXC Information Technology, Microsoft Excel 2000/2002 Made Simple,
Information Technology for CXC, Microsoft Office 2000 Introduction to Concepts and Techniques
 FLOOR AREA COST OF
CARPETING
> 30
> 600
LESSON 9
TOPIC: CREATING CHARTS/GRAPHS

OBJECTIVES

Students should:
● be able to create a chart/graph accurately from given data

Graphs and charts are common ways of

illustrating data found in spreadsheets.
They are important because they can simplify
numerical data that is confusing and make it
easier to interpret. Data series
This is a group of related data plotted in a chart
Types of charts that originates from rows or columns that make
Pie up the range on a single worksheet. You can
A pie chart is used to compare all parts to the plot one or more data series in most charts.
whole. Two ranges of data are required – one
range of labels and one range of values. The Axes
range of values is called a data series. No axes These are vertical and horizontal lines against
are required. which data is plotted. The X-axis is referred to
as the category axis and the Y-axis is known as
Column the value axis.
In Excel, the default chart type is the 2-D
column chart. A column chart is used to To determine between these two axes, always
display data changes over a period of time or remember that the category or (X-axis) is
illustrates comparison among items. It determined by the group of labels which were
consists of a category axis (X-axis) organized selected, while the (Y-axis) is determined by
horizontally, and a value axis (Y-axis) the data series selected.
organized vertically. One range of labels and
more than one data series may be used. Legends
This is a box that identifies the patterns or
Bar colours assigned to the data series or categories
A bar chart illustrates comparison among in a chart.
individual items. It consists of a category axis
(X-axis) organized vertically and a value axis Creating a chart/graph in Microsoft
(Y-axis) organized horizontally. Like the Excel
column chart, this also has the potential for Steps
comparison and may use more than one data ● Highlight the data required for the
series. chart/graph (no blank rows should be
included in the selection)
A chart contains the following: ● Click on Insert
● Click on Chart
Data range
This is the data selected to create the chart.

21
Complied from: CXC Information Technology, Microsoft Excel 2000/2002 Made Simple,
Information Technology for CXC, Microsoft Office 2000 Introduction to Concepts and Techniques
A chart wizard dialog box guides you towards 2. Chart Source Data
the final chart by going through four steps, 3. Chart Options
each with its own dialog box. 4. Chart Location
1. Chart type

Step 1
Chart type
In this dialog box you can select the type of chart that you want.
Select a chart type that is suited for your presentation.

Step 2
Chart Source Data
This dialog box has two tabs, Data Range and Series. From the Data Range tab, you can
display a representation of the graph and the data range that was used. You can change the
appearance of the chart by changing the Series in selection from columns to rows or vice versa.
The Series tab enables you to add or remove a series. When you are ready, click Next.

Step3
Chart options
This dialog box has six tabs:
● Titles
You can type a chart title, and titles for the category (X) axis and value (Y) axis.
● Axes
You can specify how the axes are labelled.
● Gridlines
You can choose whether or not to include major and minor gridlines for the axes.
● Legend
You can include a legend and select a position to place it in the chart.
● Data labels
You can include the values, percentage value and/or label for each data element.
● Data table
You can choose whether or not to show in the chart the data range that was used to create it.
When you are satisfied with the settings shown in each tab, click Next.

Step 4
Chart location
This dialog box enables you to select where the chart will be placed. When you have done that,
click Finish to complete the process. The area on a worksheet where the chart appears is called
the chart location .

22
Complied from: CXC Information Technology, Microsoft Excel 2000/2002 Made Simple,
Information Technology for CXC, Microsoft Office 2000 Introduction to Concepts and Techniques
 23
Complied from: CXC Information Technology, Microsoft Excel 2000/2002 Made Simple,
Information Technology for CXC, Microsoft Office 2000 Introduction to Concepts and Techniques
 LESSON 10
TOPIC: SETTING A WORKSHEET FOR PRINTING

OBJECTIVES

Students should:
● be able to set up a worksheet for printing.
24
Complied from: CXC Information Technology, Microsoft Excel 2000/2002 Made Simple,
Information Technology for CXC, Microsoft Office 2000 Introduction to Concepts and Techniques
Printing data
At some time or another you will want to Clearing the Print area
print a worksheet. It is necessary to adjust Steps
the print settings to ensure that the document ● Click on File
is printed the way you want it. ● Go to Print area
● Click on Clear Print area
Setting that affect print results
Page breaks Previewing a worksheet
These determine the location in a worksheet When printing a worksheet it may give one
where printed pages end and new pages page or more depending on the amount of
start. data, the margins, the paper size and the
orientation (portrait or landscape). To avoid
Margins wasting paper when printing, you may
These determine the space around the preview the spreadsheet first (using the Print
printed page. Preview icon) and edit settings to suit the
amount of data.
Orientations
These determine whether the page prints in Ways to get to print preview
portrait (printed across the width of the
page) or landscape (printed across the length Using the Menu bar
of the page). Steps
● Click on File
Print area ● Click on Print Preview
This determines what area of the worksheet
is printed.
Using the Standard toolbar
Setting the print area Step
Setting the print area is necessary to indicate ● Click on the Print Preview icon
what you want to print. Sometimes, even
though you have a lot of data on your
worksheet, you may just want to print a Using the Print dialog box
portion of it. Step
● Click on the Print Preview button
Steps
● Highlight the section required for
printing
● Click on File
● Go to Print Area
● Click on Set print area
● Preview and then print

On setting the print area on the worksheet,

you will notice a border around the area
selected to be printed.
PRINT PREVIEW OPTIONS

25
Complied from: CXC Information Technology, Microsoft Excel 2000/2002 Made Simple,
Information Technology for CXC, Microsoft Office 2000 Introduction to Concepts and Techniques
 OPTION PURPOSE

NEXT Displays the next print page

PREVIOUS Displays the previous print page

This option allows you to zoom in to get a

ZOOM closer view of the document or to zoom out to
see more of the page at a reduced size. This
feature does not affect the printing size.

This option brings up the Print dialog box

PRINT allowing the user to set the required print
options.

This option displays the Setup dialog box

allowing adjustment of the document to
SETUP required specifications, including page
orientation, scaling, paper size, margins, page
centering, headers and footers and sheet
settings.

Displays or hides the margin indicators which

MARGINS allow you to change the page margins, header
and footer margins and column widths.

This option allows you to adjust the page

PAGE BREAK PREVIEW breaks and print area.

CLOSE Exits Print Preview

Displays information about the Print Preview

HELP window.

SETUP OPTION
This option displays the Setup dialog box allowing adjustment of the document to required
specifications, including page orientation, scaling, paper size, margins, page centering, headers

26
Complied from: CXC Information Technology, Microsoft Excel 2000/2002 Made Simple,
Information Technology for CXC, Microsoft Office 2000 Introduction to Concepts and Techniques
and footers and sheet settings. The following shows the options that the setup dialog box has to
offer.

DISPLAYING AND PRINTING THE FORMULAS VERSION

OF THE WORKSHEET
27
Complied from: CXC Information Technology, Microsoft Excel 2000/2002 Made Simple,
Information Technology for CXC, Microsoft Office 2000 Introduction to Concepts and Techniques
So far, you have been working with the values version of the worksheet, which shows the results
of the formulas you have entered, rather than the actual formulas. Microsoft Excel allows you to
display and print the formulas version of the worksheet, which displays the actual formulas you
have entered, rather than the resulting values.

When you change from the values version to the formulas version, Microsoft Excel increases the
width of the columns so the formulas and text do not overflow into adjacent cells on the right.
The formulas version of the worksheet is therefore usually wider than the values version.

Displaying Formulas
Steps
● Click on Tools
● Click on Options
● Click on the View tab
● Go to Window options
● Check the box to the left of Formulas
● Click OK

Options Dialog Box

28
Complied from: CXC Information Technology, Microsoft Excel 2000/2002 Made Simple,
Information Technology for CXC, Microsoft Office 2000 Introduction to Concepts and Techniques