Data Analysis in Excel with Statistics: Get Meanings of Data

Excel Data Analysis Training. Analyze your business data statistically using the features & functions of MS Excel 2013.
4.3 (29 ratings) Instead of using a simple lifetime average, Udemy calculates a
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
378 students enrolled
$19
$95
80% off
Take This Course
  • Lectures 88
  • Length 7 hours
  • Skill Level All Levels
  • Languages English, captions
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 4/2016 English Closed captions available

Course Description

Teaching 11 Courses on Excel and Data Analysis!

OVER 25,000 visitors visit my blog ExcelDemy dot com every month!!

OVER 15,000 successful students have already taken my online courses since November, 2015 with 375 total Reviews!!!

Last updated: Nov 01, 2016 with 6 Case Studies, and 46 Problems with Solutions.

**************************************************************************************

What students are saying about this course?

~ Very clear, concise explanation of basic and more advanced statistical Excel functions - Donna M Knapp

~ This is an excellent well taught course. The explanations are clear and concise. The course moves along a comfortable pace. I learned a lot from this course and shouldn't have any difficulty applying the concepts to future projects. Well done. - Bill Hengen

**************************************************************************************

Welcome to my brand new course on Data Analysis in Excel with Statistics: Get Meanings of Data.

I want to start with a quote from Daniel Egger. He is a professor at Duke University. 

He says: “No commercial for-profit company that is in a competitive market can remain profitable or even survive over the next five years without incorporating best practices for business data analytics into their operations.”

So learning how to analyze data will be the most valuable expertise in your career in next five years.

Excel will analyze and visualize data easily – this is why Excel is created and this is why Excel is the most popular spreadsheet program in the world. 

Microsoft Company has added new data analysis features, functions, and tools in every new version of Excel. 

Before going into the course: I want to warn you about something. Excel is a just a tool. To analyze data you will use this tool. But analyzing data requires that you know some basic statistics and probability theories. 

Most of the statistics and probability concepts that are necessary to analyze data effectively are covered in your undergraduate level courses. But in this course, at first I have discussed the theory at first, then I have advanced to teach you how to use that theory in business with the help of Excel.

Let’s discuss now what I will cover in this course. It is tough to build a course on data analysis using Excel as so many topics are there to be covered. So I have used the guidelines of Project Management Institute (PMI) to create this course. 

The topics I am going to cover in this course are:

  1. Overview of Data analysis: I will start with an overview of the data analysis. I will describe how you will calculate common measures of your data, I will introduce you to the central limit theory and then I will provide my advice for minimizing error in your calculations.
  2. Visualizing Data: Then I will teach you how to visualize your data using histograms, how to identify relationships among data by creating XY Scatter charts and forecast future results based on Existing data.
  3. Building Hypothesis: Then I will show you how to formulate a null and alternative hypothesis, how to interpret the results of your analysis and how to use the normal, binomial and Poisson distributions to model your data.
  4. Relationships between data sets: Finally I will show you how to analyze relationships between data sets using co-variance, how to identify the strength of those relationships through correlation and then I will introduce you to Bayesian analysis.
  5. Case Study: Summarizing Data by Using Histograms
  6. Case Study: Summarizing Data by Using Descriptive Statistics
  7. Case Study: Estimating Straight-line Relationships
  8. Case Study: Modeling Exponential Growth
  9. Case Study: Using Correlations to Summarize Relationships
  10. Case Study: Using Moving Averages to Understand Time Series

Analyzing Business data is a must need expertise for every employee of a company. Your company will not survive another five years if it does not take seriously business data. And you could be the best employee in your company to direct the business in the smartest way. So keep learning business data analysis with this course.

What are the requirements?

  • First, you must know how to gather and organize data efficiently. A business generates a lot of data and it is your duty to bring those data into Excel so you can perform your analysis with them.
  • Next, you should have a good grasp on creating and using Excel formulas. Most of the analysis in this course will involve Excel formulas. Specifically, you should be comfortable with creating relative, absolute and mixed formula referencing.
  • And finally, you should have a working knowledge of creating and modifying charts in Excel. Charts help to understand your data insight quickly.

What am I going to get from this course?

  • Calculate Mean, Median, Mode, Minimum, Maximum, Quartiles, Variance and Standard Deviation from some numbers.
  • Get an idea about Central Limit Theorem.
  • Analyze a population using data samples.
  • Identify and minimize Margin of Errors
  • Group data, build XY charts, apply Logarithmic Scale and Trend Line on a chart, forecast from some data, and calculate running averages.
  • Formulate hypothesis, interpret your analysis, and get an idea of the limitations of building hypothesis.
  • Learn Normal Distribution, Exponential Distribution, Uniform Probabilities, Binomial Distribution, and Poisson Distribution.
  • Calculate Co-variance and Co-relation among data.
  • Perform Bayesian Analysis.

What is the target audience?

  • Business professionals who regularly try to find out the insights from their data.
  • Statistics lovers like me who think people behavior can be measured with statistics.
  • Aspiring data analysts.
  • Want to add a new expertise in their CV
  • Not for someone who hates data and Excel :)

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Welcome to the Course!
03:20
  1. Welcome to the course. The instructor of this course is thankful to you that you have joined in this course.
  2. Know which versions of Microsoft Excel has been used to create this course and get an idea which versions you can use to follow this course.
  3. Have an idea how to use this course to get the best from it.
Article

Get an idea on what you should know before joining this course. You can call them the pre-requisites of this course.

Article

Download the course resources from this lecture.

Article

Get to know how to instantly contact me when you face an Excel, Statistics or course related problems.

Section 2: [Essential Excel Knowledge 1] - Excel Formula Basics & Important Excel Functions
10:28

In this video lecture,

  1. You will be introduced with the operators that are used by Excel.
  2. You will also learn how to use different operators in Excel.
09:31

The operators have working hierarchy. All the operators in a formula do not work at the same time.

In this lecture,

  • You have learned all about Excel operator precedence.
07:17

In this video lecture,

  • You will learn how to insert a built-in function into a formula.
09:40

Excel is nothing but a word processor if you deduct Excel's capability to use functions. Functions are the hearts of Excel. In this video, you will learn how to insert Excel functions into a formula.

You will also learn how to use effectively Insert Function dialog box.

05:23

In this video lecture,

  1. You will learn with examples how relative referencing works in Excel formula.
03:23

In this video lecture,

  • You will learn how to work with Circular Reference in Excel.
07:48

In this video lecture,

  • You will learn how to give a name to a cell or a range.
08:01

In this video lecture,

  • You will learn how to give a name:
    • To a constant
    • And to a formula.
05:28

In this video lecture,

  • You will learn how to use Union (,) and Intersect ( ) operators in Excel.
07:51

In this video lecture,

  • You will learn about Excel’s one of the most important functions. The function is SUM function.
06:54

In this video lecture,

  • You will learn Excel’s AVERAGE, MAX and MIN Functions.
07:09

In this video lecture,

  1. You will learn about 5 Excel important functions:
    1. INT
    2. MOD
    3. ROUND
    4. RAND
    5. And RANDBETWEEN Functions.
03:17

In this video lecture,

  • You will learn about three count related Excel functions. They are:
    • COUNT Function
    • COUNTA Function
    • COUNTBLANK Function.
07:06

In this video lecture,

  1. You will learn
    1. How to make a 3D-Referencing,
    2. How 3D-Referecning works in different ways
    3. And how to define a name for a 3D-Reference.
07:35

In this video lecture:

  1. You will be introduced to the basic concept of Excel array formula.
  2. You will learn how to enter array formula into cells in different ways.
  3. And you will learn how to edit array formula.
05:42

In this video lecture:

  1. You will see in step by step by procedure how an array formula works internally in Excel.
  2. To demonstrate the process, I have used Excel’s AVERAGE() and IF() functions.
08:11

In this video lecture,

  1. We shall learn the syntax and use of the INDEX() function.
  2. INDEX() function is the only function that has two types of syntax; you will work with both of them.
04:32

VLOOKUP() is one of the top 10 Excel functions.

In this video lecture: you will learn VLOOKUP() function with multiple examples.

Section 3: [Essential Excel Knowledge 2] - Excel Tables & Using Structured References
06:42

Topics covered in this lecture

  1. In this video tutorial, you will learn the basics of a table.
  2. You will learn how to create a table.
  3. You will learn the differences between a table and a range.
07:22

Topics covered in this lecture

  1. In this video tutorial, you will learn how to Insert and Delete columns and rows in a table.
  2. You will also learn how to add Total Row in a table and how to work with the Total Row.
06:12

In this video lecture, you will learn basically two topics:

  1. How to highlight duplicate values from a table or a range.
  2. And how to remove duplicate values from a table or a range.
10:44
  1. In this video tutorial, you will learn how to Sort a Single Column or Multiple Columns of a Table.
  2. You will also Learn how to Filter a Column or Multiple Columns of a Table.
  3. And finally you will be introduced with the Custom Auto-filter dialog box that you can use to Filter more options.  
04:16
  1. In this video tutorial, you will learn how you can Insert Slicer to Filter a Table.
  2. You will learn how to Delete a Slicer.
  3. You will also learn how you can Remove Filtering from a Slicer.
  4. And you will be introduced with different Slicer Settings.
12:44

In this video lecture,

  • You will learn all the ways to use structured references with Excel tables.
08:31

In this video lecture,

  • You will learn how to use structured references in an Excel table.
Section 4: Foundational Concepts of Statistical Data Analysis
07:10

To calculate centrality of a data set, you need to find out the Mean, Median and Mode values from a data set. This lecture will teach you how to calculate them using Excel functions.

06:01

Calculating maximum, minimum, and quartile values from a data set is easy using Excel. This lecture will teach you those topics.

10:35

Variance and Standard deviation are the two most important ways to figure out the  dispersion  of a data set. You will learn here not only how to calculate them, but also you will visualize them.

03:30

In this lecture, you will get the concept of Central Limit Theorem with a real life example.

08:34

This lecture is very important. You will get a brief and effective method for analyzing a population using data samples. Get the concepts of this lecture very well.

03:22

Statistics is not a pure science. When you will come up with a decision using the principles of statistics, you have to keep in mind that your analysis might be wrong in many ways. This lecture will teach you to identify and minimize the sources of error while you are working with statistical analysis.

Section 5: Visualizing Data
05:20

Creating histogram from a data set is one of the main focus when you're working with data distributions. In this lecture, you will build histogram using Excel's Data Analysis ToolPak.

02:36

XY Charts show the trend between two data sets. Creating XY charts are not tough. This lecture will teach you XY chart in details.

05:36

When you work with a data set having extreme large and small number and you make charts using that data set, reading that chart will be not so easy. Converting an Axis using Logarithmic Scale will make your chart readable and effective. This lecture will teach you how to do that.

02:43

This lecture will teach you how to add trend lines to a chart. Even if you want to forecast values from existing values, you can do that using trend line. This is the topic of this lecture.

07:09

Forecasting future results can be performed in two ways: using Excel's AutoFill Handle Tool and FORECAST() function. Both of these ways are discussed in this lecture.

06:03

The calculating of running average is simple. This lecture will teach you how to calculate running average and 3-day running average.

Section 6: Testing a Hypothesis
07:23

Formulating hypothesis based on a data is set is not tough, but you have to be very careful to do that. This lecture will teach you to do that.

05:23

This lecture will teach you how to interpret the results of your hypothesis.

03:08

In this video lecture, you will learn about the limits of testing a hypothesis.

Section 7: Utilizing Data Distributions
07:57

Normal distribution or Bell Curve or Gaussian Curve is one of the most important topics in Statistics. This lecture will cover the topic in great details.

07:22

In this lecture, you will learn how to calculate exponential distribution in Excel.

06:56

This lecture will teach you how to calculate binomial distribution. You will also learn how to use it with a proper example.

Section 8: Measuring Co-variance and Correlation
04:54

This lecture will teach you how to visualize Covariance with an example.

05:37

Learn how to calculate covariance between two columns of data pairs.

0803 - Calculating Co-variance among Multiple Pairs of Columns
04:14
0804 - Visualizing What Correlation Means
09:11
03:50

This lecture will teach you how to calculate correlation between two columns of data pairs.

Section 9: Case Study: Summarizing Data by Using Histograms
0901 - Overview of the section
Article
0902 - Stock Return Analysis Using Histograms
04:53
0903 - Common Shapes of Histograms
02:58
6 More Problems and Solutions
Preview
Article
Section 10: Case Study: Summarizing Data by Using Descriptive Statistics
1001 - Overview of the section
Article
1002 - How to Get Descriptive Statistics Using Excel's Data Analysis ToolPak
02:56
1003 - What defines a typical value (or centrality) for a data set?
06:16
1004 - How can I measure how much a data set spreads from its typical value?
03:26
1005 - What do the mean and standard deviation of a data set tell me about?
05:19
1006 - How can I use descriptive statistics to compare data sets?
02:57
1007 - How to easily find the second largest or second smallest number in a data
01:32
1008 - How can I rank numbers in a data set?
07:44
1009 - What is the trimmed mean of a data set?
03:28
1010 - Alternative of Data Analysis ToolPak
01:53
1011 - Why financial analysts use Geometric mean instead of arithmetic average?
04:39
16 Problems and Solutions
Article
Section 11: Case Study: Estimating Straight-line Relationships
1101 - Overview of the section
Article
1102 - A brief introduction to dependent and independent variable
03:21
1103 - The relationship between monthly production & operating costs
08:52
1104 - Meaning and significance of R-square value
01:31
1105 - Standard error of regression to measure the accuracy of a relationship
03:30
1106 - Find intercept, slope and R-square values using Excel's functions
01:30
12 Problems and Solutions
Preview
Article
Section 12: Case Study: Modeling Exponential Growth
1201 - Overview of the section
Article
1202 - How can I model the growth of a company’s revenue over time?
09:32
6 Problems and Solutions
Article
Section 13: Case Study: Using Correlations to Summarize Relationships
1301 - Overview of the section
Article
1302 - Measuring correlation using Excel's Data Analysis ToolPak
03:22
1303 - Filling the matrix
02:18
1304 - Relation between correlation and R-square value
01:34
5 Problems and Solutions
Article
Section 14: Case Study: Using Moving Averages to Understand Time Series
1401 - Overview of the section
Article
1402 - How to apply the Moving Average based trend line to show a trend
04:20
1 Problem and Solution
Article

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Kawser Ahmed, Founder ExcelDemy.com | Data Analyst

Love to see you here!

I am Kawser Ahmed, father of two beautiful angels (Marissa and Arissa), a full-time data analysis blogger (using Excel & other tools), corporate Excel trainer, & entrepreneur.

I love to make courses in the combination of both theory and practice.

Monthly 30,000 Excel users visit my Excel blog ExcelDemy (follow the website link in the profile).

I love to learn and teach (teaching from my eighteen)!

Visit my blog!

You will find plenty of useful posts, articles, video tutorials, and important Excel resources.

What I love most is: add some value to people around me, believe me!

Here are some of my working areas:

  • 1. Corporate Budgeting, Planning & Forecasting
  • 2. Mission-critical financial processes
  • 3. Month-end close processes
  • 4. Big Data Analysis using Excel
  • 5. Company Reports (Sales, Marketing, Administrative)
  • 6. Large Projects using Excel VBA
  • 7. Dashboard Design using Excel
  • 8. And Basic Accounting


I am always around you to help and support! 

Just knock me!

Ready to start learning?
Take This Course