Intermediate-Level Data Analytics and Visualization

Intermediate-level data analytics and visualization builds on foundational analytics skills by teaching you how to transform data into meaningful insights through effective charts, dashboards, and reporting.

 

Duration 6 Days – 42 hrs.

Overview

The Intermediate-Level Data Analytics and Visualization Training Course equips participants with practical skills to transform multiple sources of business data into reliable analysis, interactive dashboards, and decision-ready insights.

Building on basic knowledge of Microsoft Excel, SQL, and data visualization, the course focuses on more advanced data preparation, analysis, data modeling, dashboard development, and business intelligence techniques. Participants will learn how to identify decision-critical data, distinguish primary information from supporting details, establish meaningful measures and Key Performance Indicators, and evaluate data based on quality, relevance, and business impact.

The course covers advanced Excel functions, PivotTables, Power Query, intermediate SQL, relational data analysis, data modeling, and dashboard creation using Microsoft Power BI or a comparable business intelligence platform. Participants will also learn how to apply analytical frameworks, conduct trend and variance analysis, identify contributing factors, and communicate findings through clear visualizations, executive summaries, and data-driven recommendations.

Hands-on exercises will use realistic business scenarios from sales, finance, operations, human resources, customer service, project management, and other organizational functions.

 

Objectives

• Apply an organized data analytics process to business problems.
• Translate business questions into measurable analytical requirements.
• Identify primary, decision-critical, supporting, and contextual data.
• Evaluate datasets based on relevance, accuracy, completeness, consistency, timeliness, and reliability.
• Combine, clean, transform, and structure data from multiple sources.
• Use advanced Microsoft Excel functions for business analysis.
• Create advanced PivotTables, PivotCharts, and interactive Excel dashboards.
• Use Power Query to automate repetitive data preparation activities.
• Write intermediate SQL queries involving joins, subqueries, conditional logic, aggregations, and Common Table Expressions.
• Analyze related data from multiple database tables.
• Develop calculated measures, business metrics, and Key Performance Indicators.
• Understand the fundamentals of dimensional data modeling.
• Build interactive reports and dashboards using Microsoft Power BI or a similar business intelligence platform.
• Select appropriate visualizations based on the analytical question, data type, audience, and intended message.
• Conduct trend, variance, segmentation, contribution, and comparative analysis.
• Distinguish observations, findings, insights, conclusions, and recommendations.
• Identify significant business patterns, performance gaps, risks, and opportunities.
• Present analytical findings through clear visualizations and structured data storytelling.
• Develop practical, evidence-based recommendations for management and stakeholders.
• Apply responsible, ethical, and secure practices when handling organizational data.

 

Target Audience

 

• Data analysts and reporting analysts
• Business intelligence analysts
• Business analysts
• Management information system personnel
• Finance and accounting professionals
• Sales and marketing analysts
• Operations and supply chain personnel
• Human resources analysts
• Customer service and customer experience analysts
• Project managers and project coordinators
• Performance management personnel
• Supervisors and team leaders
• Management trainees
• Employees responsible for preparing recurring reports
• Professionals who create dashboards and management presentations
• Business users who need to improve their data analysis and visualization capabilities

 

 Prerequisites

 

• Basic understanding of data analytics and business intelligence concepts
• Working knowledge of Microsoft Excel
• Experience using basic formulas and functions
• Familiarity with sorting, filtering, tables, charts, and PivotTables
• Basic understanding of SQL, including:
  • SELECT
  • WHERE
  • ORDER BY
  • GROUP BY
  • Basic aggregate functions
• General experience working with business reports or operational data
• Basic understanding of business metrics and Key Performance Indicators
• Basic programming experience is not required.

 

Course Outline

 

Day 1: Analytics Foundations, Data Prioritization, and Data Quality

Module 1: Intermediate Data Analytics and Business Intelligence

 

• Review of data analytics concepts
• Descriptive, diagnostic, predictive, and prescriptive analytics
• Role of business intelligence in decision-making
• Difference between:
  • Data
  • Information
  • Finding
  • Insight
  • Conclusion
  • Recommendation
• The data analytics lifecycle
• Understanding business questions and decision requirements
• Translating business problems into analytical questions
• Defining the scope of an analysis
• Identifying users and stakeholders
• Establishing measurable objectives
• Determining required data sources
• Identifying expected analytical outputs
• Recognizing assumptions, constraints, and limitations

 

Practical Activity

Participants convert a general business concern into specific analytical questions, required data, metrics, and expected outputs.

 

Module 2: Identifying and Prioritizing Decision-Relevant Data

 

• Identifying primary and decision-critical data
• Supporting and supplementary data
• Contextual and reference data
• Relevant versus irrelevant information
• Internal and external data sources
• Operational, transactional, master, and reference data
• Quantitative and qualitative information
• Leading and lagging indicators
• Input, process, output, and outcome measures
• Dimensions and measures
• Metrics versus Key Performance Indicators
• Identifying critical business drivers
• Mapping data to business objectives
• Prioritizing data based on:
  • Decision relevance
  • Business impact
  • Urgency
  • Reliability
  • Availability
• Identifying missing data
• Avoiding information overload
• Developing a basic data requirements matrix

 

Practical Activity

Participants examine a business case and classify available information as primary, supporting, contextual, irrelevant, or missing.

 

Module 3: Data Quality Assessment and Data Profiling

 

• Importance of data quality
• Data-quality dimensions:
  • Accuracy
  • Completeness
  • Consistency
  • Validity
  • Timeliness
  • Uniqueness
  • Integrity
  • Relevance
• Data profiling techniques
• Examining data types and structures
• Identifying missing values
• Detecting duplicate records
• Recognizing inconsistent formats
• Identifying invalid values
• Identifying unusual values and outliers
• Evaluating data distributions
• Detecting relationship and referential-integrity issues
• Differentiating genuine outliers from data errors
• Developing validation rules
• Documenting data-quality findings
• Creating a data-quality checklist
• Establishing corrective actions

Hands-On Exercise

Participants profile a business dataset, identify quality issues, assess their impact, and recommend corrective actions.

Day 1 Outputs

• Defined business problem and analytical questions
• Initial stakeholder and decision requirements
• Data requirements matrix
• Classification of primary and supporting data
• Preliminary data-quality assessment
• Capstone project business case selection

 

Day 2: Advanced Data Preparation and Business Analysis Using Excel

Module 4: Advanced Data Preparation Using Microsoft Excel

 

• Structuring data for analysis
• Converting ranges into Excel tables
• Using structured references
• Managing dates, text, numbers, and categories
• Advanced sorting and filtering
• Using Advanced Filter
• Removing duplicates
• Managing blank and missing values
• Standardizing inconsistent data
• Splitting and combining columns
• Flash Fill techniques
• Text-cleaning functions:
  • TRIM
  • CLEAN
  • SUBSTITUTE
  • REPLACE
  • TEXTBEFORE
  • TEXTAFTER
  • TEXTSPLIT
• Text-extraction functions:
  • LEFT
  • RIGHT
  • MID
  • FIND
  • SEARCH
• Date and time preparation
• Converting text-based dates and numbers
• Error identification and correction
• Using Data Validation
• Using Conditional Formatting for data-quality checks
• Preparing data from multiple worksheets
• Creating reusable data-preparation templates

Hands-On Exercise

Participants clean, standardize, and restructure a multi-column business dataset using Excel.

 

Module 5: Advanced Excel Formulas for Business Analysis

 

• Relative, absolute, and mixed references
• Nested logical functions
• Using:
  • IF
  • IFS
  • AND
  • OR
  • NOT
  • IFERROR
  • IFNA
• Conditional aggregation:
  • SUMIFS
  • COUNTIFS
  • AVERAGEIFS
  • MAXIFS
  • MINIFS
• Advanced lookup techniques:
  • XLOOKUP
  • XMATCH
  • INDEX
  • MATCH
• Multi-criteria lookups
• Two-way lookups
• Approximate and exact matching
• Dynamic array functions:
  • FILTER
  • SORT
  • SORTBY
  • UNIQUE
  • SEQUENCE
• Date-analysis functions:
  • YEAR
  • MONTH
  • DAY
  • EOMONTH
  • EDATE
  • NETWORKDAYS
  • WORKDAY
• Ranking and percentile functions
• Calculating:
  • Growth rates
  • Contribution percentages
  • Variances
  • Running totals
  • Moving averages
  • Performance against targets
• Basic statistical functions
• Creating reusable formulas and analytical templates

Hands-On Exercise

Participants use advanced formulas to analyze sales, operational, financial, or workforce performance.

Day 2 Outputs

• Cleaned and standardized Excel dataset
• Reusable data-preparation template
• Advanced formula-based analytical report
• Lookup and conditional-calculation model
• Initial trends, variances, and performance findings

 

Day 3: Excel Dashboards and Automated Data Transformation

Module 6: Advanced PivotTables and Excel Dashboards

• Preparing data for PivotTable analysis
• Creating advanced PivotTables
• Grouping data by:
  • Date
  • Month
  • Quarter
  • Year
  • Numeric ranges
• Using multiple dimensions and measures
• Changing value calculations
• Displaying values as:
  • Percentage of total
  • Difference from
  • Percentage difference
  • Running total
  • Rank
• Creating calculated fields
• Using report filters
• Using slicers and timelines
• Connecting slicers to multiple PivotTables
• Creating PivotCharts
• Developing KPI summary cards
• Applying Conditional Formatting
• Creating actual-versus-target comparisons
• Designing an interactive Excel dashboard
• Establishing visual hierarchy
• Improving dashboard readability
• Adding definitions, notes, and refresh dates
• Avoiding common dashboard-design problems

Hands-On Exercise

Participants create an interactive Excel dashboard using PivotTables, PivotCharts, slicers, and KPI indicators.

 

Module 7: Data Transformation and Automation Using Power Query

 

• Introduction to Extract, Transform, and Load processes
• Role of Power Query in data preparation
• Connecting to:
  • Excel workbooks
  • CSV files
  • Text files
  • Folders
  • Databases
  • Web-based tables, when permitted
• Understanding the Power Query Editor
• Data types and column profiling
• Removing and selecting columns
• Filtering and sorting rows
• Renaming and reordering columns
• Replacing values
• Managing errors and null values
• Splitting and merging columns
• Extracting text, numbers, and dates
• Creating conditional columns
• Creating custom columns
• Grouping and aggregating data
• Pivoting and unpivoting columns
• Appending multiple tables
• Merging related tables
• Understanding join types
• Combining multiple files from a folder
• Creating query parameters
• Refreshing transformed data
• Documenting transformation steps
• Improving query efficiency

Hands-On Exercise

Participants combine, clean, transform, and consolidate multiple monthly files into a refreshable analytical dataset.

Day 3 Outputs

• Interactive Excel dashboard
• KPI summary cards
• Actual-versus-target analysis
• Automated Power Query workflow
• Consolidated and refreshable analytical dataset
• Documented data-transformation steps

 

Day 4: Intermediate SQL and Data Modeling for Business Intelligence

Module 8: Intermediate SQL for Data Analytics

• Review of SQL query structure
• Using column and table aliases
• Filtering with multiple conditions
• Working with:
  • IN
  • BETWEEN
  • LIKE
  • CASE
  • COALESCE
  • NULLIF
• Handling null values
• String functions
• Date and time functions
• Mathematical functions
• Aggregate functions
• Grouping across multiple dimensions
• Filtering aggregated results using HAVING
• Joining related tables:
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
• Understanding one-to-one and one-to-many relationships
• Avoiding duplicate results caused by incorrect joins
• Using self-joins
• Combining query results using:
  • UNION
  • UNION ALL
• Subqueries
• Correlated and non-correlated subqueries
• Common Table Expressions
• Introduction to window functions:
  • ROW_NUMBER
  • RANK
  • DENSE_RANK
  • LAG
  • LEAD
  • Running totals
• Creating reusable analytical queries
• Exporting query results
• Basic SQL query validation and performance considerations

Hands-On Exercise

Participants retrieve and analyze data from multiple related tables using joins, conditional logic, subqueries, Common Table Expressions, and basic window functions.

 

Module 9: Data Modeling for Business Intelligence

 

• Importance of data modeling
• Understanding tables, fields, and relationships
• Transaction and master data
• Fact and dimension tables
• Dimensional modeling
• Star-schema concepts
• Snowflake-schema overview
• Identifying the appropriate level of detail
• Understanding data granularity
• Creating relationships between tables
• One-to-one and one-to-many relationships
• Active and inactive relationships
• Understanding filter direction
• Avoiding many-to-many relationship problems
• Using a dedicated calendar table
• Separating measures from descriptive attributes
• Calculated columns versus measures
• Basic data-model validation
• Common data-modeling mistakes

Practical Activity

Participants design a basic star-schema model for a sales, finance, operations, or human-resources reporting scenario.

Day 4 Outputs

• Reusable intermediate SQL queries
• Joined and summarized business datasets
• Ranking and running-total calculations
• Initial fact-and-dimension table design
• Basic star-schema data model
• Data-model validation checklist

 

 Day 5: Power BI Analysis, Interactive Dashboards, and Business Analysis

Module 10: Introduction to Power BI Data Analysis

 

• Overview of the Power BI environment
• Power BI Desktop interface
• Connecting to Excel, CSV, and database sources
• Using Power Query in Power BI
• Loading transformed data
• Creating table relationships
• Reviewing the model structure
• Creating calculated columns
• Introduction to Data Analysis Expressions
• Understanding row context and filter context
• Creating measures using:
  • SUM
  • COUNT
  • DISTINCTCOUNT
  • AVERAGE
  • DIVIDE
• Using CALCULATE
• Creating percentage and ratio measures
• Creating actual-versus-target measures
• Creating year-to-date and period-comparison measures
• Introduction to basic time intelligence
• Formatting and organizing measures
• Validating Power BI calculations

Hands-On Exercise

Participants create a data model and develop calculated measures for a business performance report.

 

Module 11: Interactive Data Visualization and Dashboard Development

 

• Understanding reports, dashboards, and scorecards
• Defining dashboard users and requirements
• Selecting meaningful KPIs
• Matching visualization types to analytical questions
• Visualizing:
  • Comparisons
  • Trends
  • Composition
  • Distribution
  • Relationships
  • Geographic information
  • Performance against target
• Creating:
  • Tables and matrices
  • KPI cards
  • Bar and column charts
  • Line and area charts
  • Combination charts
  • Scatter plots
  • Treemaps
  • Waterfall charts
  • Maps, when appropriate
• Adding slicers and filters
• Creating drill-down hierarchies
• Using drill-through pages
• Creating report tooltips
• Applying conditional formatting
• Establishing page navigation
• Designing detailed and executive report pages
• Applying visual hierarchy
• Using titles, subtitles, labels, and annotations
• Applying consistent formatting
• Improving report accessibility
• Testing report interactions
• Avoiding misleading or unnecessary visuals

Hands-On Exercise

Participants develop an interactive business intelligence dashboard with multiple pages, filters, drill-down features, and KPI indicators.

 

Module 12: Business Analysis Techniques

 

• Establishing baselines and comparison points
• Comparing actual versus target performance
• Period-over-period analysis
• Year-over-year and month-over-month analysis
• Variance analysis
• Trend analysis
• Contribution analysis
• Pareto analysis
• Segmentation analysis
• Ranking and prioritization
• Cohort and category analysis
• Identifying concentration and distribution patterns
• Identifying exceptions and anomalies
• Basic root-cause analysis
• Using the Five Whys
• Applying cause-and-effect thinking
• Correlation versus causation
• Distinguishing symptoms from possible causes
• Identifying performance drivers
• Recognizing business risks and opportunities
• Documenting assumptions and analytical limitations

Practical Activity

Participants examine an underperforming business area and identify its most significant contributing factors.

Day 5 Outputs

• Power BI data model
• Calculated measures and KPIs
• Multi-page interactive dashboard
• Drill-down and drill-through report
• Trend, variance, contribution, and segmentation analysis
• Identification of major performance drivers
• Draft capstone dashboard

 

Day 6: Actionable Insights, Executive Storytelling, Governance, and Capstone Presentation

Module 13: Developing Useful and Actionable Insights

 

• Moving from data to decision-ready insights
• Difference between observation and insight
• Difference between finding and recommendation
• Evaluating whether a finding is significant
• Connecting analysis to business objectives
• Identifying who is affected
• Determining and quantifying business impact
• Applying the “What, So What, and Now What” framework
• Structuring an analytical insight:
  • What happened?
  • Where did it happen?
  • When did it happen?
  • Who or what was affected?
  • Why might it have happened?
  • What is the business implication?
  • What action should be considered?
• Developing evidence-based recommendations
• Prioritizing recommendations based on:
  • Business value
  • Urgency
  • Cost
  • Effort
  • Risk
  • Feasibility
• Avoiding unsupported conclusions
• Communicating uncertainty
• Documenting assumptions and limitations

Practical Activity

Participants convert charts and analytical results into structured insights and management recommendations.

 

Module 14: Data Storytelling and Executive Presentation

 

• Purpose of data storytelling
• Understanding the audience
• Identifying the main message
• Structuring an analytical presentation
• Building a logical narrative
• Presenting the business context
• Explaining the analytical question
• Selecting supporting evidence
• Highlighting key findings
• Presenting implications and recommendations
• Using visual emphasis effectively
• Writing insight-driven chart titles
• Creating executive summaries
• Presenting detailed analysis to technical users
• Presenting concise findings to management
• Managing technical terminology
• Explaining assumptions and limitations
• Responding to questions and objections
• Delivering concise and confident presentations

Presentation Preparation Activity

Participants prepare an executive summary and management presentation based on their dashboard and analytical findings.

 

Module 15: Data Governance, Privacy, and Responsible Analytics

 

• Introduction to data governance
• Data ownership and stewardship
• Data classification
• Confidential, sensitive, and personal information
• Role-based access and authorized use
• Data privacy principles
• Secure storage and sharing of reports
• Protecting source files and exported data
• Maintaining calculation transparency
• Documenting data sources
• Maintaining version control
• Avoiding selective or misleading reporting
• Identifying potential data and analytical bias
• Responsible use of automated analytics
• Human validation of analytical results
• Establishing a report review and approval process

Practical Activity

Participants assess the governance, privacy, and ethical risks associated with a sample dashboard.

 

Module 16: Final Data Analytics and Visualization Capstone Project

 

Participants complete an end-to-end intermediate analytics project involving:

• Understanding the business problem
• Defining analytical questions
• Identifying primary and supporting data
• Creating a data requirements matrix
• Assessing data quality
• Cleaning and transforming the data
• Combining multiple data sources
• Retrieving related information using SQL
• Developing a basic data model
• Creating calculated metrics and KPIs
• Conducting trend, variance, contribution, and segmentation analysis
• Building an interactive dashboard
• Identifying significant findings and performance drivers
• Developing decision-ready insights
• Preparing evidence-based recommendations
• Presenting the findings to management or stakeholders

Final Presentation Activity

Each participant or group presents:

• Business problem and analytical objectives
• Primary and supporting data
• Data-quality findings
• Data-preparation approach
• SQL analysis
• Data model
• Metrics and KPIs
• Interactive dashboard
• Trends and performance drivers
• Key business insights
• Recommended actions
• Assumptions and limitations

Day 6 and Final Course Outputs

• Completed interactive business intelligence dashboard
• Structured analytical findings
• Decision-ready business insights
• Prioritized recommendations
• Governance and privacy assessment
• Executive summary
• Management presentation
• Completed capstone project

 

Recommended Software and Tools

 

• Microsoft Excel 2019, 2021, or Microsoft 365
• Microsoft Power Query
• Microsoft Power Pivot, when available
• Microsoft Power BI Desktop
• Microsoft SQL Server, MySQL, or PostgreSQL
• SQL Server Management Studio or another SQL client
• Microsoft PowerPoint