MICROSOFT EXCEL WORKSHOP – CONNECTING, MASHING & TRAINSFORMING DATA IN EXCEL
Lot 1-15A Level 1
Wangsa Walk Mall, Wangsa Avenue 9, Jalan Wangsa Perdana 1, Wangsa Maju, 53300 Kuala Lumpur
Workshop Session: 10.00am – 6.00pm
This is an instructor-led 2-days training program on Power Pivot, Power Query and Pivot Table. It is a notch up from the Essential Excel Skills You Must Know course in terms of difficulty level. You will be introduced to big data and databases; taught how to connect, extract and remodel complex data for further analysis and visualization.
Take a deep dive into Power Query, using it to import data, edit and merge with another source of data. Use PowerPivot to analyze millions of data rows in databases using DAX functions. Transform data without using Excel formulas.
Make complex data un-complex by working data seamlessly between Power Query, Power Pivot and Pivot Table. This course imparts the core knowledge required for you to handle voluminous, complex data by ultimately reshaping it into a useful form which could be analyzed or visualized effectively.
OBJECTIVES of the training
This course teaches the Power Pivot, Power Query and Pivot Table. You will be introduced to the basics of the tools, connecting to data sources, learning to cleanse and edit the data, reconnect broken relationships, remodel data and putting them into Excel worksheets for further analysis and visualization.
WHO should attend?
Data analysts, project managers, revenue managers, finance managers, data crunchers whose work involve analyzing or presenting data which is sourced from conventional data, big data and databases should attend.
You will be using complex big data and databases and guided hands-on using the fore-mentioned tools. Your understanding will be tested in class by requiring you to complete a mini- project.
1. The VLOOKUP function
- VLOOKUP Function
2. Database and Autofilters
- What is an Excel Database? •Database do’s & don’ts
- Creating a Table. Manipulating Tables •What is AutoFilter?
3. Pivot Table
- Pivot Table basics
- Building Effective Pivot Tables •Using Pivot Table Filters
- Sorting, Formatting Pivot Tables
- Calculations and Inserting Formulas •Pivot Table charts
- Creating Pivot Table from external sources
- Grouping Function in Pivot Tables •Slicers
- Timelines (Excel 2013 & above only)
4. Power Query
- Creating a new query
- Connecting to various data sources & importing data
- Using Power Query to replace the VLOOKUP
- Shaping & customizing a data model from multiple data sources
- Merging, appending & exporting queries
- •Exporting data to the Pivot Table
5. Power Pivot
- •What is Big Data?
- •Relational Database basics •Getting started with Power Pivot •Importing data into Power Pivot •Understanding Power Pivot &
- Creating Relationships between Tables •KPIs
- Data Analysis Expressions (DAX) language overview
- Visualizing data from Power Pivot