Using Excel for Data Analysis: The Basics

Name: Using Excel for Data Analysis: The Basics
Author: Cliff Brozo

Learn the basics of data analysis in Excel.

Preview the first lesson free — get full access to all 5 lessons.

Course: On-Demand

Welcome to KnowledgeCity's course on using Excel for data analysis. I'm Cliff Brozo, I'm your instructor, and in today's lesson, we're going to talk about the concepts of data analysis and we'll take a sneak peek at what Excel can do for us in helping us analyze our data. The process of analyzing data begins with identifying the data that you need. Once you do that, then you can go about collecting the data. After you've got it, you cleaned it up a bit, you analyze it and interpret what you found. Let's take a look at these a little bit more closely. In the identify process, you have to decide why do you need this information? When you go and collect the data, how are you going to get it? Is it going to be internal or external? Will you use surveys, interviews, questionnaires, focus groups? In order to clean the data, you'd like to remove any extraneous white space, delete the duplicate records, make the formatting the same for all of the data items. During the actual analysis phase, you're going to look for trends, correlations, spot variations, and look for patterns. And finally, in the interpreting phase, you're going to decide on a course of action based on your findings. Now let's take a look at what Excel can do for us in analyzing the data. We are in Excel 365, and I found a dataset that relates to the game of baseball. It's all about the All-Star game. And what it has is each player who played in every All-Star game, dating back from 1933, all the way down until 2015. And that's the most recent version of this data. Now, there's no way that I created all this data. It was an existing dataset, but there are over 5,000 records. Certainly, there's no way that I could do any of this analysis by myself, so I need the help of Excel. And Excel gives me the ability to analyze the data. As long as I'm in the Home tab, I can move over to analyze the data, make sure that I'm somewhere in the data, and click on the Analyze Data tab. What Excel is doing is it's looking to see what can it decide as the trends and variations in this data. And it gives me some suggestions on what I might be interested in. Now here's something, starting position by year. There's one, starting position by game number. There's one for the majority of game numbers. None of these look really exciting to me, but what I can do is modify this, to click on the little settings wheel here, and pick the things that I am interested in. Not interested in individual players or individual years or individual games. I am interested in the teams and the leagues 'cause I'd like to know which team has had the most All-Star players. So I'm interested in team ID and league ID. And I click on update, and it gives me a listing of which teams have been in the most All-Star games. As you can see, the Yankees have been in 418 All-Star games, and the St. Louis Cardinals are next at 314, followed by the Red Sox, the Cincinnati Reds, and the Detroit Tigers. I can insert a pivot table and get the full list of every single team and the number of players that have been on that All-Star team. That's an analysis that I'm interested in finding out. If I go back to analyze data again and look at those circles that didn't tell me anything, I can click on the circle and say insert a pivot chart knowing that it's not what I want. Right-click on it, which brings up the pivot table fields. And in this case, I'm interested in the player ID. And I'm interested in the player ID as both a row and a count. What that's going to do, it's going to tell me the player and the number of times that player has played in an All-Star game. I right-click. Sort this largest to smallest. And what I can see is Hank Aaron has been in 25 All-Star games. He leads everyone. Stan Musial was second. Willie Mays, third. Mickey Mantle, fourth, and so on down the list, all the way down to the people who were in the fewest All-Star games. And there's all 5,000 people in this list. This is just a sample of the things that Excel can do for you without you having to do a lot of work. And it allows you to do data analysis. I'll see you soon.

Intermediate Provider : Cliff Brozo 5 Lessons · 29m in Arabic, German, English, Spanish, French, Portuguese, Chinese

Course Description

In these lessons we’ll explain the concepts behind data analysis. We’ll go over the process for identifying needs, collecting data, cleaning the data, analyzing it, and interpreting our findings. You’ll learn the difference between operational data and analytical data, and we’ll take a deep dive into all of the different chart types that Excel offers. Finally, we’ll go over what math skills we need for performing data analytics, and the different types of analysis you can do.

What You'll Learn

Understand the data analysis process, including identifying needs, collecting data, cleaning data, analyzing it, and interpreting findings
Distinguish when to use operational data versus analytical data
Explore the different chart types that Excel offers
Identify the math skills needed for performing data analytics
Recognize the different types of analysis you can do

Key Takeaways

The course explains the concepts behind data analysis and walks through the process of identifying needs, collecting data, cleaning data, analyzing it, and interpreting findings.
Operational data and analytical data serve different purposes, and the course covers when to use each.
Excel offers a range of chart types, which the course examines in detail.
Certain math skills are needed to perform data analytics, and the course identifies what they are.
There are different types of analysis that can be performed, which the course covers.

Frequently Asked Questions

What does this course cover?

It explains the concepts behind data analysis and the process for identifying needs, collecting data, cleaning the data, analyzing it, and interpreting findings. It also covers the difference between operational and analytical data, the chart types Excel offers, the math skills needed for data analytics, and the different types of analysis you can do.

What skills will I gain from this course?

The course builds skills in analytical skills, Microsoft Excel, analytic applications, analytics, data analysis, and learning analytics.

What is the difference between operational and analytical data in this course?

The course teaches you to recognize when to use operational data versus analytical data, covered in the lesson on Operational vs. Analytical Data.

Do I need math skills for data analysis?

The course goes over what math skills are needed for performing data analytics in its Math Skills lesson, helping you recognize the skills required.

What lessons are included in this course?

The course includes lessons on Data Analysis Concepts, Operational vs. Analytical Data, Chart Types, Math Skills, and Data Analysis Types.