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. Learn at any Time.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, 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 right. So remember our headers. They are the key when we're analyzing our data. Because the header is represents the name of the field in which we want to analyze or in this case the column we want to analyze. So we want to make sure that they are named well. But 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. This is not always the case, but it's best to have unique labels when you're working with data. So 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 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..
It tells me right away that I have one blank cell. Right. 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. 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 we can convert this list into a table. Here's the first way we can actually use a shortcut. So if we press Ctrl 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 table command..
So 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 Ctrl 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 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 Ctrl Z and let's try method number two. I'll go ahead and click on format S table from the ribbon. Here. And the only difference is we can choose our table.
Posts Related:
- How To Add A Custom Homepage To Your Blogger Website - Live Blogger BlogSpot
- How To Create A Dynamic General Journal & Accounts Register In Excel
- How To Create An Inventory Assembly & Bill Of Materials (BOM) Application In Excel Free Download
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 sort by columns. We can also do some filtering as well. We also have another tool that's called the total row. So we'll take a look at these three tools 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 that'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'll sort a-to-z..
And notice how all of my records now are being sorted 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 want to do some more complex sorting. We would need to click on the data tab and click on this 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 remove 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 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. Or 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 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 a C. And inside of that department is also sorted by last name. Here's the add 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 higher date. Let's see what filters are available for the higher date..
We'll also take a look and see what filters are available for the pay rate. So we'll work with dates and numbers. Now for the date filters we have a lot of date filters. I can look 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, 2008. But before December 31st, 2006, I could 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 filters active here. I'll go ahead and click on it. And I'll click on the clear filter from here. 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 enter a value in the search box.
What if I just want to find out how many hires did I have in thousand and ten. So if I type 2010 here, notice how it collapses 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'll use the date filter. We use the search box. Now I can just choose an individual month day or year down 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 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 36, 76. Let's show all of the numbers that are greater than or equal to 30..
Here's my simple dialog. I'll type 30. And 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 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 part. 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 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 in 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 drop down. And on that drop down 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..
And it's already has a number here. The cell in the bottom right. So it says 1010 30. Well let's go ahead and click on that cell. If I click on that dropdown it's 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.31. 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, some hours in here and the gross pay, then we'll see more options that are available to us. As a matter of fact, I'll go ahead and 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 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 1516..
I'll continue this pattern down till I get to say 40. Right. And let's see then I'll just come back here and continue this pattern down as well. Why not? Then I'll simply just add a add 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 1155 hours..
What is the total gross pay? There's the sum of the gross pay 31,008 4351. 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 eight 4351. 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. I press okay. Notice how the numbers have changed. The pay rate has changed. The hours of change and the gross pay has changed based on the AC. The part of the. 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 the 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 are 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 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 group. 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 Ctrl and so we go down to row 133. So quite a bit of data here. I'm going to press Ctrl 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 ones 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. 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. When I 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 you kind of scrolling through here keep your eye on all the greens..
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 is greater than. In this case, it's giving me 23, 73. I'll just change this to 2500. So we can see it's already applying that light red fill of dark text. Maybe I want green fill with 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 right. So maybe I want equal two. I want exactly three bedrooms. I'll go ahead and press. Okay. So now I'm getting a match with a couple of different places here. I'm good to go. All right. I'll go ahead and do one more for the bathrooms. So maybe this one. Let's say I'll go for.
Greater than, say, greater them. 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 give some really nice analysis. We can highlight the top ten items on a list based on value. Top 10%, bottom ten items,.
Bottom 10% or 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. 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 the 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% right. I'll go back to the 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 filter. Let's take a look at the F function. Now the f 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 Sam Sammy Brown, Danny Meyer, a Sandberg and a John. We're tracking their sales for February. For example, in week one, as Sam had sales of 95, 50 in a week for 8965, for a total of 36,245. So the question that we want to ask is, well, was the goal 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. Is that greater than or equal to the monthly goal which is 34,000. So we need to compare these two sales 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 as well if it's true. What value are we going to place in this sell. Right. So we're either going to say yes or no..
So that's it. Was the goal met. In order to answer that. Well we use the sell reference here is 36,245 greater than or equal to 34,000. Yes. It's true. So Sam did meet the goal. We'll put a yes in here as well. Well, that's exactly what the F function will do for us. We already know the sells that we want to compare here. We're comparing cell F7 to cell B4. We already know what we want to say if that logical test is true. We're say yes..
The goal was met. If it's not true, we'll simply say no. Let's go ahead and insert our F 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. Here's my insert function dialog. I'll type the f function up top. I will press go. And on the bottom I'll press okay..
Welcome to the function arguments box. Really nice tool because it gives you the name of the function tells you what it does. And 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 cell 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.
