Slide 1IntroductionSlide 3Skill 1: Using the SUM and AVERAGE FunctionsSkill 1: Using the SUM and AVERAGE FunctionsSkill 1: Using the SUM and AVERAGE FunctionsSkill 1: Using the SUM and AVERAGE FunctionsSkill 2: Use the MEDIAN, MIN, and MAX FunctionsSkill 2: Use the MEDIAN, MIN, and MAX FunctionsSkill 2: Use the MEDIAN, MIN, and MAX FunctionsSkill 2: Use the MEDIAN, MIN, and MAX FunctionsSkill 3: Move Data, Add Borders, and Rotate TextSkill 3: Move Data, Add Borders, and Rotate TextSkill 3: Move Data, Add Borders, and Rotate TextSkill 3: Move Data, Add Borders, and Rotate TextSkill 4: Use the COUNTIF and IF FunctionsSkill 4: Use the COUNTIF and IF FunctionsSkill 4: Use the COUNTIF and IF FunctionsSkill 4: Use the COUNTIF and IF FunctionsSlide 20Slide 21Slide 22Slide 23Skill 6: Use Find and Replace and Insert the DATE FunctionSkill 6: Use Find and Replace and Insert the DATE FunctionSkill 6: Use Find and Replace and Insert the DATE FunctionSkill 6: Use Find and Replace and Insert the DATE FunctionSkill 7: Freeze and Unfreeze PanesSkill 7: Freeze and Unfreeze PanesSkill 7: Freeze and Unfreeze PanesSkill 7: Freeze and Unfreeze PanesSkill 8: Create and Sort an Excel TableSkill 8: Create and Sort an Excel TableSkill 8: Create and Sort an Excel TableSkill 8: Create and Sort an Excel TableSkill 9: Filter an Excel TableSkill 9: Filter an Excel TableSkill 9: Filter an Excel TableSkill 9: Filter an Excel TableSlide 40Slide 41Slide 42Slide 43Skills for SuccessMore Skills for SuccessSlide 46Slide 47Slide 48More Skills 12: Hide or Unhide Rows and ColumnsMore Skills 12: Hide or Unhide Rows and ColumnsMore Skills 12: Hide or Unhide Rows and ColumnsMore Skills 13: Create PivotTable ReportsMore Skills 13: Create PivotTable ReportsMore Skills 13: Create PivotTable ReportsMore Skills 13: Create PivotTable ReportsSlide 56CongratulationsSlide 58Copyright © 2009 Pearson Education, Inc. Publishing as Prentice Hall. 1Skills for Success with Microsoft® Office 2007PowerPoint Lecture to AccompanySkills for Success with Microsoft® Office 2007Excel, Chapter 4Using Excel Functions and TablesCopyright © 2009 Pearson Education, Inc. Publishing as Prentice Hall. 2Skills for Success with Microsoft® Office 2007IntroductionIntroduction•In Skills 1–10, you will:–Use functions including SUM, AVERAGE, MEDIAN, MIN, and MAX–Move data, add borders, and rotate text–Apply conditional formatting andfreeze and unfreeze panes–Create and sort an Excel tableCopyright © 2009 Pearson Education, Inc. Publishing as Prentice Hall. 3Skills for Success with Microsoft® Office 2007IntroductionIntroductionHeader rowColumn conditionally formatted using data barsTable createdConditional formatting based on colorsCopyright © 2009 Pearson Education, Inc. Publishing as Prentice Hall. 4Skills for Success with Microsoft® Office 2007Skill 1: Using the SUM and AVERAGE FunctionsFunctionA prebuilt formula that performs calculations by using specific values in a particular order or structure.Title and subtitle entered and formattedCopyright © 2009 Pearson Education, Inc. Publishing as Prentice Hall. 5Skills for Success with Microsoft® Office 2007Skill 1: Using the SUM and AVERAGE FunctionsThe SUM function may also be entered from the Math & Trig button in the Function Library.Click the AutoSum button to insert the SUM function. The SUM function displays in the cell and in the Formula Bar.Drag down to insert the desired range of cells in the SUM function.SUM functionAdds all numbers in a selected range of cells.Copyright © 2009 Pearson Education, Inc. Publishing as Prentice Hall. 6Skills for Success with Microsoft® Office 2007Skill 1: Using the SUM and AVERAGE FunctionsArgumentsThe values an Excel function uses to perform calculationsor operations.Results display in the cell and underlying formula displays in Formula Bar.Values in parentheses are arguments for the function.Statistical functionsPredefined formulas that analyze a group of measurements.Copyright © 2009 Pearson Education, Inc. Publishing as Prentice Hall. 7Skills for Success with Microsoft® Office 2007Skill 1: Using the SUM and AVERAGE FunctionsAVERAGE functionAdds a group of values and then divides the result by the number of values in the group.Result of functionClick the More Functions button, point to Statistical, and then click Average to display Function Arguments dialog box. Description of functionRange of cells included in calculationFunction displays in cell as it is being typed.Copyright © 2009 Pearson Education, Inc. Publishing as Prentice Hall. 8Skills for Success with Microsoft® Office 2007Skill 2: Use the MEDIAN, MIN, and MAX FunctionsUsing the MEDIAN functionMEDIAN FunctionFinds the middle value that has as many values above it as below it.To collapse the Function Arguments box, click the Collapse Dialog button. Select the range to include in the arguments or type it in Function Arguments box.Copyright © 2009 Pearson Education, Inc. Publishing as Prentice Hall. 9Skills for Success with Microsoft® Office 2007Skill 2: Use the MEDIAN, MIN, and MAX FunctionsStyles group used to apply Comma Style and Currency StyleCurrency cell styleAVERAGE and MEDIAN return different resultsComma [0] cell styleCopyright © 2009 Pearson Education, Inc. Publishing as Prentice Hall. 10Skills for Success with Microsoft® Office 2007Skill 2: Use the MEDIAN, MIN, and MAX FunctionsClick the More Functions button, display Statistical functions, and then click MAX.Description of MAX function and solutionMAX function displays in cell and Formula Bar.MAX functionDetermines the largest value in a selected range.Copyright © 2009 Pearson Education, Inc. Publishing as Prentice Hall. 11Skills for Success with Microsoft® Office 2007Skill 2: Use the MEDIAN, MIN, and MAX FunctionsMIN function works like MAX function.Result of the MIN function applied to the rangeMIN functionDetermines the smallest value in a selected range of cells.Copyright © 2009 Pearson Education, Inc. Publishing as Prentice Hall. 12Skills for Success with Microsoft® Office 2007Skill 3: Move Data, Add Borders, and Rotate TextDeleting the contents of a cell does not clear the formatting from the cell.Outline of cells being moved and new location as shown in ScreenTipUse ScreenTip to place cells accurately.Copyright © 2009 Pearson Education, Inc. Publishing as Prentice Hall. 13Skills for Success with Microsoft® Office 2007Skill 3: Move Data, Add Borders, and Rotate TextClick a preset to specify borderOn