# Useful Formulas & Functions (Microsoft Excel 2007 & 2010)

 Length: 1 Days Published: June 11, 2013 Language(s): English Audience(s): Information Workers Level: 200 Technology: Microsoft Excel 2010 Type: Course Delivery Method: Instructor-led (classroom)
 About this CourseThis 1 day course provides students with the knowledge and skills to the usage of useful formulas and functions in Microsoft Excel 2007 and Microsoft Excel 2010. Audience ProfileThis course is intended for users of Microsoft Office Excel who want to learn about useful formulas and functions. At Course CompletionAfter completing this course, students will be able to:Apply Formula and Functions BasicStatistical and Logical FunctionsLookup and Reference FormulasText FormulasDate and Time Formulas Array and Database Functions Efficiency Tips
 Course OutlineModule 1: Making Data Work For YouThis module explains how to understand and apply Excel basic formulas and functions.Lessons Formula basicsUsing cell referencesCopy formula without changing cell referenceTranspose formulaUsing nested functionsAfter completing this module, students will be able to:Understand and apply formula basicsUse cell referencesCopy formula without changing cell referenceTranspose formula using paste specialUse nested functionsModule 2: Statistical and Logical FunctionsThis module explains how to use logical functions including CountIf, Sumif, If, IsError.Lessons Perform calculation using CountIFPerform calculation using SumIFPerform calculation using AverageAUsing IF function to prevent division by zeroUsing IsError function to avoid error displayCreating multiple conditions using nested IFUsing logical function OR, AndAfter completing this module, students will be able to:Perform calculation using CountIf, SumIf, AverageAUse If function to prevent division by zeroUse IsError function to avoid error displayCreate multiple conditions using nested IFUse logical function OR, ANDModule 3: Lookup and Reference FormulasThis module explains how to apply and use lookup formulas including vlookup, hlookup, match and index.Lessons Use Vlookup to find specific dataUse Hlookup to find values in rowsUse Match and Index to retrieve dataAfter completing this module, students will be able to:Use Vlookup to find specific dataUse Hlookup to find values in rowsUse Match and Index to retrieve dataModule 4: Text FormulasThis module explains how to apply Text formula to help change casing of text, append text and numerical value in excel spreadsheet.Lessons Changing case of textAppend text and numerical valueConvert imported text format into numbersBreak imported date field into individual columnsAfter completing this module, students will be able to:Change case of text using Upper, Lower or Proper formulaAppend text and numerical valueConvert imported text format into numbersBreak imported date field into individual columnsModule 5: Date and Time FormulasThis module explains how to make use of calculate the difference of two given Date fields and to perform calculation with Time fields. Lessons Perform addition to Date fieldsCalculate difference between two DatesPerform calculations with Time fieldsAfter completing this module, students will be able to:Perform addition and calculate difference between two datesPerform calculations with Time fieldsModule 6: Array and Database FunctionsThis module explains how to apply and use advance formula including Array, Frequency and Database functions.Lessons Using Array FormulasCalculate the difference between Maximum and Minimum valuesUsing Frequency function to Count responsesUsing Database functions DSum and DCountAfter completing this module, students will be able to:Use Array FormulasCalculate the difference between Maximum and Minimum values in an ArrayUse Frequency function to Count responses in tabulated dataUse Database functions DSum and DCountModule 7: Efficiency TipsThis module discusses some useful Excel Tips including application of Data Validations and Auditing Tools.Lessons Shortening worksheets namesProtecting cells containing formulasUsing Data ValidationDisplaying Formula syntaxUsing Auditing Tools for errors checkingTracing precedent and dependentAdding comments to worksheetAfter completing this module, students will be able to:Understand the advantages of shortening worksheet namesProtect cells from amendments by othersUse Data validation to improve data entriesUse Auditing Tools for checking errorsAdd useful notes by commenting worksheet
 Before attending this course, students must have:A basic working knowledge of Microsoft Office Excel
 Looking for training resources, events and advice from peers? Join the Microsoft Training and Certification Community. Preparing for an exam now? Find your Microsoft Certification Study Group. Talk to us on these social networks:

### Find Training Near You

Location:

Eg: Seattle, WA or Paris, France