SPREADSHEETS

SPECIFIC OBJECTIVES
THEORY
By the end of the sub module unit, the trainee should be able to:
(i) Explain the meaning of a spreadsheet.
(ii) Identify areas where spreadsheets are applied. `
(iii) Explain worksheets layout.
(iv) Explain how to build and save a worksheet.
(v) Manipulate data in a worksheet.
(vi) Explain how to apply cell data types.
(vii) Explain formulae and functions.
Viii) Explain use of charts.

COMPETENCE
The trainee should have the ability to;
(a) Create a spreadsheet.
(b) Edit a spreadsheet.
(c) Format a spreadsheet.
(d) Save/open a spreadsheet.
(e) Use formula.
(f) Use statistical functions/analysis.
(g) Use macros in spreadsheet.
(h) Perform calculations.
(i) Print spreadsheet.

Definition
Spreadsheet is application software that is made up of rows and columns used to
present and analyse data. Spreadsheet can either be manual or electronic.
Manual spreadsheet is presented on a physical ledger book while electronic one is on a
computer program.
An electronic spreadsheet enables the user to; enter data, edit, format, perform
calculations and present data in charts among others.

1
Characteristics or features of spreadsheets
Spreadsheet has the following features:
(a) It is made of rows and columns which intersect to form cells.
(b) Columns are arranged vertically and are labeled using alphabetical letters while
rows are numbered progressively starting from the first row.
(c) Every individual cell has a unique cell address used for identification.
(d) It contains inbuilt formulas known as functions that enable the user to quickly
perform calculations.
(e) It allows presentation of information graphically using charts.
(f) They contain database tools such as forms.

Examples of spreadsheet programs include:

 Microsoft (MS) Excel
 Lotus 1-2-3
 Quattro Pro
 Multiplan
 Super Calc among others.

Areas where spreadsheets are applied

Spreadsheets can be applied in the following areas:
(i) Statistical analysis (ii) Accounting (iii) data management (iv) forecasting
(v) Scientific applications

 Statistical analysis
Spreadsheets provide tools for statistical analysis and evaluation. Some of these tools
include median, mean, average and mode among others.
 Accounting
Accounting refers to the process of keeping financial accounts statements. An account is a
record of financial income and expenditure relating to a particular period. Spreadsheet
contains functions for analyzing and managing financial transactions making the work of
an accountant easier. An example of an accounting function is rate.
 Data Management

2
Data management allows a company to provide financial data and advice to another
organization for easier development of its business. Data management facilities available
in spreadsheet include; sorting, filtering, forms and sub-totals among others.

 Forecasting (“What if” analysis)

Forecasting is the process of predicting the future happenings. Spreadsheet programs
contain features that can be used to predict the future profitability of a company based on
the current trend of events. These feature is referred to as “What if “. The “ What if “
analysis uses automatic recalculation features to predict the results. Any time values
within a data range is changed, the formula written automatically calculates and displays
updated result. This is what is referred to as automatic recalculation feature.
 Scientific Applications
Spreadsheets contain tools that can be used for scientific analysis. Such tools include sum,
standard deviation, and product among others.

Worksheet layout
A worksheet is a grid of rows and columns which intersect to form cells where data is
entered for manipulation. The active cell is usually denoted by a dark rectangular border
around it. Any cell in a worksheet can be made active by simply clicking on it.
Every cell is given a cell address. The standard addressing scheme is A1 style. The style
name a cell by the column letter then the row number. For example, cell A1 is named by
column letter A and row number 1. When a cell is selected, the cell address appears on the
name box.

Starting a Spreadsheet package

There are three possible ways of starting a spreadsheet program. These are:
A. Click the START button, select All Programs then Click on Microsoft Office
to display a list of programs contained in the office suite. Click on Microsoft
Excel 2010.
B. Click START button, click on RUN command then type excel in the box on
the dialog box that appears finally click OK, or Press ENTER on the
keyboard.
3
 C. Click START button then click Microsoft Excel 2010.

Parts of spreadsheet window

Column Formula
Menu Title Bar
Bar Minimize Restore
Button Button Close
Title Bar Button

Name
Active
Box
Cell
Active
Cell

Vertical
Scroll
Row
bar
Title

Status bar Horizontal

Scroll bar
4
Figure XX

Figure XX shows an example of the application window of a spreadsheet. The spreadsheet

package used in this case is Microsoft Excel (Ms-excel) 2010. It contains the following
parts:
Formula bar: It provides the user with a box for entering or editing data or formula in a
cell. It is located below the ribbon by default. It contains the cell naming box and the
formula box used for entering a formula or editing data. The naming box displays the
name of the active cell.

Column Title: This is a row containing the column labels.

Cell: It is the intersection of a row and a column where data is entered. The cell where
data is entered is normally referred to as active cell. By default the active cell is usually
cell A1. To make a cell active simply click on it.
NB: The other parts of a spreadsheet application window are similar to the ones discussed
in Ms-Word and they perform the same function.
Cell Referencing
A cell reference also called cell address consists of a column letter and a row number
that intersect to show the location of a cell. There are two types of cell references
namely relative and absolute cell reference.

(i) Relative cell reference: This type of reference automatically changes the cell
addresses of a formula relative to the position of a cell where it is copied. For
example, if the formula =Sum(A1:C1) is written on cell D1 then copied to cell G1,
the formula will automatically change to =Sum(D1:F1)
(ii) Absolute cell reference: In this type of reference, the formula remains the same
regardless of where it is copied. To make a cell absolute type dollar sign before the
column letter and another dollar sign before the row number. For example, if the
formula =Sum($A$1:$C$1) is written on cell D1 then copied to cell G1, the
formula will remain constant to=Sum(($A$1:$C$1)

5
Saving a worksheet
When excel is launched, it automatically opens a workbook. By default a workbook
contains three worksheets named sheet1, sheet2 and sheet3. To make a worksheet active
simply click on its name.

To save a workbook for the first time, do the following

 Click on the File Tab on the menu bar.
 Click the Save As option from the pull down menu that appears. Save As
dialog box appears.
 Specify the location where the workbook is to be stored and the name of the
workbook.
 Click Save button.

To update an already existing workbook do one of the following:

A. Press Ctrl + S on the keyboard.
B. Click the File Tab, then select. Save command.
C. Click the Save icon on the quick access tool bar.

Closing a worksheet file:

6
It done when the user wants to terminate the active workbook only without exiting the
program. To close a worksheet file do one of the following:
A. Click the File Tab then click Close command.
B. Click Close button on the menu bar of the active worksheet.

Exiting a worksheet file

To terminate the entire program do one of the following:

A. Click the File Tab then click Exit command.

B. Click Close button on the title bar of the active worksheet.
C. Press ALT + F4 on the keyboard.

Opening an existing workbook

It is also known as accessing or retrieving of a saved workbook. Do one of the following
procedures in order to open already saved workbook:
A. Click the File Tab then click Open command. Select the location where the
workbook was stored then either select or type the name of the workbook on
the dialog box appears. Finally, click Open.
B. If the file was among the files that were recently created or modified then, click
on the File Tab, click on Recent command, then finally click on the name of
the file to open it.
C. Click Open icon on the Quick Access Toolbar. The open dialog box appears.
Proceed as option A above.
D. Press CTRL + O on the keyboard and proceed as option A above.
E. Open the location where the workbook was saved then double click on the
workbook.

Entering data
When data is typed on a cell, it is displayed in the selected cell as well as the content box
on the formula bar. The data only enters the cell when the Enter key or the arrow key is
pressed.
Excel cell data types and entering data
There are four cell data types in a spreadsheet namely: Labels, Values, Functions and
Formulae.
7
(a) Labels: They are text or alphanumeric characters that are entered in a cell which
cannot be manipulated mathematically. By default, all labels are aligned to the
left of a cell.
Entering Text
(i) Point at the cell where the text is supposed to appear.
(ii) Type the text
(iii) Press the enter key or click on a different cell.
(b) Values: They are numeric data that can be manipulated mathematically. They
include numbers, dates, time and currency among others. By default, all values are
aligned to the right of a cell.
Entering Values
(i) Point at the cell where the value is supposed to appear.
(ii) Type the value
(iii) Press the enter key or click on a different cell.

(c) Formulae: They are mathematical expressions created by the user. Every formulae
must have the following components:
(i) Begin with an equal sign (=).
(ii) Contain mathematical operation such as +, -, / and *
(iii) Cell addresses that contain values.
Entering formula
Formulae can be typed within a cell in a worksheet but when the enter key is
pressed or the formula executed, the actual cell displays the result of the
calculation. To view the formulae, simply click on the cell, it will be automatically
displayed on the formula bar.

Functions: They are inbuilt formulae that the user can quickly use to perform
calculations automatically. Every function must have the following components:
(i) Begin with an equal sign (=).
(ii) The name of the function.
(iii) Arguments or data range. The data range is enclosed in parenthesis or brackets.
It contains the cell addresses which have the information to be manipulated
mathematically. The addresses can be separated by use of a colon or comma.
8
 Entering Function
The user can access the functions using paste (fx) function. To activate this
function simply click on the fx label on the formula bar. A dialog box appears as
shown in figure xx

Figure xx
To make use of this function do the following
 Take the cursor to the cell where the result is to be displayed.
 Click on the Paste function to display the Insert Function dialog box.
 Select the function required to manipulate the data under Select a function:
section on the dialog box that appears.
 Click OK to apply and to open the Function Arguments dialog box.
 Excel automatically inserts the data range under Number1 box and displays all or
some of the values to be manipulated besides the box. However, the user has an
option of either accepting the argument or typing their own. To type a new
argument click on the box and delete the given range then type another one.

9
Selecting data
Selecting data in: Procedure
A single cell Point at the cell to select and click the mouse button.
OR
Use the arrow keys or the tab key.
Block cells Click on the top left corner of the block, hold down the shift key,
press the arrow key to extend the selection in the desired direction or
click on the bottom right cell.
OR
Point at the first cell to be selected, press and hold down the left
mouse button and drag the cursor until the desired number of cells is
selected.
Entire row/column Click on the row number / column letter
Non adjacent cells Hold down the control (CTRL) key as you click on the desired cells.
Entire worksheet Click on the Select All button.

Select all
button

Editing data
This refers to changing the content or data in a worksheet. Some of the editing features
are:
(a) Inserting a Column or a Row

10
 It is possible to insert a row or a column between existing rows or columns
respectively. Do the following:
(i) Inserting a column
 Click on the column heading to select it,
 Right click on the selected column, and then click Insert from the resulting pop
menu to add a new column to the left.
(ii) Inserting a Row
 Click on the row heading to select it,
 Right click on the selected row, and then click Insert from the resulting pop
menu to add a new column to the left.

Deleting Data
To delete data that is already entered in a cell, click on the cell or select the block of cells
containing the data then either press the delete key from the keyboard, backspace to delete
from the cell or formula bar or click on the Delete command in the ribbon under the Cells
Group in the Home Tab.
Deleting Rows and Columns
To delete rows and columns in a worksheet do the following:
 Highlight a cell(s) in row(s)/column(s) to be deleted.

 Either right click on the cell then select Delete… option from the pop-up A dialog
box appears as shown in figure xx.

figure xx: Delete dialog box

 Select on Entire row option to delete the selected row(s) or Entire column option
to delete the selected column(s).

 Click OK to apply.

Naming Worksheets

11
 All worksheets in a workbook have a default name which appears at the bottom of
the worksheet just above the status bar. The default names are sheet1, sheet2….
depending on the number of worksheet in the workbook. The user can however, edit
or modify these names to give a worksheet a name that is more descriptive based on
the type of data found in the sheet. To rename a worksheet do the following:
 Point at the worksheet name.

 Either right click on the name of the sheet to be renamed then select Rename
option from the pop up menu or double click on the sheet name.

 Type the desired name then press the enter key on the keyboard.

In figure XX sheet 2 has been renamed to manner.

Edited name of a
worksheet

Figure XX: Workbook showing a renamed sheet

(b) Adjusting a Column width and a Row height

To adjust column width or column heights do of the following:
(i) Adjusting a column width
 Place the mouse pointer at the boundary between two columns.
 When the mouse pointer changes to a two headed arrow, click and drag to the
desired column width.
 Release the mouse button to apply.
OR

12
 From Home tab, on the Cells group click on Format. Figure XX shows the
resulting menu.

 Select Column width… and a dialog box will appear as shown in figure XX.

Figure XX: Column width dialog box.

 Type the desired value in the column width box.
 Click OK.
(i) Adjusting a row height
 Place the mouse pointer at the boundary between two rows.
 When the mouse pointer changes to a two headed arrow, click and drag to the
desired row height.
 Release the mouse button to apply.
OR
 From Home tab, on the Cells group click on Format
 From the resulting menu select row height… the following dialog will appear
as shown in figure XX.

13
 Figure XX: Row Height dialog box.
 Type the desired value in the Row Height box.
 Click OK.

Freezing rows and columns

The freeze command is used to permanently display selected rows or columns. If
information in the worksheet stretches down or across more than one screen,
normally as the user scrolls down or across the worksheet, the information keeps
on disappearing to the top or to the left as the ones towards the bottom or right of
the screen is displayed. For example the column or row titles can be frozen to
ensure that the data displayed is linked with the correct title after scrolling.
Therefore, to permanently display the selected rows and columns, the freeze pane
command is used as follows:
(i) Select a cell in the row to apply the freeze command.

(ii) Click on View Tab on the menu bar.

(iii) Click on the Freeze Panes command under the Window Group on the
ribbon. A drop down menu appears.

(iv) Click on one of the options displayed to effect the freezing style. The
options are:

Menu Options Function

Freeze Panes Keep a given block of rows and columns visible while the
rest the of worksheet scrolls
Freeze Top Row Keep the top row visible while scrolling through the rest of
the worksheet.
Freeze First Keep the top column visible while scrolling through the
Column rest of the worksheet.
Unfreeze Panes Unlock all rows and columns to scroll through the entire
worksheet.
NB: To
To remove any of the freeze applied, redo procedure (i) and (ii) then select
Unfreeze Panes in procedure (iii).

Formatting a worksheet
(a) Formatting cells and worksheet data
Select the cells to apply the format and do the following:

14
  From Home tab, on the Cells group click on Format.
 From the resulting menu select Format cells… a dialog box will appear as
shown in figure XX.

figure XX: format cells dialog box.

To format cells, the tab options in the Format Cells dialog box are used namely:
(i) Number: Under Category option such as currency, date, fraction are found;
select an option in the Category box, and then select the options that are needed to
specify a number format. In the Sample box chosen format is shown as it will
appear in worksheet.
(ii) Alignment: this tab has the following options which cal be applied on the
worksheet:
 Horizontal: Select an option in the Horizontal list box to change the horizontal
alignment of cell contents.
 Vertical: Select an option in the Vertical box to change the vertical alignment of
cell contents.
 Indent: It Indents cell contents from any edge of the cell, depending on the choice
under Horizontal and Vertical.
 Orientation: Select an option under Orientation to change the orientation of text
in selected cells. Degrees: Sets the amount of text rotation in the selected cell.
 Text control: Select options under Text control to adjust how the text should
appear in a cell.
 Wrap text : Wraps text into multiple lines in a cell. The number of wrapped lines
is dependent on the width of the column and the length of the cell contents.
 Shrink to fit : Decreases the apparent size of font characters so that all data in a
selected cell fits within the column.
 Merge cells: Joins two or more selected cells into a single cell. The cell reference
for a merged cell is the upper-left cell in the original selected range.
 Right-to-left : Select an option in the Text direction box to specify reading order
and alignment.
(iii) Font: In this option the user changes font type, font style, font size and other
formatting options for the selected text.
(iv) Border: Select a line style in the Style box and click the buttons under Presets or
Border to apply borders to the selected cells. To remove all borders, click the
None button. You can also click areas in the text box to add or remove borders.
15
 Color Select a color from the list to change the color of the selected text or object
to the desired color.
(v) Fill: In this option the user can change background color, pattern color, Pattern
style, fill effects and more color.
(vi) Protection: This is done through using either:
 Locked: This option prevents selected cells from being altered, moved,
resized, or deleted. Locking cells has no effect unless the sheet is protected.
 Hidden: This option hides a formula in a cell such that it doesn't appear in
the formula bar when the cell is clicked on. When this option is selected, it
has no effect unless the sheet is protected.
(b) Copying and deleting formats
Copying
Formats which have been previously applied on a cell or a group of cells can be
duplicated to other cells. To copy formats do the following:

(i) Select the cells containing the format to copy.

(ii) Click Format Painter from clipboard group in the Home
tab.
(iii) Click the cells where to copy the format.

Note: To copy the formatting in the selected cell or range to several

locations, double-click the Format Painter button. Click on the
first location to apply the format then the second, third up to the last
one.

Deleting
Formats can be cleared from a cell or a group of cells. To clear formats do the
following:
(i) Select the cells, rows, or columns which have the format to be
cleared.
(ii) Click Clear from Editing group in the Home tab.
(iii) Select Clear Format from the resulting menu options.

Notes: Pressing DELETE or BACKSPACE on the keyboard, Excel

removes the cell contents but does not remove comments or cell
formats.

(c) Applying Conditional formatting

Conditional formatting enables the user to visually answer specific questions about
the data in a worksheet. It can be applied to a cell range or an entire worksheet. It
changes the appearance of a range of cells based on the conditions (or criteria). If
the condition is true, the range of cells is formatted based on that condition.
However, if the condition is false, the range of cells is not formatted based on that
condition.
When a conditional format is created, it is possible to reference only other cells on
the same worksheet or, in certain cases, cells on worksheets in the same workbook

16
 currently open. Conditional formatting however cannot be used on external
references to another workbook.
To apply conditional formatting do the following:

 Select the data where the conditional formatting is to be applied.

 On the Home tab, in the Styles group, click the arrow next to Conditional
Formatting. A drop down menu appears as shown in figure XX.
 Select the desired option. It will be automatically effected on the worksheet.

Figure XX: Window showing the options under the conditional formatting.

 After applying a style, select the data, click Conditional Formatting on the
ribbon in the Styles group from the Home tab, and then click Manage
Rules… from the resulting menu, to specify the condition or rules for the
formatting on the dialog box as appears it appears in figure XX.

17
 Figure XX : Conditional formatting rules manager dialog box

 To delete the applied format click on the Delete Rule command button.

 To add a conditional format, click New Rule. The New Formatting Rule dialog
box is displayed. Select the new rule then click OK to apply.

To change a conditional format, do the following:

1. Make sure that the appropriate worksheet, table, or PivotTable

report is selected in the Show formatting rules for list box.

2. Optionally, change the range of cells by clicking Collapse Dialog

in the Applies to box to temporarily hide the dialog box, by
selecting the new range of cells on the worksheet, and then by
selecting Expand Dialog .

3. Select the rule, and then click Edit rule. The Edit Formatting
Rule dialog box is displayed. The box is similar to the one shown
in the New Rule procedure above.

18
  Under Select a Rule Type, click Format all cells based on
their values (default).

 Under Edit the Rule Description, in the Format Style list box,
select the desired option.

 Go to the other options in the box and make a desired selection.

 Click OK to close the box and open the Conditional Formatting

Rules Manager.

Application of Formulae and functions

There are arithmetic operators that are commonly used in Arithmetic formula. Table XX
illustrates some operators.
Operator Symbol Function
Plus + Adds all the numbers.
Minus - Subtracts numbers.
Multiplication * Multiplies numbers
Division / Divides numbers
Parenthesis(bracket () Encloses operands and operators to dictate the order of
s) execution.
The order of execution can be controlled through the use of parenthesis.
Types of formulae
There are different types of formulae as discussed below.
1. Formulae using values
This is a spreadsheet formulae that uses the actual values instead of cell references.
Assuming that, we have data in a worksheet as shown in figure XX.

19
 Figure XX: A spreadsheet worksheet
A value formulae for calculating the total mark for Charity Daniel would be:
=60+50+50. Other operators like *,-,/ can also be used together with the values.
Different operators can be used in the same formula for example the following is a
valid formula =60+50-80*4/2
The main disadvantages of this type of formulae are that they cannot be copied to
other cells. Also they are not easy to build because the user has to type every value
since selecting the cells containing the values does not apply in this type of
formulae.

2. Formulae using cell references

This is a spreadsheet formula that uses cell references instead of the values. In this
approach the formula is build using operators and cell reference either relative,
absolute or mixed cell references.

(a) Formula using relative cell references

This formula contains operators and cell references. For example:
A formula having cell references for calculating the total mark for Charity
Daniel in figure XX would be: =B3+C3+D3. Other operators like *,-,/ can
also be used together with the cell references. Different operators can be
used in the same formula for example the following is a valid formula
=B3+C3-D3*4/2
The main advantage of this type of formula is that it can be copied to other
cells for example to calculate the total for Flora, Musa, Susan and Winfred
20
 the formula applied for Charity can be copied to apply to the rest of the
students. Also they are easy to build because the user needs not to type
every reference but can select the cells containing the values.

(b) Formula using absolute cell references and relative cell references
This formula contains operators and both mixed cell references and relative
cell references. For example in figure XX all the new prices are calculated
using a common rate of 5%, the formula for calculating the new price for
Detergent would be:

Figure XX: Extract of a Excel worksheet.

=$B$7*B2+B2
Note: The mathematical rule of BODMAS (bracket of division, multiplication, addition
and subtraction) determines the order in which an operator is executed and gives the
product. It is also applicable in spreadsheets.
3. Formulae using functions and cell references
At times making calculations where many cells are involved using an operator
makes the formula very complex, prone to error and difficult to write. Therefore
the use of functions makes the formulae to be shorter and less complex. Examples
of functions: The can be categorized as follows: Statistical, Logical and
Mathematical functions.
(a) Statistical: The following are examples under this category.
(i) Average: This function provides the arithmetic mean of the values
specified in block of cells. Cells in a block (range) that are empty
are ignored and those that have zero values are included are
included in the average. The syntax for this command is as follows:

21
 =Average(Arguments): The word average refers to the function for
computing the arithmetic mean and the arguments are the operands.
These operands can be actual values, cell reference separated by
commas or a range of cells. The following are valid examples of
average function.
=Average(3,5,7,9,10,2,4),
=Average(A1,A2,A4,A7),
=Average(A1:A7)
(ii) Max: this function returns the largest value in a selected range of
cells. In the argument contains no value, this function returns a zero
value. The syntax for this command is as follows:
=Max(Arguments): The word Max refers to the function for
computing the largest number in the list or range and the arguments
are the operands. These operands can be actual values, cell
reference separated by commas or a range of cells. The following
are valid examples of the Max function.
=Max(3,5,7,9,10,2,4), =Max(A1,A2,A4,A7), =Max(A1:A7)

(iii) Min: this function returns the smallest value in a selected range of
cells. In the argument contains no value, this function returns a zero
value. The syntax for this command is as follows:
=min(Arguments): The word Min refers to the function for
computing the Smallest number in the list or range and the
arguments are the operands. These operands can be actual values,
cell reference separated by commas or a range of cells. The
following are valid examples of the Max function.
=Min(3,5,7,9,10,2,4), =Min(A1,A2,A4,A7), =Min(A1:A7)
(iv) Mode: This function returns the most frequently appearing value in
a list of values or a range. The syntax for this command is as
follows: =Mode(Arguments): The word Mode refers to the function
for determining the value that appears the highest number of times
and the arguments are the operands. These operands can be actual
values, cell reference separated by commas or a range of cells. The
following are valid examples of the Max function.
22
 =Mode(3,5,7,9,10,2,4,3), =Mode(A1,A2,A4,A7), =Mode(A1:A7)

(v) Median: This function returns the middle value in a list of values or
a range. It automatically arranges the values in a particular order to
determine the median. The syntax for this command is as follows:
=Median(Arguments): The word Median refers to the function for
determining the value that appears in the middle of a list or a range
and the arguments are the operands. These operands can be actual
values, cell reference separated by commas or a range of cells. The
following are valid examples of the Max function.
=Median(3,5,7,9,10,2,4,3), =Median(A1,A2,A4,A7),
=Median(A1:A7)
(vi) Count: This function returns the number of elements in a list of
values or a range. The syntax for this command is as follows:
=Count(Arguments): The word Count refers to the function for
determining the number of values that are in the list or a range and
the arguments are the operands. These operands can be actual
values, cell reference separated by commas or a range of cells. The
following are valid examples of the Max function.
=Count(3,5,7,9,10,2,4,3), =Count(A1,A2,A4,A7), =Count(A1:A7)
(vii) Standard Deviation: This term refers to a quantity that is calculated
to indicate the extend of variation from the mean for a set of value.
It estimates the standard deviation in the selected range of cells. The
syntax for this command is as follows: =Stdev(Arguments) The
following are valid examples of the stdev function.
= Stdev (3,5,7,9,10,2,4,3), = Stdev (A1,A2,A4,A7), = Stdev
(A1:A7)
(b) Logical
(i) IF : This function evaluates a condition and returns one of the values in
case it is found to be true and another value if it is false.
The following is the syntax of this function:
 IF for two options only:
General syntax = IF (Condition, true, false)
For example = IF (A3>=40, “Pass”, “Fail”)
23
 If the value in A3 is greater than or equal to 40 the output will be Pass else
Fail.
 IF for three options only
General syntax = IF (Condition1,
Option1,IF(Condition2,Option2,Option3))
For example =IF(A3>=60,”Credit”,IF(A3>=40,”Pass”,”Fail”))
 IF for four options only
General syntax = IF (Condition1, Option1,IF(Condition2,Option
2,IF(condition3),Option3,Option4)
For example
=IF(A3>=80,”Distinction”,IF(A3>=60,”Credit”,IF(A3>=40,”Pass”,”Fail”))
)
Note:
 It is important to note that the number of conditions are always one
less that the options. If the options are four then the conditions are
three because the last option is always the default.
 Any open bracket should be closed at the end of the entire “IF”
function.
 In the entire formula no space is used instead commas are used.
(ii) SumIf: Adds the values in the cells specified by a given condition or criterion and
returns a value. The general syntax is as follows: =SUMIF(Range,”Condition”).
For example: Suppose B4 to H4 contains the values 90, 30, 80, 40, 50, 90 and 20.
To sum all values greater than 50, the function would be:
= SumIF (B4:H4,”>50”). This function would return a 260.

(iii) CountIf: Counts the number of cells within a specified block or range of cells that
meet a given conditions or criteria. The general syntax is as follows:
=CountIF(Range,”Condition”). Suppose B4 to H4 contains the values 90, 30, 80,
40, 50, 90 and 20. To count all values greater than 50, the function would be:
= CountIF (B4:H4,”>60”). This function would return a 3.

(c) Mathematical
(i) Sum: This function adds all the numbers in a specified range of cells. The
general syntax is =Sum(Number1, Number2…Number N). Suppose B4 to

24
 H4 contains the values 90, 30, 80, 40, 50, 90 and 20. To sum all the values,
the function would be:
= Sum (B4:H4). This function would return a 400. Alternatively using the
actual values the formula would take the following form and still give the
correct answer: = Sum (90, 30, 80, 40, 50, 90,20)
(ii) Product: This function multiplies all the numbers given as arguments. The
general syntax is =Product(Number 1, Number 2… Number n) where
number1, Number2 are 1 to 30 arguments which can be included in the
function. Suppose B4 to H4 contains the values 9, 3, 8, 4, 5, 9 and 2. To
multiply all the values, the function would be:
= Product (B4:H4). This function would return a 77760. Alternatively using
the actual values the formula would take the following form and still give
the correct answer: = Product (9, 3, 8, 4, 5, 9,2)
Copying and moving of formulae
Once a formula is entered in a cell, it can be copied downwards, upwards the column or
across the same row depending on the position of the formular instead of writing a formula
in each cell. When the formula is copied, automatically cell references are adjusted
depending on the way the formula was written. To copy a formula do the following:
 Click on the cell that contains the formula.
 Take the cursor to the fill handle of the cell selector until it changes to a plus sign.

 Click and drag the cursor either across the row or column. Release the button once
all the cells where the formula is supposed to been copied are selected as in figure
XX.

25
 Figure XX: Shows an extract from a spreadsheet showing selected cells.

NB: The formula can also be copied if by right clicking on it then selecting Copy
command from the pop-up menu that appears. Go to the cell where the data is to be
copied, right click on it then select Paste. Continuously perform the paste action until the
formula has been copied to all the required cells.
Moving a Formula
To move a formula, use the cut command. Right click on the cell containing the formula
then select Cut command from the pop-up menu that appears. Point and click at the cell
where the data is to be taken, right click on it then select Paste.

Formula Errors

Some of the most common errors in Excel include #####, #NAME?, #VALUE!,
#DIV/0!, #REF!, #NULL!, #NUM! and #N/A! among others. Table XX shows the various
errors and the possible cause.

Error Cause
1 ##### When a cell contains this error code, the column isn't wide enough to
display the entire value.
2 #NAME? This error occurs when Excel does not recognize text in a formula.
3 #DIV/0! This error occurs when a formula tries to divide a number by 0 or an
empty cell.
4 #N/A! It commonly occurs while using Vlookup formula. It is shown when
some values are missing or inappropriate arguments are passed to the

26
 lookup.
5 #NULL! It occurs when incorrect range operators are used for example sum
(D30:D32C31:C33) returns a #NULL! Error, because there is no
separator between range 1 (D30:D32) and 2 (C31:C33)
6 #NUM! Shown when the formula returns a value bigger than what Excel can
represent
7 #VALUE! Shown when text parameters are used instead of numbers and the
function accepts numbers only.
For example = Product (“zy”,”kl”).

Working with Charts

A chart is defined as a graphical or pictorial representation of worksheet data. A chart
assists the reader to quickly view the trends in a given data then compare and contrast
aspects of data that would be otherwise be unclearly understood. When a chart is created it
is linked to the data such that any change on the data automatically affects the appearance
of the chart.
A chart can be embedded on the sheet which contains the data or it can be created to
appear on its own chart sheet. These options are availed to the user to determine the
location of the chart. By default charts are embedded to the sheet containing the data
unless stated otherwise.
Embedded chart: This type of chart is considered as an object in the sheet containing the
data. When saving it is considered as part of the sheet and when the workbook is opened it
appears on the same sheet with the data. These charts are important whenever a chart is
supposed to be displayed together with the corresponding data. Figure XX shows a sample
of an embedded chart.

27
Chart Sheet: This type of chart appears on a separate sheet in the workbook that contains
a chart. It is mainly used when large or complex charts are to be edited separated from the
worksheet data. Figure XX shows a sample of an embedded chart.

Types of charts
Microsoft Excel offers the following chart types to choose from:
i) Column charts – Represents data as columns arranged vertically so as to compare
values across categories. They can be used to display data over a period of time
and illustrate comparisons of items. Figure XX represents a column chart.

Figure XX: A Column chart

ii) Bar charts – A bar chart represents data as bars arranged horizontally to show
comparison between categories. A bar chart focuses on comparing values on the
items and not on time. Figure XX represents a bar chart.

28
iii) Line charts – A line chart represents data on lines with data point markers joined
by line segments. Line charts can show continuous data over time, set against a
common scale, and are therefore best for showing trends in data at equal intervals.
Figure XX represents a line chart.

Figure XX: shows a line chart.

iv) Pie charts – A pie chart is circular chart that displays the contribution of each
value to a grand total. Data that is arranged in one column or row only on a
worksheet can be plotted in a pie chart. The data points in a pie chart are displayed
as a percentage of the whole pie. Refer to pie chart diagram above.
v) Scatter charts – Scatter charts show the relationships among the numeric values in
several data series, or plot two groups of numbers as one series of xy coordinates.
It shows the density of plotted points. Figure XX represents a Scatter chart.

vi) Other charts – These includes Radar charts, Bubble charts, Doughnuts, Surface
chart and Area charts, among others. Figure XX shows all the types of charts
available in spreadsheets.

Figure XX: Insert chart dialog box.

29
Creating charts
To create a chart in Ms Excel do the following:
(i) Select the data range for the chart. Figure XX shows selected data in a worksheet.

Figure XX: Worksheet extract showing selected data.

(ii) Click Insert tab from the Chart group and select the type of chart for example
column. Figure XX shows a menu for column chart.

Figure XX: Shows different column charts.

(iii) From the resulting menu choose the type of chart desired e.g. Clustered column,
stacked column chart among others.

(iv) The chart appears embedded on the data sheet.

Elements of a Chart

30
This term refers to features that form or are part of a chart. The chart elements differs from
one type of a chart to another. Some may show all the elements mentioned below while
others show a few of them. Examples of chart elements are:
(a) Chart title :This is the heading appearing on the chart.
(b) Data range : refers to range of cells with the data required to draw a chart.
(c) Data labels :refers to information that appears inside the plot area to display actual
values representing the data series.
(d) Gridlines :Chart gridlines are parallel lines across the background of a chart to make
it easier to read.
(e) Trendlines : A graphic representation of trends in data series, such as a line sloping
upward to represent increased sales over a period of time.
(f) Legend – is a visual key for the data series plotted in the chart to shows and
differentiates data categories.

Modifying/Editing Charts
Various changes can be made on a chart once it has been created. Some of these changes
includes:
Changing the chart type

(i) In a chart, click the data series for the chart to be changed as shown in Figure
XX. This displays the Chart Tools, adding the Design, Layout, and Format
tabs.

Figure XX: Shows data series of a chart.

(ii) On the Design tab, in the Type group, click Change Chart Type.

(iii) In the Change Chart Type dialog box, select the desired chart type.

(iv)Click OK to apply.

Changing the Chart Style

31
 (i) Click on the chart to be changed.

(ii) On the Design tab, in the Chart Styles group, click the desired chart style. The
chart style is automatically changed. Figure XX shows different chart styles.

Figure XX: Extract of a ribbon showing chart styles.

Adding data labels

(i) Click on the chart area.

(ii) On the Layout tab, in the Labels group, click Data Labels, and then click the
desired option from the drop down menu that appears.

i) Chart title: To change chart title do the following:

Select the chart as shown in figure XX.

Click Layout Tab and select Chart title from the labels group and click “Above
Chart” from the resulting menu.
ii) Axis title : To change Axis titles do the following:
Click Layout Tab and select Axis title from the labels group.
Click on:
 Primary Horizontal Axis Title and select Title Below Axis from the cascading
menu then type the axis title.
 Primary Vertical Axis title and select appropriate title from the cascading menu
then type the axis title.

Formatting Charts

32
There are various methods of enhancing a chart which include:
Changing Fonts of the chart titles and axes labels.
To change the fonts do the following:
 Right click on the title or the axes whose font is to be changed.
 Select Font from the pop-up menu appears. A dialog box appears as shown in the
figure xx.
 Choose the appropriate options from the dialog box that appears.

Formatting a chart area

(i) Click on the chart to be enhanced.

(ii) On the Layout tab, in the Current Selection group, click on the Format
Selection command button. A dialog box appears as shown in figure xx.

(iii) Make the required formatting styles then click OK to apply.

33
figure xx

Adding or Hiding Legend

(i) Click the chart where the legend is to be shown or hidden.

(ii) On the Layout tab, in the Labels group, click Legend.

(iii) Do one of the following on the drop down menu that appears:

 To hide the legend, click None.

 To display a legend, click the desired display option. The legend

moves, and the plot area automatically adjusts to make room for it.

Adding Data Tables

(i) Click on the chart area.

(ii) On the Layout tab, in the Labels group, click Data Tables, and then click the
desired option.

34
 LEGEND ( Is the Key)

Printing
Printing a workbook or a worksheet
It is possible to print entire or partial worksheets or workbooks, one at a time, or several at
once.

Print Preview
Before printing, it is advisable to print preview the content first. This enables the user to
see if all the content in the worksheet are within the printable area and to change the page
layout of the information. To print preview the worksheet do the following:
 Click the File tab on the menu bar.
 Select Print from the drop down menu that appears. The document preview is
displayed on the right pane of the menu.

To print a worksheet
 Click the File tab on the menu bar. Select Print from the drop down menu that
appears.
 Under the Printer section, select the type of printer to be used.

35
  Under Settings section, select Print Active Sheets to only print the current
worksheet or Print Entire Workbook to print all the sheets within the workbook.
See figure xx

figure xx

 Click the Print command button to begin the printing.

Printing a selection

36
It is possible to print a selected section of a worksheet instead of an entire worksheet by
selecting Print Selection option under Settings section on the left pane of the menu that
appears when Print command is chosen on the File tab or defining a print area. See figure
xx.
A print area is one or more ranges of cells that the user designate to print when printing an
entire worksheet is not necessary. To define a print area do the following:

(i) On the worksheet, select the cells that you want to define as the print area. To
create multiple print areas by hold down CTRL and select the various areas
required.

(ii) On the Page Layout tab, in the Page Setup group, click Print Area, and then
click Set Print Area.

37
After setting a print area, any print action done on the worksheet will only print the
content in the print area. If more than one print area was selected then information in
each area will be printed on a different page.

Clear a Print Area

To clear print areas that were earlier set, do the following:

 Click anywhere on the worksheet for which you want to clear the print area.
 On the Page Layout tab, in the Page Setup group, click Clear Print Area.

Revision questions for the topic

1. Explain the term spreadsheet.
2. Identify any three examples of spreadsheet application programs.
3. Explain three applications of spreadsheets.
4. Explain any three of the following types of data in spreadsheet:
a) Label
b) Value
c) Formula
d) function
5. Use the table below showing the information entered in spreadsheet and answer the
questions that follow:-
Example
Student Name Subject
Maths English Biology Total
Main Aderson 67 74 71
Anna Tonui 58 65 56
Thoma Wanyama 50 55 58
Ann Kawira 60 62 64
Kambi Zuma 85 82 81

a) Enter the formula for computing the total marks for each student.
b) Identify the formula that can be able to compute the highest marks for each subject.
6. Explain the function of “What If” analysis features in spreadsheet program.
7. Outline the procedure that you would use to copy data from an existing spreadsheet
worksheet to a new worksheet.

38
8. Explain two types of data entered in spreadsheet program giving suitable examples for
each case.
9. Identify five elements of a chart.

39