Advanced Analytics using Excel

ADVANCED ANALYTICS USING EXCEL

Designed for learners who wish to design storyboards and data displays to communicate key insights, trends and findings derived from data, incorporating new and advanced visualization techniques and messaging aligned to business needs and audience.

Open to practitioners from any background who have an interest to learn advanced excel functions, visualizations and data analysis.

Basic knowledge of Microsoft Excel is required to attend this workshop

What you'll learn

The objective of the workshop is to familiarize the participants with the fundamentals of how to manage, transform and analyze data using the various functions and the power of Microsoft Excel. Excel is a very widely used tool with many organizations and departments and being able to produce a quick analysis is an asset. Along with Excel being a data source and tool to manipulate data, nowadays databases are becoming very popular and part of every organization. Hence, learning and knowing the basics of SQL (Structured Query Language) has become very important. This workshop also takes you through the nuances and fundamentals of how SQL can be used with databases to ingest, manipulate and store data.

About this course

With the basic knowledge of Excel and a love for analysis, learn how this tool combined with advanced techniques can help you build analytics systems, data pipelines and interactive reports for stakeholder analysis.

Learn In No Time

Course Duration

Hours

Days

Have a look

Course Plan

MODULE 1: LOGICAL AND AGGREGATION FUNCTIONS IN EXCEL

In this module the learners will learn about various data transformation functions and will be able to apply the relevant function based on their use case with their own data.

Logical functions: SUMIF, AVERAGEIF, IFS, etc

- Aggregation functions: SUM, AVERAGE, AGGREGATE, etc
- Additional functions for data transformation

MODULE 2: LOOKUP FUNCTIONS

In this module the learners will learn about various lookup functions as well as on how to handle errors.

VLOOKUP
HLOOKUP
IFERROR

MODULE 3: ADVANCED LOOKUP FUNCTIONS

In this module the learners will learn XLOOKUP, FUZZY LOOKUP, partial lookup using wildcards as well as lookup using INDEX and MATCH. The learners will be able to understand the usage of the various lookup functions and its applications.

MODULE 4: DATE FUNCTIONS

In this module the learners will get comfortable with different date functions like NETWORKDAY.INTL, WORKDAY.INTL and how to handle data related to the calendar.

MODULE 5: WORKING WITH TEXT

In this module the learners will learn to apply different text functions for data transformation.

Split columns with delimiters
Text functions: TEXTJOIN, CONCAT and others

MODULE 6: SUBTOTAL & GROUPING

This module will allow the learners to learn powerful features to summarize and organize the data for analysis.

MODULE 7: PIVOT TABLES

By the end of this module, the learners will get a hands-on experience working on pivot table and the various components of pivot tables. Additionally, the learners will also learn how to perform quick analysis.

Data summarization using pivot tables
Quick analysis using sparkline charts
Top N Filtering using LARGE Function

MODULE 8: DATA MODELLING & VISUALIZATION - POWER QUERY & CHARTS

By the end of this module, the learners will get an understanding of ETL process, get comfortable in extracting data from multiple sources both live and static, cleaning them in power query and finally loading them in Excel. The learners will also learn to create an interactive dashboard for detailed analysis.

MODULE 9: ADVANCED DATA ANALYTICS

By the end of this module, the learners will get a hands-on experience working on advanced analytics features.