ADVANCED MICROSOFT EXCEL TRANING
CLASSROOM FEE: ₦160,000 | ONLINE FEE: ₦120,000
COURSE DESCRIPTION: If your goal is to enter the world of Business Intelligence (BI), Data Mining and or Data Analysis you have to take this course.
This training will help participants learn how to automate all the Extract > Transform > Load (ETL) tasks required to clean the data and make it usable for further analysis in Tableau, Power BI, Excel, Access, etc.Thus, helping them focus on turning data into insight and adding true value to the company and to the participants as well.
- Microsoft Power Query
- Microsoft PowerPivot
- Data Analysis and Dashboard Reporting
Module 1 – Microsoft Power Query
Microsoft Power Query is a tool that can be used for data discovery, reshaping the data and combining data (Merge / Blend / Consolidate, etc.) coming from different sources. Power Query can connect directly to Excel, Access, Text, CSV, Hadoop, Sales Force, Azure and many other different sources of data.
- Filtering out data you don’t need
- Deleting / sorting rows, columns or blanks
- Transforming text, numeric, and date columns.
- Removing spaces and special characters from your data.
- Fixing the layout into tabular format so that it will work in Pivot Tables
- Renaming, adding / removing, Splitting / joining and reordering columns
- Creating calculating columns / fields which never existed in the source data.
- Merging / consolidating / appending data from Text files, CSV files or database tables.
- Incorporating the next month (or period) data to an existing report.
- Perform same steps again when receive new file or data in next month
Module 2 – Microsoft Excel Power Pivot
Microsoft PowerPivot is an add-on for Microsoft Excel that provides Business Intelligence functionality and reporting within the familiar environment of Excel.
- Import data into the Power Pivot Data Model using Excel 2016/2019
- Import and edit data from Microsoft Access and Microsoft Excel
- Build Relationships between Data Tables
- Create Hierarchies for end-user filtering convenience
- Shape the Data Model with Calculated Columns & Measures
- Create DAX Expressions including: SUM(), RELATED(), RELATEDTABLE(), COUNT(), DISTINCTCOUNT(), MONTH(), DATE(), CALCULATE(), ALL()
- Build custom DAX Expressions to drop into any pivot
- Create Dashboard worthy reports with Slicers and Presentation tips
- Import existing Excel data and Link Excel data into the Data Model
- Create Calculated Columns and Measures in the Data Model
- Create Measures in Excel
- Create Calculated Columns with RELATED() & RELATEDTABLE() DAX Functions
- Create Measures using the COUNT() & DISTINCTCOUNT DAX Functions
- Create Measures using the CALCULATE() DAX Function to modify the Filter Context of Pivot Tables
- Create static values for Ratio creation with CALCULATE() & ALL() DAX Functions
- Create a Date Table (Calendar) in Excel and Relate to the Data Model for Time Intelligence functionality
Module 3 – Excel Data Analysis and DashboardReporting
This module covers some of the most popular data analysis Excel functions and Dashboard tools, including;
- VLOOKUP()/XLOOKUP() Functions for Looking up Information
- INDEX()/MATCH() Functions a Powerful Alternative to VLOOKUP
- TEXT() Function for Formatting
- CHOOSE() for Ultimate Flexibility in Presenting Data
- Filtering and Advanced Filtering
- Summarizing Data with PivotTables and Pivot Charts
- Interactive Features to Create User Friendly Reports
- VBA Magic to Create Dynamic Content
- Understand and Identify the principles of data analysis
- Effectively summarize and present data clearly
- Use effective design principles when creating and presenting data
- Take full advantage of the built-in tools Microsoft Excel offers in order to analyze and build dashboard reports
- Build interactive dashboard reports
- Analyze Excel data using Excels TEXT based Functions
- Data Mine using Excels Array Functions
- Build Interactive Dashboard Reports with Buttons and Drop-Down Menus
- Streamline and Analyze Data with Excel PivotTables and Pivot Charts
TARGET AUDIENCE: Production Managers, Engineers, Office Managers, Administrators, Secretaries, Supervisors, Accountants, HR Managers, Sales Managers, Sales Executives and anyone wishing to upskill