EXCEL PART 2: FORMULAS, FUNCTIONS, AND CHARTS

EXCEL PART 2: FORMULAS,FUNCTIONS, AND CHARTSPHONE:504-838-1144IT Training TeamJefferson Parish LibraryEMAIL:jpltrain@jplibrary.net

COURSE OBJECTIVESIn this course we learn how to do thefollowing: Use formulas Use the Trace Precedents tool Use the Show Formulas tool Insert functions Insert nested functions Insert conditional functions Insert line breaks Create charts from spreadsheet data2

REVIEW: FORMULASUSING FORMULAS TO PERFORM CALCULATIONS: Formulas are mathematicalexpressions that operate on cellcontents. They are always preceded by anequals sign ( ). Formulae can contain cellreferences (like A1) or numbers(like 23). Enter a formula by typing directlyinto a cell, or use the FormulaBar.3

WORKING WITH CELLSUSE MATHEMATICALOPERATORS IN FORMULAEPrecedence(1 top; 3 bottom)ExampleThe percentoperator1 90%*M8 Theexponentiationoperator1 3 2 H3 3*Themultiplicationoperator2 5*2 D5*C5/The divisionoperator2 48/8 G12/10 The additionoperator3 A2 B2 100 25‐The subtractionoperator3 E7‐C7 100‐90OperatorDescription%Remember order of operations: PEMDAS—Parentheses, Exponents, Multiplication/Division, Addition/Subtraction.4

TRACE PRECEDENTSThe Trace Precedents command is used tobacktrack through all the cells that are usedto calculate the current formula. Each bluedot corresponds to a value used in theformula. An arrow points to the end value.You can remove these arrows by clickingRemove Arrows in the Formula Auditinggroup.5

SHOW FORMULASIf you ever want to see formulas ina spreadsheet’s cells, rather thanthe calculated answer to theformula, click the Show Formulasbutton in the Formula Auditinggroup of the Formulas tab.Formulas will appear in thespreadsheet. Click the buttonagain to return to normal view.6

FUNCTIONSA function is a preset formuladesigned to make calculationseasier.There are 9 main categories offunctions in the FunctionLibrary on the Formulas tab.AutoSum, Math & Trig, andFinancial are the mostcommonly used categories.7

FUNCTIONSTO INSERT A FUNCTION FROMTHE FUNCTION LIBRARY Click on one of the functioncategories. Hover your mouse over thedifferent options for a screen tipdescribing what each functiondoes. Click to select.8

FUNCTIONSTO INSERT A FUNCTIONFROM THE RIBBON Click Formulas InsertFunction to open the InsertFunction window. Type a description of whatyou’re looking for in thesearch box. Select a function and clickOK.9

FUNCTIONSTO INSERT AFUNCTION WITH THEKEYBOARD Type an equals sign,then start typing thename of thefunction. Double‐click theoption you wantfrom the menu.10

FUNCTIONS After you have inserted afunction, insert the arguments—i.e., the cell values you want touse in your calculation. You can type the cell referencesor you can click and drag overthe cell range. A colon reads as “through”—e.g.,A1:A4 means A1 through A4. Separate nonadjacent cellreferences with a comma.11

COMMON FUNCTIONSSUMAVERAGECOUNTMAXMINPINOWReturns the sum of the cell references.Returns the average of the cellreferences.Returns the number of cells thatcontain a non‐zero value.Returns the largest value.Returns the smallest value.Returns the number up to a certaindecimal.Returns current date and time.12

IF FUNCTIONSIF functions are called conditionalfunctions because the return valuedepends on certain conditions.Ex:IF (F2 70,“Fail”,“Pass”)If the value in F2 is less than 70, “Fail” isreturned; if more, “Pass” is returned.IF is located under Logical in the FunctionLibrary.13

RANK FUNCTIONThe RANK function returns theargument’s rank in a list of othernumeric values.First, provide the cell reference forthe number whose rank you wantto know. Add a comma, then thecolumn that contains the othernumbers.Ex: RANK(F2,F:F)This will return the rank of F2compared to the other values inthe F column.14

NESTED FUNCTIONS A nested function is a functionwithin a function. Order of operations determinesthe order in which calculations aredone. The innermostparentheticals will be calculatedfirst. Separate nested functions with acomma. Be sure that you have a closingparenthesis to match every openparenthesis. Otherwise yourfunction will return an error.15

LINE BREAKSLine breaks can make it easier to read a long, complicated formula.To add line breaks, click and drag the divider between the formula bar andthe cells down a bit.Then press Alt Enter to add a line break at various points within theformula.16

CHARTSTo create a chart, select thedata that you want to use inthe chart. This data shouldinclude some identifiers suchas the row headings shownhere.Click on the Charts tab inyour exercise file. Then selectcells A3:B7.17

CHARTS (cont.)Now click Insert Insert Pie or DoughnutChart to view a list ofpossible pie charts.Select the one youwant.We’ll do the 3‐D Pie.18

CHARTS (cont.)This action creates a chart inthe spreadsheet, showingcomparative slices for the salesper region.Note that the data that wasused to create the chart hasbeen highlighted in theworksheet.Also notice the contextual tabsin the ribbon.19

CHART TOOLS DESIGN TABWhen a chart is inserted into the spreadsheet, the Chart Tools contextualtabs appear in the ribbon. There are two Chart Tools tabs. Each tab is furthersubdivided into groups.There are five groups on the Chart Tools Design tab. Chart Layouts Chart Styles Data Type Location20

CHART TOOLS FORMAT TABThere are seven groups in the Chart Tools Layout tab: Current Selection Insert Shapes Shape Styles Word Art Styles Arrange SizeThe next few slides are better illustrated in the video at this lesson/9376721

CHANGE THE DATA SOURCERight‐click the chart and click Select Data.This will display the Select Data Sourcedialog box.22

CHANGE THE DATA SOURCE (cont.)At the top of the dialog box, theChart data range field showsthe range of cells that serve asthe current chart data: cellsA1:D5.To change the data source, useyour mouse to select the newdata range from thespreadsheet (A3:A7, C3:C7).23

CHANGE THE DATA SOURCE (cont.)You will see the newrange entered into theChart data range field.The chart itself will alsochange.24

CHANGE THE CHART TYPEClick Chart Tools – Design Change Chart Type.Select the chart type youwant, then click OK.25

COMMON KEYBOARD SHORTCUTSCTRL ASelect entire document/pageCTRL CCopy selected text/objectCTRL XCut selected text/objectCTRL VPaste selected text/objectCTRL ZUndo your last actionCTRL FFind specific text in the current documentCTRL SSave the current documentCTRL PPrint the current documentCTRL BBolds the selected textCTRL IItalicizes the selected textCTRL UUnderlines the selected textCTRL NCreate a new document26

CHANGE THE DATA SOURCE (cont.)At the top of the dialog box, theChart data range field showsthe range of cells that serve asthe current chart data: cellsA1:B7.To change the data source, useyour mouse to select the newdata range from thespreadsheet (A3:A7, C3:C7).These two ranges are notadjacent, so you must holddown the Control key to selectthe second range, C3:C7.27

CHANGE THE DATA SOURCE (cont.)You will see the newrange entered into theChart data range field.The chart itself will alsochange.28

CHANGE THE CHART TYPEClick Chart Tools – Design Change Chart Type.Select the chart type youwant, then click OK.29

ADDITIONAL RESOURCESAdditional helpful resources can be accessed through our homepage www.jplibrary.net From the JPL homepage, click on the Digital Content Tutorials(blue rectangle) next scroll bar. After the course menu opens, click Full Screenat the top of the course menu to view tutorials.30

ADDITIONAL RESOURCESAdditional helpful resources can be accessed through our homepage www.jplibrary.net From the library’s homepage, click on the JPL DigitalContent link or the Digital Content menu tab. Then click on the LinkedIn Learning icon to accessonline training using your library card number and pin.Lynda.com is now LinkedIn Learning.31

NOTESJefferson Parish Library authorizes you to view and download materials such as this handout at our web site (www.jplibrary.net) only for your personal, non‐commercialuse, provided that you retain all copyright and other proprietary notices contained in the original materials on all copies of the materials. You may not modify thematerials at this site in any way or reproduce, publicly display, perform, distribute or otherwise use them for any public or commercial purpose. The materials at this siteare copyrighted and any unauthorized use of any materials at this site may violate copyright, trademark, and other laws. If you breach any of these Terms, yourauthorization to use any materials available at this site automatically terminates and you must immediately destroy any such downloaded or printed materials.32

SHOW FORMULAS If you ever want to see formulas in a spreadsheet’s cells, rather than the calculated answer to the formula, click the Show Formulas button in the Formula Auditing group of the Formulas tab. Formulas will appear in the spreadsheet