Data Analytics With Excel and Power BI

Identify categorical versus numerical data, including ordinal and nominal types, and learn which analyses and visuals (frequency, proportions, mean, bar and pie charts, box plots) best fit.

Engage in exploratory data analysis to build a foundation for business intelligence, spot data issues and anomalies, and identify variables using graphical and non-graphical methods, including univariate and multivariate approaches.

Explore descriptive analysis by using mean, median, and mode to describe data, visualize distribution with histograms, and assess spread with variance and standard deviation.

Explore how inferential analysis uses a sample to represent the population, provides estimation with confidence intervals, and tests hypotheses using t tests and chi-squared tests.

Predictive analysis uses data mining, modeling, and statistics to forecast future events and uncover patterns and trends, with visualizations and real-time information.

Learn to enable the analysis toolpak in Excel and compute descriptive statistics: mean, median, mode, standard deviation, variance, skewness, and kurtosis for data sets, with input and output ranges.

Explore how histograms visualize central tendency by grouping data into bins and plotting occurrences in Excel. Learn how symmetry, mean, median, and skewness indicate the center and guide charting.

Create and read a histogram in Excel 2016 and later versions using the built-in histogram tool, adjust bins, exclude outliers, and interpret a left-skewed distribution.

Learn how box plots display data distributions using the five-number summary—minimum, first quartile, median, third quartile, and maximum—showing whiskers, skewness, and how to create one in Excel.

Learn to create a box and whisker plot in Excel 2016, and read its elements—minimum, maximum, median, mean, first and third quartiles, and skewness.

Distinguish categorical (nominal and ordinal) from numerical (continuous and discrete) data; summarize with frequencies and proportions, and visualize categories with bar or pie charts, numerical data with box plots.

Analyze categorical data with pivot tables in Excel to reveal how job role and salary influence upgrade decisions, using nominal and ordinal data to explore demographics.

Learn how to calculate descriptive statistics in Excel using standard formulas for mean, standard error, median, mode, standard deviation, variance, kurtosis, skewness, max, min, sum, and count.

Learn how to use Excel's rank, largest, and smallest functions on monthly return data to rank values, choose descending or ascending order, and find the third largest or second smallest.

Explore percentile and percentile rank functions in Excel using a data set to determine thresholds. See how inclusive and exclusive variants affect calculations of minimums, maximums, and probabilities.

Compare the average and geometric mean for quarterly investment returns and learn how the compound annual growth rate dampens volatility, revealing true performance differences between assets.

Explore probability as the likelihood of events, outlining experiments, sample space, events, and trials, and learn to compute probabilities like rolling a total of seven with two dice in Excel.

Use countif in Excel to count the frequency of two-dice sums and divide by the 36-outcome sample space to obtain probability, illustrating higher chances for sums like seven.

Apply the law of complements to compute not events as 1 minus P(A) and see that probabilities sum to 1. Use coin toss and two dice examples.

Explore how mutually exclusive and independent events differ, learn to calculate probabilities with P(A) + P(B) and P(A and B), and apply examples in Excel to analyze scenarios.

Explore probability for independent and mutually exclusive events using a sample space and intersecting outcomes. Learn calculations for A, B, C, A and C, and A or B.

Apply the hypergeometric distribution to calculate the chance of drawing three women in a row from a 32-person group (14 women, 18 men), using the hypgeom.dist function in Excel.

Apply the law of total probability to combine mutually exclusive events and relate marginal and conditional probabilities, using a tree map, for data analytics with Excel and Power BI.

Learn to build a joint probability pivot table in Excel to analyze single event probability, joint probability, mutually exclusive and not mutually exclusive events, and conditional probability.

Explore binomial and Poisson random variables, examine discrete trials with independent outcomes, and learn how to calculate probabilities in Excel using relevant functions, with real-world examples.

Learn to compute binomial probabilities in Excel using BINOM.DIST and BINOM.DIST.RANGE, by verifying binomial experiment conditions for four appointments with a 20 percent sale probability, and find mean as n*p.

Calculate Poisson probabilities in Excel using the Poisson distribution function with a mean for two-hour intervals. Explore probability mass distribution, cumulative, and range probabilities for calls between 60 and 80.

Explore the normal distribution, its bell-shaped curve, and symmetry, noting mean, median, and mode equivalence. Understand standard deviation and the 68-95-99.7 rule for data spread.

Apply Excel’s normal distribution and normal inverse distribution functions to calculate probabilities, percentiles, and ranges using a given mean and standard deviation for normally distributed data.

Learn how the central limit theorem makes sample means approach normality with thirty or more independent variables and use Excel functions like norm.dist and norm.inv to solve probability questions.

Learn how z scores standardize data by subtracting the mean and dividing by the standard deviation, enabling apples and oranges to be compared in Excel.

Explore how populations and samples differ, learn about census, simple random sample, parameter, mean income, and bias in data collection and analysis.

Learn to create an unbiased random sample of invoices in Excel by generating random numbers with rand, fixing values, and sorting to select the 10 lowest numbers for audit.

Explore how confidence intervals quantify the reliability of estimates in business statistics. Understand how the confidence level and alpha relate to the probability that the interval contains the true value.

Calculate confidence intervals in Excel using confidence.norm and confidence.t with a 25-stock sample over five years. Determine the mean, standard deviation, and alpha to obtain upper and lower limits.

Learn to calculate the sample size needed to estimate a population mean with 95% confidence within 25 kilometers, using sigma 40.25 and z 1.96, rounding up to 10.

A hypothesis test determines if results are due to chance by comparing a null hypothesis to an alternative, with significance levels guiding whether to reject the null in Excel.

Discover how z-tests compare population means to a sample mean using the standard normal distribution, including assumptions, p-values in Excel, and using the sample standard deviation.

Perform a z-test in Excel to test if the mean debtor days is less than 29, using known population standard deviation 2 and alpha 0.05, and interpret the p-value.

Use a t-test to compare pre and during campaign sales, check p-values with an f-test for equal variances, and conclude campaign success when differences are not due to chance.

Learn to visualize and quantify correlation in Excel using a scatter plot of temperature versus sales, add a trendline, display the regression equation and R-squared, and interpret negative correlations.

Use the Excel data analysis toolpak to perform a two-sample hypothesis test on starting salaries of graduates from college A and B, using variances, a two-tailed p-value, and 0.05 alpha.

Explore Power BI Desktop to get data from multiple sources, from files and SharePoint to online services and databases, using Power Query, and model data with measures, columns, and relationships.

Connect to data and transform it in Power BI desktop via the query editor, importing a CSP file, adjusting data types, pivoting fields, and loading results.

Explore how relationships between tables establish cardinality—one-to-one, one-to-many, many-to-one, and many-to-many—and how cross-filter direction changes data filtering in Excel and Power BI.

Connect to datasets, manage one-to-many relationships between fact and dimension tables, and build quick Power BI visualizations with charts, filters, and drill-down by date.

Explore the Power Query tour in Power BI, navigating the query editor, managing sources, and applying transformations with add column, split, group, pivot, and code in the advanced editor.

Learn how to connect to an Excel file in Power BI, select a single sheet, and apply transformations like changing data types, filling down, and grouping by country.

Duplicate or reference queries to reuse steps, change the source to new workbooks, add a sales rep column, append tables, and disable loading unused queries to optimize power pivot model.

Connect to a folder of sales files, combine them, apply transformations to create a rep column, load the data into a model, and refresh to update when new files arrive.

Create calculated columns in Power BI by multiplying quantity by unit selling price to derive sales value and margin, noting row context versus filter context and when measures are needed.

Create measures in the table and understand they differ from calculated columns, relying on filters and an aggregator to compute a percentage margin correctly.

Learn to compute total sales with sumx by iterating rows in the sales table and multiplying quantity by unit selling price, and see why measures replace calculated columns for efficiency.

Use the related function to pull selling price from the product table in a many-to-one relationship, and the related table function to sum sales in a one-to-many relationship.