Excel for Data Analysis : Intermediate Techniques

Module 1: Customizing the Excel Environment

• Configure Excel Options
• Customize the Ribbon and the Quick Access Toolbar
• Enable Excel Add-Ins

Module 2: Organizing and Analyzing Datasets and Tables

• Manual Tables VERSUS Smart Tables
• Create and Modify Tables
• Sorting Data
• Filtering Data
• Use SUBTOTAL and Database Functions
• Enabling / Disabling Smart Tables

Module 3: Creating Basic to Advanced Formulas

• Working with Cell References
• Working with Absolute Referencing and Relative Referencing
• Working with Array Formulas
• Working with Name Range

1. Descriptive Names for Cell References
   • Creating and Using a Named Range
   • Creating Slightly Smarter Named Ranges
   • Naming Formulas and Constants
   • Managing Named Ranges
   • Automatically Creating Named Ranges
   • Applying Names to Existing Formulas
2. Controlling Recalculation
3. Solving Formula Errors
   • Step-by-Step Evaluation
   • Tracing Precedents and Dependents
   • Error Checking
4. Working with Formulas And Functions

• Creating a Basic Formula
  • Excel’s Order of Operations (BODMAS)
  • Cell References
  • How Excel Formats Cells That Contain Cell References
• Functions
  • Using a Function in a Formula
  • Using Cell References with a Function
  • Using Cell Ranges with a Function
  • Excel Compatibility Functions
• Formula Errors
• Logical Operators
• Formula Shortcuts
  • Point-and-Click Formula Creation
  • Point-and-Click Formula Editing
  • The Formulas Tab.
  • Using the Insert Function Button
• Copying Formulas
  • Absolute Cell References
  • Partially Fixed References
  • Referring to Other Worksheets
  • Referring to Other Workbooks

Module 4: Working with Statistical and Database Functions

1. Working with Simple to Advanced Queries With :

• Calculating Sum , Sumif , Sumifs functions
• Working with Average, Averageif, Averageifs functions
• Counting with Count, Countif, Countifs Functions
• Analyzing Max , min , Large , Small , Rank Values

1. Working with Complex Queries With :

• DSUM
• DAverage
• DCount
• DMax
• DMin

Module 5: Manipulating Dates, Times, and Text 

1. Working with Text Functions

• CONCATENATE(): Joining Strings of Text Together
• LEFT(), MID(), and RIGHT(): Copying Portions of a Text String
• UPPER(), LOWER(), and PROPER(): Changing Capitalization

1. Working with Date and Time Functions
   • TODAY() and NOW(): Inserting the Current Date and Time
   • DAY(), MONTH(), and YEAR(): More Date Calculations
   • WEEKDAY(): Determining the Day of the Week
   • DATEDIF(): Calculating the Difference Between Dates
   • DAYS360(): Finding Out the Number of Days Between Two Dates
   • YEARFRAC(): Calculating the Percentage of a Year Between Two Dates
   • WEEKNUM(): Figuring Out in Which Week a Date Falls

Module 6: Managing Workbooks

• Arranging Multiple Workbooks
• Adding Hyperlinks
• Working with Comments
• Saving Workbooks in Different Formats
• Saving a Workbook as a Web Page
• Creating Folders
• E-Mailing a Workbook

Module 7: Grouping and Outlining Data 

• Basic Data Grouping
• Creating a Group
• Nesting Groups Within Groups
• Summarizing Your Data
• Combining Data from Multiple Tables
• Grouping Timesavers
• Auto Outline
• Automatic Sub totalling

Module 8:   Templates 

• Understanding Templates
• Creating a New Workbook from a Template
• Downloading Templates
• Creating Templates
• Understanding Custom Templates
• Building a Custom Template
• Sharing Templates with Others

Module 9: Visualizing Data with Basic Charts

• Creating Basic Charts
• Formatting and Perfecting Charts
• Working with Other Sources
• Creating Combo-Charts