Learning Excel: Data Analysis

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

0. Introduction

01 - Analyze your data effectively
02 - What you should know before starting

1. Foundational Concepts of Data Analysis

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

2. Visualizing Data

11 - Group data using histograms
12 - Identify relationships using XY scatter charts
13 - Visualize data using logarithmic scales
14 - Add trendlines to charts
15 - Forecast future results
16 - Calculate running averages
17 - Challenge - Summarize operational data visually
18 - Solution - Summarize operational data visually

3. Testing a Hypothesis

19 - Formulate a hypothesis
20 - Interpret the results of your analysis
21 - Consider the limits of hypothesis testing
22 - Challenge - Formulate and test a hypothesis
23 - Solution - Formulate and test a hypothesis

4. Utilizing Data Distributions

24 - Use the normal distribution
25 - Use a uniform distribution
26 - Use the exponential distribution
27 - Use the Poisson distribution
28 - Use the binomial distribution
29 - Challenge - Model operational data using distributions
30 - Solution - Model operational data using distributions

5. Measuring Covariance and Correlation

31 - Visualize what covariance means
32 - Calculate covariance between two columns of data
33 - Calculate covariance among multiple pairs of columns
34 - Visualize what correlation means
35 - Calculate the correlation between two columns of data
36 - Calculate correlation among multiple pairs of columns
37 - Challenge - Calculate correlations between columns of data
38 - Solution - Calculate correlations between columns of data

6. Calculating Probabilities, Combinations, and Permutations

39 - Calculate simple probabilities
40 - Calculate compound probabilities
41 - Calculate expected value
42 - Calculate permutations without duplication
43 - Calculate permutations with duplication
44 - Calculate combinations without duplication
45 - Calculate combinations with duplication
46 - Challenge - Calculate the expected value of a business scenario
47 - Solution - Calculate the expected value of a business scenario

7. Performing Bayesian Analysis

48 - Introduce Bayesian analysis
49 - Analyze a sample problem - Kahneman s Cabs
50 - Create a classification matrix
51 - Calculate Bayesian probabilities in Excel
52 - Challenge - Perform a Bayesian analysis
53 - Solution - Perform a Bayesian analysis

8. Performing Regression Analysis

54 - Introduce linear regression
55 - Enable the Analysis ToolPak add-in
56 - Perform linear regression on a single variable
57 - Interpret linear regression results
58 - Perform multiple regression
59 - Challenge - Perform linear regression
60 - Solution - Perform linear regression

Conclusion

61 - Additional resources