Excel Masterclass: A-Z Data Analysis Statistics & Dashboard

Learn how data analysis inspects, cleans, transforms, and models data to discover insights, inform conclusions, and support decision making across descriptive, diagnostic, predictive, prescriptive, and exploratory approaches.

Explore the key components of data analysis, from data collection and cleaning to EDA, transformation, statistical analysis, modeling, visualization, and interpretation for informed decision making.

Explore exploratory data analysis, a crucial phase that visually and statistically summarizes data to gain insights, identify patterns and outliers, and generate testable hypotheses for data driven decisions.

Explore exploratory data analysis methods, detailing mean, median, and mode as central tendencies and variance and standard deviation as dispersion measures, including their calculations and outlier effects.

Examine symmetric and asymmetric distributions, including the normal bell-shaped curve, mean, median, and mode relationships, and skewness, kurtosis. Explore percentiles (Q1, median, Q3) and min/max in data.

Explore frequency analysis, percentage calculation, group by aggregation, cross tabulation, and correlation analysis with Pearson's r, illustrated through histograms, bar charts, and scatter plots.

Learn the basics of statistical data analysis, including examining, cleaning, transforming, and modeling data to uncover patterns and inform decision making using tools like R, Python, and SPSS.

Differentiate statistical tests from exploratory data analysis to guide data analysis decisions. Contrast exploratory data analysis's hypothesis-free visual exploration with hypothesis-driven statistical tests using p-values.

Explore population vs. sample concepts and compare seven sampling methods—simple random, stratified, systematic, cluster, convenience, and snowball—highlighting advantages, challenges, and when to apply each.

Explore the statistics: descriptive and inferential analysis, to summarize data and infer trends. Descriptive statistics use mean, median, mode, and charts; inferential statistics rely on samples and hypothesis tests.

Recap the key measures of descriptive statistics, including mean, median, mode, range, variance, standard deviation, IQR, skewness, kurtosis, and quartiles. Explore how these measures summarize data features and describe distribution.

Explore one sample t test, independent and paired t tests to compare means, and one way ANOVA for three or more group means.

Explore chi-square tests for independence to assess relationships between categorical variables using observed and expected frequencies in a contingency table. Then measure linear relationships with Pearson correlation.

Learn how linear regression models predict a dependent variable from one or more independent variables, using simple and multiple regression, and interpret beta values, R square, and the regression equation.

Explore hypothesis testing as a structured inferential statistics method that uses sample data to test null and alternative hypotheses, determine significance, and guide decisions about population parameters.

Learn to select the appropriate statistical test for a scenario and perform assumption testing—normality, linearity, and homoscedasticity—using t tests, ANOVA, chi-square, Pearson, and regression, with box-cox transformations when needed.

Explore confidence level, significance level (alpha), and p-value as core measures in hypothesis testing. See how these values guide decisions and conclusions, using jellybeans as a relatable example.

Learn to decide between null and alternative hypotheses by comparing p values to a 5% significance level, and present a clear conclusion from the hypothesis test.

Examine a step-by-step hypothesis testing workflow comparing two classes using an independent samples t-test, with Shapiro-Wilk normality checks, p-values, and conclusions about the new teaching method versus the traditional method.

Explore how data visualization transforms numbers into charts, maps, and dashboards that reveal trends, patterns, and outliers to support data-driven decisions and clear stakeholder communication.

Explore data visualization methods with bar charts, stacked bar charts, and line graphs, showing means by category, totals and composition, and trends over time.

Use pie charts to show proportions, histograms to reveal distributions and skewness, scatter plots to illustrate relationships and correlation, and heatmaps to map magnitude with color.

Explore area and line charts for time-based trends with colored areas, bubble charts for an extra dimension via bubble size, and box plots for distribution with quartiles and outliers.

Identify and highlight duplicate values in the employee ID column using Excel, then remove duplicates to keep only unique IDs.

Identify and quantify missing values in Excel, replace numeric missing values with the average and categorical missing values with the mode, using countif, replace, and pivot tables.

Identify and remove outliers in the cost variable using the g score method, compute mean and std dev, flag values beyond ±3, and replace them with the average cost.

Identify inconsistent values in numeric and categorical data in Excel, then replace with representative values using sort, average cost, and most frequent product via pivot tables.

Use Excel's text to columns with the comma delimiter to convert comma-separated text in column A into tabular data with name, age, gender, occupation, and city.

Master filtering and sorting in Excel to narrow data by city, gender, name starts with, and age. Apply between, above average, and top ten options, with reset.

Learn to apply advanced filtering with predefined criteria in Excel, using list range and criteria range to filter by multiple conditions and copy results to another location.

Learn conditional formatting in Excel to highlight data with rules such as greater than 300, less than 300, and between 300 and 500, then sort and filter by color.

Apply conditional formatting top bottom rules to highlight top ten, bottom ten, top 10%, and above- or below-average sales, then filter by color to reveal high-revenue categories and top customers.

Learn to use conditional formatting in Excel to segment electronics customers by profitability, apply color scales and data bars, identify above-average spenders, and highlight top profits.

Apply sum, average, min, and max functions to revenue, cost, and refund in a business data set. Format results in USD and compute totals and profit.

Learn sumif to calculate total revenue by each business area and averageif to measure average refunds. Use unique to identify areas like North America, Europe, South America, and Asia.

Learn to count data using count, countA, and countIf to tally observations, handle blanks, and compute category frequencies and percentages for revenue and product categories.

Extract year, month, and day from a date-time column using Excel's year, month, and day functions to enable monthly revenue analysis and time-based insights on cost and refund.

Master if statements for conditional operations in Excel, covering basic if, nested if, the ifs function, and/or logic for data analysis and dashboards.

Master vlookup for vertical lookups in excel to retrieve an employee name and designation by id from a data table, with dropdown validation and exact-match lookup.

Master horizontal lookup in Excel with HLOOKUP to fetch monthly sales by employee using a row-wise search, data validation dropdowns, and a dynamic row index for automation.

Learn how xlookup simplifies data retrieval by replacing vlookup, automates lookups for employee id to fetch name and designation, and supports nested multi-condition searches for sales by name and month.