2.

1 Definition of a spreadsheet
A spreadsheet is essentially a ledger sheet that lets you enter, edit and manipulate numeric
data. There are two types of spreadsheets namely:
1. The manual spreadsheet.
2. The electronic spreadsheet.
A manual spreadsheet is the most commonly used type by book keepers as a ledger book
with many sheets of papers divided into rows and columns on which various amounts of
money are entered manually using a pen or pencil. You can visit your bursar’s office and
request to see a ledger sheet.
An electronic spreadsheet on the other hand is prepared using a computer program that
enables the user to enter values in rows and columns similar to the ones of the manual
spreadsheet and to manipulate them mathematically using
formulae. ‘
In this book, the word spreadsheet shall be used to refer to the electronic spreadsheet. ‘.
Advantages of Using Electronic Spreadsheets over Manual Spreadsheet
1. The electronic spreadsheet utilizes the powerful aspects of the computer like speed,
accuracy and efficiency to enable the user quickly accomplish tasks.
2. The electronic spreadsheet offers a larger virtual sheet for data entry and manipulation. For
example the largest paper ledger you can get is one that does not exceed 30 columns and
51 rows while with an electronic spreadsheet, the least ledger has at least 255 columns
and 255 rows!
3. The electronic spreadsheet utilizes the large storage space on computer storage devices
to save and retrieve documents.
4. The electronic spreadsheet enables the user to produce neat work because the traditional
paper, pencil, rubber and calculator are put aside. All the work is edited on the screen and
a final clean copy is printed. With a handwritten spreadsheet, neatness and legibility
depends on the writer’s hand writing skills.
5. Electronic spreadsheets have better document formatting capabilities. 6. Electronic
spreadsheets have inbuilt formulae called functions that enable the user to quickly
manipulate mathematical data.
6. An electronic spreadsheet automatically adjusts the result of a formula if the values in
worksheet are changed. This is called the automatic recalculation feature. For a manual
sheet, changing one value means rubbing the result and writing the correct one again.

Examples of spreadsheets
1. VisiCalc: This was the first type of spreadsheet to be developed for personal computers.
2. Lotus 1-2-3: This is integrated software with spreadsheet module graphs and database.
3. Microsoft Excel
3. VP-Planner etc.
In this book, the spreadsheet that will be considered in details is Microsoft Excel.
Components of a spreadsheet
A spreadsheet has three components ­
1. Worksheet.
2. Database.
3. Graphs.
Worksheet
This is the component in which data values are entered. It is made up of rows and columns.
The intersection between a row and a column is called a cell. A row is a horizontal
arrangement of cells while a column is a vertical arrangement of cells. Each row is labeled
with a number while each column is labeled with a letter as shown in the Figure 2.1. Each cell
is referenced using the column label followed by the row label e.g. cell B3 has the value 20. A
group of many worksheets make up a workbook.

Database
Data values can be entered in the cells of the spreadsheet and managed by special Excel
features found on the Data menu. These features were incorporated in Excel but they
actually belong to database management software. One of such feature is filtering records,
using forms, calculating subtotals, data validation pivot tables and pivot chart reports.
If the data values for the same entity (related values) are entered on the same row, they form
a record. Hence a worksheet can be manipulated to some extent as a database that has data
records entered in it. Figure 2.2 shows a worksheet having two records, Joy and Jeff.

NB: A spreadsheet file is structured in such a way that it can be visualised as a table of
records. That is why such a ‘file can be imported into a database program as will be
discusses later in databases.

Graphs
A graph is a pictorial representation of the base data on a worksheet. Most spreadsheets
refer to graphs as charts. A chart enables the user to present complex data elements from a
worksheet in a simple easy to understand format. Examples of charts are pie charts, line
charts and bar charts. As shall be discussed later, it is easy to generate charts when working
with a spreadsheet program. Figure 2.3 shows an example of a pie chart.
2.3 Application areas of a spreadsheet
Statistical analysis
Spreadsheets provide a set of data analysis tools that can be used to save steps when
developing complex statistical or engineering analyses. The user is usually expected to
provide the appropriate data and parameters for each analysis. The statistical tool then uses
appropriate automated statistical or engineering functions and then displays results in an
output table. Some of the tools generate charts in addition to the output tables.
Because most of these tools are complex, the user needs to have the statistical background
knowledge before attempting to use the tools. Examples of some simple statistical functions
include the following:
1. Average: This is used to calculate the mean of a set of values.
2. Median: This is used to return the value in the middle of a set of values.
For example a set of values may be composed of:
10 20 30 40 50 60.
The average of the set is 35 and its median is also 35. The median is found by taking the
average of the two numbers at the centre of the set, in this case 30 and 40.

Accounting
Many accountants find the spreadsheet a useful tool to use in recording daily transactions
and keeping of financial records. Spreadsheets provide a user friendly environment for
financial management and they come with inbuilt functions that make accounting tasks
easier. For example, the spreadsheet can be used by accountants to do the following:
1. To track the value of assets over time (depreciation and appreciation)
2. To calculate profits
3. To prepare budgets
Other formula like sum, average, product etc. enables the accountant to carry out his daily
work without any problem.

Data management
A spreadsheet enables neat arrangement of data into tabular structure. Related data can be
typed on the same worksheet. However, when data is on different worksheets, the
worksheets can be linked to enhance accessibility.
Data management functions include sorting, filtering (displaying only the required items) and
using forms to enter and view records.
Spreadsheets enable the user to create, edit, save, retrieve and print worksheet data and
records.

Forecasting (“What if” analysis)

The automatic recalculation feature enables the use of “What if’ analysis technique. This
involves changing the value of one of the arguments in a formula to see the difference the
change would make on the result of the calculation. For example, a formula to calculate a
company’s profit, margin may be as follows:
Profit =, (Total units sold x sale price) – (Total units bought x cost price) – Operating
‘expenses.
A sales manager in the company c n ask the following question: What if sales increase by
20%, how much profit wills the company make? The manager substitutes the total units sold
value with one that is 20% higher and the spreadsheet automatically displays the new profit.
A traditional analysis method would require a different work sheet to be prepared. Therefore,
this method can be used for financial forecasting, budgeting, stock portfolio analysis, cost
analysis, cash flow etc.
Creating a worksheet/workbook using Microsoft Excel
To start Microsoft Excel, click Start button, point to Programs and then select Microsoft Excel
from the programs menu This procedure may vary slightly depending on the version of Excel
you are using or the computer’s hardware and software configuration.
The Windows environment allows a person to place shortcuts to a program’s executable
(.exe) file in various places like the desktop. If the Excel shortcut is on the desktop, simply
double click it to start the application.
The Microsoft Excel application window opens as shown in the Figure 2.5. Make sure that
you can be able to identify all the labeled parts of the Microsoft Excel application window.
The Microsoft Excel application window
The Microsoft Excel application window is made up of the following components:
Title bar: It has the title of the application and control buttons for minimising, maximising and
closing the application
The menu bar: It displays a list of menu options e.g. File, Edit, View etc. Clicking one of them
displays a menu that has commands which can be selected in order to manipulate data in the
spreadsheet. ‘
Tool bars: The most common of these are the standard and formatting toolbars. The most
important thing is to be able to identify each toolbar by its icons. The standard toolbar has
shortcuts to some of the most commonly used menu commands like print, copy, paste and
save. The formatting toolbar has shortcuts to the commonly used commands found on the
format menu option
Formula bar: This is one of the most important components of the Microsoft Excel application
window. It enables the user to enter or edit a formula or data in a cell. You can identify the
formula bar because it has an equal sign (or fx). The name box to the left of the formula bar
displays the position of the cell in which data or a formula is being entered which is also
called the current cell. If the formula bar is not available, click on View menu then select
Formula bars. A check mark appears on the left of the selected item to show that it is now
displayed on the screen.
Cell pointer: It marks the position of the current cell or the insertion point. It is special cursors
that is rectangular in shape and makes the current cell appear as if it has darker boundaries.
The Worksheet: Consists of cells, rows and columns. Data is entered here for manipulation.
Status bar: It shows the processing state of the application. For example, on its left is the
word Ready which shows that the spreadsheet is ready to receive user commands. ‘
Worksheet labels: These are usually of the format Sheet 1, Sheet 2 etc. A workbook may
have several sheets. It is also possible to rename the sheets by right clicking on the labels
then choosing rename command from the shortcut menu that appears. The active sheet (one
being used) has its label appearing lighter in colour than the rest. To move to a particular
sheet in the workbook, simply click its sheet label.
 Vertical and horizontal scroll bars: Clicking the arrows at their ends moves the worksheet
vertically and horizontally on the screen respectively.

Worksheet layout
The worksheet has the following components: Cells: An intersection between a row and a
column.
Rows: Horizontal arrangement of cells. Columns: Vertical arrangement of cells.
Range: Is a group of rectangular cells that can be selected and manipulated as a block.

Navigating the Microsoft Excel screen

1. Click cell D5. Notice that the cell pointer immediately moves to the cell and the name box
reads D 5. Typing on the keyboard now inserts entries in cell D5 as long as the pointer is
still there.
2. Click letter A that heads the first column. Notice that the whole column is highlighted.
3. Double click cell EIO. Notice that the text cursor forms in the cell and you can now type
characters inside the cell. Also the status bar will now read enter which means that
Microsoft Excel expects you to enter a value in the cell.
4. Click the down arrow on the vertical scroll bar. The worksheet moves upwards on the
screen. The opposite happens when you click the up arrow on the vertical scroll bar.
5. Click the right button on the horizontal scroll bar. The worksheet moves to the left. The
opposite happens when you click the left button on the horizontal scroll bar.
6. Press the right arrow key on the keyboard. Notice that the cell pointer moves one column
to the right on the same row. This can also be done by pressing the Tab key once.
7. Press the left arrow key on the keyboard. Notice that the cell moves one column to the left
on the same row. Pressing gives the same results.
8. Press the up arrow key on the keyboard. Notice that the cell pointer moves one row up on
the same column.
9. Press the down arrow key on the keyboard. Notice that the cell pointer moves one row
down on the same column.
10. Press the end key. The status bar will display the message “END”. If you press the right
arrow key, the cell pointer will move right to the last cell on the row. If the left up or down
keys were to be pressed instead, the cell pointer would move to the last cell to the left, top
or bottom respectively.
11. Pressing Ctr1+Home moves the cell pointer to the first cell of the worksheet i.e. cell AI.

Creating a worksheet
At its simplest level, creating a worksheet consists of starting the spreadsheet program and
entering data in the cells of the current worksheet. , However, a person can decide to create
a worksheet either using the general format or from a specially preformatted spreadsheet
document called a template.

Using the general format

When a spreadsheet program is running it will present the user with a new blank screen of
rows and columns. The user can enter data in this worksheet and save it as a newly created
worksheet. If this is not available then click File menu option and select the new command.
The dialog box shown in Figure 2.8 will be displayed on the screen. On the General tab,
double click the workbook icon. Enter data in the new worksheet created.

Using a template
Click File menu option then new command. On the spreadsheets solutions tab, double click
the template that you wish to create. Figure 2.9 below shows some examples of templates
that may be present for selection.

NB: If the template was saved previously on the hard disk, it will open as a new worksheet
with all the preformatted features present allowing the user to enter some data. However,
some templates may require the original program installation disk in order to be able to use
them because they may not have been copied to the hard disk during program installation.

Editing a cell entry

Editing a cell means changing the contents of the cell. Before the contents in a cell can be.
Changed, the cell must be selected by making it the current cell.
To edit a cell entry proceeds as follows:
1. Move the cell pointer to the cell you wish to edit.
2. Double click the formula bar for the text cursor to appear in the bar. The status bar
message changes to edit
3. Use the keyboard to delete and add contents to the formula bar then press enter key to
apply. Click the save button on the standard toolbar to save the edited changes.

Selecting a range
As you have experienced with the previous two examples, working with one item at a time is
tedious and time consuming. Using a range saves time when working with a large .amount of
data.

A range is a rectangular arrangement of cells specified by the address of its top left and
bottom right cells, ‘separated by a colon (:) ego Range AI:CIO is as shown in Figure 2.10.
Selecting multiple ranges
When using a mouse, you can select more than one range without removing the highlight
from the previous. To do this:
Hold down the Shift key or the Ctrl key while you click on the row header of the second range
you want to highlight. What happens? Do you notice the difference when holding down
the shift and the ctrl keys?
1. Shift key will cause all columns/rows between the selected and the newly clicked cell to be
highlighted.
2. Ctrl selects individually clicked cells or range.
Hiding rows/columns
You can hide some rows or columns in order to see some details, which do not fit, on the
screen. To do this:
1. Highlight the columns/rows you want to hide
2. Click format menu, point on row or column and click hide command.

Saving a worksheet
To save a worksheet, one has to save the workbook in which it belongs with a unique name
on a storage device like a hard disk. The procedure below can be used to save a workbook:
1. Click File menu option then select Save as’ command. Alternatively, click the save
command on the standard toolbar. The save as dialog appears
2. Select the location in which your workbook will be saved in the Save in box then type a
unique name for the workbook in the File name box. Make sure that the option Microsoft
Excel Workbook is selected under the save as type box.
3. Click the Save button to save.

Retrieving a saved workbook

This means opening a workbook that was previously saved.
1. Click File menu option then the Open command. Notice that the Open command has three
dots (called ellipsis) indicating that a dialog box will open, as the user is required to provide
additional information. Alternatively just click the Open command on the standard toolbar.
The open dialog box appears on the screen.
2. Click the Look in drop down list arrow and select the drive or folder where the workbook
was saved. For example, if you saved in a diskette, insert it in the floppy drive then select 3
1/2-floppy (A:). A list of folders and files in the drive will appear in the list box.
3. Double click the icon of the workbook you want and the worksheet will be displayed in the
Microsoft Excel window. Notice that the cell pointer is in the same cell it was in when the
worksheet was last Saved.
Closing a worksheet
Click File then Close command. This closes the worksheet but does not
Close the Excel spreadsheet program. Alternatively, click the; close button of the worksheet
window
Exiting from the spreadsheet
Click File then Exit command. This closes not only the worksheet but also the spreadsheet
program as well. Alternatively click the close button of the main application window.
Cell data types
There are four basic types of data used with spreadsheets:
1. Labels
2. Values,
3. Formulae
4. Functions.
Labels
Any text or alphanumeric characters entered in a cell are viewed as labels by the
spreadsheet program. Labels are used as row or column headings usually to describe the
contents of the row or column. For example, if a column will have names of people, the
column header can be NAMES. Sometimes, numbers can be formatted so that they can be
used as labels. To achieve this add an apostrophe just before the most significant digit in the
number. For example, the number 1990 will be treated as numeric. if typed in a cell but’ 1990
will be treated as a label.
Labels are aligned to the left of the cell and cannot be manipulated mathematically.

Values
These are numbers that can be manipulated mathematically. They may include currency,
date, numbers (0-9), special symbols or text that can be manipulated mathematically by the
spreadsheet.

Formulae
These are user designed mathematical expressions that create a relationship between cells
and return a value in a chosen cell. In Microsoft Excel, a formula must start with an equal
sign. For example, the formula
=B3+D4 adds the contents ofB3 and D4 and returns the sum value in the current cell.
Excel formulae use cell addresses and the arithmetical operators like plus (+) for addition,
minus (-) for subtraction, asterisk (*) for multiplication and forward slash (I) for division.
Using cell addresses, also called referencing, enables Microsoft Excel to keep calculations
accurate and automatically recalculates results of a formula in case the value in a referenced
cell is changed. This is called automatic recalculation.

Functions
These are inbuilt predefined formulae that the user can quickly use instead of having to
create a new one each time a calculation has to be carried out Microsoft Excel has many of
these formulae that cover the most common types of calculations performed by
spreadsheets. To add the contents of cell B3 and D4 the sum function can be used as shown
below:
= Sum (B3:D4)

Cell referencing
A cell reference identifies a cell or a range of cells on the worksheet and shows Microsoft
Excel where to look for the values or data needed to use in a formula. With references, you
can use data contained in different cells of a worksheet in one formula or use the value from
one cell in several different formulae.
By default, Microsoft Excel uses the A 1 cell referencing style. This means that a cell is
identified by its column label followed by the row number. However, the Rl Cl
referencing style can be used. In this case, the cell is referencing by its row number followed
by its column number. The table below gives a few examples of equivalent referencing using
both styles.

The RlCl style is useful when automating commonly repeated tasks using special recording
programs called Macros.
Relative referencing
When performing tasks that require cell referencing, you can use formulae whose cell
references keep on changing automatically depending on their position in the worksheet. This
is called relative cell referencing. A good example would be if you type the formula =Al+Bl in
cell Cl. If the same formula is copied to cell C2 the formula automatically changes to =A2+B2.

Absolute referencing
These are cell references that always refer to cells in a specific location, of the worksheet
even if they are copied from one cell to another. To make a formula absolute, add a dollar
sign before the letter and/or number, such as $B$lO. In this case, both the column and row
references are absolute. .

Referencing using labels and names

Labels of columns and rows on a worksheet can be used to refer to the cells that fall within
those columns and rows. It is possible to create a name that describes the cell or range then
use it instead of having to specify a range with actual cell references. Such a descriptive
name in a formula can make it readable and easier to understand its purpose. For example,
the formula =SUM(SecondQuarterProfits) might be easier to identify than =SUM(AlO:C20). In
this example, the name SecondQuarterProfits represents the rangeAlO:C20 on the
worksheet. Names can also be used to represent formulae or values that do not change
(constants). For example, you can use the name .Tariffs to represent the import tax amount
(such as 7.0 percent) applied to imports.

To create a named range

To create a named range proceeds as follows:
1. Select the range to be named:
2. Click inside the name box to move the text cursor inside. Delete the Cell reference that is
there and type a name for the range.
3. Press Enter key to apply. Figure 2.13 shows a worksheet range called sales that has
values used in a formula to give the sum in cell C 11.

2.7 Basic functions and formulae

Formulae perform mathematical operations ranging from very simple arithmetic problems t9
complex scientific, financial and mathematical analysis.
Statistical functions.
1. Average: It returns the average (mathematical mean) of a set of values which can be
numbers, arrays or references that contain numbers. If the value 20 is in cell DIO and 30 in
ElO then:
=Average(D lO:E 1 0) returns 25 as the average of the two values.
2. Count: Counts the number of cells that contain values within a range e.g.
= count (AIO: EIO) many return a value 5 if all the cells have values.
3. Max: It returns the largest value in a set of values. It ignores text and logical values e.g. ==
Max (AlO:EIO) will return the maximum value in the range.
4. Min: It returns the smallest value in a set of values. It ignores text and logical values e.g. =
Min (AIO:EIO) will return the minimum values in the range.
5. Mode: It returns the most frequently occurring value in a set of values. e.g. = Mode
(AIO:ElO)
6. Rank: Returns the rank of a number in a list by comparing its size relative to the others. For
example if A 1 to AS contains numbers 7, 3.8,3.8, 1 and 2 then RANK (A2, Al :A5,1) returns
3 while RANK (AI, AI:A5,I) returns
7. The general format is RANK (number to be ranked, range, order).

Logical functions
1. If: It returns a specified value if a condition is evaluated and found to be true and another
value if it is false. If (marks > 50, “pass”, “fail”) will display a pass if values are more than 50
else it will display fail.
2. Countif: Counts the number of cells within a specified range that meet the given condition
or criteria. e.g. suppose A 1 0 : E 1 0 contains eggs, beans, beans, eggs, eggs,
countif(AIO:EIO, “Eggs”) will return 3.
3. Sumif: It adds values in the cells specified by a given condition or criteria. e.g. For example
if AIO to ElO contains values 10,50,60, 30, 70, to sum all values greater than 50 =
Sumif(AIO:EIO, “>50”). This returns 130.

Mathematical functions
1. Sum: adds values in a range of cells as specified and returns the result in the specified
cell. e.g Sum (AIO:EIO) adds values in the range
2. Product: multiplies values in a range of cells and returns the result in the specified cell. For
example if A 10 has 30 and BIO has
3. Product (AlO:BIO) will return 90.

Arithmetic formulae – using operators

Operator Function

+ (plus) adds values as specified

– (minus) . subtracts values as specified
* (multiplication) multiplies values
/ (division) divides values.
( ) parenthesis encloses arguments to be calculated first.

For a formula =(Al +C3)/E20, if the value in E20 is not zero, the result is displayed in the
current cell.

Order of execution
If several. Operators are used in a single formula; Microsoft Excel performs the operations in
the order shown in Table 2.3. Formulas with operators that have same precedence i.e. if a
formula contains both a multiplication and division operator are evaluated from left to right.
Enclosing part of the formula to be calculated in parentheses or brackets makes that part to
be calculated first.

2.8 Editing a worksheet

Coping and moving data
Spreadsheet software automates many processes that could have been tedious if done
manually. For example with Microsoft Excel, you can do calculations using formulae fairly
easily as you give the data and correct instructions to the program. Copying and moving of,
data can also be done quickly and efficiently.
When data is cut or copied from the worksheet, it is temporarily held in a storage location
called the clipboard.

Copying data
To copy a cell or a range of cells:
1. Highlight the cells or range you want copied
2. Click the Edit menu then select Copy command.
3. Select the cell in which you want to place a copy of the information 4. From the Edit again,
click Paste command. The Paste command puts a copy from the clipboard on the specified
location

Moving data
Unlike the Copy command where a duplicate copy is created, the Move command transfers
the contents of the original cell (s) to a new location.

To move a range of cells:

1. Highlight the range you want to move.
2. From the Edit menu, select Cut.
3. Specify the location you want to move the contents to.
4. From the Edit menu again, click Paste.

2.9 Worksheet formatting

Worksheet formatting refers to enhancing the appearance of the worksheet to make it more
attractive and appealing to the reader. Appropriate formatting should be used to lay
emphasis, catch attention and bring otherwise II hidden detail to the fore of the document.
The golden rule of formatting is to use simple clear formats. It essentially consists of
changing text colour and typeface (font), size, style and alignment. In Microsoft Excel, format
the cells whether empty or not and their contents will acquire the set format automatically.
To format a single cell, make it the current cell then format menu option and select the cells
command In the format cells dialog box, make the formatting specifications that you wish
then click the ok button to apply. If it is a range of cells, they must be highlighted first before
formatting them as a block of cells.
Formatting text
1. Highlight the cells that have the text to be formatted.
2. Click Format menu then cells command. The dialog box appears
3. Select the font tab as shown in the figure by clicking it.
4. Select the font type e.g. Times New Roman. Other font formatting features like style, size,
underline and colour are available and can be selected.
5. Click button to apply.

NB: Alternatively, use the formatting toolbar to accomplish all your text formatting needs.
Notice that the options in the font dialog box are commands on the formatting toolbar.

Formatting numbers
1. Highlight the cells that have the numbers to be formatted.
2. Click Format menu then cells command. The dialog box in Figure 2.15 appears.
3. Select the Number tab as shown in the figure below.
4. You can now choose number formats as explained below:
Number Meaning
General general format cells have no specific number format.
Number Used for general display of numbers e.g. 2345.23.
Currency For displaying general monetary values e.g. $100, Ksh.10.
Accounting Lines up the currency symbols and decimal poin s. Displays date in chosen
format.
Date Displays time in chosen format.
Percentage Multiplies the value in a cell with 100 and display ‘ it as %.
Text Formats cells to be treated as text even when numbers are entered.
Custom For a number format not predefined in Microsoft Excel, select custom then
define the pattern.

Worksheet borders
You may need to put a printable border around your worksheet or in a range of cells to make
it more attractive and appealing. To put a border:
1. Highlight the range you wish to insert borders. From the format menu, click cells command.
2. Click the borders tab and specify the border options for left, right, top and bottom. .
3. From the style options, select the type of line thickness and style. Also select the preset
options.
4. Click the ok button. The selected range will have a border around it.

Formatting rows and columns

Sometimes, the information entered in the spreadsheet may not fit neatly in the cell set with
the default height and width. It therefore becomes necessary to adjust the height of a row or
the width of a column. The standard width of a column in Microsoft Excel is 8.43 characters
but can be adjusted to any value between 0 and 255.

Changing column width

1. Move the mouse pointer to the right hand side line that separates the column headers i.e.
for instance the line between A and B.
2. Notice that the mouse pointer changes from a cross to a double arrow
3. Click the mouse button and hold it down so that you can now resize the width of the
column by dragging to the size you wish. After Dragging to the required point release the
mouse button and the Column will have a new size.

NB: Alternatively, move the cell pointer to one of the cells of the column then click Format,
point to Column then click Width command from the sidekick menu. Type a width in the dialog
box that resembles Figure 2.17 then click Ok.button to apply.
NB: To change the widths of several columns at the same time, highlight them first before
following this method.

Changing row height

1. Point to the line that separates two row numbers e.g. the line between 1 and 2. The mouse
pointer becomes a double arrow.
2. Drag the line until the height of the row is as required then stop and release the mouse
button.
NB: Alternatively, click Format point to Row then click Height from the sidekick menu that
appears. Type the height that you wish in the dialog box that appears and then click OK
button to apply.

Inserting rows and columns

I, Click cell A5 to make it the current or active cell.
2.clik insert then columns to insert a ‘row above cell A5 and shift all the other rows downward.
OR
Click insert then Columns to insert a column to the left of column A and shift all the others to
the right.
NB: Alternatively, click insert then cells to display the dialog box select the entire row or
entire column options to insert a row or column respectively.

Global worksheet formatting

The word global in this case refers to the entire worksheet. In order to format the whole
worksheet globally, it must be selected as a whole.
Two methods can be used to select a worksheet globally:
1. Click the top left comer of the worksheet that has a blank column header i.e. immediately
on the left of A and just above I,
OR
2. Press Ctrl+A on the keyboard.

Notice that the whole worksheet becomes highlighted. It can now be formatted as one big
block using format cells command.

Using autoformat
It allows the user to apply one of sixteen sets of formatting to & selected range on the
worksheet. This quickly creates tables that are easy to read and are attractive to the eye..
1. Select a range e.g. B 1 :G7 to make it active.
2. Click format then select the auto format command on the menu that Appears. Select a
format from the autoformat dialog box shown in Figure 2.19.
3. Click the ok button to apply the format to the selected range.

2.9 Data management

At times, it becomes necessary to use advanced data management tools to manage large
,data stored on a ‘worksheet. For example, if the worksheet has many records, it may
become necessary to arrange them in a particular order using a method called sorting for
easier access to data items. Other methods of data management include use of filters,
total/subtotal function and forms.

Sorting
To carryout sorting proceed as follows:
1. Highlight the range that you wish to sort by clicking its column header letter.
2. Click Data then Sort . Notice that the Sort by field is already reading the field that you
selected. This field is called the criteria field.
3. Select the field to be used as the key for sorting and the sort order as either descending or
ascending then click OK button to apply.

Filtering data
Filtering is a quick and efficient method of finding and working with a subset of data in a list. A
filtered list will only display the rows that meet the condition or criteria you specify. Microsoft
Excel has two commands for filtering lists.
1. The auto filter: It uses simple criteria and includes filter by selection.
2. Advanced filter: It uses more complex criteria.
In this Pupil’s Book we will look at the autofilter.

Autofilter
Filters can be applied to only one list on a worksheet at a time.
1. Click a cell in the list that is to be filtered; usually the list is in a column.
2. On the Data menu, point to Filter, and then
3. To display only the rows that contain a specific value, click the arrow in the column that
contains the data you want to display as shown in Figure 2.21.
4. Click the value that is to be displayed by the filter from the drop down list. e.g in the
example below, the selected value is 34.
NB: Sometimes while looking through a list of values on a large worksheet, you may come to
a value of interest and want to see all other occurrences of the value in the spreadsheet.
Simply click the cell that has the value then click auto filter on the standard toolbar. Microsoft
Excel turns on AutoFilter and then filters- the list to show only the rows you want.

Subtotals function
Consider the following scenario: A company that has many salespersons
will need to know how much each of them should be paid at the end of a period by looking at
individual sales volumes. Also, the grand total for all the payments has to be calculated.
Therefore, if the salespersons are held in a list, there would be need to calculate the amount
due to each of them. This can be called a subtotal in the list. All the subtotals can then be
added together to make the grand total. Consider the following list:
Name Amount Owed
Stephen ` 6000
Joy 3000
Stephen 2000
Virginia 5000
Joy 800
Stephen 200
Virginia 5000

Microsoft Excel can automatically summarise the data by calculating subtotal and grand total
values of the list. To use automatic subtotals, the list must have labelled columns and must
be sorted on the columns for which you want subtotals. In this example, the list is first sorted
by name
1. Click a cell in the list that will have subtotals e.g. cell A3.
2. On the Data menu click Subtotals 3. Notice that all the data range is now selected.
3. In each change in box, select Name from the drop down list because we want a subtotal
for each of the names.
4. In the Use function box select the sum function then select the list for which subtotals will
be inserted in the add subtotals box by checking the appropriate label. In this case it is
the amount owed field.
5. Click ok button to apply and the list will now have sub totals inserted
Totals function
Use theAutoCalculate feature in Microsoft Excel to automatically show the total of a selected
range. When cells are selected, Microsoft Excel displays the sum of the range on the status
bar. Right clicking this function displays other functions like Min, Max and Average that can
also be used. To find the total of a range, highlight it then click the autosum icon ∑ on the
standard toolbar.

Forms
A form is a specially prepared template that the users can use to enter data in a worksheet. It
is specifically formatted to enable users to enter data in a format that is more convenient to
them. If data is collected on paper before entering in the computer, then a form can be
created to have the layout of the data on the paper to quicken data entry procedures. To
display a form: Click ‘Data, then form.

2.10 Charts/graphs
Charts/graphs are graphics or pictures that represent values and their relationships. A chart
helps the reader to quickly see trends in data and to be able to compare and contrast aspects
of data that would otherwise have remained obscure. Microsoft Excel has both two-
dimensional and 3-dimensional charts that can be used instead of the raw data in the table
that has to- be studied for a long time to understand it.
The various types of charts available include column, bar, line. Pie, bubble and area charts
among others. Consider carefully the type of chart that would best represent the base data in
the worksheet before creating one. For example, if the aim is to depict the performance index
of a student from Form I-to 3, a line chart would be most appropriate because it clearly shows
the trend in performance.
Types of charts
1. Line chart – represents data as lines with markers at each data value in the x-y plane.
2. Column chart- represents data as a cluster of columns comparing values across
categories. .
3. Bar chart – data values arranged horizontally as clustered bars. Compares values across
categories.
4. Pie chart – it displays the contribution of each value to a grand total.
5. Scatter chart – compares pairs of values on the same axis.
To view types of charts, right click the chart object then select the chart type command.

Creating a chart
A chart must be based on values that are already entered in the worksheet.

To create a chart:
1. Select the range of values for which you want to create a chart.
2. Click the Chart wizard button on the standard toolbar and the chart wizard dialog box will
open as shown in Figure 2.25
3. Click the type of chart you wish to .create. If the office assistant appears, close it. The chart
sub-type preview will show several styles of the selected chart type.
4. Click the Next button to move to the dialog in Figure 2.26.
5. Click the Series tab then the collapse dialog button on the labels text box.
This will shrink the dialog box so that only the category labels text box is shown. Highlight the
data labels from the worksheet.
6. Click the Expand dialog button to bring the full dialog box into view then click the: Next
button. In step 3 of the wizard, use the appropriate tabs to type the title of the chart, show a
legend, select whether to display gridlines or not etc. After all these click the Next button.’
7. At step 4 determine whether the chart will be inserted in the current worksheet or a new
worksheet then click Finish button (Figure 2.27).

Moving and resizing a chart

Once the chart is created, its size and location can be changed in the worksheet. The chart
element is enclosed inside a boundary called the chart area and hence both can be resized
independently. Simply click the object you wish to resize and use the object handles just like
in objects to drag to size. To move the chart, click inside the chart area then drag to the
desired position.

Data ranges
A data range is a rectangular block of cells that provides the base data that is used to create
the chart. In charting, a data range is referenced as an absolute range e.g. .
=Sheetl !$B$2:$C$8 which means that the base data is found on Worksheet 1 and absolute
range B2:C8.
To see the data range of a chart, right click it then select the Source data command. .

Labels
Each representation of data on a chart can either be labelled by a value
or text label. For example, in a bar chart that compares the height of pupils, each bar can be
given a value label to make it more readable.

To label:
1. Right click the chart then select the Chart options command from the shortcut menu.
2. Click the lables tab and choose whether you want value or text labels then click OK button
to apply. .

Headings and titles

Each chart must have a heading showing clearly what it represents. To I make the chart
understandable, include axis titles.
To include axis titles proceed as follows:
1. Right click the chart then select the. Chart options command.
2. Click the Titles tab then type the chart title (heading). And axis titles respectively.
3. Click OK button to apply.

Legends
The legend is like a key that explains what each colour or pattern of the data representation
in the chart means. For example, Microsoft Excel may give red colour to one data value and
green to the other. Without a legend it would be difficult to know how to differentiate the two
sets of values.
To create a legend:
1. Right click the chart then select the Chart options command.
2. Click the legends tab and specify that it be displayed in the chart area.
3. Click OK button to apply.

2.11 Printing worksheets

A worksheet will finally be printed for sharing with others or for filing purposes. If it contains
objects like charts, it may not fit on a standard printing page using the default printing options
and settings. Therefore, Microsoft Excel allows the user to preview and set up the pages of a’
worksheet in order to fit them on the hard copy page.

Page setup
1. Click .File menu option then Page setup command to display the page setup dialog box. . .
2. On the Page tab, select the orientation of the page. Study the meanings of each buttons
and options in Figures 2.28.
3. After making the necessary selections, click OK to apply.

Print preview
It displays the worksheet from the point of view of the printer i.e. exactly the way it will look
when printed. Before using this command, make I sure the chart is deselected.
1. Click the Print preview button on the standard toolbar.
2. The worksheet will be displayed in the print preview window with the status bar reading
preview.
3. Click Setup to start the page setup dialog box. To close the preview, click the Close.
Button.
Print options
To print click File then Print command. The print dialog, box appears as shown in Figure 2.29
.
1. Select printer – the name box in this dialog box enables a person to select the printer that
will be used to print the document. All the printers that are installed on the computer will be
available here.
2. The print what options are:
Selection – this prints the selected worksheet area.
Workbook – prints all the worksheets in the workbook.
Selected chart – prints the selected chart only.

Page orientation
As explained earlier, page orientation refers to the layout of the text on the page. A worksheet
can also be printed on either landscape or portrait depending on the number of columns
across the worksheet.

Pages and copies .

The number of copies box specifies how many copies of a particular worksheet or workbook
should be printed.
Sometimes only some specified pages in a workbook are specified for printing e.g. if a
workbook has 100 pages and you wish to print only pages 50 to 60 select the page(s) range
button then type 50 and 60 in the from, to boxes respectively before clicking the OK button.

Printing
After selecting all the options, click the OK button to print.

Some common printing problems

1. A message appears on the screen saying that the printer specified could not be found in
the directory.

Possible problems and solutions

1. The printer could be off. Switch it on and it will start printing.
2. The data cable to the printer could be loose. Make sure it is firm at the ports.
3. The wrong printer could have been selected. Select the right one in the print dialog box
and send the print job again.
4. A message appears on the screen reading that there is paper jam.
The printer is clogged with a paper jam. Alert the lab, technician or the Teacher to clear the
paper jam.