Excel Shortcuts for A-Z Data Analysis Statistics & Dashboard

Name: Excel Shortcuts for A-Z Data Analysis Statistics & Dashboard
Rating: 4.5 (40 reviews)

Master Excel for Data Analytics, Statistical Analysis, Hypothesis Testing and Interactive Dashboard in No Tiime!

Created byAnalytix AI

Last updated 6/2024

English

English [Auto],

What you'll learn

Understand and apply techniques for cleaning and preparing raw data in Excel.
Learn to identify and handle missing data, outliers, and inconsistencies.
Utilize Excel functions and tools for data validation and transformation.
Explore fundamental statistical concepts and their application in Excel.
Learn to perform descriptive statistics, inferential statistics, and hypothesis testing using Excel functions and tools.
Understand how to interpret and communicate statistical results effectively.
Design and build interactive dashboards in Excel for effective data visualization.
Learn to use PivotTables, PivotCharts, and slicers to create dynamic and user-friendly dashboards.
Explore various data visualization techniques available in Excel, including charts, graphs.

Course content

6 sections • 35 lectures • 3h 2m total length

Introduction to data analysis3:38
Explore the data analysis process, from inspecting, cleaning, transforming, and modeling data to uncover insights and support decision making, including descriptive, diagnostic, predictive, and prescriptive analysis.
Steps in data analysis workflow2:54
Explore the complete data analysis workflow from data cleaning to hypothesis testing, covering data cleaning, manipulation, exploratory data analysis, distribution assessment, and statistical analysis for actionable insights.

Understanding statistical analysis8:08
Understand statistical analysis, hypothesis testing, and population versus sample to infer insights from data; learn steps and how to build a predictive model using null and alternative hypotheses.
Various elements of hypothesis testing5:05
Explore three core measures of hypothesis testing: confidence level, significance level, and p value, and learn how they guide decisions between null and alternative hypotheses.
Steps in hypothesis testing5:08
Learn the step-by-step hypothesis testing process using a school scenario to compare new versus traditional teaching methods, set significance at 0.05, and apply one-way ANOVA, correlation, or regression.

Dealing with missing values13:52
Discover how to handle missing values in Excel data by replacing blanks in non-numeric fields with the most frequent value and blanks in numeric fields with the median.
Practice data - Missing values0:04
Dealing with inconsistent values9:03
Identify and fix inconsistent values in Excel data: use pivot tables to flag categories and replace with the most frequent value; for numeric data, sort and replace with the median.
Practice data - Inconsistent values0:04
Dealing with outliers8:43
Learn to identify and fix outliers in Excel by calculating g scores (z-scores) from average and standard deviation, flagging beyond ±3, and replacing with the median value (income and age).
Practice data - Outliers0:04
Dealing with duplicated values3:24
Identify and highlight duplicate values in Excel using conditional formatting, then remove duplicates to keep only unique names in the data set.
Practice data - Duplicated values0:04

Install Excel Data Analysis Tool pack (If Necessary)0:06
Frequency and percentage analysis11:44
Explore frequency and percentage analysis in Excel using pivot tables to reveal profession distribution, compute counts and percentages, and visualize results with bar and pie charts.
Practice data - Frequency and percentage analysis0:04
Descriptive analysis (mean, std. dev., skewness, etc.)12:17
Learn to perform descriptive analysis in Excel with the data analysis toolpak, computing mean, std dev, skewness, and kurtosis, and visualize results via histograms and box plots for numeric data.
Practice data - Descriptive analysis0:04
Group by analysis in excel pivot table15:46
Explore exploratory data analysis with Excel pivot tables to compute average price, quantity, and rating by category, then visualize insights with pivot charts and color-based total sales.
Practice data - Group by analysis0:05
Crosstabulation analysis in excel pivot table6:08
Learn to perform cross tabulation with Excel pivot tables to analyze rating distribution across product categories, using counts and a clustered bar chart to reveal key insights.
Practice data - Crosstabulation analysis0:04

Independent sample t-test12:27
Perform an independent sample t test in Excel with the data analysis Toolpak to compare scores between Group A and Group B and assess significance at 0.05.
Practice data - Independent sample t-test0:04
Paired sample t-test8:29
Learn to perform a paired sample t test in Excel to compare before and after scores, interpret the p value and alpha 0.05, and conclude whether training is effective.
Practice data - Paired sample t-test0:04
Analysis of variance (ANOVA)8:33
Learn to perform one-way anova in Excel with the data analysis toolpak to test price differences across two or more categories, using the p value and null hypothesis.
Practice data - ANOVA0:03
Pearson correlation analysis12:33
Learn how to perform Pearson correlation analysis on numeric data to assess the relationship between age and income, interpret the correlation coefficient and significance using Excel data analysis toolpak.
Practice data - Correlation analysis0:04
Multiple linear regression analysis17:17
Learn how to perform multiple linear regression in Excel using the Data Analysis Toolpak to predict savings from income and expense, and interpret p-values and r-squared.
Practice data - Regression analysis0:04

Creating canvas for dashboard7:51
Create a dashboard canvas in Excel by selecting and arranging five charts—frequency and percentage by profession, product rating, color sales, and category—adding filters and a title for interactive data visualization.
Creating the final dashboard8:54
Create a final Excel dashboard by decorating the canvas, adding a title, plotting and arranging multiple graphs, and using slicers to filter by category and color for interactivity.
Practice data - Dashboard0:04

Requirements

Microsoft Excel
Desire to learn

Description

Unlock the full potential of Microsoft Excel as a powerful tool for data analytics, statistical analysis, and interactive dashboard creation. This comprehensive course will equip you with the skills and knowledge needed to harness Excel's capabilities for data cleaning, statistical insights, and dynamic visualization. This course combines hands-on practical exercises, and interactive sessions to ensure participants gain a thorough understanding of data analytics, statistics, and dashboard creation using Microsoft Excel.

Key Learning Objectives:

Data Cleaning and Preparation:
- Techniques for identifying and addressing missing data, outliers, and inconsistencies.
- Utilizing Excel functions and tools for effective data validation and transformation.
Statistical Analysis:
- Exploring fundamental statistical concepts applicable in Excel.
- Performing descriptive statistics and inferential statistics using Excel functions.
- Conducting hypothesis testing to make data-driven decisions.
Interpretation and Communication:
- Developing the ability to interpret statistical results accurately.
- Effectively communicating insights derived from data analysis.
Interactive Dashboard Design:
- Building dynamic and user-friendly dashboards in Excel.
- Mastering PivotTables, PivotCharts, and slicers for interactive data exploration.
- Incorporating various data visualization techniques, including charts and graphs.

By enrolling now, you will be able to unleash the full potential of Excel for data-driven decision-making and improve your ability to translate raw data into actionable insights through the use of engaging visuals.

Who this course is for:

Data analysis enthusiast
Excel enthusiast

Excel Shortcuts for A-Z Data Analysis Statistics & Dashboard

What you'll learn

Explore related topics

Course content

What Is Data Analysis?2 lectures • 7min

What Is Hypothesis Testing?3 lectures • 18min

Step 1 - Data Cleaning and Manipulation in Excel8 lectures • 35min

Step 2 - Exploratory Data Analysis in Excel9 lectures • 46min

Step 3 - Statistical Analysis and Hypothesis Testing in Excel10 lectures • 1hr

Final step - Putting All Together in Dashboard3 lectures • 17min

Requirements

Description

Who this course is for: