Foundational Statistics for Data Analysis with Excel: Analyze Data with Excel

0:07<v ->Hello and welcome.</v>

0:09My name is Jonathan Barrios

0:10and I'm excited to be your trainer for this course

0:13on foundational statistics for data analysis with Excel.

0:17In this first skill,

0:18we're gonna start off with data analysis with Excel,

0:22and we're gonna be talking about concepts.

0:24So let's check out what we're gonna learn

0:26in this first skill.

0:29All right, so we're gonna dive into

0:31data analysis with Excel.

0:33And in this very first skill,

0:35we're gonna talk about setting up Excel.

0:38And then we're gonna talk about using Excel

0:41for data analytics

0:42because it's not always the case

0:44where Excel is gonna be the best tool for data analytics

0:47and we'll dive into that a little bit more.

0:50And then we're gonna dive into the user interface

0:52just to open it up and get started and check out the ribbon

0:56and the file menu and things like that.

0:58And then we'll talk about the limitations,

1:00again, through example,

1:02and then we're gonna dive into data types

1:04and then some Excel formulas and some of the limitations

1:08and why we might need to use functions.

1:11And then we'll talk about the data Analysis ToolPak.

1:15And again, what the data Analysis ToolPak is,

1:17is added functionality to extend Excel's ability

1:21to perform statistical tests.

1:23And that's something that you add on

1:25and that's what we'll cover in that skill.

1:28And at the very end, there's a challenge

1:30that gives you the opportunity

1:31to apply everything that you've learned so far.

1:36All right, so that's it for the introduction.

1:39I hope this has been informative,

1:41and I'd like to thank you for viewing.

0:07<v ->Alright, in this video we're gonna jump into Excel</v>

0:11and the context of data analysis,

0:13why you should use it in this course,

0:16and maybe why you would not use it.

0:18It's not generally my recommended tool for data analysis.

0:21And we'll talk more about that.

0:23And then we'll see how easy it is to get set up.

0:26We'll go to the Microsoft website

0:27and talk a little bit about the online version

0:29and the desktop version, the standalone version,

0:32and then we'll get started with Excel

0:34in the subsequent videos.

0:36All right, let's check it out.

0:38Alright, so Excel for data analysis.

0:41Alright, let's check this out.

0:44So when to use Excel for data analysis.

0:47You might be thinking, well,

0:48why don't I use it all the time?

0:50Well, there are many reasons why you wouldn't do that.

0:53It's generally not my recommended tool for data analysis.

0:56And there's many, many reasons.

0:58First of all, anything Excel can do,

1:01R or Python can do better and much faster.

1:05So R and Python are programming languages

1:09and we'll talk more about them in subsequent videos

1:12and that's just the first reason

1:13that there's a better way to do this.

1:15That's the first one.

1:16Number two is that it has row and column limitations.

1:21You can't really work with big data

1:22if you're working with Excel,

1:24so it depends on your application

1:26and where you're using Excel.

1:28And it's also not appropriate for historical data storage

1:32and we'll talk more about that, but that's another reason.

1:35And there's some companies that use it

1:37as a single source of truth

1:38and whenever there's some updating going on,

1:42it's very easy for data to get lost,

1:45especially when you're updating.

1:46So it's definitely not a good use

1:48for historical data in storing that data.

1:52Let's go back to these row and column limitations.

1:54So the rows we're talking about,

1:58I would say 1 million tops.

2:01And just because you can get a million rows

2:03doesn't mean that you're gonna be able to work with

2:05a million rows.

2:06Depends on your computer

2:07and the kind of data that you're working with.

2:09It can be very, very slow.

2:10And as far as columns are concerned,

2:13we'll have a top of 16,384, I think

2:17columns or something like that.

2:21And so there's a limitation on the columns as well.

2:24That's a lot of columns,

2:26and a lot of rows to be quite honest.

2:30However, it's gonna get very, very slow and sluggish

2:32whenever you start to get anywhere near that.

2:36Okay, and there are many other reasons why not

2:39or why you wouldn't use Excel for data analysis.

2:41And definitely I would recommend Googling it.

2:44For Pandas users you can see the Pandas documentation

2:48and Pandas is a open source library for Python

2:51that allows you to work with data

2:53and you can see comparison between Pandas and Excel.

2:57And I'll also link that documentation above this video

3:00if you wanna take a look at that.

3:02That might even help you get an idea

3:03of what Pandas is, right?

3:05So for Pandas users, this will give you an idea of Excel

3:09and vice versa.

3:11Alright, so I just talked about why it's not the best

3:14to use Excel.

3:15So why are we using it in this course?

3:19Great question.

3:19Well, first it's a great visual learning tool.

3:23When you're working with Pandas,

3:25it's a programming language,

3:26so you're gonna get code

3:28and you're not gonna be able to see things

3:30like you do in Excel.

3:32Excel is a very visual tool.

3:35It uses a graphical user interface

3:37and you can see what you're working on.

3:38Whereas with Pandas and programming languages in general,

3:41you're just typing code and something happens

3:43and you'll get an output.

3:44But a lot of that, what happens in Excel

3:47gets abstracted away.

3:49And that's kind of what I wanted to mention here.

3:52And it's also easy to use without much training.

3:54So that's why it's so popular.

3:56And again, not the greatest tool for data analysis,

3:59but it is ubiquitous and everybody uses it.

4:01It's hard to imagine an office that hasn't,

4:03that doesn't use spreadsheets in one form or another.

4:06And it's easy to share, not necessarily collaboration,

4:11but it is easy to share so that there is another plus there.

4:14And that's why we're using it

4:15because I can share these workbooks with you

4:18and it's still used today and it's good to know.

4:20So as I said before,

4:22it's hard to imagine an office environment

4:24that's not using a spreadsheet.

4:26So I chose Excel for this course

4:27because it's a great teaching tool,

4:30especially when it deals with analytics.

4:33I feel that doing this the slow way manually

4:37is a really great learning experience.

4:39For example, when you're doing a T test in Pandas or Python,

4:43you're just typing one line of code

4:45and then you get a P value, it just shoots it out,

4:49it's abstracting the process.

4:51But in Excel it's kind of cumbersome to perform statistics,

4:54but at least you go through every single step.

4:57And that's why I'm using it in this course

4:59because it's a great teaching tool.

5:01Alright, so again, it's not generally what I recommend

5:05for data analysis.

5:07So what am I talking about?

5:08Excel.

5:09Excel's not generally recommended for data analysis.

5:11However, we are gonna get a lot of use out of it

5:14in this course and in this scale.

5:16And we're going to dive into Excel

5:19and what is great about it,

5:21how to get some extended functionality out of it

5:24so that we can perform statistics

5:26using the analysis tool pack and so on.

5:29All right, so let's jump into Excel.

5:32And go to Microsoft Office website at office.com.

5:37And I will link this above the video,

5:40but it's basically office.com.

5:42And here you have some choices.

5:46So if you just sign in, if you have an account, great.

5:49If not, you might need to create an account.

5:52And you can also get a free version here.

5:54I wouldn't recommend using the free version

5:56and would instead recommend that you get

5:59a trial version for a month.

6:01So you can do that by clicking on Get Office.

6:04And let me just point out that

6:07whenever you do sign up for a membership

6:10or once you do have that free month trial

6:12to see if you wanna continue with Excel or not,

6:15you'll have the option of using Excel,

6:17not just as a standalone application,

6:21but also an online version.

6:23So there's two versions.

6:24There's an online version and a desktop version.

6:27So let's take a look at that now.

6:30Okay, so this is what we're gonna dive into

6:33in the next video,

6:34but I wanted to show you this workbook.

6:36And what is a workbook?

6:37Well, a workbook is what you see here,

6:41and we have two worksheets.

6:44One is called start and one is called solutions.

6:48And this is the online version,

6:50you can tell because it has this sort of green bar

6:53at the very top.

6:55And this is the standalone application version.

6:58And you can see it's gray at the top and not green.

7:01So that's the main distinction.

7:02So let's look at the difference.

7:04This one also has a little bit bigger.

7:07It looks a little bit bigger.

7:08It's at 160.

7:09This one's also at 160.

7:11It's not as big, but I could zoom it in even a little more

7:15by clicking on this plus arrow here, something like that.

7:18And that gets it a little closer,

7:20but as you can see, there are two different versions

7:22and they're slightly different.

7:24And you can't use the statistic analysis tool pack,

7:29the analysis tool pack that we're gonna install later.

7:31You can't use that here in this version,

7:33in the online version.

7:35That's why I recommend that you use the desktop version

7:37for this course.

7:39All right, so that's about it for this video.

7:42Just an introduction into why you would want to use Excel

7:46for data analysis and when that might be a good choice

7:49and when to maybe consider using a programming language

7:52or an analytics suite.

7:54And we'll talk about those in upcoming videos as well.

7:57All right, I hope this has been informative

7:59and I'd like to thank you for viewing.

0:07<v ->In the last video, we talked about Excel for data analysis</v>

0:11and when it's good to use Excel for data analysis

0:14and when it's not.

0:15And why I decided to use it in the course.

0:18So we kind of got a general idea of what the pros

0:20and cons of Excel are and how there's an online version

0:24and a desktop version.

0:26Well, we're gonna do in this video is talk about the

0:28differences between the online version

0:30and the desktop version,

0:32and look at the ribbon, the spreadsheet areas of rows

0:36and columns, the limitations, and the different ribbons

0:39and how those work.

0:41And then the difference between the analytic tool pack,

0:43how it works on one version

0:45and not the other version.

0:46All right, let's get started.

0:49Okay, so we already talked about the online version,

0:52and this is the online version.

0:54And so the difference here is that you

0:56have this green tab at the very top.

0:59The standalone version, it's all gray at the top.

1:02And so that's the main distinction visually, right?

1:06And there are another distinctions, other distinctions.

1:09For example, if you go to data,

1:11and then you go to analysis tools,

1:13you have this analysis tool pack.

1:15We'll be installing this later so

1:16that we can perform statistical tests.

1:19Without this, you would not be able to do that,

1:22and here you do not have that data option.

1:26And that's because it's not available on the online version.

1:29It's only available on the desktop version.

1:33All right, so everything else should be the same though,

1:36more or less the ribbons are a little bit different.

1:38So let's take a look at the desktop version.

1:41So first we have what's hidden out of view

1:44is the file menu up here.

1:45So you have all of these file menus,

1:48which are sort of repeated in the ribbon.

1:51So I'm gonna show you this ribbon.

1:52So if you click on home twice, it'll go away.

1:56But if you click on it once, you'll see this ribbon pop up.

1:58And this ribbon is giving you a lot of great stuff.

2:01So first, what it's doing right about here,

2:04this is all for text basically.

2:07So we're working with text,

2:09and this is something that you would see on a text editor.

2:13And then you would see something here.

2:15These are for the data type.

2:17So you can change the data type.

2:18Let's say you have a number and you want it to be a percent,

2:20or you want to take some of the decimals off, right?

2:24So the rounding, you can actually do that here.

2:27And so this is a collection of different tools

2:30that are on the home because they're probably

2:32used the most.

2:34And here we have some conditional formatting

2:36and here's some more formatting.

2:38And then here you can insert different things,

2:41like you can insert a row or a column or delete them

2:45and do some formatting.

2:46And then here you have some filtering,

2:50and selecting, and auto sum, and clearing,

2:53and some additional tools.

2:54So let's take a look at each one of these.

2:56So that's the home ribbon.

2:58The insert ribbon has a lot of stuff.

3:01So right here we have pivot tables,

3:05and some tables, and you can add pictures,

3:07and some 3D objects.

3:09And then here you would have some maps and graphs,

3:13and things that you can add in.

3:15And because we're in the insert tab,

3:17and here you can insert some graphs or charts.

3:21And so you can click on recommended chart.

3:23Let's say we had, maybe if you click on this right now,

3:27it'll say, I don't know, there are no recommendations.

3:30But if you add some numbers here, 3, 3, 2,

3:35and we're just looking for any number right here.

3:37And then you highlight this,

3:38and then you click on recommended charts,

3:41then you'll see that it's recommending these charts,

3:43and you could say, "Well, I would like to have this one."

3:46And then it just builds that chart for you.

3:48Okay, so let's take that off,

3:50and let's delete these,

3:52and we'll move on.

3:53So what else do we have?

3:55Okay, so that's an insert.

3:57And so we were talking about recommended charts,

4:00and here you can add links, comments, and some text box,

4:04and some other things that you can insert.

4:06Draw is pretty straightforward, so if you want

4:08to draw something, well here you go.

4:10This is how you would do that.

4:12And you go to the draw ribbon

4:14and then give these options mostly

4:15highlighting and things like that.

4:18Page layout is very straightforward

4:21and is dealing with page layout.

4:23So background and margins, orientation, colors, fonts,

4:26and things like that.

4:28Formulas, this could be very helpful.

4:30I don't use this that much,

4:31but we'll get into formulas later

4:34and then we'll talk about that.

4:35So this is all just about formulas.

4:37So here we're inserting a function.

4:39There's financial, logical, text, date.

4:42And so these are functions that are put into

4:44different categories for ease of finding,

4:47and use, error checking and things like that.

4:51Then we go to data.

4:52And this is something where you

4:53can actually connect to data.

4:55So you can get data from somewhere else.

4:57You can also get data from a picture,

5:01you can refresh things,

5:03and you can also put in stocks or geography.

5:07So you can kind of change things up here.

5:10And this is the, since this we're in the data tab,

5:12you get a bunch of other things like sorting, filtering

5:15and advanced tools, text to columns, remove duplicates,

5:20which is very useful and we'll talk more about that later.

5:23And then you have this, again, the analysis tool pack.

5:26And this is something that we're gonna add

5:27so that we can do some things like T-tests

5:30and be able to perform AB and AB testing

5:33or AA and AB testing.

5:37Okay, so that is the data layout or data ribbon.

5:41Now let's look at the review ribbon.

5:43So here again, I don't use this too much,

5:45but you can protect your notebook here and your worksheet,

5:49and you can also look at your statistics

5:52and maybe use some spelling and things like that.

5:54So this is adding some comments,

5:56show comments and some notes.

5:58And the view tab, the view ribbon

6:01is pretty straightforward for view.

6:03And if you go here and you look at this,

6:05you're gonna see a lot of these repeated, right?

6:07So this is just giving you that visual sort of format

6:11that makes Excel so easy to use and accessible, right?

6:16That's probably why it's so popular.

6:18And then finally we have this tell me section

6:21where you can say, "Oh, I want a pivot table."

6:23Right?

6:24So I can just type in pivot.

6:25And before I'm done typing,

6:27it says pivot chart, pivot table.

6:29Cool, that's what I wanted.

6:30I click on that.

6:31So this tell me section is great when you

6:33don't know where something is.

6:35And that happens to me a lot. (chuckles)

6:37So I just keep it on the home tab.

6:39And also you have this quick access toolbar at the very top.

6:44So you can go home.

6:45If you click on that, it takes you to the home,

6:47so that you can grab some documents.

6:50You can also, what is this one right here?

6:53Saving, undo, and repeat clear.

6:57And if you click on this, three little dots,

6:59you can actually customize this.

7:01So you can add more commands, you can add sorting,

7:03ascending, descending, whatever you use the most.

7:06You can definitely put that in the quick access toolbar.

7:10And let's talk about the rows and column limitations.

7:13So we say that we have a million rows, let's prove it.

7:16Well, if I'm gonna use shift command down arrow,

7:21or what did I do?

7:22So command, command down arrow.

7:24So that takes me to the very bottom.

7:26And here we have 1,048,576 rows.

7:30If I use command and go back up,

7:32and I believe on Windows, I'm on a Mac.

7:34So on Windows you would use control down arrow.

7:38And now let's take a look at the max columns.

7:40If I use control right arrow goes all the way to the right

7:45and you get 16,384 rows. (chuckle)

7:48Or columns, I should say.

7:50And let's talk about how that works.

7:52Well, you have A, B, C, D, all the way to, it goes to Z.

7:56And then it goes to AA, AB, C, D,

8:00and it keeps doing that.

8:01And then it'll cycle to the next letter in the alphabet,

8:04which would B, so BA, B, C, and so on and so forth.

8:07And if I go to the very end here, we are on E, XE,

8:14and then XEZ, and then XEA, B, C, D,

8:17and then it stops on D for whatever reason,

8:20and that's the max 16,384.

8:23All right, so that's it for the user interface for Excel

8:27and checking out some of those limitations

8:29and the differences between the online

8:31and the desktop application version of Excel.

8:35And how you can install the stat,

8:37the analysis tool pack in the online version.

8:40It only works in the desktop version.

8:42In the next video, we're gonna cover data types.

8:46I hope this has been informative,

8:47and I'd like to thank you for viewing.

0:07<v ->In the last video, we talked about the user interface</v>

0:10and the differences between the online version of Excel

0:13and then the standalone desktop version of Excel

0:17and how you can't use the analytical tool pack

0:19with the online version,

0:20but you can use it with the desktop version.

0:24In this video, we're gonna dive into data types,

0:27and it's really important that we do that.

0:28This is just, again, a review

0:30if you're already familiar with Excel.

0:32So definitely, if this is a review for you, review it,

0:35or just feel free to skip forward.

0:37However, it's really important that we cover this

0:40because again, this is available to beginners.

0:42So if you don't know about these data types,

0:44there may be some sort of confusion later down the road.

0:46And this will be pretty straightforward

0:48and it won't take very long.

0:49So let's dive into Excel data types.

0:54Okay, so here we have the desktop version of Excel,

0:58and what we're gonna do

0:59is take a look at each one of these data types.

1:02So here, we have player.

1:04So there's some sort of a game, right?

1:05So you have score_1, score_2, some results,

1:10the average of those scores,

1:11and then the date that this took place.

1:15Okay, so this took place in January of 2021.

1:18And let's take a look at this.

1:19Well, so what data type would this be?

1:21So there are multiple data types.

1:23So this would be considered a text data type.

1:27So go ahead and write in the data types

1:28below each one of these.

1:30And you wanna check your answer with the solutions,

1:34and we'll do that at the very end.

1:35So right here, we have...

1:38Let me write in all the different kinds of data types.

1:41So text, we have numbers,

1:45these can be integers, floats,

1:47or ones and zeros for Boolean values.

1:49So these are numbers.

1:51And then we have date and time, right?

1:55So these will be the same objects, same data type.

1:59And then we have logical.

2:01And then we have...

2:03We already talked about logical,

2:05and then there's dates and let's see what else.

2:10Yeah, we can also do error.

2:13Okay, so these are our dated types.

2:15So let's match them to the bottom here.

2:18So that's what we're doing in this video.

2:20So this is clearly text, right?

2:23And we know that

2:24because the only thing that's inside of this column is text.

2:27So it's good that we have the same data type.

2:30That's usually very helpful.

2:33For example, if you wanted to sum all of these

2:36and then you had a name in there,

2:38well, you couldn't add name with some numbers,

2:40so that's why you wanna have them

2:43to remain the same data type.

2:45Okay, so this one and this one,

2:46what do you think these are?

2:47Well, these are numbers.

2:50If I drag this little handle over,

2:52it'll give me the same value.

2:54So that's a nice little feature

2:56for dragging this handle down and up

2:59so that you repeat that value

3:01across multiple columns or rows.

3:03Okay, so we have text, numbers.

3:06And then what is this one right here?

3:09So let's go to the solutions

3:11and you can see that it says logical.

3:13So let's dive into that.

3:15So let's first type in logical

3:19and let's click on this.

3:21Whenever I do that, this value,

3:23you're gonna see it right here

3:24in this sort of formula window,

3:27what's the content of that formula.

3:29So this is logical.

3:31So the first one, double-click here,

3:33and then you see what's actually happening.

3:35So we're saying B2 here is greater than 50

3:41and C2 is greater than 80, which these are two.

3:47That is not the case for either of these,

3:49so you get a FALSE.

3:51If these are both true,

3:53if this was 51 and this was 81,

4:01it's now TRUE.

4:02And that's why it's logical,

4:04because it's using this AND, which is a logical operator.

4:09We'll talk more about this,

4:10but there's gonna be IF, AND,

4:12or different kinds of logical operators.

4:16And this is a logical data type,

4:17so that's the takeaway here.

4:19Not necessarily what we're doing,

4:21even though that's pretty clear.

4:24You can see that the condition here, same thing,

4:26we're checking to see if these two scores,

4:29the first one is over 50 and if the second one is over 80.

4:33And we can go to a true one right here.

4:35So this one's over 60 and this one's over 80,

4:38and that's why it's TRUE.

4:40Okay, so this is the, again, the logical data type.

4:43Let's keep going.

4:44So here, we have an average,

4:46and this first one right here is an error.

4:49So let's go back to the solutions.

4:51So you can see clearly it's an error data type.

4:53And I'm referring to these.

4:55I'm not referring to the ones that are correct,

4:57just these data types right here.

4:59And those are called error data types, okay?

5:04And then let's talk about that.

5:06So here, what's the error?

5:08Well, first we're dividing,

5:09we're adding B and C scores, right?

5:15And then we're dividing them by two, I believe.

5:17Yeah, so that we can get an average

5:19and that is 34.525.

5:22Here, we were dividing this score by zero.

5:27So you're gonna get an error

5:29whenever you try to divide by zero.

5:31So that's a very common error

5:33and that's why it's an error data type.

5:34And we're again, just,

5:35we're really focusing on these errors right here.

5:38All of these worked fine and these are functions

5:40and we'll talk about those a little bit later.

5:43Or actually, these are formulas

5:45and we'll talk about functions a little bit later.

5:47Okay, and so this last one is pretty clear.

5:49This is a date/time object

5:53and that's pretty straightforward.

5:55Let's look at the solutions.

5:56And that's exactly what we have.

5:57We have text, numerical, logical, error, and date, okay?

6:04And let's go back to the start.

6:05And there's things that you can do with these.

6:08For example, here, if you wanted to add the sum,

6:10you could just say something like equal this one

6:14plus this one plus this one,

6:18and so on and so forth.

6:19We could keep doing this, C2, C3, C4.

6:23And what is that equal? 189.

6:25C2, C3, C4.

6:27And that's what these scores add.

6:28And that's why it's good to have these

6:30in that sort of data type.

6:32But you can also use functions

6:33so that you can just say something like SUM

6:36and then you can grab all of these scores up to there

6:40and then you get the sum of all of those.

6:42And that's why it's important

6:43that they're all the same data type.

6:45All right, so that's it for Excel data types.

6:47Again, just a review.

6:49In the next video, we're gonna actually start using these

6:52and start adding numbers together

6:53and working with different cells.

6:56And in doing so, you're gonna quickly find out

6:59that there's a limitation

7:01and there could be an easier way to do things,

7:03and that's when we jump into functions.

7:06So we'll check out formulas first and do things manually,

7:08and then we'll see why it's advantageous to use formulas.

7:12I hope this has been informative

7:13and I'd like to thank you for viewing.

0:07<v ->Welcome back.</v>

0:08In the last video, we talked about Excel data types

0:12and in this video, we're gonna talk about formulas

0:16and how to use those data types to perform math operations.

0:20So we're gonna keep this focused on math operations

0:23and the reason for that is I think it's the easiest way

0:26to demonstrate the usefulness of using functions.

0:29When you do this manually,

0:30there's gonna be an easier version

0:31and that's what we're gonna lead into.

0:33And then in the next skill, we'll jump into functions.

0:37But for now, let's check out the Excel data types

0:40and use them to write our own formulas.

0:45Okay, so here we have addition, subtraction, division,

0:49multiplication, percentage, and exponentiation.

0:54So let's talk about each one.

0:55So how do we do 1 + 1?

0:57Well, if you just typed in 1 + 1 enter,

1:01nothing would happen.

1:02And the reason for that

1:04is that you need to add an equal sign.

1:06So that's what creates a formula.

1:08Once you have that formula, then you get an answer, right?

1:12And you don't have to use a parentheses either.

1:15You can and it'll still give you the same answer,

1:18but it's optional.

1:19But some people prefer that,

1:21especially if you're doing multiple operations

1:24and it might make sense to do that,

1:25it might be cleaner.

1:27And we will dive into parentheses

1:30when we're using functions again,

1:32but for right now, to do 1 + 1,

1:35this is how you would write it.

1:36It's = 1 + 1 with or without the parentheses.

1:40Then you get that answer. So how about trying this one?

1:44So how would you subtract 2 from 1?

1:47And again, this is why Excel is so great.

1:49It's pretty intuitive.

1:51So from what I've shown you already,

1:53you could see that it's basically like this

1:55and you'll get 1.

1:56And you don't have to use the parentheses.

1:58So how about 15 divided by 5?

2:01Equal sign and the division symbol

2:05is this slash and then you get 3.

2:08All right, so let's do multiplication.

2:10So multiplication isn't written like this.

2:13And let me actually be very clear about this.

2:20You don't write it like this.

2:22If you do that, we found a typo in your formula

2:25and we tried to correct it to.

2:28And look what it does. It actually changed it for us.

2:31And if we go back there and double click on it,

2:32you see that it changed it from that X to this asterisk.

2:36And that's the multiplication symbol.

2:38So that's the correct answer.

2:405 times 5 is 25.

2:42And percentage. What do we do here?

2:44Well if we do 0.5, then you get 0.5.

2:49And if you grab this and then you type,

2:52click on this percentage style,

2:54then you get 50%, right?

2:56So 50% is 0.5, it could be 0.50.

3:00And you can also change that from a number to a percentage.

3:03Okay, and so here we're gonna leave it as a percent.

3:06And exponentiation, this is how you would write it.

3:08With an equal sign first.

3:11And you use this caret, the up caret.

3:18And you get 4.

3:19So 2 times 2 is 4.

3:22And we could try this with different numbers.

3:23So let's take a look at the solution so far.

3:27Actually let's keep going

3:28and look at the solution at the very end.

3:30I think that's always the best practice anyway.

3:32Okay, so let's jump over here.

3:34So now we have these two sets of numbers ,

3:36two column of numbers.

3:38So num a and num b. So let's do things to them.

3:42So here we'll be just adding our information

3:44into these cells, right?

3:46Like = 15 / 5 for 15 divided by 5.

3:51But that's not the most common way of using Excel.

3:53You would have some sort of existing values

3:55and then you would wanna perform

3:56some sort of operation on them.

3:58So here, let's go ahead

4:00and for every pair of these numbers, add them.

4:05So how would you do that? Try to give it a shot.

4:08I think it should be pretty intuitive.

4:10Okay, so let's say I'm here

4:11and I wanna add 2 plus 8.

4:14I would say equals.

4:15And I could do this

4:18and get the right number,

4:20but if I drag this down,

4:22I'm just gonna get the same information

4:25over and over and over.

4:26So that's not the best way to do that.

4:28So what you can do is grab this cell, right?

4:32So this is a cell reference.

4:34So we're referencing H2 plus I2.

4:40And if we hit enter, then we get 10.

4:43And here's what's really cool.

4:45So if you get this handle in this bottom right corner,

4:49the drag handle, the fill handle,

4:52and you pull it all the way down,

4:54it's gonna give you the appropriate cells.

4:59If you look here, you can see what's happening

5:02every time I click on one of the, let me escape

5:05and then click on these.

5:06And you can see that here.

5:07You can see the formulas are changing, right?

5:10And yeah, so that's very useful.

5:13So it'd be pretty easy to fill these out.

5:15So you only have to do the first one

5:16and then use the fill tool to just drag them down.

5:19So, what am I gonna do here?

5:21Well I'm gonna say equal sign H2 minus I2.

5:27And this time I just wrote it in,

5:28I didn't drag on it 'cause it's faster.

5:31And if you do that, you get negative six. That makes sense.

5:34And then drag it all the way down.

5:36And here you get all of those answers. All right.

5:38So try to do the rest of these yourself

5:42by pausing on the video before I do them, all right?

5:45So hopefully you got the division.

5:47So let's go ahead and do that.

5:48So it's equals H2.

5:54And then I2.

5:57Okay, so that's how you would do that.

5:58And then we get this fill handle and we do all of them.

6:01And then we get all of the division.

6:03So multiplication, let's do the same thing there.

6:06So we're gonna say H2 times I2, okay?

6:122 times 8 is 16. And we drag this down.

6:162 times 3 is 6. Or 3 times 2 is 6.

6:20So that looks correct.

6:21And percentage, what are we gonna do there?

6:24What's the percentage of these two?

6:27Okay, so first, what is 2 of, what percentage is 2

6:33when you're thinking about 8, right?

6:35So 8 goes in here 4 times, it'd be 25%.

6:39So how do we do that?

6:40So here we're just gonna say H2

6:44divided by I2, and then let's do that.

6:48And you get 0.25, right?

6:51And if you went here

6:53and you added the percentage sign, and we do that here,

6:56you'd get 25%.

6:58Okay? So then you could do that, just like that.

7:00Let me go back to General and leave that like that.

7:06Okay, so we have this and then we're gonna just say percent

7:08and add that and then go ahead

7:10and drag this all the way down.

7:12And so it's basically what we did here

7:14is the same as division, except that we did the division

7:17and we changed it into the percentage.

7:20So that's one way of doing that.

7:21There are multiple ways of doing that.

7:22All right, so last one is exponentiation.

7:25So what are we gonna do here?

7:27So equal H2

7:30to the power of I2.

7:35Okay, 256.

7:36All right, and so let's just take our fill handle

7:38and drag it down and that's what we get.

7:43Okay, so let's go to solutions and here are your solutions.

7:47And if you have any questions,

7:49just check these out and be sure to review those

7:52and make sure that you're clear on the Excel data types

7:55and the formulas.

7:57Now, if we wanted to do this same thing,

8:01but with functions, right?

8:03For example, if you wanted the average.

8:06Let's say like you wanted the total here,

8:08you could actually add a total.

8:11The easiest way to do that would be a sum.

8:13And that's gonna be a sum function.

8:15And all you'd have to do is just grab this

8:18there to there and just say, that's my sum.

8:20And then you could actually say total.

8:25And that's how you would do that.

8:26And the same thing, you can grab this

8:28and just grab it over here

8:30and then now you see that by just dragging it over.

8:34Look, if you're looking over here,

8:36or if I double click in here,

8:38you could see that we're talking about the sum of H2 to H11.

8:42If I double click in this cell,

8:44we're talking about the sum of I2 to I11.

8:48And I did that just by dragging it to the right.

8:52And so we could continue that here.

8:53I could just drag these like this.

8:56This one doesn't have anything

8:58because there's no numbers here.

9:00But here this is the totals, right?

9:02So that's one way and that's

9:04what makes it so useful is that what we're doing here

9:07is you we're using a sum function.

9:10All right, so I think that's it for this video.

9:13I hope this has been informative

9:14and I'd like to thank you for viewing.

0:07<v ->Welcome back.</v>

0:08In the last video we talked about formulas

0:11and how to perform math operations,

0:13and then at the very end we saw the the need for functions

0:17and how that could be really useful.

0:19So in this video, we're not gonna get into the formulas

0:21or the functions yet.

0:23That's gonna be in the next skill, the next group of videos

0:26for now, what we're gonna do is make sure

0:28that we have the analysis tool pack installed

0:31and take a quick look at the Titanic data set

0:33that I downloaded from Kaggle

0:36and then perform some quick exploratory

0:38data analysis on that.

0:40So to give you an idea, well, well first of how

0:42to install the analysis tool pack

0:44and how to use it as we're progress through this course,

0:47we'll use the analysis tool pack

0:50for statistical testing such as a T-test

0:53to perform an AA test and a B test.

0:56And then finally for hypothesis testing.

1:00Alright, so let's take a look at the analysis tool pack

1:02and how to install it and how to use it.

1:06Okay, so here we have, this is the Titanic data set.

1:09So when you download this, this is what you'll have.

1:13And so let's go ahead and install the tool pack.

1:17So to do that I'm gonna Mac

1:19and I think this should be pretty easy for,

1:22or the same instructions for a Windows operating system.

1:25Go to the data toolbar or the ribbon

1:29and then you click on analysis tools

1:32and then we're gonna click on analysis tool pack

1:35and click on, okay.

1:38Okay, so now we have data analysis here.

1:40So let's go ahead and grab passenger ID

1:44and then click on data analysis.

1:49Okay, so now we have some different options here.

1:54A lot of options, right?

1:55So what we're gonna do is use descriptive

1:57to statistics first,

1:58but lemme just show you that you can have a histogram T-test

2:02and this is what we're going to be using if T test

2:04for two sample, assuming unequal variances.

2:07That's what we're gonna use for an AA test

2:10and an AB test later.

2:12But you can also do regression, a Nova correlation testing

2:17and lots of, I mean this is pretty cool,

2:20so this makes it a lot easier to perform statistics.

2:23And what we're gonna do is jump into descriptive statistics.

2:28Okay, so here we have a label in the first row.

2:30Yes, it's gonna be from here,

2:34colon there.

2:39All right, and summary statistics. Okay.

2:47All right, there we go. So there is our statistics

2:49for our EDA for the passenger id.

2:53Let me click on these. So

2:54what I did was I just clicked on this in

2:56between section here to make them bigger.

2:59When they're a little squished like this,

3:01you can just double click here

3:02and that sets the width so that you can see everything.

3:06Okay, cool. So here's the passenger id

3:09and let's do one more.

3:11And again, if you wanted to do each one of these manually,

3:14you'd have to do basically formulas, right?

3:17So that's kind of cool.

3:19For example, min would be a formula min for minimum,

3:23max for maximum.

3:24Then the mean, would that be an average function?

3:27And so you'd have to do all of these individually.

3:30So right here you can already see

3:31that it's saving you a lot of time.

3:32So let's take a look at the age column

3:35and get some EDA there as well.

3:39Okay, so I'm just gonna click on age, then go to the data so

3:44that I have the data ribbon.

3:45Then go to data analysis, descriptive statistics.

3:48So labels in the first row.

3:52So I'm just gonna say F1 so that it knows

3:54that F1 is age.

3:58So that's the first row

3:59and we're gonna grab some summary statistics

4:01and make sure that's clicked.

4:02And this is gonna go to Q3, which is right here.

4:06So that looks about right. So

4:07let's go ahead and check that out.

4:08Okay, so let's go ahead

4:10and double click here on Q to make that a little bit bigger.

4:13And now we can see some information.

4:15So first the passenger ID is something that doesn't repeat

4:19and that's what you want in an id.

4:21So that's good. Now when you look at this,

4:24the mean will tell you that that what the average is, since

4:27that's not super helpful, but there's no mode, right?

4:29Because there's no repeated number and that's good.

4:33That's what we want to know.

4:34And the minimum is one and the maximum is 8 91.

4:37So that's how many records that we have. 891.

4:40When you look at the age,

4:42you can see some interesting information here.

4:44And again, these are all be functions.

4:45So I did all of this all at once. So a great time saver.

4:49And here's the average, the mean

4:51and the the median and the mode.

4:56And here we're getting, so we have the, the,

4:59the median and here's the average.

5:01It's a little bit higher here.

5:02So there's some skewing going on.

5:04And the count is what's interesting here

5:06that's really standing out for me

5:09while we have a a a hundred and something missing values.

5:13So there, there's quite a few missing values

5:15and we already explored that.

5:16They're just missing values here.

5:18I don't know exactly why that was.

5:19However, if we look at this again,

5:22we can look at the minimum and maximum age

5:24and we could see that, you know, there were some babies

5:28and the oldest person on the Titanic was 80.

5:31Okay, that's good to know.

5:32So this is really just giving us a lot of information very,

5:35very quickly and it really was just pretty easy to install.

5:38All we did is just went to the data analysis tool pack here

5:41and make sure this was clicked and clicked, okay.

5:45All right. And there are other things that we could do.

5:47I don't think we're gonna do those right now.

5:50Let me go ahead and make sure this is back to

5:51where it was at like one 60 or something.

5:54So for example, if I went to data analysis, I said

5:58that I wanted to do correlation testing, we could do that.

6:01We are also going to do t-test and you can even do a z test.

6:09And so what we're gonna do is later we're gonna perform a

6:12two sample T-test, assuming on equal variances.

6:15So there's a lot that you can do here.

6:17There's moving averages, histograms

6:20and we already, you did descriptive statistics,

6:22there's co correlation analysis of variance.

6:26Lots of good stuff here.

6:28Okay, so that's pretty much it for this video.

6:33Alright, so we've installed the analysis tool pack

6:35and you can see that it's a time saver in a lot

6:37of different ways, especially when we get into

6:39t-tests and things like that.

6:41For right now, we explored the Titanic data set

6:45and we did some quick exploratory data analysis using the

6:49analysis tool pack as again it was like 15 functions

6:53or something like that, but it just popped it out

6:55immediately and we're able to explore the, the IDs

6:58and compare those to the age and,

7:00and see that we had some missing records and,

7:02and again we're able to see the minimum and maximum age

7:05and the average age and see that it was skewed

7:08because we saw that the median was a little bit different

7:10than the the mean things like that.

7:12Okay, so that's it for this video.

7:14I hope this has been informative

7:16and I'd like to thank you for viewing.