Intended for:
For experts who daily use MS Excel for data processing, accounting, reports and analytics.
Challenge:
Inspirational stories about the endless possibilities of computing, machine learning and artificial intelligence are often shared on social media. However, the real day-to-day of data processing tends to be much more down-to-earth.
Preparing daily reports is taking up more and more of the day. Data from various sources must be collected manually. Files received from colleagues require extensive reformatting efforts. The people in the IT department are friendly, but sometimes you have to wait weeks and months for practical help from them.
The good news is that these situations can be prevented! True, more nuanced questions must be answered on the way to a solution. What to do with excessively large Excel files? How to fix broken links between different files forever? What if the amount of data exceeds Excel's limits? How to easily automate routine reporting?
We will find the answers to these and other questions in Elchin Jafarov's seminar "Power Excel for experts".
Benefits of the course:
Improved efficiency of participant data processing in MS Excel environment with:
-
skills to reduce manual work;
-
tools for automated data extraction from various sources;
-
the ability to optimize data analysis that improves decision-making;
-
reduce the risk of erroneous calculations and erroneous decisions;
-
development of the most effective solutions to your company's challenge;
-
insight into Power BI.
Course format:
-
During training, all participants must work with their own personal computer.
-
At least the 2013, but preferably the 2016 or MS Excel 365 version must be installed on the computers.
-
The training files and structure are stored in the cloud platform. After the lessons, the "solutions" of the tasks will be available on the platform. All files can be downloaded to your personal computer and used even after the course.
-
Participants' questions from real practice will be heard and practically solved.
Elchin Jafarov
-
M.Sc. (Stockholm University).
-
15+ years of experience in financial management at General Electric Group (USA) and Citadele Group (USA/Latvia), etc.
-
Teaches at Riga University of Economics (SSE Riga), Banking University/Swiss Business School, etc.
-
Qualification in mathematical statistics and financial mathematics (Institute of Actuaries of UK).
-
Uses Excel, PowerQuery, Power BI and R programming on a daily basis, manages R, VBA, DAX, C#, SQL, HTML/CSS, etc. technologies.
-
He has developed applications, databases, web-solutions, advises on the arrangement of data flows, automation and implementation of IT systems.
-
Conducted trainings at Swedbank, Twino, Latvenergo, UPB, 4Finance, ALTUM, Citadele Bank, Signet Bank, BlueOrange Bank, etc. teams.
Program:
2 online learning sessions | 8 academic hours
Session I
ACQUISITION OF DATA
4 academic hours
TRAINING CONTENT
Automation of data acquisition
-
Data from www, databases,Exlift, .csv, etc.
Data transformation
-
Power Query tools for working with data
Fast merging of data tables
-
Merge and Append
Transforming awkward data tables
-
Wide data long data
Top actions that are better to do withExcel Power Query
Data models and relationships
-
Preparation of a unified data model
EXERCISES
Exercises:
Data mining from IMDB
Transforming unwieldy data sources into handy tables
Joining multiple tables from different sources
What to do when SUMIFS can't help?
Lookup in automation
Session II
DATA ANALYTICS AND VISUALIZATION
4 academic hours
TRAINING CONTENT
InsightDAX in formulas
Basic formulas that make things easier than Excel
PowerPivot
-
ModernPivotTable functionality
Automatically renewing schedules
-
Pivot Charts and insightPower View
OLAP Cube functions
-
How to redoPivot Table to the desired format
EXERCISES
We analyze the operation of the veterinary clinic