MS EXCEL.ppt

Microsoft Excel

1. MS Excel

2. Chap 5: MS EXCEL 2003 2
Intro to MS Excel 2003
MicrosoftExcelisthefirstspreadsheet
applicationintheMicrosoftOfficeSuitethat
allowedtheusertodefinetheappearanceof
spreadsheets.
Spreadsheet
-atableofvaluesarrangedinrowsand
columnswhereeachvaluecanhavea
predefinedrelationshiptotheothervalues
thatsitintheirrespectivecell.

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

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

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

6. Chap 5: MS EXCEL 2003 6
Workbook
-Thisisalsocalledaspreadsheetanda
uniquefilecreatedbyExcel.
-automaticallyshowsintheworkspacewhen
youopenMicrosoftExcel.
-eachworkbookcontainsthreeworksheets
(labeledSheet1,Sheet2,andSheet3).
MS Excel 2003
-aworkbookmustcontainatleastone
worksheet.

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

8. Chap 5: MS EXCEL 2003 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. Chap 5: MS EXCEL 2003 10
Namebox-showstheaddressofthecurrent
selectionoractivecell.
Formulabar-displaysinformationbeing
enteredasyoutype-inthecurrentoractive
cell.
Cells-littleboxesthatareformedfromthe
intersectionofcolumnsandrows.
Thecontentsofacellcanalsobe
editedintheFormulabar.
MS Excel

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

12. Chap 5: MS EXCEL 2003 12
Eachcellhasa
uniquecelladdress
(i.e.thecelladdressof
theselectedcellis
B3).
MS Excel
cellpointer-the
heavyordarkened
borderaroundthe
selectedcell.

13. Chap 5: MS EXCEL 2003 13
Differentspreadsheetinformationcanbe
enteredintoacellsuchastext,numbersor
mathematicalformulas.
Text-anyentrythatisnotanumberor
formula.
MS Excel
Numbers-valuesusedwhenmaking
calculations.
Formulas-mathematicalcalculations.

14. Chap 5: MS EXCEL 2003 14
EnteringDataintheCell:
Thiscanbedonebyclickingonthecell
whereyouwanttotypetheinformation.
Aninsertionpointappearsinthecellas
thedataistyped.
Thedatabeingtypedappearbothinthe
activecellorintheFormulabar.
MS Excel

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

16. Chap 5: MS EXCEL 2003 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. Chap 5: MS EXCEL 2003 17
CreatingSimpleFormula(MDASOperation):
Step4:Typethemathematicaloperator/s(*,
/,+,-)toletExcelknowthatan
operationistobeperformed.
Step5:Typethesecondnumbertobe
includedintheoperationthewayitis
doneinstep2especiallyifitinvolves
anumberinanothercell.
Step 6:Press Enterto complete the formula.
Using Formula

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

19. Chap 5: MS EXCEL 2003 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. Chap 5: MS EXCEL 2003 20
Using Formula

21. Chap 5: MS EXCEL 2003 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. Chap 5: MS EXCEL 2003 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. Chap 5: MS EXCEL 2003 23
Copy and Paste FormulaMethod
Using Formula

24. Chap 5: MS EXCEL 2003 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. Chap 5: MS EXCEL 2003 25
Fill FormulaMethod
-allowsyoutocopyaformulaandfillitinto
manydifferentconsecutivecellsatthesame
time.
Fill Handle
Using Formula

26. Chap 5: MS EXCEL 2003 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. Chap 5: MS EXCEL 2003 27
ConditionalformattingallowsExcelto
applyadefinedformattocellsthatmeet
specificcriteria.
Itsmaingoalistomakeimportantcells
standoutsoyoucanfindthemeasier.
Conditional Formatting
Theseformatmightincludeadifferent
backgroundcolor,fontcolororborder.

28. Chap 5: MS EXCEL 2003 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. Chap 5: MS EXCEL 2003 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. Chap 5: MS EXCEL 2003 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. Chap 5: MS EXCEL 2003 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. Chap 5: MS EXCEL 2003 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. Chap 5: MS EXCEL 2003 33
InExcel,youcaninsertaroworcolumn
anywhereyouneedit.
Rows and Columns
Anewrowisinsertedabovethecell(s)you
originallyselected.
Anewcolumnisinsertedtotheleftofthe
existingcolumn.
Bydefault,Excel'scolumnsare8.43
characterswide,buteachindividualcolumn
canbeenlargedto240characterswide.

34. Chap 5: MS EXCEL 2003 34
Rows and Columns
Youcanadjustcolumnwidthmanuallyor
useAutoFit.
ToaccessAutoFitchooseFormat>
Column>AutoFit>Selection.
Tomanuallyadjustthecolumnwidth,place
yourmousepointertotherightsideofthe
graycolumnheader.

35. Chap 5: MS EXCEL 2003 35
Rows and Columns
Themousepointerchangestothe
adjustmenttool(double-headedarrow).
Changingtherowheightisverymuchthe
samewithadjustingacolumnwidth.

36. Chap 5: MS EXCEL 2003 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. Chap 5: MS EXCEL 2003 37
Text Control
Text Controlallows you to control the
way Excel 2003 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. Chap 5: MS EXCEL 2003 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. Chap 5: MS EXCEL 2003 39
Text Orientation
TextOrientationisanothertypeofcell
alignmentintheFormatCellsdialogboxwhich
allowstexttobeoriented90degreesineither
directionupordown.

40. Chap 5: MS EXCEL 2003 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. Chap 5: MS EXCEL 2003 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. Chap 5: MS EXCEL 2003 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. Chap 5: MS EXCEL 2003 43
The Chart Toolbar
Chartscanbecreatedinanumberofways
andthequickestwaytocreateandedityour
chartsistousetheChartToolbar.
ChartObjectsListBox-letsyouselect
differentpartsofachartforediting.

44. Chap 5: MS EXCEL 2003 44
Chart Types
Excel2003allowsyoutocreatemany
differentkindsofcharts.
Areachart-emphasizesthetrendofeach
valueovertime.
Italsoshowstherelationshipofpartsto
awhole.
Columnchart-usesverticalbarsorcolumns
todisplayvaluesoverdifferentcategories.
Theyareexcellentatshowing
variationsinvalueovertime.

45. Chap 5: MS EXCEL 2003 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. Chap 5: MS EXCEL 2003 46
Chart Types
Piechartdisplaysthecontributionofeach
valuetothetotal.
It’saveryeffectivewaytodisplay
informationinrepresentingdifferentpartsof
thewhole,orthepercentagesofatotal.
Other charts in Excel 2003:
Doughnut Stock Bubble
XY (scatter) Radar Surface
Cylinder Cone Pyramid.

47. Chap 5: MS EXCEL 2003 47
Parts of a Chart
SourceData-therangeofcellsthatmakeup
achart.
Thechartisupdatedautomatically
whenevertheinformationinthesecells
change.
Title -the title of the chart.
Legend-thechartkey,whichidentifieseach
coloronthechartrepresents.

48. Chap 5: MS EXCEL 2003 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. Chap 5: MS EXCEL 2003 49
End
Clicktoaddsubtitle