Mastering Microsoft Excel: PivotTable & Functions

 

Functions and Formulas are some of the advanced features of Microsoft Excel that allow you to become more productive and increase your day-to-day efficiency. The Mastering Microsoft Excel: Formulas & Functions training will expose you to how Formulas and Functions are used to perform complex calculations and data analysis.
You’ll learn:
  •  How to use PivotTable to Analyze & Summarize your database for easy understanding
  • Data, reports, graphs and formulas—making the most of Excel’s many talents
  • How to solve the Break Even Point, convert calculations to Tables of Variable for analysis purposes
  • Spreadsheets & more—easy ways to make spreadsheets and figures into colorful, clear and precise presentations
  • And much more
Who should attend?

Account Executives, Managers, Supervisors, Project Managers, Sales Executives, Directors, Business Owners and Individuals who want to improve the Microsoft Excel skills

About the Trainer

Kelvin Ng is a Microsoft Certified Trainer (MCT) and Microsoft Office 2010 Master Specialist with 15 years of training experience. He is able to relate practical usage of Formulas and Excel Features in your daily work and save you a great deal of time.

Why you should train with us?

Quality Assured

Generate New Idea

Telephone Support

Certified Instructor

Improve Productivity

SMS Support

Practical Example

Updated Resources

Email Support

Courses Outline

Duration: 2 Days

Module 1: Reporting Using Pivot Table

  • Introducing the Pivot Table
  • Understanding the Anatomy of a Pivot Table
    – Understanding the Values Area in your layout
    – Assign the fieldnames in the Row & Column Labels Area
    – Display what you want to present using the Filter Area
  • All you need to know on Creating the Basic Pivot Table Report
    – Understanding the layout of the Pivot Table
    – Change your report Layout the way you want to present the Pivot Table
    – Using different view of a Pivot Table Report
    – How to Update Your Pivot Table?

Module 3: Formulas and functions—Mathematical Functions

  • ROUND Function
  • ROUNDDOWN Function
  • ROUNDUP Function
  • MROUND Function
  • CEILING Function
  • FLOOR Function
  • Large and Small Functions
  • SUMIF Function

Module 5: Formulas and functions—Statistical Functions

  • COUNT Function
  • COUNTA Function
  • COUNTIF Function
  • COUNTIFS Function
  • COUNTBLANK Function

Module 7: Formulas and functions—LOGICAL Functions

  • IF Function
  • AND & OR Functions
  • IFERROR Function

Module 2: Data Analysis—What-If-Analysis

  •  Work your formula Backward by changing cell value using Goal Seek
  • Analyze the potential of a calculation using Data Table
    – Using One-Input Data Table when using single set of Variables
    – Using Two-Input Data Table when using two sets of Variables
  • How to multiple Case Studies using Scenario

Module 4: Formulas and functions—Date Functions

  • TODAY Function
  • NOW Function
  • WORKDAY Function
  • NETWORKDAYS Function
  • EDATE Function
  • EOMONTH Function
  • DATEDIF Function

Module 6: Formulas and functions—TEXT Functions

  •  CONCATENATE Function
  • LEFT, RIGHT & MID Functions
  • UPPER & Lower Functions
  • TRIM Function

Module 8: Formulas and functions—LOOKUP Functions

  • VLOOKUP Function
  • HLOOKUP Function
  • CHOOSE Function

Module 9: Formulas and functions—DATABASE Functions

  • DSUM Function
  • DAVERAGE Function
  • DMAX Function
  • DMIN Function
  • DCOUNT Function

Download PDF Version Here

Interested to know more about this course? Reach out to us now!

15 + 2 =