Discover

Filter events and get the results you need

Big Data Analysis, Power Query, Power Pivot and VBA in Excel

By UrBizEdge
Fri, Apr 12. 8AM - Sat 4PM
URBIZEDGE LIMITED, Lagos
past about 1 month
Big Data Analysis, Power Query, Power Pivot and VBA in Excel

This is only for very proficient Excel users. To qualify for this training you must be in a role that requires complex daily or weekly reports, you must have above basic level knowledge of Excel and you must have Excel 2016 or above.




The highlights are:


1. Working with Large Datasets using Power Pivot and Power Query

Too many people see their Excel struggle and slow down when handling large data. Some even think it is impossible to handle millions of rows of data in Excel. We help you cruise through those imaginary ceilings.


2. Advanced Data Transformation and Task Automation with Power Query

Welcome Excel’s most revolutionary tool since the Pivot Table. With it you can natively connect to other datasources and automate data transformations.


3. Data Modeling in Microsoft Excel

Getting the most out of your reports require setting the foundation right. We show you how to set your report models right and avoid common spreadsheet modeling issues.


4. The Deep End of Formulas

Array formulas, SUMPRODUCT (the complex use of it), INDEX, SWITCH, IFS, etc


5. Advanced Tips and Tricks

Lets show you what we’ve learned over about a decade of consulting and getting stuff done for large foreign and local companies report-wise.


6. VBA (recording macros)

Break into automating repetitive tasks in Excel


7. VBA (coding from scratch)

Start doing the heavy lifting with VBA coding from scratch.




The training details are as follows:


Cost: N150,000


after-training


The cost covers your training materials, after-training support, video learning materials, certificate, and lunch. Try to pay early to secure your spot and send us your name as you would like it to appear on your certificate.




Payment Details


Account Name: URBIZEDGE LIMITED


Bank: FCMB


Account No: 2888666014


Branch: Onipanu






Title: Big Data Analysis, Power Query, Power Pivot and VBA in Excel






Training Outline




1. Big Data Tools in Excel


a. PowerQuery to connect directly to over 100 data sources (including big data DB and data warehouse)


b. PowerPivot to analyze millions of rows of data fast and with a vertipaq compression engine that keeps the files small


c. Data Models in Excel


i. Create relationships between different datasets


ii. Enable automated report generation


d. Formula optimization for large data




2. Report Automation in Excel


a. Automatic consolidation of data in different files using combine & load


b. Automatic consolidation of data in different sheets even when the rows/data can grow


c. Setting up recurrent reports in ways that minimize manual work


d. Maximizing data linking and formulas to achieve scalable and agile reports




3. Advanced Data Shaping and Transformation


a. Split Columns (advancement of Text-to-Columns)


b. Unpivot (a relatively new powerful tool)


c. Group data


d. Transpose tables


e. Cleaning out error values


f.Replacing error values


g. Auditing


h. Inserting steps (without using undo)


i. Other data transformation steps




4. Creating Dashboards in Excel


a. Understanding what dashboards are and when to create one


b. Different ways of creating dashboards


c. Pivot Table and Pivot Chart approach


d. Formulas and charts approach


e. Using non-native Excel charts




5. Charts


a. Column chart


b. Line chart


c. Pie chart


d. Bar chart


e. Area chart


f.XY Scatter chart


g. Stock chart


h. Surface chart


i. Doughnut chart


j. Bubble chart


k. Radar chart


l. Treemap chart


m. Sunburst chart


n. Histogram chart


o. Box and Whisker chart


p. Filled map chart


q. Waterfall chart


r. Funnel chart




6. Advanced Formulas


a. Lookup functions – VLOOKUP, LOOKUP, HLOOKUP, XLOOKUP, and others


b. IF, IFS, IFERROR, SWITCH, AND, OR, ISBLANK, and others in the same family


c. INDEX, SUMPRODUCT, RANK


d. WORKDAY, NETWORKDAYS, DATEDIF, EOMONTH, and other special date formulas


e. TEXTJOIN, CONCAT, FIND, SEARCH, MID, LEFT, RIGHT and other text formulas




7. Planning and Strategy Tools in Excel


a. Goal Seek


b. Scenario Manager


c. Data Table


d. Solver




8. Integrating Excel with Data Science tools


a. R (using BERT)


b. Python (using xlwings)


c. Others




9. Excel VBA


a. Recording macro


b. Coding from Scratch


i. Userforms


ii. Modules


iii. User Defined Functions



Google Map

Other Interesting Events

upcoming in 6 days
May 25
upcoming in 6 days

Big Ideas Platform 2024: Information Technology and Behaviour Change

Shehu Musa Yar' Adua Centre
upcoming in 3 months
upcoming in 5 days
upcoming in 22 days
Jun 10
upcoming in 22 days

Blockchain Interoperability Hackathon #LBW2024

Radisson Blu Hotel, Lagos Ikeja, Isaa...
upcoming in 5 days
May 24
upcoming in 15 days
Jun 03
upcoming in 15 days

Financial Modeling and Financial Planning Training

URBIZEDGE LIMITED
upcoming in 11 days
May 30
upcoming in 11 days

PITCH DAY by CorpsAfrica/Ghana

CorpsAfrica/Ghana Office
upcoming in 5 days
May 23
upcoming in 5 days

#SheDISRUPTS - Book Tour

Bourbon House Cafe
RSVP
upcoming in 3 days
May 21
upcoming in 3 days

Introduction To Bitcoin

Pinocchio & La Piazza Restaurants (OSU)
upcoming in 3 days
May 22
upcoming in 3 days

Introduction to Artificial Intelligence and Robotics

19b Amore Street
    Top Locations
    Locations & Categories
    All Event Categories

    Sign In

    Discover great experiences with eGotickets


    Sign Up     Lost Password     Confirmation