This training is designed for highly skilled Excel users who are engaged in roles that involve the creation of complex reports on a daily or weekly basis. Participants are required to possess a solid understanding of Excel, with version 2016 or newer.
The main topics include:
1. Managing Large Datasets with Power Pivot and Power Query
Many users encounter difficulties and slowdowns when dealing with extensive data in Excel. This session aims to eliminate those challenges, demonstrating that handling millions of rows is indeed possible.
2. Advanced Data Transformation and Automation using Power Query
Participants will explore one of Excel's most innovative tools since the creation of Pivot Tables, which allows for seamless connections to various data sources and the automation of data changes.
3. Data Modeling Techniques in Excel
The foundation of successful reporting is rooted in proper data modeling. Attendees will learn best practices to avoid common pitfalls in spreadsheet design.
4. In-Depth Analysis of Formulas
This session will cover advanced formula techniques, including array formulas, SUMPRODUCT, INDEX, SWITCH, IFS, and more.
5. Insider Tips and Techniques
Attendees will gain insights from over a decade of consulting experiences, learning valuable report generation strategies used in large organizations.
6. Introduction to VBA (Macro Recording)
Learn how to automate tedious tasks in Excel through macro recording.
7. VBA Programming from Ground Up
This section focuses on the foundational aspects of VBA coding and creating custom automation solutions.
The details of the training are as follows:
Cost: N150,000
This fee includes training materials, support after training, video resources, a certificate, and lunch. Early payment is encouraged to secure a place, along with submission of the preferred name for the certificate.
Payment Information
Account Name: URBIZEDGE LIMITED
Bank: FCMB
Account No: 2888666014
Branch: Onipanu
Title: Advanced Data Analysis, Power Query, Power Pivot, and VBA in Excel
Training Content Overview
1. Tools for Big Data in Excel
a. Utilizing PowerQuery to connect with over 100 data sources, including large databases and data warehouses.
b. Employing PowerPivot to swiftly analyze vast amounts of data with a compression engine that minimizes file size.
c. Creating data models in Excel, establishing relationships between datasets, and enabling automated reporting.
d. Optimizing formulas for handling large datasets.
2. Automating Reports in Excel
a. Consolidating data from multiple files automatically using combine & load functions.
b. Automating data consolidation across various sheets, accommodating data growth.
c. Setting up recurring reports to reduce manual input.
d. Enhancing data connections and formulas for flexible and scalable reporting.
3. Advanced Data Shaping and Transformations
a. Splitting columns, an advancement of Text-to-Columns functionality.
b. Utilizing Unpivot, a potent new tool for data analysis.
c. Grouping data efficiently.
d. Transposing tables and cleaning erroneous values.
e. Replacing erroneous values and auditing data transformations.
f. Implementing additional data transformation techniques without the use of Undo.
g. Other essential data processing steps.
4. Dashboard Creation in Excel
a. Understanding the concept of dashboards and determining when to create them.
b. Exploring various methods of dashboard construction.
c. Utilizing Pivot Tables and Pivot Charts for dashboard creation.
d. Employing formula-driven approaches for visual representation.
e. Integrating non-native Excel charts for enhanced visual representation.
5. Chart Types Available in Excel
a. Column charts, line charts, pie charts, bar charts, area charts, XY scatter charts, stock charts, surface charts, doughnut charts, bubble charts, radar charts, treemap charts, sunburst charts, histogram charts, box and whisker charts, filled map charts, waterfall charts, and funnel charts.
6. Advanced Formula Techniques
a. Using lookup functions – including VLOOKUP, LOOKUP, HLOOKUP, XLOOKUP.
b. Exploring logical functions such as IF, IFS, IFERROR, SWITCH, AND, OR, ISBLANK, and others.
c. Mastering formulas like INDEX, SUMPRODUCT, and RANK.
d. Utilizing special date functions such as WORKDAY, NETWORKDAYS, DATEDIF, EOMONTH, among others.
e. Incorporating text manipulation formulas including TEXTJOIN, CONCAT, FIND, SEARCH, MID, LEFT, RIGHT, and more.
7. Planning and Strategy Tools Offered in Excel
a. Discovering Goal Seek functionalities.
b. Exploring the Scenario Manager.
c. Utilizing Data Tables.
d. Implementing Solver functions.
8. Integrating Excel with Data Science Tools
a. Employing R (using BERT integration).
b. Utilizing Python (via xlwings).
c. Other integrations available.
9. Excel VBA Programming
a. Recording macros.
b. Coding from scratch.
i. Userforms development.
ii. Structuring modules.
iii. Creating User Defined Functions.