Table Of Content®
A Guide to Microsoft Excel 2013
for Scientists and Engineers
®
A Guide to Microsoft Excel 2013
for Scientists and Engineers
Bernard V. Liengme
St. Francis Xavier University, Canada
AMSTERDAM (cid:129) BOSTON (cid:129) HEIDELBERG (cid:129) LONDON
NEW YORK (cid:129) OXFORD (cid:129) PARIS (cid:129) SAN DIEGO
SAN FRANCISCO (cid:129) SINGAPORE (cid:129) SYDNEY (cid:129) TOKYO
Academic Press is an imprint of Elsevier
AcademicPressisanimprintofElsevier
125,LondonWall,EC2Y5AS
525BStreet,Suite1800,SanDiego,CA92101-4495,USA
225WymanStreet,Waltham,MA02451,USA
TheBoulevard,LangfordLane,Kidlington,OxfordOX51GB,UK
Copyright#2016ElsevierLtd.Allrightsreserved.
Nopartofthispublicationmaybereproducedortransmittedinanyformorbyanymeans,electronicormechanical,
includingphotocopying,recording,oranyinformationstorageandretrievalsystem,withoutpermissionin
writingfromthepublisher.Detailsonhowtoseekpermission,furtherinformationaboutthePublisher’s
permissionspoliciesandourarrangementswithorganizationssuchastheCopyrightClearanceCenterandthe
CopyrightLicensingAgency,canbefoundatourwebsite:www.elsevier.com/permissions.
ThisbookandtheindividualcontributionscontainedinitareprotectedundercopyrightbythePublisher(otherthan
asmaybenotedherein).
Notices
Knowledgeandbestpracticeinthisfieldareconstantlychanging.Asnewresearchandexperiencebroadenour
understanding,changesinresearchmethods,professionalpractices,ormedicaltreatmentmaybecomenecessary.
Practitionersandresearchersmustalwaysrelyontheirownexperienceandknowledgeinevaluatingandusingany
information,methods,compounds,orexperimentsdescribedherein.Inusingsuchinformationormethodsthey
shouldbemindfuloftheirownsafetyandthesafetyofothers,includingpartiesforwhomtheyhaveaprofessional
responsibility.
Tothefullestextentofthelaw,neitherthePublishernortheauthors,contributors,oreditors,assumeany
liabilityforanyinjuryand/ordamagetopersonsorpropertyasamatterofproductsliability,negligenceorotherwise,
orfromanyuseoroperationofanymethods,products,instructions,orideascontainedinthematerialherein.
BritishLibraryCataloguinginPublicationData
AcataloguerecordforthisbookisavailablefromtheBritishLibrary
LibraryofCongressCataloging-in-PublicationData
AcatalogrecordforthisbookisavailablefromtheLibraryofCongress
ForinformationonallAcademicPresspublications
visitourwebsiteathttp://store.elsevier.com
ISBN:978-0-12-802817-9
Preface
Thisbookisforpeopleintechnicalfields,studentsandprofessionalsalike.Itsaimistoshowthe
®
usefulnessofMicrosoft Excelinsolvingawiderangeofnumericalproblems.Exceldoesnot
compete with the major league symbolic mathematical environments such as Mathematica,
Mathcad, Maple, and the like. Rather it complements them. Excel is more readily available
andiseasiertolearn.Furthermore,itgenerallyhasbettergraphingfeaturesandwaysofhan-
dlinglargedatasets.
Theexampleshavebeentakenfromarangeofdisciplinesbutrequirenospecializedknowledge,
sothereaderisinvitedtotrythemall.Donotbeputoffbyanexercisethatisnotinyourareaof
interest.EachexerciseisdesignedtointroduceandexplainanExcelfeature.Thetwomodeling
chapterswillhelpyoulearnhowtodevelopworksheetsforavarietyofproblems.
Thisisverymuchapracticalbookdesignedtoshowhowtogetresults.Theproblemsetsatthe
endsofthechaptersarepartofthelearningprocessandshouldbeattempted.Manyoftheques-
tions are answered in the last chapter. The Guide issuitablefor use as either a textbook ina
courseonscientificcomputerapplications,asupplementarytextinanumericalmethodscourse,
oraself-studybook.ProfessionalsmayfindExcelusefultosolveone-offproblemsratherthan
writinganddebuggingaprogram,orforprototypinganddebuggingcomplexprograms.Afew
topicsarenotcoveredbytheGuide,suchasdatabasefunctionsandmakingpresentationwork-
sheets.ThesearefullycoveredinExcelbookstargetedatthebusinesscommunity,andthetech-
niquesareapplicabletoanyfield.
Iwasagreeablysurprisedbythewarmreceptiongiventothefirstandsubsequenteditionsofthe
Guide.Iamgratefulforthemanye-mailedcommentsandsuggestionsfromreadersandaca-
demics. This edition has involved a major rewrite since Excel 2013 has several features that
differfromearlierversions.Theopportunityhasbeentakentoaddnewexercisesandproblems.
I wish again to thank David Ellert, JohnQuinn, and Robertvan den Hoogen for theirearlier
assistance.IamhonoredthatMicrosoftawardedmetheMostValuableProfessionalinExcel
in2014fortheeighthconsecutiveyear.MyfinalthanksgotomywifePaulineforherencour-
agementandwordskills;withouther,thisbookwouldneverhaveseenthelightofday.How-
ever,Iclaimresponsibilityforallerrorsandtypos.
Iwelcomee-mailedcommentsandcorrections,andwilltrytorespondtothemassoonasIcan.
Please check my web site and the Guide’s companion web site http://booksite.elsevier.com/
9780128028179/forsupplementarymaterial.
Ihopeyouenjoylearningto“excel.”
BernardV.Liengme
bliengme@stfx.ca
http:/people.stfx.ca/bliengme
xi
xii Preface
CONVENTIONS USED IN THIS BOOK
Informationboxesinthemarginsareusedtoconveyadditionalinformation,tips,shortcuts,and
thelike.
Adistinctivefontisusedfordatathattheuserisexpectedtotype.Thisavoidstheproblemsof
usingquotes.Forexample:IncellA1,enterthetextResistorCodes.Italicsareusedfornewterms,to
highlightExcelcommands,foremphasis,andtoavoidtheconfusionsometimesassociatedwith
quotationmarks.Nonprintingkeysareshownwithagraphicalfont.Forexample,ratherthan
asking the reader to press the Control and Home keys, we use text such as: Press . When
two keys are shown separated by +, the user must hold down the first key while tapping
thesecond.
Anasteriskagainstaproblemnumberattheendofachapterindicatesthatasolutionisgivenat
theendofthebook.Excelfilesforsomeansweredproblemsandadditionalfilesmaybefound
onthecompanionwebsite:http://booksite.elsevier.com/9780128028179/.
1
Chapter
(VARIABLE ERROR - unrecognised syntax)dotfd"
Welcome to Microsoft® Excel 2013
CHAPTERCONTENTS
Exercise1: Customizing theQAT 5
Exercise2: Customizing theRibbonControl6
The Worksheet 6
Excel 2013Specifications and Limits 9
Compatibility with Other Versions9
Exercise3: TheStatus Bar 9
WhenMicrosoftExcelisstarted,youarepresentedwithawindowsimilarto
that in Figure 1.1. From there, you may (i) select from the left panel a
recently opened workbook, (ii) click on Open Other Workbooks, or (iii)
click on the icon Blank workbook to start a new project. Note that while
in Word, we speak of a document, in Excel, we use the term workbook.
Ineithercase,wearereferringtoafile.Inthischapter,weshallnotexplore
usingSkyDrive(nowrenamedbyMicrosofttoOneDrive)sowecanignore
theSigninoptioninthetoprightcorner.
Whenweopenanewworkbook,wehaveawindowshowingtheExcelinter-
face. Figure 1.1 is a screen capture from the author’s computer with the
Excelwindow“restoreddown”tooccupyabouthalfofthemonitorscreen.
TheExcelwindowonyourcomputermaydifferslightlydependingonyour
monitorsizeandresolution.
Itishelpfultoknowthecorrectnameforthevariouspartsofthewindow.
ThismakesusingtheHelpfacilitymoreproductiveandaidsinconversing
withotherusers.Asanewtermisintroduced,itisdisplayedinitalics,and
thereadershouldtrytorememberthemeaningofsuchterms.
Itisrecommendedthatyoureadthischapterwhileseatedatthecomputer
andexperimentasyoureadit.Rememberthatpressingthe keywillback
yououtofanactionyoudonotwishtopursue.
AGuidetoMicrosoftExcel2013forScientistsandEngineers
#2016ElsevierLtd.Allrightsreserved. 1
CHAPTER 1 Welcome to Microsoft Excel 2013
2
n FIGURE1.1
Title bar: This is at the very top of the window. To the left is the Quick
Access Toolbar (QAT), which is described below. In the center, we have
the name of the currently opened file together with the word Excel. To
therightareabuttontoactivatetheHelpfacility,abuttontocontrolhow
the Ribbon is displayed, and the three controls to minimize, restore, and
closetheExcelwindow.
Quick Access Toolbar (QAT): When Excel 2013 is first installed, the
QATholdsthecommandsSave,Undo,andRedo.However,itmaybecus-
tomized to hold others. Furthermore, one can change the location of the
QAT from above the ribbon to below the ribbon. Click on the launcher
button at the far right of the QAT to open the QAT customization
dialog box.
Asyouworkthroughthischapter,youwillbeaskedtosaveExcelfiles.Itis
strongly recommended that you create a separate folder (perhaps called
WarningregardingUndo:Excel
ExcelPractice)inwhichtokeepthese.Thefirst toolontheQAT,dis-
keepsasingleundostack.This
meansthatifyouissueanundo playing an icon of a floppy disk (something no one uses anymore!), will
command,youmayundo opentheFileExplorerwhereyoucanmakefoldersandsavefiles.
changesmadetoworksheets Ribbon:TheRibbonstretchesacrossthewindowunderthetitlebar.Itcon-
otherthanthecurrentlyactive sistsofanumberoftabs(File,Home,Insert,PageLayout,etc.).TheRibbon
one.Ifmorethanone
inFigure1.2hastheHometabselected.Theappearanceofatabwillchange
workbookisopen,youmay
withtheamountofspaceallocatedtotheExcelwindow.Eachtab,otherthan
evenundoanactioninanother
File,containscommandsdisplayedingroups.Acommandisactivatedby
workbook.
clicking on its icon. In Figure 1.2, the Home tab is open—note the box
Welcome to Microsoft Excel 2013 3
n FIGURE1.2
aroundHome.TheHometabholdsmainlyformattingcommands.Usethe
mousetoopenanothertabbyclickingit.Wewilllearninalaterchapterhow
toaddtheDevelopertabtotheRibbon.Additionaltabs(contextualtabs)get
displayed when you are performing certain operations; for example, the
Chartstabappearswhenyouareworkingonachart.Othertabsmayappear
afteryouinstallcertainsoftware.
Somegroupshavealaunchbutton ontheirfarrightandsomecommand
iconshaveasimilarbutton .Ineachcase,clickingononeofthesebuttons
expands the choice of commands available to the user. We shall discuss
theseonanas-neededbasis.
Filetab:Thistab(theonlyonetohaveacolor)givestheuseraccesstothe
so-calledbackstagetodothingslikeopen,save,orprintafile.Italsogives
usaccesstotheOptionsdialogboxwherewecancustomizecertainExcel
features.Wewilllookatthisinlaterchapters.
Title bar tools: To the far right of the Title Bar, we have five icons
.
Helpfacility:ClickingthefirsticonopenstheMicrosoftExcelHelp
dialogbox.Bydefault,thisconnectsyoutotheonlinehelpfacilityatthe
MicrosoftExcel2013site.Unlessyouareapoweruser,itisadvisedthat
youskipoveranyarticleinHelpthathasthetermDAX(DataAnalysis
Expressions)initstitle.
CHAPTER 1 Welcome to Microsoft Excel 2013
4
RibbonControl:Bydefault,theRibbondisplaystabs,theirgroups,and
commands.TheRibbonControltool givesustheoptionsofbothtabs
andcommands,showingonlythetaborhavingtheRibbonautohide.
Thesecondtwooptionsareusefulwhentheuserneedstoseemoreofthe
workingareaofthewindow.
Minimize,Maximize,andClose:Thelastthreebuttonsarefamiliarto
allusersofMicrosoftproductsandneednofurtherexplanation.
FormulaBarandNameBox:JustundertheRibbonistheFormulaBarwith
theNameBoxtotheleft.InFigure1.2,theNameBoxisdisplayingE6.You
willnoticethatboththeEcolumnandthe6rowheadingsarehighlightedand
thatthecellattheintersectionofthiscolumnandrowispickedoutbyaborder.
WecallE6theactivecell,andwesaythattheNameBoxdisplaysthereference
(oraddress)oftheactivecell.Whentheactivecellcontainsaliteral(textor
number),theFormulaBaralsodisplaysthesamething,butwhenthecellholds
aformula,thentheFormulaBardisplaystheactualformulawhilethecellgen-
erallydisplaystheresultofthatformula.Quickexperiment:TypeB4inthe
NameBoxandpress ;notehowthistakesyoutocellB4.
Worksheet window: The worksheet window occupies most of the Excel
space. A workbook (i.e., a single Excel file) may contain worksheets and
chartsheets(collectivelycalledsheets);wewillconcentrateonworksheets
for now. A worksheet is divided into rows (horizontally) and columns
(vertically);theintersectionofarowandacolumniscalledacell.
Sheettabs:Belowtheworksheetwindow,wehavetoolstonavigatefrom
sheettosheetandtoscrollasheethorizontally.Bydefault,Excel2013opens
a new workbook with one worksheet; this number can be changed in the
Note:Itisbecomingcommon
totalkabouttabswhen Optionssetting.Totheleftofthefirstsheet tabarearrowsfornavigat-
worksheetsaremeant.Thisis
ingfromsheettosheet;butmerelyclickingasheettabisthemostrapidway.
verypoorpracticesinceitcan
Totherightofthelastsheettabisatooltoinsertanewworksheet.Tothe
causeconfusionandwillnot
rightofthesheettabsisthehorizontalscrolltool;theverticalscrolltoolison
benefitausersearchingin
therightsideoftheworksheet.Wewillseelaterhowtorenamesheets.If
Help.
yourmousehasawheel,youcanuseittoscrollupanddownaworksheet.
Statusbar:AttheverybottomoftheExcelwindow,wehavethestatusbar.
To the left is the mode indicator. When you move to a cell, this displays
READY; when you start typing, it becomes ENTER; if you double click a
cell (or press the F2 key), it becomes EDIT. Other status conditions like
POINTINGandCopy/Pastewillbediscussedlater.Wewillignorethesec-
ondtool(macrorecorder)fornow.Totheright,justbeforetheZoomtool,
wehaveWorkbookViewsbuttonsthatletusdisplaytheworksheetindifferent
ways—Normal,PageBreakPreview,PageLayout,andCustomViews(more
onthistopiclater).Finally,thereisZoomtoolthatenlarges/reducesthedis-
play.Youcanalsochangethemagnificationoftheworksheetbyrotatingthe
mousewheelwhileholdingdownthe key.
Exercise 1: Customizing the QAT 5
IfweexperimentwiththeWorkbookViewsbuttons,wemaynoticethatthe
worksheetgetsverticalandhorizontaldottedlines.Theseshowhowmuch
willfitonaprintedpage.Right-clickingthestatusbarbringsupadialogbox
thatallowsyoutocustomizethestatusbar.Wewillshowmorefeaturesof
thestatusbarinExercise3.
EXERCISE 1: CUSTOMIZING THE QAT
AnyoftheExcelcommandscanbereachedbyopeningtheappropriatetab
andlocatingthecommandwithinoneofthetabgroups.Ifthereisanoper-
ationthatyouperformfrequently,itisconvenienttobeabletoaccessitfrom
the QAT, which explains its name. As a demonstration, we will add the
OpencommandtotheQAT:
a. StartExcelandletthemousepointerhoverovertheQATlaunchbutton
,whichisalwaysthelastitemontheQAT.Ascreentipboxwillopen
withthetextCustomizeQuickAccessToolbar.
b. Nowclickonthelaunchbuttontoopenthedialogboxasshownin
Figure1.3.Onthisdialogbox,weseethemorecommonlyneeded
commands.ToaddoneofthecommonitemstotheQAT,justclickonit
tobringupacheckmark.Correspondingly,clickonanitemwithacheck
marktoremoveit.Thedialogboxclosesimmediatelysoitmustbe
reopenedtomakefurtherselections.
c. Ifthecommandyouneedisnotshown,thenclickonMore
Commands...tobringupaseconddialogbox(Figure1.4).Toadda
Note:IfaPrintcommandis
commandtotheQAT,selectanitemintheleftpanelandclicktheAdd
neededontheQAT,itis
button.Toremoveacommand,clickitintherightpanelandclickthe
recommendedthatoneuses
Removebutton.LocatetheCopycommandandaddittotheQAT.Close
PrintPreviewandPrintrather
thedialogboxbyclickingtheOKbutton(orCancelbuttontocorrecta thanQuickPrint.Thislessens
mistake). theriskofwastingpaperat
d. ThereislittlemeritinhavingtheCopycommandontheQATsincethere homeormistakenlyprinting
isaveryconvenientshortcut( +C)forthispurpose.Rightclickonthe confidentialmaterialinan
officesettingenvironment.
CopycommandontheQAT(itlooksliketwosheetsofpaper)anduse
theRemovecommandinthepop-upmenu.
e. Itissometimessaid,tongueincheek,thattherearealwaysthreewaysof
doingthesamethinginExcel!Todemonstratethatthisisnottoogreat
Note:Theprocedureabove
anexaggeration,opentheFiletab,ontheleftside,andlocateandclick
showshowtoaddany
onOptions.Thisopensadialogbox;clickonQATintheleftpanel.This
commandtotheQAT,butthere
againbringsustothedialogboxshowninFigure1.4.Closethedialog
isamuchsimplermethodfor
boxbyclickingtheCancelbutton. commandsthatarealreadyon
theRibbon.Justrightclickthe
AsyoubecomemorefamiliarwithExcel,wewillcondensethesecondand
commandiconandselectAdd
thirdsentencesintheabovetothesimpleinstruction:useFile/Options/ toQuickAccessToolbar.
QuickAccessToolbar.
