How to Perform Data Analysis in Excel

Video Activity
Join over 3 million cybersecurity professionals advancing their career
Sign up with
Required fields are marked with an *

Already have an account? Sign In »

9 hours 53 minutes
Video Transcription
Hi, guys. Welcome back. I'm Katherine MacGyver, and this is your lean six sigma green belt. Today in this module, we're gonna go over our data analysis and excel. So
in the last module, I said, I'm not going to teach you the formulas because I'd rather you understand the concepts. That is absolutely true. But now I'm gonna add on to it. So you are learning to be an efficiency expert, and one of the important things about efficiency
is knowing what tools you have available and using them as the tools that they are. So that being said were actually going to go over doing data analysis in one of the most prevalent tools
out there in the business world?
So the first thing that I want to air is I have no loyalty to excel. The reason why I chose Excel as what we're going to go over is because it's is ubiquitous as Cheetos and gas stations. It is more common for people not toe have excel
than it is for people to have excel. That being said, I understand that the world is changing. I'm I know that
Google is changing the dynamic Reggie Sweet quite a bit. So if you for some reason are not one of the people who have excel, please reach out to me and we'll figure out a good solution for you. Um, that being said, I have a moral obligation to tell you about your other options out there.
Many tab is the number one. It is the top of the industry. It's the Cadillac or the test Love stats software. If you go for your certification exams to take the exam to be a certified green belt, you may run into
some many tab questions and the certification exams. So
depending on what agency you go with again, don't hesitate to reach out. I'm happy to walk you through many tab. It's actually amazing in that it basically thinks for you. Um, what we love about it is you plug your data in, you upload, or you directly put it in because it has a spreadsheet option, and then it
ask you a whole series of questions like, Is this continuous or discrete?
Is this'll orginal or, um categorical? How does this data work? And then it spits everything out for you. You don't have to do anything other than answer a couple of questions and it says, Boom, here you go, Which is pretty awesome, but you do pay for it. So
many tab is very pricey. Depending on your relationship with your employer,
they may or may not laugh hysterically when you ask them to buy your license. Um, jump is another one of the options JMP I have not used this in many, many years, so I'm not all that hopeful with it.
Other than I do know that for a while it was very popular with lean six Sigma practitioners because it has a few features in it s P S s is very, very common
with researchers and social scientists because it has some data coating aspects to it. So using some of that qualitative data and being able to create some analysis from it, so s pss is also very common. And then, of course, where we're going to spend our time,
which is Microsoft excel with just everywhere. It just like, shows up magic
with that. The first thing that I want to do is make sure that you're getting the most out of your ex Elsom.
It doesn't matter if you have, 03 65 or desktop APS. You're going to need the desktop app for these. Adan's you online version has some limited functionality, or if you have older versions, all of it's going to be there.
This is something that you have, regardless of whether it's an enterprise or personal license. Eso The first thing that you're going to do is go to your file option.
We excuse me, your file, UH, tab, select options and then goto Adan's. We're gonna want to get these add ins added for you. Um, with that, Once you're at your ad in screen, go to the manage at the very bottom and select um,
Excel add in. So it's going to be a drop down. Make sure it's Excel Adan's
Pop the Go button, and you'll get a checklist that looks like this. Depending on the type of licence that your organization have, you may have Mawr Adan's, but the ones that you want to look for will be in any license type. Those are analysis, tool pack and solver, added thes. They're going to be your get down from a lean six Sigma
perspective using, except
so once you get these added, they will show up on your data tab on the far right, you'll get a section that will say Analyze. You'll have these two options. This is where you're going to come from. We are going to use primarily the data analysis or the the devil analysis to a pack
because it gives you the tools already
broken out. So you'll notice is we're looking through this this list and they're quite a few more. I just highlighted his diagram because it's the one that we're going to remember from Yellow Belt. You'll notice descriptive statistics, hissed a grand regression we're gonna talk a lot about in a little bit. That's where you're gonna find all of these. Play with, um,
that being said, there are a couple of not even important hints. There are a couple of cautions that I need to tell you about With Excel. The first thing is you have to be familiar with the test that you want to perform. So if you know what a hist a gram is and you know that you want a history, Graham, you're going to select the history RAM option.
If you don't know what a co variances. And you don't know what type of data that needs to go into a co variance.
You can select the co variance option, which will leave me toe warning number two. It will not tell you if your results are funky. So what that means is is I had hinted at in yellow Boat you can use, um, are continuous data tools to analyse discrete data.
you will end up with some very bizarre, very not accurate results. So with that, it's very important that you understand what type of data are you working with? How does that data need to be arranged? Um, So, for example, in our history, Grams, my recommendation to you is is one
that, um, you play with it?
That's next light. Um, my recommendation to you for a heads diagram because that's gonna be one that you're going to do quite a bit of is that you create your own buckets because excel will create buckets for you, but they may not hit the intervals that you want. So understanding how to arrange your data
and then really understanding what tool and what test you want to apply.
So if you want a regression? Do a regression if you want Descriptive statistics do descriptive statistics. There's no harm in playing with it. Um, and if you ever need some download advice, I can't say it enough. I'm always available to give somebody a moment of advice, but with that, so
Excel is not many tab. It doesn't think for you you This is going back to our last module. When I said that, I want you to understand the concepts. This is why is because you have to understand the concepts when you're using these tools for analysis.
So we did introduce our analysis tool set at some point where we were talking about presidents and their date of birth and how told they are on and the year that they were elected.
That file is available for you to play with on through your cyber a course, so play with it, get familiar. That being said, you do have a homework assignment, so this is a two part homework assignment. The 1st 1 is set up data analysis in your Adan's. Hopefully you followed along and you're super comfortable doing it.
The second thing is, is I want you to create a sample data set. It needs tohave
between 20 and 50 data points. More is better. But I don't know how much patience you have in building your own data set. There is a random number generator in the data analysis tool pack. If you wanted to use that to create some randomized data set otherwise just plug some numbers and
the first thing that I want you to do is use the descriptive statistics tool
and create that table, See what statistics it gives you. You will notice that it gives you, of course, mean median mode. It will also give you a measure of skewed nous. It will give you a range, It will give you variants and I'll give you standard deviation. So the measurements that we talked about in our last module of central tendency and dispersion
you can get all of those from your descriptive statistics table,
so get really comfortable doing it.
The second thing I'd like you to do is create a history ram. So I hinted in the last module, hissed A grams and excel can be kind of funky, create to actually so create one using excel, creating the buckets for you. See what that looks like? And then look at your data and
make some determinations about what logical buckets would be. So think back to our module on measurement scales.
One per bucket is probably too fine. If we look at multiples per bucket, we want to make sure that we're not. We're not being too broad in our measurement scales. So reference the measurement scales and then create some buckets of your own
with a Today we got your Excel data analysis added in and in our next module, we're gonna We're gonna really start talking about data distributions, so I will see you guys there.
Up Next