Learning Path
Business Intelligence
Skill Level
Advanced
Application Deadline
Closed
Start Date
To be Announced
Instructor-Led Live Session
Live Session Recording
Expert Support
Case Studies & Projects
Dedicated Resource Portal
Limited slots available
04 weeks (Sat & Sun)
04:00 PM to 06:00 PM
(08 Sessions)
Data analysis is THE skill you must have to thrive in today’s workforce.
This course concentrated on the core concepts and techniques of data analysis in Excel, allowing you to turn your data into actionable insights. You will learn advanced Excel formulas and tools from VLOOKUP to Pivot Tables, create graphs and visualizations that can summarize critical business insights, and utilize Data Analysis ToolPak for descriptive and regression analysis. You will be also introduced to Excel BI Tools including Power Query and Power Pivot.
Gain a competitive advantage in the job market by learning the fundamentals of data analysis and visualization in Excel.
What will you learn?
In this course you will learn:
- How to prepare raw data for analysis.
- How to use Excel functions, both simple and complex
- How to perform data analysis using the PivotTable function
- How to Present Your Insights Using Pivot Charts
- How to highlight areas of your data with Conditional Formatting
- How to Forecast
- How to fill data gaps with Goal Seek, Scenario Manager, and Solver
What do you get in the training?
You will get
- Exercise and Project files
- Pre and Post Assessment
- Quizzes and Homework assignments
- 1-month access to the resource portal
- HD Recording of all live sessions
Certificate of Completion
You will be awarded a course completion E-Certificate by TechTAR Solutions after fulfilling the course requirements.
- Excel users who are looking for a transition into a business intelligence role.
- Analysts who are using Excel to analyze large datasets (>1mm rows), connect to external sources, or build relational data models.
- Candidates striving to learn Excel Data Analytics
- Professionals regularly working with a bulk of Business Data
- Anyone seeking to explore Excel’s business intelligence
- An Intermediate level understanding of how to use Microsoft Excel
- Classes will be interactive throughout & LIVE on Zoom. You would be able to ask questions & share views with the Technical Assistant, Trainer & class other participants.
Recommendations:
- Computer with internet access, a webcam, microphone, and Zoom App.
- Microsoft Excel 2016+ or Office 365 (ideally for PC/Windows)
Data Analysis Workflow
- Understand the Business Case
- Define the KPIs
- Identify Data Sources
- Data Prep and Understanding
- Data analysis and visualization
- Develop data-driven insights
- Measure, test, and optimize
Data Prep and ETL
- Overview of Excel Power Query
- Excel Power Query
- Why Power Query?
- Power Query Interface
- Query Editor – Ribbons
- Loading Data with Power Query (Get & Transform)
- Importing External Data from Text File
- Importing Data From Excel Spreadsheet
- Importing Data from Folder
- Data Profiling
- Column quality
- Column Distribution
- Column Profile
- Proper Data Set Structure
- 6 steps for preparing Proper Data Set
- Preparing Data for Analysis
- Power Query Transformation
- Pivoting / Unpivoting
- Merge / Append
- Formatting Data as an Excel Table
- Define a Table
- Excel Table Styles
- Convert tables to cell ranges
- Excel Table Name Rules
Excel Formulas for Merging and Lookup Data
- VLOOKUP (Exact)
- VLOOKUP (Approx)
- VLOOKUP with Data that Expands
- HLOOKUP
- VLOOKUP and MATCH
- INDEX and MATCH
- XLOOKUP
- Multiple lookup with DSUM
- Creating Ranking with SMALL and LARGE
- Data validation list with Lookups
- Pro Tip: Dependent Data validation list
Excel Formulas to Make Better Decisions
- IF Function
- Working with Nested Ifs
- IFS Function
- Error handling with IFERROR and IFNA
- Using MAX and MIN instead of the IF Function
- Using SUMIFS and COUNTIFS
- SUMPRODUCT
Analyzing Data with Pivot Table
- An introduction to PivotTables
- Creating a PivotTable
- Formatting a PivotTable
- Summarizing Data
- Calculations in PivotTables
- Grouping Fields
- Using Slicers to Filter information
Visualizing Data with Pivot Charts
- Creating a Pivot Chart
- Line Chart
- Bar Chart
- Pie Chart
- Column Chart – Clustered
- Column Chart – Stacked
- Column Chart – 100% Stacked
- Scatter Plot
- Area Chart
- Histogram
- Formatting a Pivot Chart
- Using Sparklines
- Custom and Conditional Formatting
- A Basic Interactive Dashboard
10 Techniques for Data Analysis
- Key Metrics
- Comparison Analysis
- Trend and Seasonality Analysis
- Ranking Analysis
- Contribution Analysis
- Pareto Analysis
- Frequency Analysis
- Correlations
- Variance Analysis
- Root Cause Analysis
Analyzing Data with Power Pivot
- Understanding Power Pivot
- Importing Data into Power Pivot
- Creating Relationships between Tables in the Model
- Managing Relationships
Introduction to Statistics
- A brief overview of Statistics
- Activation of Data Analysis ToolPak Add-in
- Mean, Median, and Mode
- Percentile and Percentile Rank
- Frequency Distributions
- Standard Deviation and Variance
- Correlation
- Linear Regression
Forecasting
- Forecast Sheets
- Slope – Intercept
- The Forecast Function
WhatIF Analysis
- Goal Seek
- Data Tables
- Scenario Manager
- Solver
Thinking Beyond Excel Formulas
- Reduce spreadsheet risk
- Building a client-ready model
- Make your model faster
Case Studies - Solving problem on paper
In this section, We will solve a case study using the analytical problem-solving process.
Individual Price
PKR 8,000
Per Person
Total charges for complete training
Group of Two
PKR 7,200
Per Person
Avail 10% off on a Group of two
Group of Three
PKR 6,800
Per Person
Avail 15% off on a Group of three
Group of Four or more
PKR 6,400
Per Person
Avail 20% off on a Group of four or more
Course Reviews
We have engaged TechTAR Solutions for a number of session in Excel and Power BI for our employees. Sohail Merchant is wonderful to deal with when making the bookings and the high level of experience the trainers hold is evident. We will definitely use them again in the future.!
I was tasked with seeking out a company to assist us in recreating our department’s appraisal Matrix and after meeting with TechTAR Solutions’ consultant, I knew they were the right fit to help get the job done! Their expertise and understanding of our needs made the whole process so simple.
Sohail Merchant from TechTAR Solutions’ [ explained the theory very well, also gave us sufficient room to ask questions and clarify our queries. Enough resources were provided to read and increase our knowledge on individual capacity..
Lots of useful information, Ideas & strategies with many practical examples. I learned a lot throughout the course & implementing it in my business. Sohail Merchant from TechTAR Solutions’ expertise gave me an amazing learning experience. Thank you.
The professionalism and Personalized service that we got from TechTAR Solutions was unlike any other company we worked with and they made us feel so comfortable by their natural communication and let’s do it attitude. Incredibly trustworthy and high value! Thankyou!
Please keep developing the products and solutions to make our lives more easier and work to be more focused efficient targeted and productive. Appreciate the contributions of the entire community in this.