A Guide to Microsoft Excel 2013 for Scientists and Engineers PDF
Preview A Guide to Microsoft Excel 2013 for Scientists and Engineers
® 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 [email protected] 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.
The list of books you might like
