0:00 The exercise files for today's course are located in the video description below. Don't forget to like, and subscribe. Welcome to Microsoft Excel introduction to data analysis. You're learning with Mo Jones, IT professional and educator. And this compact course will be learning all the essentials for data analysis. Here's what we'll explore today. We'll convert lists into a table and take a look at the table tools that are available. We'll use Excel's aggregate functions to conditionally apply calculations to our data. We'll use pivot tables to summarize our data set. And then we'll take it a step further. We'll create pivot charts to create visuals from our data set. Go ahead and open up the practice file
0:49 for this course. And we are going to dive right in. Looking to support our channel and get a great deal? Become a member today to unlock ad-free videos. That's right, your favorite courses without a single ad. Interested in a specific video? Purchase one of our ad-free courses individually. Looking for even more? Gain access to exams, certificates, and exclusive content at learnitanytime.com. More information can be found in the video description below. On our Creating Tables worksheet tab, We have some basic employee data. So notice that we have well-defined headers. We have employee ID, last name,
1:36 first name, department, email, phone extension, location, hire date, and pay rate. So that's a good first step. We want to make sure that we have well-defined headers. So remember our headers, they are the key when we're analyzing our data because The headers represents the name of the field in which we want to analyze, or in this case, the column that we want to analyze. So we want to make sure that they are named well, that they're meaningful and they accurately describe the data below. The next thing that we want, we want to make sure that we have unique labels. Now this is not always the case, but it's best to have unique labels when you're working with data. So
2:23 in this case, our unique labels, we can see that we have unique employee ID numbers and this will give us some better opportunities to kind of just filter our data down to what we're looking for as well. The other thing that we want to make sure that we have complete records. So as long as our list has well-defined headers, unique labels and complete records, meaning no blanks, then we are good to go. We can see that this data set, we don't have any blank cells in here. We have complete records on row five. We have a complete record for Joe Gonzalez. All of the information is filled out. If we do have blank cells in our data set, we'll run into some
3:11 problems. One of the problems that we'll run into is that our data analysis will be incorrect or incomplete because we're actually missing data. So it's usually a good idea just to kind of scroll through your data set, make sure that you don't have any blanks in there as well. You can always use the count blank function and this will kind of help you to count the amount of blanks that you have in your data. So I'll just enter the count blank function here and I'm going to go ahead and highlight all of my data. So Excel is going to count all the blank cells that it finds in the range A3 down to I40. If I press enter. I have zero blank cells and I'm good to go. Now, if I were to delete a cell, it tells me right away that I have one
3:58 blank cell. So those are some good first steps. Some other tricky things to kind of look for, especially when you're working with dates and numerical values, you want to make sure that they are formatted properly. So we can see if we take a look at our higher dates here, we can see that they are properly formatted as a date. This is not always the case. Sometimes you have data that's extracted from one program to the next. You may notice sometimes, even though these look like dates, they say something else. Up here, it may say something like custom or general. So you want to make sure that you make those adjustments here.
4:44 We're all good here for the pay rates as well. We can see that that's using the currency format. And so We are pretty much ready to go ahead and analyze this data in a table. Let's convert this list into a table, and then we'll have some tools that we can use to analyze the data. Now there's two ways that we can convert this list into a table. Here's the first way. We can actually use a shortcut. So if we press control plus T, that will convert this to a table for us. As long as we're selected inside of the list. The other way is from our ribbon. So from the home ribbon, if we head on over to the styles command group, we have the format as
5:30 table command. Those are two ways that we can convert this. So I'll go ahead and click anywhere inside of my list here and I'll use the first method, which is control T. So if I press control T, it just basically asks me You want to create a table. Where's the data for your table? And does your table have headers? My table does have headers, so I'm good to go. And if I scroll down, I can see that it's highlighting all of my data. I'll go ahead and press okay. And it converts it to a table for me. So here's the table form. We know that this is a table because if we take a look at the ribbon, we now have the table design tab, and this is where we'll find all the tools that we need to be able to analyze the data. We can filter our data. We can turn on the
6:20 total row. We can insert a slicer. We can also go ahead and take a look at the different table styles that are available as well. I'm going to go ahead and undo that. So I'll press control Z and let's try method number two. I'll go ahead and click on format as table from the ribbon here. And the only difference is we can choose our table style ahead of time. So there you go. So here is my list. That's now converted to a table. Well, now that we've converted this to a table, we can do things like sorts by columns. We can also do some filtering as well. We also have another tool. It's called the total row. So we'll take a look at these three tools
7:08 here as well. Notice for all of my headers. Now I have a filter. So if I click on the last name filter here, several things I can do at the top of the filter. I can go ahead and sort. And then down here, I can use a logical filter. I can filter by a search term, or I can go ahead and select a unique value from down here. So there's actually one, two, so four different opportunities for us to use this filter dropdown to analyze our data. Well, I want to go ahead and sort. So I'll click on the, I'll sort by last name here and I will sort A to Z and notice how all of my records now are being sorted
7:56 by last name. The entire record moves. We're not just sorting the column explicitly. The entire record is moving as well. So if I were to go ahead and maybe sort by employee ID number, we can see that the entire record travels here as well. So we can do some simple sorting in here as well. But what if I wanted to do some more complex sorting, we would need to click on the data tab and click on the sort icon here in order to do some complex sorting. Because at this point we can only sort on one column at a time. You saw that when I sorted by employee ID, it removed the sort from my last name here. Well, what I would like to do is maybe go ahead and sort by Let's see, I'll go ahead and sort by last name and
8:44 department. So how can we sort by last and department? Well, we'll need to click on this sort icon here, and this will allow me to select the different columns in which I want to sort by. I'll go ahead and click on sort. Now here we can see that my data has headers is already checked off. Now I can select the column that I want to sort by. So I want to sort by last name. Well, maybe I'll do department first. Department is a higher level. So let's sort by department and then by last name. Well, for me to do that, I need to click on add level so I can add another level here so that I can go ahead and sort by last name. So I'll click on add level and I'll choose last name. I'll make sure that
9:30 they both say A to Z and I'm good to go. Once I press okay, we can see it's sorted by department. So here's AC. And inside of that department is also sorted by last name. Here's the AD department and that's also sorted by last name. So that's some basic sorting that we can perform on our table. Let's take a look at the different filters that we can use. I'll go ahead and click on a filter for the hire date. Let's see what filters are available for the hire date. We'll also take a look and see what filters are available for the pay rate. So we're working with dates and numbers. Now for the date filters, we have a lot of date filters. I can look
10:15 for a higher date based on an exact date before a certain time, after a certain time and between a certain time. Then I can be more specific, right? Last year, last month, last quarter. So those are different things that I can do. So if I click on between, what if I want to know how many hires we had between two dates. So if I click on between here, here's my little logical operator. I want to find out how many hires did we have? Let's say after 2000, so I'll say January 1st, 2000 and, but before December 31st, 2006,
11:03 I can also click on the date picker here. and choose my date from here as well. Let's see how many hires did we have between 2000 and 2006. I press okay. Here are my five records. If you take a look at your status bar, tells you that five of 37 records were found. We can see that these all fit that description. They all fall between those two particular dates. So that's one way that we can filter using that logical operator. I'm going to go ahead and clear the filter from the higher date. So we can see the filter is active here. I'll go ahead and click on it and I'll click on the clear filter from higher date. This will reset my table, display my 37 records. Well, let's go back to the higher date and this time let's go ahead and
11:49 enter a value in the search box. What if I just want to find out how many hires did I have in 2010? So if I type 2010 here, notice how it collapses is for me for 2010. So it looks like I had a couple of hires in here. If I press okay, here are my three hires for 2010. So we can see the filters, really nice tool, excellent tool for doing simple data analysis. I'll go ahead and clear this filter as well. And then we'll run one more. So this is the other level. So we use the date filter. We use the search box. Now I can just choose an individual month, day or year down
12:36 here as well. So what I'll do is I'll unselect all, and I just want to find out how many hires we had in 2006 and 2007. If I press okay, here are my four records. So either or in 2006 or 2007, I'll go ahead and clear the filter from there. Let's take a look at the pay rate. So the pay rate offers us the same opportunities, not as many filters as the date filter. We have some basic ones in here. So if I wanted to take a look at the pay rates that are greater than or equal to a certain amount, let's say we can see we go from 20 to 3676. Let's show all of the numbers
13:23 that are greater than or equal to 30. Here's my simple dialogue. I'll type 30 in here. I'll go ahead and press. Okay. And there we go. So here are my. 13 records. So 13 employees are earning 30 or greater per hour. So those are some basic filters. The good thing about filters as well, we can run multiple filters on our data set. So in this case, the pay rate is $30 or greater per hour. And what if we want to filter by department as well? I'll go ahead and click on the department filter here. I only want to see from. let's say my HR department, I'll go ahead and press. Okay. And this is a very nice tool. So we're
14:09 able to narrow it down to the HR department where the pay rate is $30 or more per hour. Here are two records. It's a really nice way to isolate the data so that you can work on it. And once you're done, we can just go ahead and move on to the next department. So I'll move on to MF here. And there we go. So really nice way to filter. We can run multiple filters together. As long as we understand logically what we're trying to accomplish. Sometimes we need to kind of just stop and pause and think about what we're trying to accomplish here as well. I'll go ahead and clear my filters. I do have two filters running. So what I can do from the data tab, I can click on the clear filter icon here, and this will clear all the filters that are
14:55 running on my table. So it's going to clear my department filter and my pay rate filter. I'll go ahead and click here. My table is reset to display my 37 records. Now there's another tool that we can use, which is called the total row. So if we click on the table design here and the table style options, the filter is already turned on. Let's go ahead and turn on the total row. The total row is going to place a row at the end of our table and each cell will provide a dropdown. And on that dropdown will be a list of basic functions that we can use to summarize the different columns on our table. I'll go ahead and turn on the total row here. We can see the total row has been added on row 41.
15:43 And it's already has a number here in the cell on the bottom, right? So it says 1010 30. Well, let's go ahead and click on that cell. If I click on that dropdown, giving me the sum of the pay rates. Well, that doesn't make sense. If I want to find out what is the average pay rate here, I'll click on average and it automatically calculates that column. So the average pay rate is $27 and 31 cents. Now, one of the nice things is, so we don't really have a lot of columns that we can run formulas and functions in here, but if I were to add in, you know, maybe, um, some hours in here, and the gross pay, then we will see more options that are available to us. As a matter of fact, I'll go ahead and
16:31 do that. I'm just going to insert a column here. So I'll go ahead and put in the hours and then I'll go ahead and put in, um, the gross pay. So I'll just go ahead and put in some random numbers here. I'll start with 20. I'll start at 15. 16, I'll continue this pattern down till I get to say 40. Right. Yeah. Let's see. Then I'll just come back here and continue this pattern down as well. Why not?
17:18 Then I'll simply just add a at a calculation here. Now this is a major benefit of analyzing data in a table. Look, what's going to happen once I write a formula here on the, on this top cell, as soon as I write the formula is automatically going to fill down the rest of the formulas for me. So that's the benefit of working with the table. So for the gross pay, I'll say equal pay rate times the hours worked. Once I press enter, We can see it's automatically calculated all of our gross pays, which is a nice feature of a table. Now we have three other columns that we can run numbers on. So what are the total hours worked? I'll use the sum function for that. 1,155 hours. What is the total gross pay?
18:08 Here's the sum of the gross pay. 31,843.51. So this allows us to quickly answer questions on our data set. Average pay rate is 2731 total hours, 1155 resulting in gross pay of 31, 843 51. The other thing to mention about this is if I filter my data, these numbers will update as well based on what I'm filtering by. I'm going to go ahead and filter by department. I just want the AC department. If I press okay, notice how the numbers have changed. pay rate has changed, the hours have changed, and the gross pay has changed based on the AC department.
18:55 Conditional formatting is another type of filter. So here we just have some houses. We have the listing price, the town, the square footage, the bedrooms and the bathrooms as well. Well, sometimes when you're filtering, instead of hiding some of the data, that does not meet that filter criteria, you may want to still be able to see all of the options. Sometimes we just kind of feel like we're missing out on something. So what we can do is use conditional formatting and conditional formatting can be found on the home tab. And here's some of the different conditional formatting options that are available. So here's conditional formatting. So we have cell rules. We have top and bottom rules. We have data bars and we have color scales as well. The great thing about conditional formatting, there's a lot of rules that are built
19:45 in and we simply just need to go ahead and use them. The first step in using conditional formatting is you first need to go ahead and select your range. So step one, you need to go ahead and select your range. And then step two, you want to go ahead and choose your formatting. So we'll take a look at a different A couple of different types here. We'll take a look at data bars first. Then we'll take a look at color scales. Then we'll kind of double back and take a look at some cell rules and some top and bottom rules as well. So let's see how much data we're working with here. I'll go ahead and press control. And so we go down to
20:31 row 133. So quite a bit of data here. I'm going to press control home to go back to cell A1. Well, There's a lot of numbers here for the listing price. I could sort this, but I just want to go ahead and see which one stand out. I'll go ahead and highlight all of my listing prices here. Now that they're all highlighted, I'm going to go ahead and click on conditional formatting and I'll use one of the data bars here. So here's a data bar that I can use. I'll click on the, I'll go over the solid fill. So notice what this does if I kind of make this column wider. So this gives us a clear indication in terms of which values are higher than the other. I can clearly see on row 22, this house represents one of the higher prices.
21:20 And if you scroll through, this gives you a really good visual in terms of which ones are cheaper or which ones are more expensive. Now, I'm going to go ahead and apply another one here. Let's take a look at. color scales. So here are different color scales. Here's the green, yellow, red color scale. I'm going to go ahead and remove the other conditional formatting from there. So I'll click on manage rules and I want to remove this one here for the data bar. I'll click on delete. I'll click apply and I'll click. Okay. So here's the color scale. So the color scale, we can see the green represents the higher values and the red represents the lower values. So as you kind of scrolling through here,
22:06 Keep your eye on all the greens. And that gives you a really good visual in terms of which ones are standing out as well. So very good. Let's take a look at some other conditional formatting. What about square footage? I'll go ahead and highlight all of my square footage range here. And for this one, I'll click on conditional formatting. Well, this time I want to, let's say I want to find a home where the square footage is greater than, less than, or between a certain value. Well, I'll say greater than. So as long as there's greater than, in this case, it's giving me 2373. I'll just change this to 2500. So we can see it's already
22:54 applying that light red fill of dark text. Maybe I want green fill of dark text and I can go ahead and press okay. So I can already start to eliminate some of the houses that I don't want because they're not meeting my square footage requirement here. Well, what about bedrooms? We'll go ahead and run another one for bedrooms. And maybe I want exactly. Maybe I want equal to, I want exactly three bedrooms. I'll go ahead and press. Okay. So now I'm getting a match in a couple of different places here. I'm good to go. I'll go ahead and do one more for the bathrooms. So maybe this one.
23:43 Let's say I'll go for greater than, say greater than two. Greater than two bathrooms. I'll go ahead and press okay. And it looks like we have a nice match here on row 121. All of my requirements are being fulfilled. So that's what conditional formatting can do for us. We do have another type of conditional formatting in here, which are top and bottom rules. So these really gives a really nice analysis. We can highlight the top 10 items on a list based on value, top 10%, bottom 10 items, bottom 10%, whatever is above the average. So to do that, I'm going to go ahead and clear the conditional formatting for my listing price here. So I'll go back to manage rules.
24:32 And for this entire sheet, I'll go ahead and clear that graded color scale. I'll delete that. I'll press apply and I'll press. Okay. Let's take a look at one of the other type of rules, which is the top bottom rules. So top 10%, which one of these listing prices are in the top 10%. I'll go ahead and click here and we can see it's already highlighting them for me. I can change the number here. Maybe the top 20%. I'll go back to top and bottom rules. What about the bottom 10%? So some quick analysis that we can use there as well. So conditional formatting is another type of
25:20 filter. Let's take a look at the if function. Now the if function is a really nice tool. It's kind of the base or the standard for all of our logical functions. So in this case, we'll ask a particular question here is, we have a few salespeople, S. Sam, E. Brown, W. Danny, M. Meyer, S. Sandberg and A. John. We're tracking their sales for February. For example, in week one, S. Sam had sales of 95.50 in week four, 89.65 for a total of 36,245. So the question that we want to ask is, well, was the gold met? So that's the number one question we need to ask. The next question we need to answer here is, well, is the 36,245,
26:13 is that greater than or equal to the monthly goal, which is 34,000. So we need to compare these two cells and see if that is true or if that is false. So that's the second question we need to ask. The third question we need to ask is, well, if it's true, what value are we going to place in this cell? So we're either going to say yes or no. So that's it. What's the goal met? In order to answer that, we use the cell reference here. Is 36,245 greater than or equal to 34,000? Yes, it's true. So as Sam did meet the goal, we'll put a yes in
26:59 here as well. Well, that's exactly what the if function will do for us. We already know the cells that we want to compare here. We're comparing cell F seven to sell B four. We already know what we want to say. If that logical test is true or say yes, the goal was met. If it's not true, we'll simply say no. Let's go ahead and insert our if function. I'm going to click on the formulas tab here. So I'll start off by clicking on the cell where I want to place my answer. I'll click on the Formulas tab and I want to go ahead and insert my IF function. So I'll click here.
27:45 Here's my Insert Function dialog. I'll type the IF function up top. I will press go and on the bottom I'll press OK. Welcome to the Functional Arguments box. Really nice tool because it It gives you the name of the function, tells you what it does, and it also shows you what it's asking for. So the first thing we need to fill out is our logical test. Well, our logical test is remember we're comparing the value and sell F7. So I'll go ahead and click on F7 and I'll use my operator. I'll say, is that greater than or equal to the value that is in B4? Excel has already evaluated that statement. The value in F7 is
28:35 indeed greater than or equal to the value that's in B4 because 36245 is greater than or equal to 34. It's telling us that it's true. So if it's true, I'll simply say yes. I'll say yes. Gold met. And if it's no, or if it's false in this case, rather, I'll say no, goal not met. We can see our answer is going to be yes, goal is met because that condition is true. If I press OK, we get yes, the goal is met as well. Now, I don't feel like writing this formula
29:21 another five times here. So remember our little friend, the fill handle, we can grab the fill handle here. and pull it down and that will copy the formula for us. But we're going to run into an issue and let's do a little bit of troubleshooting to see what happens here. I'll go and grab the fill handle and I'll fill down. And it's copying the formula for me, but we can see we're running into an issue here. We can see that E Brown did not meet the goal, right? 31, 475 is not. greater than or equal to 34. So that does not compute. Neither does, let's see, neither does S Sandberg did not meet the goal. So let's figure out what's
30:09 going on. We can double click on the cell to gain some insight to see what's happening with the formula. If I double click here on the cell, so we can see what's happening. It's moving away from that monthly goal, which is 34,000. So it went down one row. it's using relative referencing. So if I go down to row nine, we can see it's locking on to the correct total in row nine, but it's already moved away two spots from our monthly goal. So what we need to do is we need to absolute reference the monthly goal. So to absolute reference, we're going to write, instead of writing B4, we're going to say dollar sign B dollar sign four, and that will Excel to lock
30:56 onto that cell. Don't keep moving down one row at a time here. I'll go back to my original formula and right here in the cell, I'll place the dollar sign right in front of the B. I'll place the dollar sign right in front of the four. I'll go ahead and press enter. And now if I auto fill down, we can see that this is working properly. we do a quick test. If I double click, I can see it's still locking on to cell B4. If I go to the last calculation here, it's still locking on to B4. It did not move down several rows as well. So that is the IF function.
31:42 It evaluates a statement to see if it's true, returns one value if it's true, another value if it is false. Let's take a look at a few useful database functions. We'll take a look at a few. We'll take a look at some if, we'll take a look at average if, and we'll also take a look at some ifs. Now, here is our peer company Q1 expenses. We have several divisions, East, North, South, and West. We're also tracking different categories such as tech support, telephone, copying, overhead, software. And we're tracking the expenses. So basically in the East,
32:28 in January, we had $800 worth of expenses for tech support. In March, we had $700 worth of tech support in the East. Total for quarter one for tech support in the East was 2,150. So we already have our data set here. It goes down to row 61. So we can see it's row 61. So let's go ahead and answer some questions. Well, what are the total expenses by category? So if I wanted to go ahead and create a small worksheet over here to summarize, what were the total expenses where the category was software? So what I would need to do,
33:16 I need to go down the category range here. And every time I see software, I need to go ahead and add that to the running list. So here's one on row eight, another one down here on row 23. And let's see another one, a row 37. Pretty sure I may be missing one here. Another one on row 53. And we need to go ahead and add the totals from our last column here. So we'll add this 37 85 to the running total. This 37 50. this 3805 and so forth and so on. So we add all those numbers up and we'll go ahead and get our total for software. Well, we don't need to do that.
34:05 There is a function that will do that for us. And that function is called the sum if function. Let's go ahead and take a look at some if. So basically what we're saying is we are going to summarize the total expenses. If category is equal to software. So we're going to go ahead and summarize the total expense column if the category is equal to software. So there's a function that's already programmed that's going to ask us for these things. It's going to ask us in particular, well, what is the range? What is the criteria? And what is the sum range?
34:54 Now we already have all this information. We just need to go ahead and point to these and the sum of will take care of the rest for us. Let's go ahead and insert our sum if statement. I'll click on cell J7. I'll head on over to the formulas tab. I'll insert a function and I'll look for some if I'll press go, I'll press okay. Here is some if, and as I mentioned, ask us for the range, the criteria and the sum range. Well, our keys are right here. We want to search the category range. So I'll highlight all of my categories from C4 down to C61. The next one is, well, what is my criteria? Well, I could type software in
35:40 here, but I'd rather refer to the cell reference because then I can use this as a little input field. So I'll refer to that cell instead of software. I'll go ahead and click here. So I seven, whatever's an I seven will perform our calculation to down one to go. The last one is the sum range. So which column do I want to summarize? Is it the January expenses, February, March? No, I want the total expenses. I'll highlight G four to G 61. We already have our answer. It's already found all those instances where the category is software. The total is 17,215. I'll go ahead and press. Okay.
36:31 And there's my answer. I'll just go ahead and give that the currency format and I'm good to go. Now the average, if it works the same way, it's going to ask us for the same things, right? Range criteria and the average range. So for this one, to answer this question, we'll use the average if function. So the question we want to ask is, well, what is the average per rent expense? So we'll count the amount of rent expenses we had. We'll summarize the total expenses and divide that by the amount of entries that we have for rent. Where we have all the information that we need. The range is going to be
37:18 the category. The criteria is going to be the rent. We want to go ahead and run the math on our total expenses column. So I'll go ahead and insert the function here. I'll look for average. If I'll press go, I'll press. Okay. What is our range? Well, category. So same thing. Go ahead and highlight that range C4 to C61. The criteria needs to be rent. In this case, it's cell I-13. The average range needs to be the total expenses. Highlight all of my total expenses and I'm good to go. So the range
38:05 is our category range. The criteria is expressed right here in this cell. And then finally, We want to run the count the numbers on the total expenses column. So there we go. Our total is 17,790. I'll go ahead and press. Okay. And we are good to go. I'll also apply the same format to there. I'll use my format painter 17,790. Now the benefit of using these cell reference as opposed to typing it in here. Now what I can do, is I can use this as a dashboard. What were
38:50 the total expenses for supplies? If I type supplies in here and press enter, it gives me the updated answer right away. I'll change this from rent to supplies as well. How we get our instant answer as well. So that is the sum if function and the average if function. Let's write a function that will give us the expenses by division and category. So what were the total expenses where the division is East and the category is software. So for example, we need to go ahead and find, we can see that there's
39:36 a match right here on row number eight. Here's the division is East. The category is software. Here's the total expenses here as well. There may be some other matches. Let's just go ahead and check. So to do this, we need a function that will allow two logical tests. So two ranges and two criteria. Some if only allows for one. So we need to use some ifs, which will allow for multiple. I'll go ahead and click on my formulas tab here. Let's insert the sum ifs function. Here's my sum ifs. So again, it's going to ask us for the same thing. What is the sum range? What is the range? And what is the criteria that wants to get the
40:23 sum range out of the way right away? We already know we want to summarize the total expense column. So I'll go ahead and highlight all of these here. G four to G 61. Here's a preview of the array. Now criteria range one. Well, it's going to be division. So I'll highlight all of my divisions. Criteria one, which division are we looking for? Well, we're looking for the East division represented in cell I-19. Now that we've finished criteria range one and criteria one, we now have room for criteria, criteria range two. I'll go ahead and click here. The second one, the category needs to be software.
41:10 So we'll highlight all of our categories. criteria two needs to be software and we're done. The total 58 75. So apparently there was only one entry or one instance where we had a software expense in the East region, 58 75. So that is the sum ifs function. Now it did not matter. I could have used the category range first. And then division second does not matter. It would still make the calculation for us here as well. I'll go ahead and press okay. And I am good to go. Now, because we're using the cell
41:58 reference, I can go ahead and change this. So what about, um, instead of software in the East, what about rent in the East? I get an instant answer, 24,000. What about rent in the North? So now we have a really nice dashboard that we can use to summarize all of our data. So this one is the sumifs function. Really nice tool takes away the manual work of highlighting several columns in several cells and adding it all manually. Let's take a look at recommended charts. Here we just have some sales figures. Here's our sales rep. We have R. Smith, H. James, S. O'Brien, L. Carey, and K.
42:46 Dunn. Here are their weekly totals. So week one, R. Smith, 45, 20. Week two, 36, 20. Week three, 25, 60. And then week four, 27, 50. Well, we can create a chart from this. I just need to go ahead and click on the insert tab here. Now, when we're inserting a chart, I don't have to highlight the entire data. I can just click anywhere inside of my list here because it's a well-defined list, no blank cells, rows or columns. So I can go ahead and insert any type of chart that I want. But what I want to do is insert a recommended chart because Microsoft Excel is going to give me a customized set of charts that it thinks will fit best with my data. So I can get an automated chart here, which is exactly what I want.
43:37 If you like shortcuts, press Alt plus F1 on our keyboard, we can get the default chart for this data set here. I'll go ahead and press Alt F1 on my keyboard. And just like that, here is our chart for our data. Now I'm going to go ahead and undo that. I'm going to go ahead and delete my chart here. So the traditional way is to click on the insert tab. Here is recommended charts. If I click here, Here's that clustered column chart. There's different charts that we can choose from. Here's a stack column. Here's another clustered column. It just kind of changed the data around. We have the weeks in here instead of each of the sales reps. If you scroll
44:22 down, we have different charts, clustered bar, line chart. So lots of charts that we can use. I'll stick to the default one here, which is the clustered column. I'll go ahead and press. Okay. And here is my chart. So pretty cool. I have a few buttons over here in the top right of my chart. These are chart elements. So what I can do is I can turn some of these features offer on. For example, if I wanted to add an access title, I can go ahead and do that. I'll click on access title, adds a little text box on the horizontal and vertical plane, but I just want one on the horizontal. So I'll remove it from the vertical here and I can just go ahead and type some data in here. Maybe I'll
45:10 just say sales reps. I'll go ahead and add a title for my chart. I'll just call this February sales. We'll get to go. Now some other things I can show the data labels at the top of the columns if I want to. I can show the data table on the bottom of the chart, which is pretty cool. And then I can turn off the grid lines. I can also turn off the legend or I can move the legend. So if I click on the small arrow here, I can show the legend at the top or I can show it over at the right. I'll leave it over here on the right for now. Another option is to click
45:58 on the paintbrush here. So maybe we would just want to go ahead and use one of the predefined chart styles. So here's style number two. So style number two, in my case here, it's showing the data labels at the top of my columns here. It's basically removed my vertical axis over here. If I click on style number three, it's incorporating the data labels as well. If I keep scrolling down, there's just different types that I can use. Right now, the other styles, my data labels are just kind of overlapping each other. I can resize the chart. Just going to make it a little bigger. See if that looks any better. Still having some issues. They're still kind of over overlapping. So I'll turn off the data labels and I'll go ahead
46:47 and choose another style. Style number eight is a dark theme. So that looks pretty good. I'll go ahead and stick with that one. I can also change the colors. So if I click on the color tab here, I'll have different colorful palettes to choose from. Here's colorful palette two, three. And four on the bottom, I have some monochromatic color palettes to choose from different shades of blue and so forth and so on. I'll go ahead and stick to the original one here, that colorful palette, and I'm good to go. If I want to change the chart type, I can always do that. Notice that we have two contextual tabs here for our chart design. If I click on chart design,
47:32 I can do a couple of things. I can change the chart type. I can also move the chart to another worksheet if I want to. I can switch the rows and columns and I can also go ahead and select which data I want to be displayed on here as well. I'm going to go ahead and switch the row and column and just a really subtle effect. But if I click on switch row and column and basically switches it. So now instead of my sales reps being in these different clusters, it's showing week one. So week one, here's the totals for our Smith. James O'Brien, Kerry and done. So just as subtle switching the row and column can just kind of give you another look for your chart here as well. So that's how we insert a recommended
48:20 chart. Let's go ahead and add some data to our chart. Now, if I were to add another record here on row number 10, my chart will not show that data right away. what I would need to do is I would need to come back here and click on select data and go ahead and reselect all of the data that I want to be displayed on my chart. But what I prefer to do is just to convert this list here into a table. So remember we can press control T, convert this to a table. And now the benefit of doing this is if I add another record, my chart, will update automatically. So charts are dynamically linked to a table. So look what happens when
49:07 I start adding some data in here. Notice it's already reserved a space for my new entry. So pretty cool. I'll go ahead and put some values in here. And there we go. So that's a nice, simple way to be able to have your chart dynamically linked to your data set as well. Now we can change the chart type. I'll click on chart design here. I'll change the chart type. Maybe I want something else like a 3D clustered column. We'll go ahead and press okay. Also go ahead and change it to something
49:53 else. Maybe I want a line chart. Go ahead and press okay. So whatever chart you change it to, it's still going to incorporate your data into that chart. So we're good to go. I'll go ahead and move this chart. I'll go ahead and move it to its own sheet. I'll use the new sheet command here. I'll press okay. Here's the chart on a special sheet. We can notice that the grid lines have been removed and we have the capability just to go ahead and display our chart here. Even though we moved the chart to another sheet, it's still dynamically linked to our original data set. So if I go back and make any changes, it will update automatically for my chart here as well. Another type of chart
50:41 that we can take a look at is a small chart. So what if we do not want to have a large chart? We just want to have a chart in a cell, maybe for some trend analysis. Well, let's take a look here. We have a few sales reps here down to row 31. Our first salesperson is Kathy Albertson. So we're tracking Kathy's sales for May, June, July, August, and September and October as well. So if I were to ask you, if you can just kind of give us an analysis to see how Kathy's sales are trending. Well, we can see that from May to June, we went down in June. We went up in July, went back down in August,
51:28 went up significantly in September and also went up in October as well. So this is my attempt at just kind of showing how Cathy is trending here as well. Well, we'll appreciate the trend line better than my failed attempt here at trying to manually create a trend analysis. Let's insert what's called a sparkline. So I'll go ahead and put trend analysis right here. So here's my trend analysis. So click on the insert tab. Now when you click on the insert tab, you'll notice that we have a group that's called sparklines. There's three different types of
52:14 sparklines that we can use. We have wind loss, we have column, and we have the line. I'm going to go ahead and insert a line sparkline. We can always change it. the column and wind loss later, but let's go ahead and insert a line here. So I'll click on the line and it tells us right there. Sparklines are many charts placed in single cells, each representing a row of data in your selection. Well, that's exactly what I want. It's going to ask me two things. Where do I want to place the sparkline and where's the data set that I want to analyze? So pretty nice. So here we go. The location. I want the spark line to be right here in this cell, I 12, it's already selected. Well, my data range, well, I want to go ahead and analyze the data.
53:02 Kathy's sales from May through October. So I simply need to go ahead and highlight this range. So we're good to go. Here are the numbers that I want to analyze. Here's where the spark line will be placed. I'm good to go. Sparkline is going to do the work for me. There's a lot happening in the background that we're not privy to really appreciate this tool. I'll go ahead and press. Okay. And here's our trend analysis. So we can see one down, one up, one down, went up significantly in September and kind of stayed the same for October as well. Now I'm going to go ahead and make the role a little higher. So I'll make it 28.
53:52 There we go. So that allows us to kind of see it a little better here. We can also do things like change the background color of the cell to make sure it's kind of standing out, but we're good to go. Now, once we insert the spark line, I can click on the spark line right here and I can do some pretty nice things. So I can kind of highlight the high point and the low point here as well. I can also go ahead and give it a particular style, really just changing the color of the spark line here. So I'll go ahead and notate the high point, notate the low point as well. We can see the low point is here for June. High point is for October. I'll also go ahead and change the style.
54:42 So lots of different styles here. Again, changing the fill color may help for these to show a little better. I'll look for, go for orange and maybe I'll change the fill color of that cell to, that's probably too dark. And I'll go for a dark blue here. And so there we go. Now for the high point, I'll change the color to, if I go with white here. So we can do some pretty nice things with the spark line here as well. I'm going to go ahead and remove the fill color from there. I'll say no fill. Need to go ahead and put the marker color for my high point back here as well.
55:32 So there we go. Now it's really nice about the trend analysis. If I change the values here, this is going to update automatically. Right? So notice how it updates automatically here as well. So pretty, pretty cool. So another, object that's dynamically linked to our data set here as well. Now I want to go ahead and create sparklines for my remaining sales reps here. I don't have to come in here and create individual sparklines. What I can do is just grab our little friend here, the fill handle, go ahead and pull it down. And this will create the individual sparklines for the rest of my sales reps as well. Before I do that, I'm just going to go ahead and change this to the column. So if I click back on the sparkline here, I want to go ahead and change this to the column. Let's see what that looks like.
56:23 So here is the small column chart. You can see high, low, high, low, high, and then high. And we can also see that the high and low points are being indicated here as well. We also have win-loss. Win-loss only looks for positive or negative values. So positive values appear at the top of the cell. Negative values appear at the bottom. So it does not really apply here because we only have positive values to work with. I'll go ahead and put this back to line and then I'll go ahead and auto fill down, pull it down to row 31. And here are individual spark lines. I'll go ahead and adjust the row height for all of them so we can see them a little better.
57:10 So right click, change the row height to 28. And there we go. So that is how to insert and manage sparklines. Here we have some order details for December of 2023. If we scroll down, this goes down to row 68. So quite a few entries here. Now, what if we were asked to be able to answer some questions on this data set? So we need to go ahead and present this data. in a way that our audience will be able to decipher the information, understand, be able to ask questions as well. So we can see that we have a few things
57:55 here. We have the order ID, we have the order date, customer ID. But what if one of the questions was, well, what were the total sales for our products based on city? What about by state? What about by region? sales rep. What about by product or by category? So we can see that we have several options here. We have the revenue that's being calculated here and column use with quite a few columns as well. So in terms of categories here, the distinct categories that we have. So we want to be able to run some numbers
58:43 based on categories. We have several products, lots of products as well. How can we find out what the total sales were for green tea in New York? Now in terms of, let's say region, we have four regions, East, North, South, and West. What about sales by sales rep? We have quite a few sales reps here as well. So this is a daunting task because again, this is a lot of data. And we would need to be able to manipulate this data, sort, filter, run some calculations, right? Use a lot of the table tools, but it's going to be a lot of work just to answer one question. We're going to have to make space and
59:31 then move things around. That's where a pivot table comes in. So the pivot table already has a lot of built-in functionality for us. We simply need to go ahead and convert or extract this data into a pivot table, and then we'll have a lot of analysis tools available to us. I do want to check out the quick analysis tool though. So for the quick analysis tool, I'll just say quickly analyze. So this is something that I use often here. If you press control plus Q on your data set, This will open up the quick analysis dialog. So I'll press control Q and down here in the bottom is giving me some, some quick
1:00:19 analysis tools. So remember, I want to insert a pivot table. So I'll click on tables here and let's see what pivot tables Excel can create for me. So look at this very first one. This is giving me the sum of revenue by region. So I can see that the highest revenue was in the north region, 26,912. So pretty cool. Some of what's this one, some of revenue by shipper name. So we can see company C we had the most revenue through that shipper. And then this one here is the sum of the shipping fees. That one doesn't really help too much. So we can see that we can get some quick analysis and this is basically what we want to do. We want to condense. all of these
1:01:08 rows and columns into a nice simple table where we can quickly answer some questions. Now, if I click here, this will actually insert that pivot table for me. So if I click here, here is my pivot table right out of the box and I can see all the data right here as well. But let's go ahead and create it from scratch. I'll go back to my data tab here and insert a pivot table. let's go ahead and let's first convert this to a table. So I'll press control T convert this to a table. When you insert a pivot table from a table, you simply need to write click on the pivot table and refresh. If you want to update the pivot table to
1:01:56 show any new values or records that were added to the, your dataset. So it's much more efficient than working with a list. Okay. Now, One of the table tools that's available is actually to summarize our data with a pivot table. So I can click on here and this will open up a new worksheet for me and I'll have the blank canvas to work with. I can also click on the insert tab and choose the pivot table command. They both do the same thing. I'll do this the traditional way. I'll click on insert and I'll click on pivot table. Just make sure that you clicked on any cell inside of your data set here. I'll go ahead and click on pivot table. Here's my dialogue box.
1:02:41 As always, it's always asking us what we want to do before we actually proceed. So it's asking me, well, where's the table or range it's locking onto this table, which is now named table six. I want to go ahead and place my pivot table on a new sheet. I'll go ahead and press. Okay. And so here we are. Here's our blank canvas. Here's our pivot table over here to the right. Here are all the fields from our dataset. And then we have these four areas, filters, columns, rows, and values. And we'll see just by dragging these fields to these boxes, it will start to generate our pivot table over here as well. But the first thing I want to
1:03:27 do, I want to go ahead and rename my pivot table. So I'll go ahead and right click here. and I'll choose the pivot table options command. Now right at the very top, it says pivot table name. Right now it says pivot table five. I'll go ahead and change that. I'm going to call this December sales. It's one more thing I want to do. If you take a look at our, our fields here, we can see that they are not sorted in alphabetical order. We have quite a few fields. So it'll make sense if we can sort these A to Z. To do that, I'll go ahead and click on the display tab here. And on the bottom, I'll be able to sort A to Z. So I'll sort the fields A to Z. I'll press okay. And there we go.
1:04:17 All right. Let's go ahead and start building our table. Let's just add a few. I want to go ahead and get the total revenue. So I'll look for revenue. I'll drag that to the values box. And right there, it gives me my answer, my total revenue, $81,107 and 85 cents. So pretty cool. Now, if I want to go ahead and change that, I want to give the accounting or currency format. What I can do is click on the dropdown right here in the values box. I want to get to the value field settings here. And from here, I can go ahead and change the number format to accounting. Go ahead and press OK, and then I'll go ahead and press OK again. There
1:05:06 we go. So that's how we insert a pivot table, and that's how we add a field to the box to get our first answer. Now, let's go ahead and try and answer some questions on this data set here. Now, what we want to do when we're summarizing data, we want to add numerical values to our values box here. Now we can build the table either by dragging, for example, the revenue to the values box here, or we can ask the pivot table to kind of construct itself for us. So if I were to just click on the checkbox here for revenue, it places it into the values box for me because it's a numerical value. It's also summarizing the revenue as well.
1:05:56 So, but I can change this. So what if instead of summarizing the revenue, I want to count how many transactions we had, let's say by region. So because we have a order ID, let's see, here's my order ID. I'll go ahead and click here. So it's giving me the sum of the order IDs. Well, that's not what I want. I want to count how many orders I had. So to do that, instead of the sum, I want to change this to the count. So to do that, I'll click on the dropdown here. I'll go back to value field settings and notice right here, I can change it from the sum to the count function. So I'll go ahead and change that to the count
1:06:46 and appear for the custom name that appears instead of saying, you know, count of order ID, I'll change this to something else. Like I'll just say orders. I'll go ahead and press OK and let's see what happens. So we can see it now it says orders. And so I have 65 total orders and that's correct because we had 65 records on our original data set. So it's nice to know that I had 65 orders, but now let's go ahead and figure out how many orders we had, let's say by region. So I'll click on region here. And so now I'm getting some more information. So here's my grand total for all of my orders is 65, but now
1:07:34 I'm getting some more specific information. So 16 orders in the east, 20 in the north, 12 in the south and 17 in the west. So as you're beginning to see the pivot table, there's a lot that it's doing behind the scenes that we're not privy to. A lot of calculations, a lot of logical functions, right? That's happening here as well. Well, now that I have that, I'll go ahead and reset my table and let's go ahead and focus on the actual revenue. For here, I have the, let's see, I have the by region. Let's go ahead and add another detail in here. Let's put the region into the columns instead. And let's put something else, a lower level into the rows. Maybe I'll put
1:08:23 the ship state. in the rows. So now just by doing that, we can get some really specific answers here as well. So we can see that, for example, in California and the West region, we had four orders submitted in that particular region. So we'll start to see as we start moving these fields into these different boxes, our table is going to get more advanced and provide more information for us as well. But for now, that's how we change how our values are being summarized. Let's go ahead and add the revenue to
1:09:08 the values. Let's go ahead and apply the accounting format as we did before. Now we have the sum of revenue. Let's go ahead and get the revenue by region. Let's place the region into the columns. And let's figure out what else we want. Let's go ahead and grab the sales by category. I'll place the category into the rows and let's just pause for a moment and take a look at the data that's being displayed here. So this is quite a bit of data to take a look at, right? But think about this in tabular form. I'm actually going to go ahead and click on the design tab here and I'll change the report layout to more look like an Excel table. We don't have the
1:09:55 grid lines in here. I'll change the layout here to tabular form. There we go. So now we can see that we are getting our totals. So let's go ahead and identify all of these numbers here. So here's our grand total of all of our revenue down here in the bottom. Here's the total for the East region, North, South and West. So we're summarizing that column here as well. And over on the right, here's the totals for baked goods and mixes. So just by having the region and the columns, the categories and the rows and the revenue and the values, we
1:10:44 can see we can answer a lot of questions in condensed form here. We can also sort. So if I wanted to sort this, this total column here, I can click on the data tab. I'll sort Z to A. largest to smallest and we can see that beverages we had the highest amount of sales for beverages 19 630. oil generated the smallest revenue 1046 and 15. now we have a lot of fields in here that we can use now when you're building a pivot table you don't have to use all the fields right you don't really have to do that you can just keep it simple go ahead and use the fields that you want to answer a particular question. I'm going to go ahead and change my view. Instead of having the region in the columns, I'm going
1:11:31 to go ahead and move the region into the rows. And let's see what happens when we have two fields in one of these boxes. So now that I have two fields in the rows, I have two levels. So region is level one and my category is a level two item. So therefore we're going to see that indicated on the pivot table. So here's the region that's a level one item. And then we can see that our categories are level two as well. So they're kind of indented over so we can see that detail here. If I switch them, I'll get the opposite. So here's beverages for the east, north, south and west, dried fruit for the east, north, south and west. So that's the nature
1:12:17 of the pivot table. It just automatically updates all the data for us, changes all the numbers. based on what we're trying to answer. Now, this is not the best example. You want to use higher level items at the top of each of those boxes so you can get a better view off your data here as well. I'll go ahead and put the region back into the columns and I am good to go. I'll remove the category here. So I'll remove that and I'll go ahead and click on city. I'll move the city into the rows. And now I have a breakdown for our cities here as well. Let's go ahead and filter our pivot table. We can filter a few ways we can filter.
1:13:02 Notice that we have a row and column label filter. We also have a filter box. And finally, we have on the pivot table analyze tab, we have what's called a slicer. So if I click here, here is my slicer. Let's take a look at the built in filters here. Here's our first filter so we can filter by city. So because we have the city and the rows, if we click on this row label filter, we can filter by city. And this is nice because if I want to go ahead and filter, let's say I just want to filter by. I'll go for on select these here. I'll just choose Chicago and Denver by press. Okay. Notice how my table is only showing the data for Chicago and Denver.
1:13:51 Well, I can also go ahead and filter by region. I'll go ahead and reset this filter. What if I want to just filter by the East region? If I press OK, it only shows the states that make up the East region here and my data as well. We can use filters together, just like with a table to drill down to find what we're looking for. Let's take a look at another filter. Well, what if I want to go ahead and filter by product name? I'll go ahead and drag the product name to the filters box here. Now that I have the product name here, we can see that we have a filter at the very top of our Excel document. And now I can
1:14:37 go ahead and filter by product. I have several products. I'll go for just the first three. So I'll select the first three here. And if I press OK, we can see all the numbers have been updated. So for those particular items, total of 5916. So that is using the filter box here. I'll go ahead and clear that filter. We can see the filter is active right here. And we can also clear it from here. I'll go ahead and make sure that everything is selected. I'll press OK. Everything is back to normal. So those are just some different ways that we can filter our pivot table. Let's take a look at the third filter,
1:15:23 which is a slicer. Let's go ahead and use the slicer to filter our data. I'll go ahead and click on insert slicer here. Now notice we have a slicer for each of our fields, right? One for city, product name, so forth and so on. Let's go ahead and insert just a few. We have a lot of fields. Again, we'd want to keep it simple. I'll go ahead and insert a slicer for the state. I'll also insert one for the region and one for the sales person. I'll go ahead and press OK. Here are my three slicers. I can resize them so I can see all the values. So I'll just resize them so I can see the bare minimum here. Now
1:16:13 our slicers, they come with a contextual tab, so we can change the colors. We can also change the amount of columns that's being displayed. I'll just give them each a different color. So there we go. Now, if I want to filter by a region, if I just wanted to answer a question based on the East, if I click on the East button here on my slicer, it's only showing for the East. I'll go ahead and clear that. If I want to get sales revenue based on the sales rep, let's say Michael. Michael had some sales in Milwaukee. $9,111.75.
1:17:00 What about by state? I'll go for California. Here's California here as well. If you want to select multiple, you can hold down the control key and select multiple. So I'll go ahead and select Colorado. Florida, New York, Oregon. And then we can see
…
