Power Pivot and Power Query

3.9

Course Objectives

  • To extract only useful data from multiple and large data sets
  • Track the sums of data from long rows or columns
  • Grouping data into categories or break down into time period

Audience 

  • Sales Executive / Managers
  • Business Analysist
  • Resource Planning Team
  • Project Managers
  • Purchasing Manager
  • Financial Executive / Managers

METHODOLOGY 

This program will be conducted with interactive lectures, PowerPoint presentation, discussion, and practical exercise.

COURSE OUTLINES

Lesson 1: Getting and transforming data from multiple of sources

  • Import data from various database / system
  • Cleaning duplicate entry
  • Formatting the right data type
  • Splitting or joining columns or text
  • Removing spaces and special characters from your data

Lesson 2: Joining multiple tables

  • Joining all rows from multiple query into one query
  • Joining selected columns from multiple query into one query

 

Lesson 3: Getting Data to Analyse

  • Loading query into Data Model
  • Creating table relationship
  • Adding new calculated column based on existing column

Lesson 4: Analysing Data in Power Pivot

  • Creating Pivot Table from Data Model
  • Formatting Pivot Table
  • Refreshing Pivot Table for additional data

Target Audience

  • This course is designed for Clerks, Officers, Executives, Supervisors, Administrators, Managers of all levels; and personnel who already know and understand and want to further enhance their knowledge and practical uses of Microsoft Excel.

Methodology

This program will be conducted with interactive lectures, PowerPoint presentation, discussions and practical exercise

Course Modules

  • Module 1: Getting Different Views on Your Data

    Topic A: Outlining and Grouping Data
    – Using Automatic Outlining
    – Displaying and Collapsing Levels
    – Grouping Data Manually
    – Creating Subtotals

  • Module 2: Planning, Forecasting & Projection of Your Data

    Topic A: Using Data Analysis Tools
    – Using a One or Two
    – Input Data Table
    – Using Goal Seek

    Topic B: Exploring Scenarios
    – What is a Scenario?
    – Creating a Scenario
    – Saving Multiple Scenarios
    – Creating a Scenario Summary Report

    Topic C: Using Solver
    – Understanding Solver
    – Generating Reports and Scenarios with Solver
    – Changing Solver Values
    – Managing Solver Constraints
    – Using Solver as a Goal Seek Tool

  • Module 3: Advanced Functions

    Topic A: Working with Array Formulas
    – What are Array Formulas?
    – Using Functions within Array Formulas

    Topic B: Using the LOOKUP Function
    – Using VLOOKUP to Find Data
    – Find Exact Match with VLOOKUP
    – Find Closest Match with VLOOKUP
    – Find with HLOOKUP

    Topic C: Alternative LOOKUP Function
    – Using the INDEX Function
    – Using the MATCH Function
    – Combining the MATCH and INDEX functions

    Topic D: Linking, Consolidating, and Combining Data
    – Linking Workbooks
    – Consolidating Workbooks
    – Combining Worksheets

  • Module 4: Present Data with Visual Report

    Topic A: Create Combo Chart
    – Comparing 2 Types of Values with high Variance
    – Creating Combo Chart
    – Editing Combo Chart

  • Module 5: Summarizing and Analyzing Data with PivotTable, Slicer and PivotCharts

    Topic A: Create a PivotTable
    – Create PivotTable Dialog Box
    – PivotTable Fields Pane
    – Summarize Data in a PivotTable
    – Show Values as Functionality of a PivotTable
    – Personalize the default PivotTable layout

    Topic B: Filter Data by Using Slicer
    – Slicers
    – Insert Slicers Dialog Box

    Topic C: Analyzing Data with PivotChart
    – Creating PivotChart
    – Editing a PivotChart

  • Module 6: Navigating Large Workbooks

    Topic A: Customizing Workbooks
    – Inserting Hyperlinks
    – Editing Hyperlinks
    – Formatting Hyperlinks
    – Using Hyperlinks in Excel

  • Module 7: Macros

    Topic A: Macros
    – Recording Macros
    – Saving to Macros Enabled Workbook
    – Closing and Re-Opening a Macro Workbook
    – Security Warning Message
    – Playing a Macro
    – Assigning a Keystroke to a Macro

  • Module 8: Prevent User Changes with Protection

    Topic A: Protecting Worksheet and Workbook
    – Protecting the Current Sheet
    – Protecting an Entire Workbook
    – File Protection

Get To Know The Trainer

Trainer P.L

 4.5

She is an expert on Microsoft Applications, Publisher (Version 2000, XP, 2003 & 2007, VBA, Introduction To Internet, Multimedia Fundamentals, IT Skills For Non-PC Users, Basic IT skills and related programs. In her previous company, she was responsible for developing training courseware and customizing training curriculum.

Trainer Jarina

 4.5

Managing and maintaining a Microsoft Windows Server 2003 Environment - educate the IT professionals who implement, manage, and troubleshoot existing network and system environments based on the Windows Server 2003 platform. Implementation responsibilities include installing and configuring parts of systems. Management responsibilities include administering and supporting systems

Trainer Azrul

 

Azrulnizam Ahmad has been working in the business industry since 2004 and has been exposed to all the common challenges faced by business personnel. With his years of experience working with business and management, IT services and end users, he understands the importance of business value, infrastructure efficiency and user productivity.He shares his training passion in Microsoft Office 2007 Application - Excel, PowerPoint, and Word; Microsoft Office 2010 Application - Excel, PowerPoint, and Word and also emphasizes on the criticality of this aspect in any implementation

Trainer Nazarudin

 4.5

Nazarudin Aziz started freelancing as a Corporate Trainer since the year of 2005 specializing in the field of Multimedia Graphic Designer and Microsoft Office Applications. His list of clients are Perodua, Grand Pacific Hotel, Cosway, UDA Mall, Institut Teknologu Rekabentuk Perabot Malaysia, Telekom Malaysia, Lembaga Tabung Haji, Yayasan Pelajaran MARA, Kementerian Pembangunan Wanita & Masyarakat, Hospital Tengku Ampuan Rahimah Klang, United Engineers Malaysia, Malaysian Maritime & Dredging Corporation, Jabatan Perdana Menteri, Indah Water Consotrium and many more.

Rahul

 4.5

Rahul Thomas graduated in Bachelor of Information Technology majoring Computer Security and currently pursuing in Master of Information Technology majoring in Cybersecurity. He is an internationally certified trainer with successful years of experience in Cybersecurity, Cloud Computing and Microsoft Office. He is also recognized for his unconditional consistent in performance excellence and contributions to success in the Information Technology industry. Besides that, he also has a very enthusiastic personality when it comes to training, and he has been recognized for disseminating remarkable trainings.

Trainer Siva

 4.5

Siva is a highly motivated, customer focused, IT / Web / Multimedia professional / Microsoft Office. Siva has excellent communication, interpersonal, and leadership skills. Siva is knows to have strong analytical and troubleshooting skills. He is also very effective at conceiving and initiating new ideas and strategies. Ability to work productively with a wide range of people but can also work well independently. An enthusiastic team player, who works well under pressure, is extremely focused, and is able to deliver high quality of service within tight deadlines.

  View more trainers

Chat with us LIVE to get a

FREE QUOTATION!

THIS PUBLIC COURSE INCLUDES

ALL IN FROM ONLY

*T&C Applies

3.9

  • 38%
  • 38%
  • 13%
  • 0%
  • 13%
  • SJ
    Syazwani Johari
    Senior Executive, Accounting and Finance

    5

  • SH
    SITI NOR AZIRA BINTI CHE HASSAN
    Junior Executive, Accounting and Finance

    5

  • MA
    MUHAMAD ASHRAF NASIRUDIN BIN ASHRI
    Junior Executive, IT Management

    5

  • Mh
    Muhammad Nabil Afiq bin Mohd hair
    Executive, Accounting and Finance

    4

  • NS
    Nur Atiqah Sulaiman
    Junior Executive, Accounting and Finance

    4

  • CM
    C Mala
    Manager / Supervisor, Accounting and Finance

    4

  • PC
    Phang Yin Ching
    Executive, Accounting and Finance

    3

  • LK
    Liew Chen Kiong
    Assistant Manager, Operations

    1

    Boring.

Courses you may like

Optimized by Optimole

MEMBERS ONLY FEATURE

Kindly sign-up or log-in to get access.

LOGIN

[nextend_social_login]

Login with your social account

or

Don’t have an account with us?

FREE FOR LIFE

Sign Up to Bookmark your Favourite Course
or Request for a Quotation instantly

[nextend_social_login]

Sign in with your social account

or

By signing up, you agree to our Terms of Use and Privacy Policy

Have an account with us already?