Learning Excel: Data Analysis (2022)

Course details

Microsoft Excel is an important tool for data analysis. It helps companies accurately assess situations and make better business decisions. This course helps you unlock the power of your organization's data using the data analysis and visualization tools built into Excel. Author Curt Frye starts with the foundational concepts, including basic calculations such as mean, median, and standard deviation, and provides an introduction to the central limit theorem. He then shows how to visualize data, relationships, and future results with Excel's histograms, graphs, and charts. He also covers testing hypotheses; modeling different data distributions; calculating the covariance and correlation between data sets; and calculating probabilities, combinations, and permutations. Finally, he reviews the process of calculating Bayesian probabilities in Excel. Each chapter includes practical examples that show how to apply the techniques to real-world business problems.

Concepts

Introduction

Analyze your data effectively
What you should know before starting

Foundational Concepts of Data Analysis

Calculate mean and median values
Measure maximums, minimums, and other data characteristics
Analyze data using variance and standard deviation
Introduce the central limit theorem
Analyze a population using data samples
Identify and minimize sources of error
Challenge - Summarize and analyze business data
Solution - Summarize and analyze business data

Visualizing Data

Group data using histograms
Identify relationships using XY scatter charts
Visualize data using logarithmic scales
Add trendlines to charts
Forecast future results
Calculate running averages
Challenge - Summarize operational data visually
Solution - Summarize operational data visually

Testing a Hypothesis

Formulate a hypothesis
Interpret the results of your analysis
Consider the limits of hypothesis testing
Challenge - Formulate and test a hypothesis
Solution - Formulate and test a hypothesis

Utilizing Data Distributions

Use the normal distribution
Use a uniform distribution
Use the exponential distribution
Use the Poisson distribution
Use the binomial distribution
Challenge - Model operational data using distributions
Solution - Model operational data using distributions

Measuring Covariance and Correlation

Visualize what covariance means
Calculate covariance between two columns of data
Calculate covariance among multiple pairs of columns
Visualize what correlation means
Calculate the correlation between two columns of data
Calculate correlation among multiple pairs of columns
Challenge - Calculate correlations between columns of data
Solution - Calculate correlations between columns of data

Calculating Probabilities, Combinations, and Permutations

Calculate simple probabilities
Calculate compound probabilities
Calculate expected value
Calculate permutations without duplication
Calculate permutations with duplication
Calculate combinations without duplication
Calculate combinations with duplication
Challenge - Calculate the expected value of a business scenario
Solution - Calculate the expected value of a business scenario

Performing Bayesian Analysis

Introduce Bayesian analysis
Analyze a sample problem - Kahneman s Cabs
Create a classification matrix
Calculate Bayesian probabilities in Excel
Challenge - Perform a Bayesian analysis
Solution - Perform a Bayesian analysis

Performing Regression Analysis

Introduce linear regression
Enable the Analysis ToolPak add-in
Perform linear regression on a single variable
Interpret linear regression results
Perform multiple regression
Challenge - Perform linear regression
Solution - Perform linear regression

Conclusion

Additional resources