The Complete Data Analytics Course in Excel

Name: The Complete Data Analytics Course in Excel
Rating: 4.4 (1108 reviews)

A complete data analysis course using the tools you already have.

Created byMichael Parent

Last updated 5/2024

English

English [Auto],

What you'll learn

Data Analytics
Data Visualization
Hypothesis Testing
Statistics
Forecasting
Excel
Regression
Monte Carlo Simulation
Linear Programming
Pivot Tables
Business Intelligence
Excel Analytics

Course content

6 sections • 71 lectures • 15h 4m total length

Navigation Part 111:24

Get more resources at: www.sixsigma-consulting.com
Follow on LinkedIn: https://www.linkedin.com/company/michaelparentconsulting/
Like and subscribe on YouTube: https://www.youtube.com/channel/UCDLNIjI1OQulldhfkZibX7A
Also consider my other course on Udemy: https://www.udemy.com/course/control-charts/?referralCode=E6197D5696854010A842
Navigation Part 29:25
**The file that used in this lecture is from the Lecture 1**
Basic Excel Formulas13:29
Learn how to use basic arithmetic and built-in Excel formulas to analyze data, including sum, countA, average, standard deviation, max, min, median, and mode.
Structuring Data in Excel6:54
Intermediate Excel Functions13:50
Descriptive Statistics Part 17:07
Descriptive Statistics Part 211:39
**The file used in this lecture is from the Lecture 6**
Module #1 Practice & Solutions25:29
The video of this solution contains an error: Problem #2 B) the correct result is 659,159.23 which is now reflected on the Solution document.
The cause of the error was that the "Reason for Recall" category had two variations of "E.Coli O157:H7". We have removed the inconsistencies between the two reason labels in both the initial and solutions document.
Therefore, the method presented in the video is still correct, even if the result is a bit different.

Introduction to Visualizations and Pie Charts14:52

Get more FREE resources at: www.sixsigma-consulting.com
Follow on LinkedIn: https://www.linkedin.com/company/michaelparentconsulting/
Like and subscribe on YouTube: https://www.youtube.com/channel/UCDLNIjI1OQulldhfkZibX7A
Also consider my other course on Udemy: https://www.udemy.com/course/control-charts/?referralCode=E6197D5696854010A842
Histograms8:31
**The file used in this lecture is from the Lecture 9**
Bar Charts13:02
**The file used in this lecture is from the Lecture 9**
Line Charts14:44
**The file used in this lecture is from the Lecture 9**
Box and Whisker15:00
**The file used in this lecture is from the Lecture 9**
Radial Charts9:54
**The file used in this lecture is from the Lecture 9**
Combo Charts13:21
**The file used in this lecture is from the Lecture 9**
Scatter Plots13:02
**The file used in this lecture is from the Lecture 9**
Conditional Formatting Part 18:37
Create in spreadsheet visualizations using conditional formatting to highlight data and alert users, and preview spark lines, top and bottom rules, and color scales for dashboards.
Conditional Formatting Part 211:03
**The file used in this lecture is from the Lecture 17**
Conditional Formatting Part 37:18
**The file used in this lecture is from the Lecture 17**
Sparklines7:29
**The file used in this lecture is from the Lecture 17**
Appendix: Control Charts8:05
This lecture was added later than the bulk of the course and features another cool visualization that many people find useful: Control Charts. The lecture describes what a control chart is, how it is used, and how to create one within Excel.
Module #2 - Practice & Solutions18:19
The video has two errors:
1. Question (a) The box plot data uses the dosage labels (0.5, 1, etc) as data. These are data labels stored in row 5 and are not part of the data set themselves.
2. Question (b) The calculation to find out how many trials exceeded the goal was incomplete. it should be 60-29=31. Similarly, you can calculate this by changing the direction of the inequality sign.

Both of these issues have been addressed in the current solution resource.

Introduction to Pivot Tables13:30

Get more FREE resources at: www.sixsigma-consulting.com
Follow on LinkedIn: https://www.linkedin.com/company/michaelparentconsulting/
Like and subscribe on YouTube: https://www.youtube.com/channel/UCDLNIjI1OQulldhfkZibX7A
Also consider my other course on Udemy: https://www.udemy.com/course/control-charts/?referralCode=E6197D5696854010A842
Root Cause Analysis14:10
**The file used in this lecture is from the Lecture 22**
Comparative Analysis14:03
**The file used in this lecture is from the Lecture 22**
Pivot Charts and Slicers11:01
**The file used in this lecture is from the Lecture 22**
Module #3 - Practice & Solutions30:49

Types of Data14:57
Get more FREE resources at: www.sixsigma-consulting.com
Follow on LinkedIn: https://www.linkedin.com/company/michaelparentconsulting/
Like and subscribe on YouTube: https://www.youtube.com/channel/UCDLNIjI1OQulldhfkZibX7A
Also consider my other course on Udemy: https://www.udemy.com/course/control-charts/?referralCode=E6197D5696854010A842
Fundamentals of Sampling11:29
Explore how sampling from a population enables statistics, using random samples to estimate preferences, margins of error, and distributions from nominal to continuous data.
Distributions Part 18:51
**The file used in this lecture is from the Lecture 28**
Distributions Part 210:35
**The file used in this lecture is from the Lecture 28**

I made a mistake at around ~1:00min mark in this video.
When calculating the normal distribution, I'm pulling column D which is the count of people within a grams consumed range (column F) but I speak about it as if these are the actual grams consumed!

To get the average grams consumed:
Find the range with the most number of people consuming that amount. In our example, this would be the range of 25-30g because 17 people fell within this range.
The average cannot be a range. So we would need to make an assumption. That the average of those 17 people is in the middle of 25 and 30. (30+25)*0.5 = 27.5. Would be the mean average.
This assumption isn't arbitrary either. It's a *very* cool phenomenon that statisticians use called boot-strapping. Essentially, we take a sample that is reflective of a population. It's normally distributed. This also means that a sample of the sample (in this case a sample size of 17) will also be normally distributed! So there's something here more than a midpoint formula.
Introduction to Hypothesis Testing14:53
Learn to formulate null and alternative hypotheses, perform hypothesis tests, interpret p-values, and understand type I and type II errors, power, and decision rules using a heart rate example.
T-tests Part 113:50
T-tests Part 26:48
**The file used in this lecture is from the Lecture 32**
T-tests Part 39:58
**The file used in this lecture is from the Lecture 32**
Chi-Squared Test Part 114:20
Chi-Squared Test Part 213:39
**The file used in this lecture is from the Lecture 35**
Test for Normality Part 114:21
Explore using the chi-squared goodness-of-fit test to determine if data follow a normal distribution, by computing mean and standard deviation, ranking values, and comparing observed to expected.
Test for Normality Part 210:40
**The file used in this lecture is from the Lecture 37**
ANOVA Part 114:09
Explore one-way ANOVA in Excel: test differences among multiple groups, interpret p-values, and use main effects plots to identify which group differs, while noting limitations and equal variance assumptions.
ANOVA Part 213:13
**The file used in this lecture is from the Lecture 39**
Simple Regression Part 111:44
Explore simple linear regression with continuous X and Y to test for a relationship, interpret the regression equation and slope, and review p-values and R-squared with trendline visuals.
Simple Regression Part 29:38
**The file used in this lecture is from the Lecture 41**
Multi-Variate Regression Part 16:48
Explore multiple linear regression by analyzing how one output relates to many X variables, using Excel's data analysis tool to build a correlation matrix from Boston housing data.
Multi-Variate Regression Part 210:52
**The file used in this lecture is from the Lecture 43**
Multi-Variate Regression Part 38:04
**The file used in this lecture is from the Lecture 43**
Module 4 - Practice & Solutions20:55

Introduction to Forecasting14:20
Get more FREE resources at: www.sixsigma-consulting.com
Follow on LinkedIn: https://www.linkedin.com/company/michaelparentconsulting/
Like and subscribe on YouTube: https://www.youtube.com/channel/UCDLNIjI1OQulldhfkZibX7A
Also consider my other course on Udemy: https://www.udemy.com/course/control-charts/?referralCode=E6197D5696854010A842
Factor Forecasting - Regression Part 113:27
Factor Forecasting - Regression Part 213:50
**The file used in this lecture is from the Lecture 48**
Factor Forecasting - Monte Carlo Simulation - Part 19:56
Factor Forecasting - Monte Carlo Simulation - Part 213:39
**The file used in this lecture is from the Lecture 50**
Factor Forecasting - Monte Carlo Simulation - Part 314:29
**The file used in this lecture is from the Lecture 50**
Time Series Forecasting - The Simple Moving Average Part 112:30
Explore time series forecasting using time as a single factor, applying simple moving average, weighted moving average, and exponential smoothing to forecast stock prices from historic data and assess performance.
Time Series Forecasting - The Simple Moving Average Part 25:20
**The file used in this lecture is from the Lecture 53**
Time Series Forecasting - Parameter Tuning Part 18:37
Time Series Forecasting - Parameter Tuning Part 213:27
**The file used in this lecture is from the Lecture 55**
Time Series Forecasting - Parameter Tuning Part 310:40
**The file used in this lecture is from the Lecture 55**
Time Series Forecasting - Auto Regression12:29
Time Series Forecasting - Time Series ARMA6:52
**The file used in this lecture is from the Lecture 58**
Time Series Forecasting - Time Series ARIMA12:18
**The file used in this lecture is from the Lecture 58**
Module 5 - Practice Set #1 & Solutions25:36
Module 5 - Practice Set #2 & Solutions28:59

Intro and Goal Seek4:48
Get more FREE resources at: www.sixsigma-consulting.com
Follow on LinkedIn: https://www.linkedin.com/company/michaelparentconsulting/
Like and subscribe on YouTube: https://www.youtube.com/channel/UCDLNIjI1OQulldhfkZibX7A
Also consider my other course on Udemy: https://www.udemy.com/course/control-charts/?referralCode=E6197D5696854010A842
Scenario Analysis10:44
Explore scenario analysis in Excel to compare budgets and project plans with the scenario manager, enabling quick comparisons of payback, break-even, and savings across scenarios.
Data Tables7:20
Introduction to Excel Solver Tool9:54
Excel Solver Tool - The Backpack Problem11:07
**The file used in this lecture is from the Lecture 66**
Excel Solver Tool - The Mixing Problem12:53
**The file used in this lecture is from the Lecture 66**
Excel Solver Tool - The Traveling Salesman Problem10:58
**The file used in this lecture is from the Lecture 66**
Module 6 - Practice & Solutions34:29

Requirements

Basic Math

Description

Do you think you need to learn new software to perform data analysis, statistics, simulations? You don't! Everything you need to get started in the world of data analytics is already on your computer in the wonderful application called Excel.

This course will first acquaint you with the Excel environment including how to use simple and complex functions, hot-key shortcuts and navigation tips to make sure you work efficiently and effectively. From there, I lead you in lectures and practice exercises on the fundamental topics of data analytics.

Data Visualizations - Visualizing data is an important part in analyzing data as well as presenting and explaining what it means. Lectures in this course Included how to craft and use bar charts, line charts, radial charts, histograms, box and whisker charts, pie charts, conditional formatting and Sparklines.

Pivot Tables - Pivot Tables are very powerful, simple to use tools built into excel. The lectures will include how to structure data to get the most out of pivot tables, and then a few use cases including root cause analysis, comparative analysis, along with other visualization tools specific to pivot tables.

Statistics - The Statistics module is by far the most densely packed module in this course. As a statistician by training, I present a deep dive into the data behind the statistics and how to use different statistical tests based on different circumstances. The module is filled with different lectures and exercises involving ANOVA tests, T Tests, Chi-Squared Tests, Tests for Normality, Regression Analysis and more.

Forecasting - Organizations have to be able to anticipate the future. The forecasting tools presented in this course help us accomplish this feat. Forecasting is presented in two different ways - Factor forecasting and Time Series forecasting. For each approach to forecasting, many tools and techniques are introduced and reviewed including Regression analysis, Monte Carlo Simulation, Simple Moving Averages, and Auto-Regressive techniques.

Excel Tools - Excel has a lot of useful tools that don't quite fit neatly into any of the other modules. The last module of this course explores the use cases of using these tools. In particular, several archetypal problems are introduced and solved using the miscellaneous data analysis tools found in excel, including Excel Solver.

Who this course is for:

Operations managers
Quality professionals
Industrial Engineers
Data Analysts
casual Excel users curious about data science

The Complete Data Analytics Course in Excel

What you'll learn

Explore related topics

Course content

Excel Environment and Excel Basics8 lectures • 1hr 39min

Visualizations14 lectures • 2hr 43min

Pivot Tables, Charts and Slicers5 lectures • 1hr 24min

Statistics: Hypothesis Testing and Regression20 lectures • 4hr

Forecasting16 lectures • 3hr 36min

Miscellaneous Analytical Tools8 lectures • 1hr 42min

Requirements

Description

Who this course is for: