Data Analytics in Microsoft Excel (A Complete Course)

Name: Data Analytics in Microsoft Excel (A Complete Course)
Rating: 4.7 (20 reviews)

Master Data Analytics in Microsoft Excel – Learn Essential Skills and Advanced Techniques for Real-World Data Analysis

Created byMuhammad Jamilu

Last updated 1/2026

English

What you'll learn

Learn to navigate Excel's interface and use essential functions for data analysis.
Import, clean, and preprocess raw data using Excel tools and functions.
Master complex formulas like VLOOKUP, INDEX, MATCH, and IF statements for data analysis.
Create charts and PivotTables to visualize trends and insights in your data.
Perform descriptive statistics and apply hypothesis testing, correlation, and regression analysis.
Use tools like What-If analysis, Power Query, and automation with Macros and VBA.
Build financial models and time series forecasting models to predict trends.
Design interactive dashboards for reporting with Excel's visualization tools.
Integrate Excel with external data sources and build data models using Power Pivot.
Apply skills to real-world projects and case studies across various industries.

Course content

11 sections • 67 lectures • 21h 53m total length

Course Files2:20
Download the 119 MB course files zip and extract it to access all materials. Navigate the module folders to find the files you need to practice along.
Meaning, Types and Roles in Data Analytics12:32
Understanding the Data Analytics Process7:21
Explore the data analytics process in Excel, from data collection and cleaning to transformation, modeling, analysis, visualization, and insights, using pivot tables, power pivots, charts, and dashboards.
Excel Features for Data Analysts: An Introduction5:59
Setting Up Excel for Efficient Data Analytics3:48
Install a recent version of Microsoft Excel, organize data in dedicated workbooks and worksheets, and use add-ins like Power Query, Solver, and Data Analysis Toolpak to enhance analytics.

Importing Data into Excel (CSV, Text Files, Web Data and others)22:55
Identifying and Handling Missing Data (NULL, Blanks)7:17
Identify and fix missing data in Excel by converting to a table, locating blank cells, diagnosing if errors are from data collection or calculation, and correcting or removing affected rows.
Removing Duplicates and Unnecessary Information57:33
Data Validation and Error Checking46:58
Using Text Functions for Data Cleaning (e.g., TRIM, CLEAN, CONCATENATE)22:38
Learn to clean data in Excel with text functions like trim, clean, substitute, replace, concat, left, and right to standardize customer records, fix IDs, and format currency and phone numbers.
Data Formatting for better presentation17:17
Format and present data effectively in Excel by creating tables, correcting number formats, using text to column and transpose, and grouping data for a clean, navigable presentation.
Project 2.1: Clean and Prepare a Real-World Sales Data Set3:24
Clean and prepare a real-world sales data set in Excel by applying data cleaning techniques to correct order IDs, dates, customer IDs, and other fields.

Introduction to Power Query5:34
Explore data transformation in Excel using the Power Query editor, launching Power Query and navigating the home, transform, add column, and view menus.
Data Preparation (Home Tab)1:13:29
Master data preparation in Microsoft Excel by using home tab tools to clean, organize, and analyze datasets for actionable data analytics.
Data Transformation (Transform Tab)1:05:17
Adding Custom Columns (Add Column Tab)25:00
Explore Power Query's add column tools to create new columns from patterns, formulas, and conditions, including column from example, custom column, conditional column, index, and invoke custom function.
Visualization & Diagnostics (View Tab)12:02
Explore the view tab icons in Power Query to control query settings, the formula bar, monospace, whitespace visibility, column quality, distribution, and query dependency.
Advanced Automation51:51
Master advanced data cleaning and transformation in Power Query using messy CSV data, including correcting dates, times, and text and handling nulls in Excel.
Project 3.1: Transform a Product Sales Dataset to Create Time-Based Metrics1:17

Introduction to Formulas and Functions in Excel9:00
Learn how formulas and functions power data analysis in Excel, distinguish between formulas and functions, and navigate the function library to clean, transform, and analyze data.
Key Functions for Data Analysis1:04:23
Master key functions for data analysis in Microsoft Excel to enhance your data analytics workflow. Apply practical techniques from this complete course to transform data into insights using Excel.
Text Functions for Data Cleaning30:10
Date and Time Functions14:30
Explore date and time functions in Excel, including now, today, year, month, day, weekday, and date value, and apply network days to measure working days between dates.
Error Handling and Troubleshooting Functions19:57
Learn to handle and troubleshoot Excel errors using iferror, iserror, isna, isnumber, isblank, islogical, and the error type function. Diagnose NA, div/0, and other errors with practical, example-driven guidance.
Advanced Functions for Data Analysis14:11
Project 4.1: Using Functions to Analyze and Summarize Sales Data5:44
Learn to analyze sales data in Excel using formula and function, including unique and sumif, to identify the branch location with the highest total sales.

Introduction to Data Modelling in Excel17:48
Power Pivot Basics5:25
Creating Model and Building Relationship Between Tables22:32
Establishing a Connection between Power Pivot with PivotTables8:41
Connect the Power Pivot data model to PivotTable in Excel to enable cross-table analysis and create PivotTables from the model.
Using PivotTables and PivotCharts for Analysis25:21
Explore how to use power pivot to build a data model, create pivot tables and pivot charts, and analyze revenue and sales across products, customers, and order status by period.
Creating Measures and KPIs37:00
Automating Analysis with Power Pivot and Power Query23:53
Explore automating analysis with Power Query, Power Pivot, pivot tables, and pivot charts, using DAX and bridge tables to connect product, sales, and region data for insights.
Project 5.1: Build a Sales Data Model Using Power Pivot6:01
Analyze customer behavior to identify revenue contributors and repeat customers, compare average order size by segment, and calculate churn rate using a Power Pivot data model and Power Query.

Introduction to PivotTables26:21
Master pivot tables in Excel to analyze data by dragging fields into values, rows, columns, and filters, turning raw data into insightful summaries.
Data Analysing with PivotTables25:28
Learn to create and analyze pivot tables in Microsoft Excel to identify top products, branches, and channels, and to track sales trends, returns, and payment methods.
Managing pivot table design tab10:18
Filtering and Slicing PivotTable Data13:54
Learn to create slicers and timelines to filter pivot table data, connect multiple slicers to pivot tables, and control which fields they affect.
Creating Calculated Fields in PivotTables9:27
Creating PivotCharts from PivotTables12:17
Project 6.1: Analyze Sales Data Using PivotTables and PivotCharts5:03
Analyze sales data by building a data model with Power Pivot, importing tables, establishing relationships, creating pivot tables and pivot charts, and cleaning data with Power Query.

Introduction to Excel Charts and Graphs29:16
Creating and Customizing Bar, Line, and Pie Charts46:01
Create and customize pivot charts and a variety of bar, line, and pie charts in Excel, refine dashboards with data labels, formatting, and storytelling.
Advanced Charting Technique with Combo Charts5:04
Learn to create and customize combo charts in Excel by combining column and line charts, adding data series, managing axis priority, and switching chart types for product versus location.
Using Conditional Formatting for Visual Insights5:37
Creating Dynamic Charts with Excel Tables and Named Ranges11:08
Create dynamic charts in Excel using tables that auto expand and named ranges for locations and values. Explore pivot table and named ranges to keep charts updated as data grows.
Visualizing Trends with Sparklines3:40
Visualize trends in Excel with sparklines by inserting line or column sparklines, selecting data ranges, and editing styles to highlight highs and lows for dashboards.
Project 7.1: Visualize Company Sales Performance Using Multiple Chart Types1:28
Learn to visualize company sales performance in Excel by building pivot charts from a prepared pivot table, reuse the same data across modules, and create visuals for a dashboard.

Introduction to Excel Dashboards and Their Importance9:26
Designing an Effective Dashboard Layout20:42
Design an effective Excel dashboard by configuring a distraction-free 16:9 layout, creating a background with shapes and shadows, and a heading such as Cardinal Coca Cola sales dashboard.
Using PivotTables and PivotCharts in Dashboards34:43
Arranging Pivot Charts and other Visuals in Dashboard25:30
Enhancing Dashboards with complementing Colours6:46
Apply a dominant color with complementary shades and a restrained two-color palette to create simple, readable dashboards with focused visuals and minimal distractions.
Present Insight in Dashboard8:29
Project 8.1: Build a Sales Performance Dashboard for Executive Reporting3:02
Build a sales performance dashboard by leveraging a pivot table and pivot chart, replicate instructor examples, and design a report-ready dashboard in Excel with Word integration for executive reporting.

Introduction to Reporting Techniques3:35
Structure of Reports4:39
Creating First Draft of Report Based on Analysis in Excel14:21
Linking Excel and Microsoft Word for Efficient Reporting Techniques7:59
Presenting Final Draft of Report3:20
Finalize your report in Excel by expanding charts and refining headers, footers, and backgrounds using the sales report template, then present insights and recommendations clearly.
Project 9.1: Prepare a Sales Performance Report for a Business1:06

Using Data Analysis ToolPak for Statistical Analysis5:24
Activate and use the data analysis tool pack in Excel to generate descriptive statistics, then explore diagnostic and predictive analytics with pivot tables, dashboards, and reports.
Performing Regression Analysis in Excel18:40
Time Series Forecasting with Excel14:06
Explore time series forecasting in Excel by transforming quarterly sales data, building pivot tables, and using Forecast.ETS or forecast sheet to predict future quarterly sales.
Scenario Analysis and What-If Analysis Tools (e.g., Goal Seek, Solver)35:04
Project 10.1: Conduct a Financial Forecasting Analysis Using Regression2:46
Learn to conduct a forecasting analysis in Excel with regression, goal seek, data tables, and solver. Use real GDP as the Y variable with four X variables and interpret results.

Requirements

A basic understanding of computer operations.
Microsoft Excel installed on your computer (Excel 2016 or later recommended).
No prior experience in data analysis is required; the course is suitable for beginners.
A willingness to learn and practice Excel functions and features.
Access to the internet to watch video lessons and complete exercises.
A desire to apply Excel skills to real-world data analysis problems.

Description

Data Analytics in Microsoft Excel (A Complete Course) offers a practical bridge between theory and world analysis. As one of the most accessible tools for handling data, Excel enables professionals to transform raw information into actionable insights with speed and precision. The course covers a full spectrum of techniques, from data cleaning and validation to advanced calculations, modelling, and visualization.

Learners master essential functions, logical and lookup formulas, and the power of PivotTables to summarize complex datasets. They also explore data transformation with Power Query and data modelling with Power Pivot, opening avenues for scalable analysis without leaving the familiar interface. Practical exercises emphasize reproducibility, documenting assumptions, and building dashboards that communicate findings clearly to stakeholders. By integrating charts, conditional formatting, and storytelling through data, participants develop the confidence to support decisions with evidence rather than intuition.

The curriculum is designed for diverse users, including business professionals, analysts, and students seeking a competitive edge. Upon completion, graduates possess a robust toolkit for reporting, forecasting, and decision support, all within a widely adopted platform. Excel remains a versatile foundation for data analytics, adaptable to evolving data challenges and opportunities. This course empowers professionals to extract value from data every day, confidently

Who this course is for:

Beginners who want to learn data analysis using Microsoft Excel from scratch.
Professionals looking to enhance their data analytics skills for career growth.
Students or recent graduates who want to gain practical skills in Excel for data analysis.
Entrepreneurs and small business owners seeking to make data-driven decisions using Excel.
Anyone interested in improving their Excel proficiency for business, finance, or research purposes.
Data analysts or aspiring data analysts who want to master Excel for advanced data analysis techniques.

Data Analytics in Microsoft Excel (A Complete Course)

What you'll learn

Explore related topics

Course content

INTRODUCTION TO DATA ANALYSIS IN EXCEL5 lectures • 32min

DATA PREPARATION AND CLEANING7 lectures • 2hr 58min

TRANSFORMING DATA IN POWER QUERY7 lectures • 3hr 55min

BASIC ANALYSIS USING FORMULA AND FUNCTIONS7 lectures • 2hr 38min

DATA MODELLING IN EXCEL8 lectures • 2hr 27min

ADVANCE DATA ANALYSIS USING PIVOTTABLES7 lectures • 1hr 43min

VISUALIZATION TECHNIQUES IN EXCEL7 lectures • 1hr 42min

BUILDING DASHBOARDS IN EXCEL7 lectures • 1hr 49min

REPORTING IN EXCEL6 lectures • 35min

ADVANCED DATA ANALYSIS TECHNIQUES IN EXCEL5 lectures • 1hr 16min

Requirements

Description

Who this course is for: