Microsoft Excel Advanced Course Contents

Microsoft Excel Advanced Online Courses

Microsoft Excel Advanced (Course 307) – Section 1

  • Microsoft Excel Program Options
  • Introduction to Excel Databases 
  • How to Create an Excel Database
  • Using Forms 
  • Navigate around your form
  • Find Information Using a Form 
  • Find Information Using the Find Command 
  • Searching an Entire Workbook for Text 
  • Search an Entire Workbook for a Cell Reference 
  • Finding Text in Comments

Microsoft Excel Advanced (Course 307) – Section 2

  • Labels and Information 
  • Names used in Formulas 
  • Named Ranges are Absolute Values 
  • Find a Range of Named Cells 
  • Named Ranges can be used in Combination 
  • The Naming Manager & Naming Constants
  • Applying Named Ranges to Formulas 
  • Apply Name Ranges from a Selection

Microsoft Excel Advanced (Course 307) – Section 3

  • Open Data Files in Excel 
  • Using the Text Import Wizard
  • Sorting and Filtering 
  • Removing a Column or Row 
  • Filtering
  • Advanced Filter using Wildcards (search text within cells) 
  • Advanced Filter for Multiple Search Criteria (OR) 
  • Narrow the Results using Advanced Filter (AND)
  • Data Validation, Drop Down Lists and Named Ranges 
  • Formatting Cells with Drop Down Lists

Microsoft Excel Advanced (Course 308) – Section 1

  • Using AutoFilter 
  • Cleaning up data using AutoFilter – part 1
  • Cleaning up data using AutoFilter – part 2
  • Perform an Advanced Filter 
  • Advanced Filter using Wildcards (search text within cells) 
  • Advanced Filter for multiple search criteria 
  • Narrowing search results using Advanced Filter

Microsoft Excel Advanced (Course 308) – Section 2

  • Inserting a Pivot Table 
  • Filtering data in a Pivot table 
  • Sort dates by month in a pivot table 
  • Changing the value field settings in a pivot table 
  • Creating pivot table charts
  • The CHOOSE function
  • Get month values and Choose
  • Automatically produce ratings
  • Choose the calculation you want to occur

Microsoft Excel Advanced (Course 308) – Section 3

  • Get months values using the Choose function
  • Show grades for student results data using CHOOSE function
  • Case Study: Calculating PAYG & Super from Net Payments to staff
  • Insert a VLOOKUP which tells us the Isle and price of a product 
  • Perform vlookup using Named Ranges 
  • HLOOKUP 
  • Paste Special for Formulas, values, comments and formatting 
  • MATCH AND INDEX 
  • Find the match 
  • Incorporate cell information in text 
  • Use Index to find the value in a matched row 
  • NESTED MATCH AND INDEX FUNCTIONS
  • Performed a nested MATCH & INDEX function 
  • Combine MATCH with IFERROR functions 
  • PROTECTION 
  • Open a protected workbook 
  • Understanding cell protection 
  • Finding Cells which contain calculations

Microsoft Excel Advanced (Course 309) – Section 1

  • Consolidating data from several sheets into one
  • Consolidate data using named ranges
  • Duplicate formatting between sheets using Format Painter
  • Grouping and ungrouping Rows in a worksheet
  • Manually grouping rows in Excel
  • Case Study: Using financial calculations and goal seek, discover how much we can afford to borrow to buy a house

Microsoft Excel Advanced (Course 309) – Section 2

  • Turning on the Solver Tool in Excel 
  • Using Solver and Constraints to achieve our objective
  • Case Study: Use the solver to help a business startup find the best way to reach their budgeted target income given different products and services available for sale.

Microsoft Excel Advanced (Course 309) – Section 3

  • Inserting a Form Component (Combo box) – Developer Tab
  • Case Study: How to save thousands off your mortgage

 

See Microsoft Excel Course Summaries         Enrol into this course