Corporate Training By Zack Academy - Advanced Excel Power Users 2010

Why Should You Take This Advanced Excel Power Users 2010?

The Advanced Excel Power Users 2010 group class concentrates on taking your Excel knowledge to the next level. By the end of the Excel training course, you will feel confident in writing macros to create pivot tables and charts, utilizing advanced nested text, lookup, date, array formulas, and advanced comparison of multiple lists. We not only show you how to do more advanced formulas, but how to use all the important shortcuts to use Excel quickly. This Excel class is perfect for those who work with Excel consistently in fields such as the financial, real estate, HR, non-profit or executive assistant positions.

The Following Materials Are Included:

• Custom Printed Manual
• Multiple XLS and XLT examples for in class and after class
• Shortcut sheets
• After class resources

The Following Topics Will Be Covered:

• Lesson 1 - Advanced Keyboard Navigation and Referencing
• Lesson 2 - Working with Lists
• Lesson 3 - Linking to Live Data on the Web to Create Live Currency Converter
• Lesson 4 - Consolidating Data from Multiple Worksheets or Workbooks
• Lesson 5 - Working with Advanced Text and Text Formulas
• Lesson 6 - Advanced Date Formulas
• Lesson 7 - Advanced Custom Formatting
• Lesson 8 - Match and Index Functions to Quickly Return Any Information From A Database
• Lesson 9 - Creating Dynamic Lists and Submenus
• Lesson 10 - Using Goal Seek
• Lesson 11- Arrays
• Lesson 12- Creating Macros Through Fundamental VBA

Syllabus

Lesson 1 - Advanced Keyboard Navigation and Referencing:

• Advanced Keyboard Navigation
• Naming Ranges
• Named Range Practice
• Dynamic Named Ranges

Lesson 2 - Working with Lists:

• Remove Duplicates
• Remove Duplicate Practice
• Highlighting Items that are in First List Only
• Highlighting Items that are in Second List Only
• Highlighting Values in Both Lists

Lesson 3 - Linking to Live Data on the Web to Create Live Currency Converter

Lesson 4 - Consolidating Data from Multiple Worksheets or Workbooks

• Consolidate Practice

Lesson 5 - Working with Advanced Text and Text Formulas

• Splitting Text
• Finding the first name using text functions
• FIND Function
• Replace function
• Return the Last Word of text string
• Joining Text Strings together
• Other Useful text Functions
• LEN Function
• RIGHT Function
• SUBSTITUTE Function
• MID Function
• PROPER Function
• LOWER Function
• UPPER Function
• TRIM Function
• Text function practice

Lesson 6 - Advanced Date Formulas:

• Network Days
• WEEKNUM Function
• Finding the last day of a month
• EOMONTH Function
• EDATE Function

Lesson 7 - Advanced Custom Formatting:

• General Formatting codes
• Date codes
• Miscellaneous & Conditional codes

Lesson 8 - Match and Index Functions to Quickly Return Any Information From A Database:

• MATCH Function
• Match Function Practice
• INDEX Function
• INDEX function practice

Lesson 9 - Creating Dynamic Lists and Submenus:

• OFFSET Function
• INDIRECT Function
• INDIRECT Function practice
• Creating Submenus
• Payment Function
• PMT Function practice

Lesson 10 - Using Goal Seek:

• Goal Seek Practice

Lesson 11- Arrays:

• Single-cell array formulas
• Advantages of Arrays
• Disadvantages of Arrays

Lesson 12- Creating Macros Through Fundamental VBA:

• Finding the name of the current worksheet
• Finding the last Row used
• Finding the last column used
• VBA to create a Clustered Column Chart
• Creating a pivot table using our captured last row and column
• VBA to create a Pivot tables

Miscellaneous Topics:

• Importing Data from a text file, Delimited file or CSV
• Making identical changes across worksheets
• How to stop the workbook from re-calculating

Resources:

• NYIM Forum

Appendix:

• Converting 2010 to 2007 or earlier
• Quick Access Toolbar
• Excel Options
• Ribbon Collapsing
• Customizing Excel – Add-Ins

Shortcuts:

• Common Shortcuts
• All Shortcuts