MS EXCEL.ppt

for ICT subjects

1. MS Excel

2. 2
Intro to MS Excel
MicrosoftExcelisthefirstspreadsheet
applicationintheMicrosoftOfficeSuitethat
allowedtheusertodefinetheappearanceof
spreadsheets.
Spreadsheet
-atableofvaluesarrangedinrowsand
columnswhereeachvaluecanhavea
predefinedrelationshiptotheothervalues
thatsitintheirrespectivecell.

3. 3
Intro to MS Excel
formula-therelationshipbetweencells.
labels-thenamesofthecells.
ElectronicSpreadsheet
Atypeofanaccountingcomputersoftware
primarilyusedtoworkwithnumbersandtext
forperformingmathematicalcomputationsona
spreadsheet.
Itinvolvesthefollowing:

4. 4
Anelectronicspreadsheethasthe
followingpurpose:
1.Helporganizeinformation,likealphabetizing
alistofnamesororderingrecords,or
Intro to MS Excel
2.Calculateandanalyzeinformationusing
mathematicalformulas.

5. 5
Intro to MS Excel
Advantages:
Ithasintelligentcellrecomputation.
Excelallowslargenumbersofcalculationsto
becarriedoutsimultaneously.
Italsohasextensivegraphingcapabilities,
andenablesuserstoperformmailmerge.

6. 6
Workbook
-Thisisalsocalledaspreadsheetanda
uniquefilecreatedbyExcel.
-automaticallyshowsintheworkspacewhen
youopenMicrosoftExcel.
-eachworkbookcontainsthreeworksheets
(labeledSheet1,Sheet2,andSheet3).
MS Excel
-aworkbookmustcontainatleastone
worksheet.

7. 7
Worksheet-agridofcells,consistingof
65,536rowsby256columns.
SheetTabs-separateaworkbookintospecific
worksheets.
Navigationbuttons-allowyoutomoveto
anotherworksheetinanExcelworkbook
andisusedtodisplaythefirst,previous,
nextorlastworksheetsintheworkbook.
MS Excel

8. 8
Columnheadings-contains256columns
referencedbyalphabeticcharactersinthe
grayboxesthatrunacrosstheExcel
screenbeginningwithColumnAand
endingwithColumnIV.
Rowheadings-contains65,536 rows
referencedbynumbersappearingonthe
leftandthenrundowntheExcelscreen
startingfromfirstrow(row1)tothelast
row(row65536).
MS Excel

9. Chap 5: MS EXCEL 2003 9
MS Excel

10. 10
Namebox-showstheaddressofthecurrent
selectionoractivecell.
Formulabar-displaysinformationbeing
enteredasyoutype-inthecurrentoractive
cell.
Cells-littleboxesthatareformedfromthe
intersectionofcolumnsandrows.
Thecontentsofacellcanalsobe
editedintheFormulabar.
MS Excel

11. 11
Celladdress-namedesignatedtoeachcell
whichiscomprisedoftwoparts:
a)thecolumnletter;and
b)therownumber.
Activecell-referstothecellthatcanbeacted
uponorreceivesthedataorcommandyou
giveitwhichrevealsadarkborder.
Allothercellsrevealalightgray
border.
MS Excel

12. 12
Eachcellhasa
uniquecelladdress
(i.e.thecelladdressof
theselectedcellis
B3).
MS Excel
cellpointer-the
heavyordarkened
borderaroundthe
selectedcell.

13. 13
Differentspreadsheetinformationcanbe
enteredintoacellsuchastext,numbersor
mathematicalformulas.
Text-anyentrythatisnotanumberor
formula.
MS Excel
Numbers-valuesusedwhenmaking
calculations.
Formulas-mathematicalcalculations.

14. 14
EnteringDataintheCell:
Thiscanbedonebyclickingonthecell
whereyouwanttotypetheinformation.
Aninsertionpointappearsinthecellas
thedataistyped.
Thedatabeingtypedappearbothinthe
activecellorintheFormulabar.
MS Excel

15. 15
EditingInformationintheCell:
Method1:DirectCellEditing
Thiscanbedonebydouble-clicking
onthecellthatcontainstheinformationtobe
changed.
Method2:FormulaBarEditing
Thiscanbedonebysingleclickingon
thecellthatcontainstheinformationandedit
itintheformulabar.
MS Excel

16. 16
CreatingSimpleFormula(MDASOperation):
Step 1: Click the cell where the formula will be
defined.
Step 2:Type the equal sign(=) to let Excel
know a formula is being defined.
Step 3:Type the first number to be included in
the operation.
If it involves numeric value
contained in another cell, click the cell
in which the number is contained.
Using Formula

17. 17
CreatingSimpleFormula(MDASOperation):
Step4:Typethemathematicaloperator/s(*,
/,+,-)toletExcelknowthatan
operationistobeperformed.
Step5:Typethesecondnumbertobe
includedintheoperationthewayitis
doneinstep2especiallyifitinvolves
anumberinanothercell.
Step 6:Press Enterto complete the formula.
Using Formula

18. 18
CreatingComplexFormulas:
Simple formulas have one mathematical
operation.
Check Point!
Theorderofmathematicaloperationsis
veryimportantespeciallyindealingwith
complexformula.
Using Formula
Complex formulasinvolve more than one
mathematical operation.

19. 19
Order of operations inComplex Formula:
1.Operations enclosed in parenthesis.
Using Formula
2.Exponential calculations (to the power of).
3.Multiplication and division, whichever comes
first.
4.Addition and subtraction, whichever comes
first.

20. 20
Using Formula

21. 21
ApplyingFormulastootherCells:
Formulaareessentialelementinusing
Excelespeciallywhenyourdealingwitha
rangeofcellsinagivencolumnorrow.
Two ways to apply formula to other cells:
1.Copy and Paste Method
2.Fill Formula Method
Using Formula

22. 22
CopyandPasteFormulaMethod
Theprocesstocopyandpasteaformula
isidenticaltothatprocessusedtocopyand
pastetext.
Step 1:Select the cell that contains the
formula to be copied.
Step 2: Click the Copybutton.
Marching "ants" appear around
the copied cell(s).
Using Formula

23. 23
Copy and Paste FormulaMethod
Using Formula

24. 24
Copy and Paste FormulaMethod
Step 3:Select the cell where the copied
formula is to be pasted.
Step 4: Press the Enter key. The formula is
copied to the new location.
Using Formula

25. 25
Fill FormulaMethod
-allowsyoutocopyaformulaandfillitinto
manydifferentconsecutivecellsatthesame
time.
Fill Handle
Using Formula

26. 26
UsingtheFillHandle:
Step1:Clickonthecellthatcontainsthe
formulatobecopied.
Step2:Positionthemousepointeroverthe
fillhandle.
Step 3: Click and hold the left mouse button,
and then drag the contents to the cell
that's to receive the fill formula.
Step 4:Release the mouse button
Using Formula

27. 27
ConditionalformattingallowsExcelto
applyadefinedformattocellsthatmeet
specificcriteria.
Itsmaingoalistomakeimportantcells
standoutsoyoucanfindthemeasier.
Conditional Formatting
Theseformatmightincludeadifferent
backgroundcolor,fontcolororborder.

28. 28
Two Methods:
1.Cell Value Is
2.Formula Is
Syntax: =IF(Op1OperatorOp2,True,False)
Example: =IF(B9 > B8,True,False)
You could apply conditional formatting to a
row or column.
Conditional Formatting

29. 29
Cell Value Is
The simplest method is to have Excel
apply the conditional formatting if the cell
meets a certain criteria.
When using this method, you’re allowed up
tothree conditions.
Conditional Formatting

30. 30
Formula Is
Excelalsoallowsyoutouseformulasfor
conditionalformatting.
Benefits of using formulas:
2.Itisveryusefulindoingcomparative
analysisasyoucancolorcodeitemsthat
fallaboveorbelowcertainranges.
1.It can reference the values in another cell.
Conditional Formatting

31. 31
Autofilterisafeaturethatmakes
temporarilyhidingofdatainaspreadsheetvery
easyandallowsyoutofocusonspecific
spreadsheetentries.
1.Select Data-> Filter, then AutoFilter.
2.Click the drop-down arrow next to the
heading you would like to filter.
3. Click the drop-down arrow again and select
Allto display all of the original data.
Autofilter

32. 32
Sortinglistisacommonspreadsheet
taskthatallowsyoutoeasilyreorderyour
data.
1.Select Data -> Sort, a Sortdialog box will
appear.
2.Select the category to Sort by.
3.Select Ascendingto sort in alphabetical
order from A to Z or Descendingto reverse
the sorting.
Sorting List

33. 33
InExcel,youcaninsertaroworcolumn
anywhereyouneedit.
Rows and Columns
Anewrowisinsertedabovethecell(s)you
originallyselected.
Anewcolumnisinsertedtotheleftofthe
existingcolumn.
Bydefault,Excel'scolumnsare8.43
characterswide,buteachindividualcolumn
canbeenlargedto240characterswide.

34. 34
Rows and Columns
Youcanadjustcolumnwidthmanuallyor
useAutoFit.
ToaccessAutoFitchooseFormat>
Column>AutoFit>Selection.
Tomanuallyadjustthecolumnwidth,place
yourmousepointertotherightsideofthe
graycolumnheader.

35. 35
Rows and Columns
Themousepointerchangestothe
adjustmenttool(double-headedarrow).
Changingtherowheightisverymuchthe
samewithadjustingacolumnwidth.

36. 36
Rows and Columns
Drag the Adjustment toolleft or right to the
desired width and release the mouse button,
Double-clickthe column header border.
Twowaystomanuallyadjustthecolumnwidth:
Excel "AutoFits" the column, making the
entire column slightly larger than the largest
entry contained in it.

37. 37
Text Control
Text Controlallows you to control the
way Excel presents information in a cell.
To change Format cells-> Alignment->
then choose one of the types.
1.Wrapped Text
2.Shrink-to-Fit
3.Merge Cells
Three types:

38. 38
Text Control
WrappedText-wrapsthecontentsofacell
acrossseverallinesifit'stoolargethanthe
columnwidth.Itincreasestheheightofthe
cellaswell.
Shrink-to-Fit-shrinks the text so it fits into the
cell; the more text in the cell the smaller it
will appear in the cell.
Merge Cells-can be applied using the Merge
and Centerbutton on the standard toolbar.

39. 39
Text Orientation
TextOrientationisanothertypeofcell
alignmentintheFormatCellsdialogboxwhich
allowstexttobeoriented90degreesineither
directionupordown.

40. 40
Text Orientation
To Change Text Orientation:
Selectacellorcellrangethenchoose
FormatCellsfromthemenubar(Format
Cellsdialogboxopens).
Click the Alignmenttab.
Increase or decrease the number shown in
the Degreesfield or spin box.
Click the OKbutton.

41. 41
SUM-adds a range of cells together.
AVERAGE-calculates the average of a range of
cells.
COUNT-counts the number of chosen data in a
range of cells.
MAX-identifies the largest number in a range of
cells.
Commonly Used Functions

42. 42
MIN-identifies the smallest number in a range of
cells.
COUNTIF-countsthenumberofcellswithina
rangethatmeetthegivencondition.
ROUND–roundsanumbertoaspecifiednumber
ofdigits.
Commonly Used Functions
IF-checkswhetheracondition’smetandreturns
avalueifTrue,andanothervalueiffalse.

43. 43
The Chart Toolbar
Chartscanbecreatedinanumberofways
andthequickestwaytocreateandedityour
chartsistousetheChartToolbar.
ChartObjectsListBox-letsyouselect
differentpartsofachartforediting.

44. 44
Chart Types
Excelallowsyoutocreatemanydifferent
kindsofcharts.
Areachart-emphasizesthetrendofeach
valueovertime.
Italsoshowstherelationshipofpartsto
awhole.
Columnchart-usesverticalbarsorcolumns
todisplayvaluesoverdifferentcategories.
Theyareexcellentatshowing
variationsinvalueovertime.

45. 45
Chart Types
Bar chartis similar to a column chart except
these use horizontal instead of vertical bars.
Like the column chart, the bar chart
shows variations in value over time.
Line chartshows trends and variations in data
over time.
This type of chart displays a series of
points that are connected over time.

46. 46
Chart Types
Piechartdisplaysthecontributionofeach
valuetothetotal.
It’saveryeffectivewaytodisplay
informationinrepresentingdifferentpartsof
thewhole,orthepercentagesofatotal.
Other charts in Excel:
Doughnut Stock Bubble
XY (scatter) Radar Surface
Cylinder Cone Pyramid.

47. 47
Parts of a Chart
SourceData-therangeofcellsthatmakeup
achart.
Thechartisupdatedautomatically
whenevertheinformationinthesecells
change.
Title -the title of the chart.
Legend-thechartkey,whichidentifieseach
coloronthechartrepresents.

48. 48
Parts of a Chart
Axis-verticalandhorizontalpartsofachart.
Vertical axis-the Y axis,
Horizontal axis-the X axis.
Data Series-the actual charted values,
usually rows or columns of the source data.
ValueAxis-theaxisthatrepresentsthe
valuesorunitsofthesourcedata.
Category Axis-the axis identifying each data
series.

49. 49
End
Clicktoaddsubtitle