Mastering Data Analysis in Excel from Coursera

Important: The focus of this course is on math - specifically, data-analysis concepts and methods - not on Excel for its own sake. We use Excel to do our calculations, and all math formulas are given as Excel Spreadsheets, but we do not attempt to cover Excel Macros, Visual Basic, Pivot Tables, or other intermediate-to-advanced Excel functionality.

The second big idea this course seeks to demonstrate is that your data-analysis results cannot and should not aim to eliminate all uncertainty. Your role as a data-analyst is to reduce uncertainty for decision-makers by a financially valuable increment, while quantifying how much uncertainty remains. You will learn to calculate and apply to real-world examples the most important uncertainty measures used in business, including classification error rates, entropy of information, and confidence intervals for linear regression.

All the data you need is provided within the course, all assignments are designed to be done in MS Excel, and you will learn enough Excel to complete all assignments. The course will give you enough practice with Excel to become fluent in its most commonly used business functions, and you’ll be ready to learn any other Excel functionality you might need in the future (module 1).

The course does not cover Visual Basic or Pivot Tables and you will not need them to complete the assignments. All advanced concepts are demonstrated in individual Excel spreadsheet templates that you can use to answer relevant questions. You will emerge with substantial vocabulary and practical knowledge of how to apply business data analysis methods based on binary classification (module 2), information theory and entropy measures (module 3), and linear regression (module 4 and 5), all using no software tools more complex than Excel.

What's inside

Syllabus

About This Course

This course will prepare you to design and implement realistic predictive models based on data. In the Final Project (module 6) you will assume the role of a business data analyst for a bank, and develop two different predictive models to determine which applicants for credit cards should be accepted and which rejected. Your first model will focus on minimizing default risk, and your second on maximizing bank profits. The two models should demonstrate to you in a practical, hands-on way the idea that your choice of business metric drives your choice of an optimal model.The second big idea this course seeks to demonstrate is that your data-analysis results cannot and should not aim to eliminate all uncertainty. Your role as a data-analyst is to reduce uncertainty for decision-makers by a financially valuable increment, while quantifying how much uncertainty remains. You will learn to calculate and apply to real-world examples the most important uncertainty measures used in business, including classification error rates, entropy of information, and confidence intervals for linear regression. All the data you need is provided within the course, and all assignments are designed to be done in MS Excel. The course will give you enough practice with Excel to become fluent in its most commonly used business functions, and you’ll be ready to learn any other Excel functionality you might need in future (module 1). The course does not cover Visual Basic or Pivot Tables and you will not need them to complete the assignments. All advanced concepts are demonstrated in individual Excel spreadsheet templates that you can use to answer relevant questions. You will emerge with substantial vocabulary and practical knowledge of how to apply business data analysis methods based on binary classification (module 2), information theory and entropy measures (module 3), and linear regression (module 4 and 5), all using no software tools more complex than Excel.

Excel Essentials for Beginners

In this module, will explore the essential Excel skills to address typical business situations you may encounter in the future. The Excel vocabulary and functions taught throughout this module make it possible for you to understand the additional explanatory Excel spreadsheets that accompany later videos in this course.

Binary Classification

Separating collections into two categories, such as “buy this stock, don’t but that stock” or “target this customer with a special offer, but not that one” is the ultimate goal of most business data-analysis projects. There is a specialized vocabulary of measures for comparing and optimizing the performance of the algorithms used to classify collections into two groups. You will learn how and why to apply these different metrics, including how to calculate the all-important AUC: the area under the Receiver Operating Characteristic (ROC) Curve.

Information Measures

In this module, you will learn how to calculate and apply the vitally useful uncertainty metric known as “entropy.” In contrast to the more familiar “probability” that represents the uncertainty that a single outcome will occur, “entropy” quantifies the aggregate uncertainty of all possible outcomes. The entropy measure provides the framework for accountability in data-analytic work. Entropy gives you the power to quantify the uncertainty of future outcomes relevant to your business twice: using the best-available estimates before you begin a project, and then again after you have built a predictive model. The difference between the two measures is the Information Gain contributed by your work.

Linear Regression

The Linear Correlation measure is a much richer metric for evaluating associations than is commonly realized. You can use it to quantify how much a linear model reduces uncertainty. When used to forecast future outcomes, it can be converted into a “point estimate” plus a “confidence interval,” or converted into an information gain measure. You will develop a fluent knowledge of these concepts and the many valuable uses to which linear regression is put in business data analysis. This module also teaches how to use the Central Limit Theorem (CLT) to solve practical problems. The two topics are closely related because regression and the CLT both make use of a special family of probability distributions called “Gaussians.” You will learn everything you need to know to work with Gaussians in these and other contexts.

Additional Skills for Model Building

This module gives you additional valuable concepts and skills related to building high-quality models. As you know, a “model” is a description of a process applied to available data (inputs) that produces an estimate of a future and as yet unknown outcome as output. Very often, models for outputs take the form of a probability distribution. This module covers how to estimate probability distributions from data (a “probability histogram”), and how to describe and generate the most useful probability distributions used by data scientists. It also covers in detail how to develop a binary classification model with parameters optimized to maximize the AUC, and how to apply linear regression models when your input consists of multiple types of data for each event. The module concludes with an explanation of “over-fitting” which is the main reason that apparently good predictive models often fail in real life business settings. We conclude with some tips for how you can avoid over-fitting in you own predictive model for the final project – and in real life.

Final Course Project

The final course project is a comprehensive assessment covering all of the course material, and consists of four quizzes and a peer review assignment. For quiz one and quiz two, there are learning points that explain components of the quiz. These learning points will unlock only after you complete the quiz with a passing grade. Before you start, please read through the final project instructions. From past student experience, the final project which includes all the quizzes and peer assessment, takes anywhere from 10-12 hours.

Good to know

Know what's good

, what to watch for

, and possible dealbreakers

Develops advanced business data analysis methods, which are necessary for business data analysts

Taught by highly experienced instructors, ensuring quality of instruction

Builds beginner-friendly Excel skills, allowing for accessible and practical learning

Emphasizes hands-on learning through practical assignments to enhance practical skills

Provides valuable data science concepts and techniques for real-world application

Reviews summary

Statistical modeling in excel

Students say this course covers statistical modeling in Excel, and focuses more on the underlying concepts behind statistical models than on how to use Excel. Learners report that while the course content is relevant and challenging, the course can be difficult to follow and the assessments may be unclear. Students with a background in statistics may find this course to be easier to follow. Overall, learners rate this course moderately and recommend it with reservations.

The assignments, such as quizzes and the final project, are engaging and help learners apply the concepts they've learned.

"I found new things, and each time something clicked I felt like it was a huge achievement."

"The assignments are very much rewarding in a sense that by the end of the assessments, you gain a better understanding on how the topics and concepts taught in the lectures could be applied in a practical sense in the world."

The course material is relevant, interesting, and provides a good understanding of statistical modeling.

"I learned so much in this course."

"To my pleasant surprise, this course exposes you to model development for a credit-card company."

Assessments such as the final project can be very confusing.

"I found significant portions of the Final Project to be very confusing."

"Part 1 asked us to basically "guess and check" to find a good model, which doesn't seem particularly efficient or reliable."

The course material is very challenging and it may take longer to complete than the estimated time.

"This course is very hard."

"It took me 13 weeks, so basically twice as long."

The course's organization and presentation are sub-par and assignments are explained poorly, incorrectly, or not at all.

"Course assignments are often explained poorly, incorrectly, or not at all."

"Course videos are poorly organized and often difficult to follow for many reasons."

The course concentrates more on the statistical concepts and formulas than on teaching how to build Excel sheets and tools.

"They have provided reading material assuming students would understand on themselves how to apply the concepts on Excel."

"They have only taught the concepts and mathematical formulas in videos instead of applying the concepts on Excel."

The course assumes students have prior knowledge of statistics and probability.

"This is not an "introductory" course by any means - if you do not have some prior knowledge of the mathematical and statistical concepts covered here you will flounder."

Activities

Be better prepared before your course. Deepen your understanding during and after it. Supplement your coursework and achieve mastery of the topics covered in Mastering Data Analysis in Excel with these activities:

Read 'Data Science for Dummies'

Show steps

Enhance your foundational understanding of data science concepts by reading this introductory book, which provides a comprehensive overview of the field.

View Artificial Intelligence For Dummies (For... on Amazon

Show steps

Read selected chapters relevant to the course topics.
Take notes and highlight key concepts for future reference.

Refresh Excel Basics

Show steps

Start by recapping the basics of Excel and its most common functions to ensure a smooth start to the course.

Browse courses on Excel

Show steps

Review basic Excel functions, such as SUM, AVERAGE, and COUNTIF.
Create a simple spreadsheet to practice data entry and formatting.

Compile a Glossary of Key Terms

Show steps

Create a comprehensive glossary of important terms and concepts covered in the course, ensuring a clear understanding of the terminology used.

Show steps

Review course materials and identify key terms.
Define each term concisely and accurately.

Five other activities

Expand to see all activities and additional details

Show all eight activities

Participate in a Study Group

Show steps

Connect with fellow learners to discuss course concepts, share insights, and provide support, fostering a collaborative learning environment.

Show steps

Join or form a study group with other students in the course.
Meet regularly to review materials, solve problems, and prepare for assessments.

Practice Binary Classification Metrics

Show steps

Complete exercises to strengthen your understanding of binary classification metrics, ensuring proficiency in evaluating model performance.

Browse courses on Binary Classification

Show steps

Calculate AUC, TPR, FPR, and Accuracy for given binary classification scenarios.
Interpret ROC curves to assess model performance and identify optimal thresholds.

Explore Information Theory and Entropy

Show steps

Follow online tutorials or read articles to deepen your understanding of information theory and entropy, which are crucial concepts for quantifying uncertainty in data analysis.

Browse courses on Information Theory

Show steps

Research and understand the concepts of entropy, information gain, and cross-entropy.
Apply entropy calculations to real-world scenarios to measure uncertainty.

Develop a Linear Regression Model

Show steps

Apply your knowledge by creating a linear regression model using real-world data. This hands-on project will solidify your understanding of model building and evaluation.

Browse courses on Linear Regression

Show steps

Collect and prepare data relevant to the chosen business problem.
Build a linear regression model and evaluate its performance using metrics such as R-squared and MSE.
Interpret the model's coefficients and make predictions based on the model.

Work on the Final Course Project

Show steps

Engage in the comprehensive final project, which challenges you to develop predictive models for credit card applications. This project will test your ability to apply the concepts learned throughout the course.

Browse courses on Predictive Modeling

Show steps

Analyze the provided credit card application data.
Develop two predictive models, one for minimizing default risk and one for maximizing bank profits.
Evaluate the performance of both models using relevant metrics.

Career center

Learners who complete Mastering Data Analysis in Excel will develop knowledge and skills that may be useful to these careers:

Business Analyst

Business Analysts use data to solve business problems. They work with stakeholders to understand their needs and then use data to develop solutions. This course may be useful for Business Analysts who want to learn more about data analysis techniques, such as binary classification, information theory, and linear regression. These techniques can be used to build predictive models that can help businesses make better decisions.

See salaries and explore the career path for Business Analyst

Data Scientist

Data Scientists are highly trained professionals who use their knowledge of math, statistics, and computer science to extract insights from data. This course may be useful for Data Scientists who want to learn more about data analysis techniques, such as binary classification, information theory, and linear regression. These techniques can be used to build predictive models that can help businesses make better decisions.

See salaries and explore the career path for Data Scientist

Market Researcher

Market Researchers collect and analyze data about consumers. They use this information to help businesses develop products and services that meet the needs of their customers. This course may be useful for Market Researchers who want to learn more about data analysis techniques, such as binary classification, information theory, and linear regression. These techniques can be used to build predictive models that can help businesses make better decisions about product development and marketing.

See salaries and explore the career path for Market Researcher

Quantitative Analyst

Quantitative Analysts use mathematical and statistical models to analyze financial data. They use this information to make investment recommendations. This course may be useful for Quantitative Analysts who want to learn more about data analysis techniques, such as binary classification, information theory, and linear regression. These techniques can be used to build predictive models that can help investors make better decisions.

See salaries and explore the career path for Quantitative Analyst

Risk Analyst

Risk Analysts assess and manage financial risks. They work with businesses to identify, measure, and mitigate risks. This course may be useful for Risk Analysts who want to learn more about data analysis techniques, such as binary classification, information theory, and linear regression. These techniques can be used to build predictive models that can help businesses make better decisions about how to manage their risks.

See salaries and explore the career path for Risk Analyst

Operations Research Analyst

Operations Research Analysts use mathematical and statistical models to improve the efficiency of business operations. They work with businesses to identify and solve problems, such as how to optimize production schedules or how to improve customer service. This course may be useful for Operations Research Analysts who want to learn more about data analysis techniques, such as binary classification, information theory, and linear regression. These techniques can be used to build predictive models that can help businesses make better decisions about how to operate their businesses.

See salaries and explore the career path for Operations Research Analyst

Data Engineer

Data Engineers build and maintain the infrastructure that is used to store and process data. They work with data scientists and business analysts to ensure that data is available and accessible for analysis. This course may be useful for Data Engineers who want to learn more about data analysis techniques, such as binary classification, information theory, and linear regression. These techniques can be used to build predictive models that can help businesses make better decisions about how to use their data.

See salaries and explore the career path for Data Engineer

Statistician

Statisticians collect, analyze, and interpret data. They work with businesses and organizations to help them make informed decisions. This course may be useful for Statisticians who want to learn more about data analysis techniques, such as binary classification, information theory, and linear regression. These techniques can be used to build predictive models that can help businesses make better decisions about how to use their data.

See salaries and explore the career path for Statistician

Machine Learning Engineer

Machine Learning Engineers build and maintain machine learning models. They work with data scientists and business analysts to develop and deploy machine learning models that can be used to automate tasks and make predictions. This course may be useful for Machine Learning Engineers who want to learn more about data analysis techniques, such as binary classification, information theory, and linear regression. These techniques can be used to build predictive models that can help businesses make better decisions about how to use their data.

See salaries and explore the career path for Machine Learning Engineer

Economist

Economists study the production, distribution, and consumption of goods and services. They use data to analyze economic trends and make predictions about the future. This course may be useful for Economists who want to learn more about data analysis techniques, such as binary classification, information theory, and linear regression. These techniques can be used to build predictive models that can help businesses make better decisions about how to operate their businesses.

See salaries and explore the career path for Economist

Actuary

Actuaries use mathematical and statistical models to assess and manage financial risks. They work with insurance companies and other financial institutions to help them make informed decisions about how to price insurance policies and manage their investments. This course may be useful for Actuaries who want to learn more about data analysis techniques, such as binary classification, information theory, and linear regression. These techniques can be used to build predictive models that can help businesses make better decisions about how to manage their risks.

See salaries and explore the career path for Actuary

Financial Analyst

Financial Analysts analyze financial data to make investment recommendations. They work with businesses and individuals to help them make informed decisions about how to invest their money. This course may be useful for Financial Analysts who want to learn more about data analysis techniques, such as binary classification, information theory, and linear regression. These techniques can be used to build predictive models that can help businesses make better decisions about how to invest their money.

See salaries and explore the career path for Financial Analyst

Software Engineer

Software Engineers design, develop, and maintain software applications. They work with businesses and organizations to help them solve problems and improve their operations. This course may be useful for Software Engineers who want to learn more about data analysis techniques, such as binary classification, information theory, and linear regression. These techniques can be used to build predictive models that can help businesses make better decisions about how to use their data.

See salaries and explore the career path for Software Engineer

Computer Scientist

Computer Scientists study the theory and practice of computing. They work with businesses and organizations to help them solve problems and improve their operations. This course may be useful for Computer Scientists who want to learn more about data analysis techniques, such as binary classification, information theory, and linear regression. These techniques can be used to build predictive models that can help businesses make better decisions about how to use their data.

See salaries and explore the career path for Computer Scientist

Data Analyst

Data Analysts collect, clean, and analyze data. They work with businesses and organizations to help them make informed decisions. This course may be useful for Data Analysts who want to learn more about data analysis techniques, such as binary classification, information theory, and linear regression. These techniques can be used to build predictive models that can help businesses make better decisions about how to use their data.

See salaries and explore the career path for Data Analyst