Introduction to Spreadsheets.ppt

Introduction to spreadsheets

1. SPREADSHEETS

2. XP
Objectives
•Understand the use of spreadsheets and Excel
•Learn the parts of the Excel window
•Scroll through a worksheet and navigate
between worksheets
•Create and save a workbook file
•Enter text, numbers, and dates into a
worksheet
•Resize, insert, and remove columns and rows
2

3. XP
Objectives(Cont’d)
•Select and move cell ranges
•Insert formulas and functions
•Insert, delete, move, and rename
worksheets
•Work with editing tools
•Preview and print a workbook
3

4. XP
Definition and History
•Aspreadsheetisaprogramthatletsusers
enterdatainatwo-dimensionalgridof
individualcells.
•Theyarecommonlyusedforstoringdataina
simpledatabaseandforfinancialand
numericalcalculations
4

5. XP
History of Spreadsheet software
•Oneofthefirstspreadsheetprogramswas
VisiCalc,releasedforApplecomputersin
1979.ithelpedtoshowhowpersonal
computerscouldbeusefulasbusinesstools
ratherthanjustforhobbyiststoplaywith.
•Laterpopularspreadsheetsincludetoday’s
MSExcelandGooglesheets,aswellasother
programspreviouslypopular,especially
1980sfavoriteLotus1-2-3
•Spreadsheetshaveconsistentlybeenoneof
themostpopulartypesofbusinesssoftware
5

6. XP
History of Spreadsheet software(Cont..)
•WhileSpreadsheetsoftwarehasgainednew
features,usersofoldertoolslikeVisiCalc
wouldquicklyrecognizethelayoutofa
modernspreadsheetsprogram
6

7. XP
Limitations of Spreadsheets
•Vulnerabletofraud:Thereisinherentlackof
controls,whichmakesitsoeasytoalter
eitherformulasorvalueswithoutbeing
detected.
•Susceptibletotrivialhumanerrorslike
missednegativesignsandmisalignedrows
•Obstructivetoregulatorycompliance:There
areanumberofregulationsthathavea
seriousimpactontheuseofspreadsheets.
E.g.EUDataProtection
7

8. XP
Limitations of Spreadsheets(Cont.)
•Unfitforagilebusinesspractices:We’renow
inanagewhenmajorchangesareshaping
andreshapingthebusinesslandscape.Since
spreadsheetsarenormallyhighly
personalizeduserdevelopedapplications
whenit’stimeforanewpersontotakeover
aspartoflargescalebusinesschange,the
newcomermayhavetostartfromscratch.
8

9. XP
Limitations of Spreadsheets(Cont.)
•Notdesignedforcollaborativework.Plans,
forecasts,budgetsandreportsrequire
informationfromdifferentindividuals
belongingtodifferentdepartments,making
thefinaldocumentsaresultofmultiple
exchangesofdataandideas.Ifindividuals
areseparatedbylongdistances,theonly
waytoexchangedatainspreadsheetswillbe
viaemail.Suchmethodofexchangeis
susceptibletoduplicateandevenerroneous
data
9

10. XP
Limitations of Spreadsheets(Cont..)
•Incapableofsupportingquickdecision
making:inaspreadsheet-basedenvironment,
extractingdatafromdifferentdepartments,
consolidatingthemandsummarizingthe
informationtoaidmakingsounddecisions
canbeverytimeconsuming.
•Scalespoorly:Asanorganizationgrows,
spreadsheetdatagetmoredistributed;
subsequentlycompoundingtheissues
outlinedabove.Spreadsheetsaretherefore
notsuitableforlargeorganizations
10

11. XP
Features of Spreadsheets
•Built-in-supportforcomplexmathematicalcalculations
andformulas
•Abilitytogenerategraphsfromdata
•Abilitytocustomizewhattypesofdataarestoredin
whichrowsandcolumns
•Abilitytoadjustcolumnwidthandrowheights,hide
rowsandcolumnsandfreezepanes
•Abilitytocreate,edit,saveandretrieveworksheets
•Havedataformatting:bothtextandnumericdatacan
beformattedusingpredefinedformats.E.g.decimal,
scientificinteger
•Havedatasortingandfilteringcapability
11

12. XPIntroduction to Spreadsheets
•Aspreadsheetisessentiallyaledgersheetthatlets
youenter,editandmanipulatenumericdata.
•Oftenusedinbusinessforbudgeting,
inventorymanagement,anddecision
making
•Twotypesofspreadsheetsnamely;
•Manualspreadsheets
•Electronicspreadsheets
12

13. XP
•Amanualspreadsheetisusedbybookkeepersasa
ledgerbookwithmanysheetsofpapersdividedinto
rowsandcolumnsonwhichvariousamountsofmoney
areenteredmanuallyusingapenorapencil
•Electronicspreadsheetontheotherhandisprepared
usingacomputerprogramthatenablestheuserto
entervaluesinrowsandcolumnssimilartotheonesof
amanualspreadsheetandtomanipulatethem
mathematicallyusingaformula
13
Types of Spreadsheets

14. XPExamples of spreadsheet software
MS Excel
VisiCalc
Lotus 1-2-3
Etc
14
Introduction to MS Excel
MicrosoftExcelisacomputerprogram
usedtoenter,analyze,andpresent
quantitativedata

15. XP
Loading the application and looking at
the features
15

16. XPDescription of Excel window elements
16

17. XP
17

18. XPTerms Associated with Spreadsheets(Excel)
•Cell:Referstotheintersectionbetweenrowsand
columns.
•Range:Thisisagroupofadjacentcells.
•Worksheet:Referstoagridofrowsandcolumns,in
whichyoucanenter,editandviewdata
•Workbook:Referstoacollectionofvarious
worksheets
•CellAddress:Referstoreferencetoaparticularcell.
Itistheformatofcolumnnumber,rownumber.E.g
B20
18

19. XP
Terms Associated with Excel(Cont.)
•RangeAddress:Referstoarangereference
whichhasaformatoftop-leftcelladdress:
bottom-rightcelladdress.E.g.C10:G15,etc
•HomeCell:Referstothefirstcellina
spreadsheet.IthastheaddressA1.
•CellPointer:Thickdarkrectanglethatindicates
thecurrentlyselectedcell
•ActiveCell:Referstothecellthatisindicatedby
thecellpointer.Orthecellthattheuseris
currentlyworkingin.
19

20. XP
Terms Associated with Excel(Cont.)
•NameBox:Containstheaddressofthe
activecellinaparticularworksheet
•FormulaBar:Usedtoenteroreditdatain
worksheetcells.
•WorksheetArea:Mainworkingareaina
spreadsheetinwhichdataisenteredor
viewed.
•SheetTabs:Theyindicateworksheets
presentinthespreadsheet.Theyareusually
labeledSheet1,sheet2,sheet3.
20

21. XP
Navigating a Worksheet
21

22. XPOperations with Excel
•Makingentries,editinganddeletingentries
•Insertingnewsheets,renaming,movingand
deletingworksheets
•Inserting,deletingrowsandcolumns
•Editingworksheets(select,copyandpaste,cut
andpaste)
•Formatting(number,alignment,font,borders),
autoformatting
•Resizing,insertingandremovingcolumnsand
rows
22

23. XP
Operations with Excel(Cont..)
•Creating and saving a work book file
•Previewing and printing a worksheet
23

24. XP
Performing calculations in Excel
•OperatorscanbeusedtomanipulatedatainExcel.
Excelsupportsmanytypesofoperatorswhich
include:
•Arithmeticoperators
•Logicaloperators
•Comparisonoperators
•Etc.
24

25. XPArithmetic Operators
25

26. XPLogical Operators
Operator Description
AND Local and; returns true if
bothexpressions are true
OR Logical or; returns true if
either of the expressions is
true
NOT Logical not; returns false if
the expression is trueand
true if the expression is false
() Parenthesesused for
grouping
TRUE Returns logical value True
FALSE Returns logicalvalue False
26

27. XP
Comparison Operators
Operator Description
< Less than
> Greater than
= Equal to
<> Not Equal to
<= Less or equal to
>= Greater or equal to
27

28. XP
Entering Text, Numbers, and Dates
in Cells
•Theformulabardisplaysthecontentoftheactive
cell
•Textdataisacombinationofletters,numbers,and
somesymbols
•Numberdataisanynumericalvaluethatcanbe
usedinamathematicalcalculation
•Dateandtimedataarecommonlyrecognized
formatsfordateandtimevalues
28

29. XP
Entering a Formula
•Aformulaisanexpressionthatreturnsavalue
•Aformulaiswrittenusingoperatorsthatcombine
differentvalues,returningasinglevaluethatis
thendisplayedinthecell
•Themostcommonlyusedoperatorsarearithmetic
operators
•Theorderofprecedenceisasetofpredefined
rulesusedtodeterminethesequenceinwhich
operatorsareappliedinacalculation
29

30. XP
Entering a Formula
•Clickthecellinwhichyouwantthe
formularesultstoappear
•Type=andanexpressionthatcalculates
avalueusingcellreferencesand
arithmeticoperators
•PresstheEnterkeyorpresstheTabkey
tocompletetheformula
30

31. XP
Introducing Functions
•Afunctionisanamedoperationthatreturnsa
value
•Forexample,toaddthevaluesintherangeA1:A10,
youcouldenterthefollowinglongformula:
=A1+A2+A3+A4+A5+A6+A7+A8+A9+A10
Or,youcouldusetheSUMfunctiontoaccomplish
thesamething:
=SUM(A1:A10)
31

32. XP
•Average():Calculatesandreturnsthe
averageofnumericvaluesintherangeof
cells.E.g.
=Average(A1:A10)
•Max():Returnsthelargesvalueintherange.
E.g.=Max(A1:A10)
•Min():Returnsthesmallestvalueinthe
range.E.g.=Min(A1:A10)
•Count():Countsthenumberofcellsthat
containnumbersinthespecifiedrange.E.g.
=Count(A1:A10)
32
Functions (Cont..)

33. XP
•Today():Returnsthecurrentdate.
E.g.=Today()
•Now():Returnstheserialnumberofthethe
currenttimeanddate.
E.g.=Now()
•Iffunction():Itisalogicalfunctionusedto
determinewhetheraconditionistrueorfalse.
Onevalueisreturnediftheconditionistrueand
adifferentvalueisreturnediftheconditionis
false
33
Functions (Cont..)

34. XPExercise
Calculate the Amount column
Item DescriptionCost QuantityAmount
1. Yogurt 800 6
2. Cakes 600 7
3. Ice Cream2000 3
4. Chocolate2400 5
5. Crisps 1000 4
34
MOLLY CORNER

35. XPGiven the following information
Name Basic
Pay
HAGrossNSSF Net Pay
Samuel. G400000
Sophie. B750000
Angela. K950000
James. H800000
Margaret. Y4800000
Jack. E 450000
35
Entertheaboveinformationinthespreadsheet
programandaddborders

36. XP
36
SupposeHousingAllowance(HA)isaquarterof
basicpay,NSSFis5%oftheGrosspayand
PAYEiscomputedbasedonthefollowing
information:
15%ofGrosspayifGrosspayislessthan500000
25%ofGrosspayifGrosspayismorethan
500000.
Required:
InsertaPAYEcolumnbetweenNSSFandNet
pay
ComputetheNSSF,PAYE(useifstatement)
GrosspayandNetpayforeachemployeeinthe
abovetable
FormatdatausingUGXCurrency

37. XP
Examples
Names Scores Comment
Babangida69
Komboti 45
Nalumansi90
Mabirizi 48
Okalebo 80
Nakasi 36
37
UsingtheIFstatement,FillintheComment
Columngiventhatifthescoreislessthan
50FAILiswrittenagainstastudentand
PASSisdisplayedotherwise