Course Detail
Big Data Analysis, Power Query, Power Pivot and VBA in Excel
The training is aimed at already proficient Excel users who work in report/analysis heavy roles dealing with large amount of raw data (even spanning 2 to 200 million rows) and have Excel 2016 or above.
If you qualify for this, email team@urbizedge.com with details of which industry you work, your role and your most advanced use of Excel. Optionally, we will reply with the details and schedule for the class. We do this because from experience too many people misjudge their needs — going for what sounds fancy and new rather than what would really give them the practically useful skill they need. Thanks for understanding and complying.
More Details
Training Highlights
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.
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.
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.
The Deep End of Formulas
Array formulas, SUMPRODUCT (the complex use of it), INDEX, SWITCH, IFS, etc.
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.
VBA (recording macros)
Break into automating repetitive tasks in Excel
VBA (coding from scratch)
Start doing the heavy lifting with VBA coding from scratch.
Training Outline
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 analyse 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
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
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
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
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
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
Planning and Strategy Tools in Excel
- a. Goal Seek
b. Scenario Manager
c. Data Table
d. Solver
Integrating Excel with Data Science tools
a. R (using BERT)
b. Python (using xlwings)
c. Others
Excel VBA
a. Recording macro
b. Coding from Scratch
i. Userforms
ii. Modules
iii. User Defined Functions
Increase your Productivity
Get the special edge you need. Register for our premium online classes.