Power Excel

Release the power of Excel and enhance your work day-to-day

                                                                                                                   

Course Overview

This 20-hour hands-on based course is designed to help people to learn and use the most commonly used Excel features, functions and scripts, to improve their day-to-day work in more effective, efficient, accurate and reliable way.

In totally five classes, handy works and tasks are provided so that people can get chances to practice the most generally utilized Excel functionalities like they do in a real business environment, with a step-by-step guidance, starting from using basic functions to complicated embedded formulas.

At the end, the participants may learn some advanced features and functions to achieve their daily business activity goals, by capably resolving complex data calculations, statistics, and processions.

This training course is based on Microsoft Office 2007, and shipped with class slides and practical exercises. Some basic understanding of Excel is nice-to-have, but not strictly required as must.

 

Course Outline

Part I. Basic Excel – Calculating data in Excel

1.1. Excel basics

  • Excel overview
  • Basic operations
  • Keyboard shortcuts
  • References (relative, absolute, mixed, cross-sheet)
  • Using Excel functions
  • Excel data types and conversions
  • Handling different data types

 

1.2. Excel functions and formulas

  • Creating formulas with Excel functions
  • Conditional formulas
  • Nesting functions
  • Aggregation functions
  • Using lookup to cross-reference data
  • Dealing with unique and duplicate data

 

Part II. Advanced Excel – Processing data in Excel

2.1. Statistics

  • Data type and functions
  • Managing text with functions
  • Sorting and formatting
  • Index, search and match
  • Aggregating and pivoting data
  • Presenting graphical data (pie, line and bar)

 

2.2. Processing data

  • Enhancing the work
  • Invoking Macros
  • Adding controls in Excel
  • Processing large amount of data with million rows
  • Dealing multiple Excel and other data sources
  • Thinking of enhancement

 

Part III. Power Excel – Making magic in Excel

3.1. Introduction of VBA

  • Handling dynamic and various scenarios
  • Managing external data

 

3.2. The magic of VBA

  • Using Access database
  • Invoking queries
  • Excel in and out
  • Finalization

 

【主讲人】Mr. Liu;计算机软件专业毕业,MCITP-DBA证书,拥有20多年北美大型信息系统设计开发经验。曾经在中国主持设计开发超过1000个用户的大型政府部门办公自动化系统,AT&T全美联机系统的Senior Consultant,加拿大著名金融公司、银行及政府部门的Senior SQL Server DBA Consultant。擅长大型系统的设计,复杂数据结构的应用,企业级数据库系统的维护和开发。对于数据库系统理论及应用有精辟及独到的见解,所教授的SQL Server DBA及Access VBA 课程一直受到众多学员的好评。