HPCA MERP_ 012D: HPCA Patient Excel Register Training Manual, October 2010 1

BASIC EXCEL TRAINING

MANUAL












HPCA MERP_ 012D: HPCA Patient Excel Register Training Manual, October 2010 2


THE SPREADSHEET PACKAGE:EXCEL

I NTRODUCTI ON

A spreadsheet is an electronic table composed of rows and columns that store text and
numbers. Excel is a spreadsheet application package designed to help you create and use
professional quality spreadsheets. The outstanding feature of a spreadsheet package is the
effective way in which calculations are done. Excel makes it easy to manipulate numbers, by
performing calculations, sorting data and presenting information in the form of graphs.
Some of the special features available when using Excel are:
Results are calculated automatically when using formulas and functions
Results are recalculated automatically when data entries are changed
Data can be displayed as graphs or charts
Data can be sorted in ascending or descending order
Modern spreadsheet packages offer functions with dynamic linking powers. Cells
from one spreadsheet can be linked to that of another. This means that if data is
altered in one spreadsheet it will automatically be updated in any other linked
spreadsheet.
Spreadsheets are mainly used in situations where calculations are required, as opposed to
word processing where documents should merely be typed.











HPCA MERP_ 012D: HPCA Patient Excel Register Training Manual, October 2010 3



Learning Outcomes 1:
To understand the layout of a Spreadsheet Document
Note: A spreadsheet document looks different from a word processing document. You have
to understand the layout to be able to use the spreadsheet effectively.

The spreadsheet screen consists of rows and columns that form a grid of cells
Rows extend horizontally across the screen and are numbered 1,2,3 etc
Columns extend vertically across the screen and are A,B,C, etc
Each position where a row and column intersects is called a cell. Each cell is
identified by a cell address, e.g. the cell address E4 indicates the cell where column E
crosses row 4.
The information the cell contains is called the contents of the cell and can be any of
the following:
o Numeric values (any number);
o Text (any combination of letters or special characters);
o Formulas and/or functions (used for calculations)
o Dates or times
The following is an example of a spreadsheet:
A B C D E
1 MYMONEY
2 BUDGET 1st Term 2006
3 Description Jan Feb March Total
4 Snacks 50 60 70 180
5 Movies 60 60 60 180
6 Travel 20 30 35 85
7 TOTAL 130 150 165 245

In this example:
Row 1&2 contain the heading for the document
Row 3 contains the headings for each of columns
Row 4,5 and 6 contain the data for the individual budget items

HPCA MERP_ 012D: HPCA Patient Excel Register Training Manual, October 2010 4

Row 7 contain the totals of each column
The cells B4,C4 and D4 contain the amounts budgeted for snacks for the months
January, February and March
Cell E5 contains the total expenses budgeted for movies for the period January-
March.
NB: A Spreadsheet is also called a workbook. A workbook can consist of a number of
different worksheets, i.e. like different pages in a book.

Practice

Exercise 1
Consider the example given above. Write down the cell contents of each of the following
cells: B3; C5; D6; and A7.

End of Practice

Learning Outcomes 2:
To Activate Excel
Note: To use the Excel programme, you must open (activate) the application. There is more
than one way of doing it.

Practice

Click the - Start button - Select Programmes - Select Microsoft Excel
OR
Click on the Microsoft Icon on the desktop:

HPCA MERP_ 012D: HPCA Patient Excel Register Training Manual, October 2010 5

Microsoft Office
Excel 97-2003 Worksheet


The following screen will appear:


Learning Outcomes 3:
To explore the excel screen
Note: In order to create a spreadsheet it is necessary to become familiar with the Excel
window and worksheet elements

You will find that working in Excel is in many ways similar to working in Word, even though
there are some functions that are specific to working with spreadsheets.
The following appear and work the same in both Word and Excel:
The Menu Bar, Standard Tool Bar ,Title Bar, Formatting Tool Bar and Status
Bar

HPCA MERP_ 012D: HPCA Patient Excel Register Training Manual, October 2010 6





The following are unique to Excel:
The Formula Bar displays the cell reference address and the contents of the cell
:

Rows extend horizontally across the screen and are numbered 1,2,3 etc
Columns extend vertically across the screen and are A,B,C, etc
Each position where a row and column intersects is called a cell. Each cell is
identified by a cell address, e.g. the cell address E4 indicates the cell where column E
crosses row 4.
An Active Cell is the framed cell
See this picture for the above:

HPCA MERP_ 012D: HPCA Patient Excel Register Training Manual, October 2010 7


A workbook consists of a number of Sheets (pages).To move from one sheet to the
other, you click on the sheet tags at the bottom of the spreadsheet. You can name the
sheets as you like.
(To copy or rename a sheet, Right click on the sheet. Make your selection e.g. Select
move or copy and tick create a copy. Select rename give it a new name.).
Learning Outcomes 4:
Working with Worksheets/Sheets


Note: An Excel workbook is like a notebook that can consist of different sheets.
Sheets allow you to organise and categorise your work in a logical manner. A
workbook is automatically created with three worksheets. You can add,delete,rename
or copy worksheet

Practice

4.1 To insert a new sheet

HPCA MERP_ 012D: HPCA Patient Excel Register Training Manual, October 2010 8


Right click on any Sheet - Tab: e.g. Sheet 1



4.2 To insert, Delete Rename, Move or Copy

Right click on the sheet tab you need, and select the command you need.



HPCA MERP_ 012D: HPCA Patient Excel Register Training Manual, October 2010 9


Learning Outcomes 5:
Entering Data into a Spreadsheet

Note: You can move from cell to cell using the Enter-key, Tab-key or any of the arrow keys.
To enter:
1.4.1 Text-Type any alphabetical letter or the special character () in front of numbers.
These will be left aligned in the cell and will not be used in any calculations.
1.4.2 Numbers-In the required cell type any digit .These are right aligned in the cells

1.4.3 Formulas and Functions start the formula with = or +
Never:
Use comma (,) for decimal. Use full stop (.). (e.g. 1.2333 not 1,2333)
Type R in front of a number, this will be read as text. Format the cell to include
currency
Use a spacewhen typing values (e.g. 23678 not 23 678)
Learning Outcomes 6:
Editing (or changing) Cell Contents
Note: You can edit(or change) the contents of a cell at any time
1.5.1 To correct a mistake whilst typing Use backspace
1.5.2 To replace the contents of a cell - click on the cell and type the new information
1.5.3 To change parts of the contents of a cell In the formula bar use backspace, delete
and arrow keys to get the position you need to change. Type the required information.
1.5.4 To erase the contents of a cell in the required cell, click the Delete Key





HPCA MERP_ 012D: HPCA Patient Excel Register Training Manual, October 2010 10



Learning Outcomes 7:
Highlighting (Marking or Selecting a Range)
Note: You can highlight a specific section of the cells in a spreadsheet. The highlighted
section is called a range, and is named by referring to the top most left cell in the black and
the bottom most right cell in the block
To select a cell, click on the cell
To select a block, click-and-drag it by clicking on the cell in the top most left corner,
holding down the left mouse button, moving to the cell in the bottom most right
corner and releasing the mouse button
To select non-adjacent cells ( a selection consisting of more than one block of cells
that are not connected), select the first block and then hold down the CTRL-key while
you click to select other cells.
To select a complete row, click on the row number
To select a complete column, click on the column letter
To select the entire workbook, click on the position between the first row and the first
column.
To cancel a selection, click anywhere outside the selection.
Learning Outcomes 8:
Inserting and Deleting Rows and Columns
Note: You can insert rows and columns or delete them

1.7.1 To insert rows or columns
Place the cursor in any cell where the row or column is required Click on Insert Select
Row or Column.

1.5.5 To delete rows or columns
Place the cursor in any cell in the row/column to be removed - Right click the mouse
Select Delete Select either Entire Row or Entire Column as required Click on OK


HPCA MERP_ 012D: HPCA Patient Excel Register Training Manual, October 2010 11


Learning Outcomes 9:
Changing the height and width of rows and columns
Note: You can change the width of columns and the height of rows

1.8.1 To set the column width by dragging and dropping
Move the cursor to the column headers, i.e. between column A and B. The large mouse
marker will change to a two way arrow Press the left mouse button down and click and drag
the column border to the right to increase it, or to the left to decrease it.
1.8.2 To set the height or a row by dragging and dropping
Move the cursor to the position between the two row indicators on the left of the screen, e.g.
between row 1 and row 2 The large mouse marker will change to a two way arrow Press
the left mouse button down and click-and-drag the row border downwards to increase it, or
upwards to decrease it.

1.1 Practice
Exercise 1.1
1. Key in the spreadsheet as it appears
2. Adapt the column widths where necessary
3. Save the document as BANKING.
NAME
QUESTION 3A

Banking Transactions

Jan Feb April
2004 2004 2004
Bank Charges
Service Fees 20 30 50
ATM Charges 15 25 45
Stop Orders 10 20 40
Debit Orders 10 20 40



HPCA MERP_ 012D: HPCA Patient Excel Register Training Manual, October 2010 12


Exercise 1.2
1. Retrieve the spreadsheet BANKING
2. Edit the spreadsheet as follows:
2.1 Change the question number to 3B
2.2 Banking Transactions must appear in bold capital letters
2.3 Bank Charges must appear in capital letters and it must be underlined
2.4 The column headings Jan and Feb must appear in full January and February
2.5 The column headings must change to Italic and it must be centered in the column
2.6 Insert a new column with heading March between February and April
2.7 Delete the row under Banking Transactions
3. Save the document as BANKING.

Learning Outcomes 10:
Formatting Cell Contents

Note: Cell values can be displayed in different ways, e.g. Currency, Percentage, Dates, Times
etc. Formatting can either be done global, in which case it is relevant to the entire
spreadsheet, or it can be specified to be applied to a selected range.

Highlight the range to be formatted Click on Format Select Cells Click on Number -
Select the Format Tab you need (e.g Number, Alignment etc).

1.2 Practice

Exercise 1.3
1. Create the following spreadsheet exactly as it appears below
2. Adjust the column width when necessary. Format the cells individually to display the
different format for DATES and TIMES as indicated

HPCA MERP_ 012D: HPCA Patient Excel Register Training Manual, October 2010 13

3. Save the documents as TIMETABLE and print the documents

QUESTION 3
NAME:
EXAMINATION TIMETABLE
DAY DATE TIME SUBJECT
Monday 1 December 2004 09h00 Sesotho 1
st
paper
Monday 2004-12-01 11:00 Sesotho 2
nd
paper
Wednesday 3- Dec-04 9:00 AM English 1
st
paper
Wednesday December 3,2004 1:00 pm Maths 1
Thursday 12/04/04 9:00 Maths 2

Learning Outcomes 11:
Understanding basic Functions and Formulas

Note: Calculations in Excel are done by using formulas and functions. They are typed in the
cell where the result of the calculation must be dispalyed

11.1 Important terminology
A formula/function always starts with a prefix, i.e a character that distinguishes it
from ordinary text. The most commonly used prefixes are + and =
Operators are used to indicate arithmetic calculations i.e.
+ addition
_ subtraction
* multiplication
/ division
Cell addresses are letters and numbers that refer to a cells location in the spreadsheet
i.e A9, B4 etc.
A range of cells is a reference that indicates a continuous block of cells i.e A1:B20
indicates all the cells from cell A1 to Cell B20


HPCA MERP_ 012D: HPCA Patient Excel Register Training Manual, October 2010 14


A constant is a fixed number used in calculations e.g interest rate
11.2 Functions and Formulas

Functions are predefined modules that are built into the spreadsheet and can be used to do
calculations. Most functions consist of a prefix, function name and range. An example of a
function is:
= sum (B1:B7) meaning {add all entries between B1 and B7}

There is a wide variety of functions available. A function can be either created by typing it in
or be selected by clicking on the Insert Function button and selecting the function needed.

Formulas are arithmetic expressions that consist of a prefix,operators, relevant cell addresses
and/or constants. Formulas could also include functions. An example of a formula is:
=C5+D3/5


NB: You are allowed to use brackets to ensure the correct sequence of calculations.

HPCA MERP_ 012D: HPCA Patient Excel Register Training Manual, October 2010 15

e.g.: = ((C5+D3)/5*20) will add and then devide by 5 and multiply by 20 which will have a
different answer to: = C5+D3/5*20

NB.: If you know the cell addresses, you can type in the formula of function without using
the mouse to do selections.

HPCA MERP_ 012D: HPCA Patient Excel Register Training Manual, October 2010 16


Learning Outcomes 12:
Copying Functions and Formulas

Note: It often happens that the same formula/function is used repeatedly in a spreadsheet.
Formulas and functions can be copied like any other cell, which means that it is not necessary
to retype the formula/function at every position where it will be used.
12.1 Relative Cell references
When formulas or functions are copied, the cell references are changed relative to their
position i. In the following example the function used in cell B6 to calculate the total
expenses for one month is :=sum(B3:B5). When this function is copied to cell D6, where
you want it to work out the total expenses for another month, Excel automatically changes the
function to: =sum(D3;D5).In this case you have used relative cell references, i.e. the cell
references change as it is copied



To copy a function/formula down or across a range of cells:
Click the cell that has the formula
Go to the bottom right end of active cell
Find a thin cross
Click and drag it through the range


HPCA MERP_ 012D: HPCA Patient Excel Register Training Manual, October 2010 17

IMPORTANT NOTES:
The following error messages can appear in a cell to show that the function or formula
could not be calculated.

ERROR MESSAGE REASON SOLUTION
####### Column is not wide enough, or a
negative date or time is used.
1. Increase the width of the
column
2. apply a different number
format
#DIV/0! Division by zero or division by
a cell with no value
Change the divisor to a number other
than zero or replace a blank cell with a
value.
#NUM! There is a problem with the
number e.g. Trying to
calculate a square root of a
negative number. Or when you
enter R1, 000, where the number
1000 is expected in the formula.
Make sure the arguments used in the
function are numbers
#REF! The formulas refers to a cell
which does not exist- happens
when a cell is deleted
1. Change the formulas
2. Restore the cells on the worksheet
by clicking Undo immediately after
you delete or paste the cells.
#VALUE! Occurs when the wrong type of
argument or operand is used e.g.
when trying to do calculations
using a cell that contains text.

#NAME? Occurs when Microsoft Excel
doesnt recognize text in a
formula
1. Correct the spelling. Insert the
correct function name into the
formula by clicking Function on
the Insert menu.
2. Enclose text in the formula in
double quotation marks.
3. Make sure all range references in
the formula use a colon(:) ; for
example, SUM(A1;C10)





HPCA MERP_ 012D: HPCA Patient Excel Register Training Manual, October 2010 18

Capturing Data and running month-end on the Excel Patient Register
1. Enter all the patient information from the data capturing tool
NB: (Make sure all information is correct)
2. Enter information only in the cells that are specified for it, noting the format if
specified) (e.g. if date is specified to be yyyy,mm,dd; do not enter dd;mm;yyyy)
3. Do not enter data where there are formulas, this will change your register and cause
problems at the end.
4. Follow the following steps to run the month end/new month processes:
a. Open the previous months patient register
b. Go to HBC STATS sheet. Row # 32-(total to be carried forward to next month) and
write down or copy all the numbers.

c. Enter the numbers carried forward in Row 4-HBC STATS Sheet of new month
d. Save the document for the month you are working with (e.g.: Patients May
2010).
e. Go to HBC STATS sheet .Enter the numbers you carried forward; into row #4
(totals carried forward from last month).
f. In HBC REGISTER Sheet go to the STATUS column, filter

HPCA MERP_ 012D: HPCA Patient Excel Register Training Manual, October 2010 19


all rows with exits. (RIP, NR, D and ND); and delete the rows
NB: (Make sure you delete the whole row, not just parts of it).
g. Unfilter
NB:(Always remember to unfilter before going on to doing other things)
h. Go to STATUS column again. Change all New patients from last month (N ) to old
patients (O).
NB:(Make sure you only have status=O)
i. Go to HBC FORMULA SHEET to refresh the formulas. This will remove all rows
with #REF and #VALUE.
-Highlight the data in the whole 1
st
row
-find the thin cross in the last cells bottom right corner
- double click on it or drag it to the last row of data
NB:( this copies the formulas throughout the spreadsheet)
j. Enter all new patient details.(STATUS=N)
NB: Make sure you use the right codes (e.g. DIAGNOSIS: HIV=1;Black=B etc).
k. Save your document
l. Go to HBC STATS sheet, and make sure all the numbers highlighted in pink are
the same. This means your register is balanced.
NB: (DO NOT CHANGE THE NUMBERS MANUALY AND DELETE FORMULAS)
5. E-mail the patient register to the M&E officer, by the 25 of every month
NB: DO NOT SEND A REGISTER THAT DOES NOT BALANCE.ASK M&E OFFICER FOR
HELP IF YOUR NUMBERS DO NOT BALANCE.







HPCA MERP_ 012D: HPCA Patient Excel Register Training Manual, October 2010 20

Common Errors on Patient Register and Solutions

ERROR SOLUTION
Pink numbers do not balance Check what total does not balance with others, and
correct. e.g if it is diagnosis(H18) , then check the
diagnosis column in HBC Register FOR ANY
ERRORS
New patients row (Row 11) has
zeroes or less numbers than expected
a) Check that you have put N in Status column
for all the new patients you entered.
b) Make sure you have removed filters because
all new patients will be entered at bottom rows
that may not be having formulas
c) Check that your formulas are refreshed cause
deleted rows will interfere with the continuity.
Pink Numbers for Total
Patients(Row 12) does not balance
with other pink numbers.
a) Check your numbers carried forward from last
month.
b) Check that no extra patients that may be
hidden at bottom far rows are being counted
Total number of males/females in
category or diagnosis does not equal
in the overall patient total
Check the Gender column. Make sure all genders are
entered and there are no spaces before the letters.
( e.g. space F will not count)
Entries after a certain row do not get
counted
Check that you have refreshed your formulas
Nothing seems to be working that
you are trying
a) Phone your M&E officer and explain clearly
what the problem is.
b) Start with last months register or the last
register that was working properly and start
the new month process again.