We've noticed this is not your region.
Redirect me to my region
What do you want to learn today?

ADVANCED MICROSOFT EXCEL TRANING

ENDED
Inquire Now
On-Site / Training
Ended last Apr 21, 2023
NGN  160,000.00

Details

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.

Outline

COURSE MODULES

  • 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.

Topics:

  • 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

Reviews
Be the first to write a review about this course.
Write a Review
AM - AKEMSON KONSULT is a Management and Business Development consulting firm with seasoned professionals committed to delivering world class consulting services with the aim of equipping the workforce with current knowledge and skill needed to overcome the challenges confronting businesses and human resources in this globalised business world.


Our Faculty: We have in-house and external resource persons - highly qualified and experienced professionals drawn from both private and public sectors.

Sending Message
Please wait...
× × Speedycourse.com uses cookies to deliver our services. By continuing to use the site, you are agreeing to our use of cookies, Privacy Policy, and our Terms & Conditions.