MODULE 3 - SPREADSHEETS
Automatic Calculator
Spreadsheet package
• An application program used to work with
numbers and to draw graphs.
• They have replaced use of calculator/manual
calculations in modern offices.
Common uses:
To process accounting data.
To process scientific/ engineering
data.
To do budgeting.
To do production and financial
forecasting.
To process weather data.
To process student marks.
Common spreadsheet
packages
MS Excel
Ability Office.
Plan Maker.
Calculate.
Resolver One.
Spreadsheets vs. manual calculations
Calculation results are produced very
quickly.
Results are very accurate.
Formula can be easily replicated/copied to
different sets of data.
Functions greatly simplify complex
calculations.
Calculation results can be expressed to any
desired number of decimal places.
Different types of graphs easily constructed.
Spreadsheet structure
Workbook- a set of one or more related
worksheets.
Worksheet- A very large grid of rows and
columns.
Cell- a single box on the spreadsheet grid.
Cells can be given names by the user.
Row- a line of cells across the spreadsheet.
Each row has a reference number.
Column- a line of cells down the
spreadsheet. Each column is normally
referenced by a letter.
Cell reference/address – the cell’s
combined row and column references.
Information display
There are two types of information on a worksheet:
a)Data/Values – Numbers entered into the spreadsheet and
are used in calculations. Results of one calculation may be
used in another.
b) Labels – Text and numbers entered on a spreadsheet to
describe data on the spreadsheet. They help users
understand purpose of numeric entries on the spreadsheet.
They are not used for any calculation purposes e.g.:
1. Spreadsheet title
2. Headings
3. Column/row names
Data types
• Spreadsheet packages allow users to enter and process data and labels of
different types.
• Examples of data types:
o Text- text values
o General-display values with no specific data format
o Percentage-multiply cell value by 100 and display value with a % symbol.
o Number – integers and real values.
o Currency-display general monetary values. Specify currency symbol.
o Accounting-allows more formatting of monetory values.
o Scientific-display values in standard form. Useful for very small/large
numbers.
o Date- date values. There are different date formats.
o Time- time values. There are different time formats.
o Custom-allows user to specify the number format code/mask.
Formatting spreadsheet
• This makes it professionally presentable e.g:
Aligning/justifying cell contents.
Changing font size and type.
Emboldening, underlining and italizing cell values.
Bordering worksheet.
Inserting page numbers, headers, footers, comments
and watermarks.
Print worksheet using portrait/landscape orientation.
Formulae
• A mathematical expression that performs a
particular calculation in a spreadsheet.
• There are two types:
Custom formula
Functions
Custom formula
• A formula created by the user.
• A formula/function always starts with an ‘ =‘ sign.
• Mathematical symbols used in formulae
+ - addition
- - subtraction
/ - division
* - multiplication
() – parenthesis/brackets
• Comparison symbols used in formulae:
o > - greater than
o < - less than
o >= - greater than or equal to
o <= - less than or equal to
o <> - not equal to.
Examples of custom formulae
• D3=B3-10
• F2=A2+B5
• C3=(C3+B3)/2
• B1=0.01*D3
• E10=20-(A4+B4)
• M8=G8*K8
• A20=15%* C20
Functions
• Formulae that come bundled in a spreadsheet
package ready for use.
• Perform common/standard calculations on
numbers.
• Also called standard/built-in formulae.
• Easier and simpler to use than custom formula.
Examples of functions
Function Purpose Example
SUM Find total value of two or more cells =SUM(B1:B5)
AVERAGE Finds the arithmetic mean of a block of cells =AVERAGE(C10:C15)
MIN Finds lowest value from a set of cells =MIN(K2:K20)
MAX Finds highest value from a set of cells =MAX(G10:G50)
SQRT Finds square root of a number stored in cell =SQRT(B8)
LOG Finds logarithm of a number in a cell =LOG(E4)
NOW() Display current system date/time in a cell =NOW()
COUNT Counts number of cells that contain numbers =COUNT(A1:A10)
only/specified number
IF Sets data in some cells depending on data in =IF(E6<50,0, E6*0.1)
other cells
Spreadsheet graphs
• Spreadsheet packages allow user to use
spreadsheet data to construct a graph.
• Hence all graph axes must be clearly labeled and
it must have a suitable title.
• User can specify maximum and minimum values
and a scale for a graph.
• Graphs are also called charts.
• User must choose type of chart best illustrates
the data.
Common types of graphs
• There are many different types of graphs/charts:
Line graph
Bar graph
Column graph
Pie chart
• Components of a graph:
Plot area
Title
Axis
Legend/Key
Advantages of graphs
• Gives a visual aid to understand trend in data.
• Allows data to be analyzed in a glance.
• Makes it easier to compare different sets of
data.
• Provides a neat presentation of data.
• Makes data easier to read and understand.
Spreadsheet facilities/ skills check list
Create and save workbooks and worksheets.
Change column width and row height as needed.
Insert/delete rows/columns as needed.
Move and copy blocks of cells.
Insert comments in significant cells and border worksheets.
Display numbers to a desired number of decimal places.
Enter and process data of different formats/types.
Use custom formula in calculations.
Use standard/built-in functions in calculations.
Replicating/copying formula to other cells/block of cells.
Perform what if calculations.
Use linking formula to construct graphs/pull data from different worksheets/workbooks.
Automate tasks using macros.
Construct different types of graphs/charts.
Format worksheet to make it professionally presentable.
Print worksheet with data and labels/formulae only/with or without grids/only part of a worksheet.
Use online help to perform different spreadsheet functions.
NB Be able to use above functions in MS Excel.
NB A spreadsheet with many columns must always be displayed and printed using landscape orientation.
Spreadsheets marking criteria
• There are three items assessed:
Production – has the candidate produced the
required worksheet/graph?
Accuracy – Is the worksheet/graph free from
accuracy errors/spelling and grammatical
errors?
Format/layout- Is the worksheet/graph
professionally presented?
AUTOMATIC
ARITHMETIC!
