Navigate through the different worksheets. So if I right click on those left and right arrows at the bottom left hand corner, it'll open up what's known as the activate box. The activate box will showcase all the worksheets we're working with today. And we're going to be using creating tables as our first one. Now tables are one of my favorites in Excel because they allow for us to add extra features to our datasets. You'll see here that I have some simplistic data. We have the employee information list, and in this list we have topics like employee ID, last name, first name, department, Email, their phone extension,.
What location they work in, when they were hired, and what their pay rate is. Now, of course, this is a great dataset. This is a well defined list of data, where we have headers, we have some sort of ordering system with the employee ID. There's no missing data or spaces between our data, so this is a well defined list of data. But there's a couple things that could be better. For instance, readability. Everything kind of looks the same, and I can't differentiate between each row. Or if I scroll down, how many of you have ever done this? And it's okay if you have. You're scrolling through the data, and you're like, wait a.
Second, what is this number? And you gotta scroll back to the top, oh yeah, the pay rate. Or you scroll down again and you're like, wait a second, what is this number? Scroll back to the top, oh yeah, on extension. We tend to forget what data we have, especially if we have massive amounts of data. Taking this data and turning it into a table will help with that. It allows for us to lock our headers to our columns, and it also allows for alternating rows. Right? So we'll be able to read our data a lot better. The first thing we need to do to turn this to a table is select all.
Our data and to select all our data. There's two ways to do it. You can either just simply click and drag on your data, but then sometimes I scroll and I go too far or maybe I go too fast. What I like to do is just simply use the control key plus. A, A for all, right? You're selecting all the data. So just simply click somewhere in the middle of your data, use control A and it'll select all of your data. Once you have all your data selected, you can then go up from the top to the insert tab and inside of the insert tab, over in the tables command group, this is where you'll find the table command, where we can take this.
Not only that, I do notice that we also have filters. We have our drop down filters, and we'll talk more about filters coming up in our next section, but it's good to know that we can quickly organize our data any way that we like, sorting, or even use text filters, or filter what we want to see, and that comes right away with the table. Now if you don't like the filters on there, no worries. We do have table design tools where we can turn that filter button off and when we click on it, we no longer have the filters there..
So we do have a lot of options when it comes to working with our table, whether we want to turn things on or off. If you don't like those banded rows or banded columns, or maybe you want to make the first column look different or the last column look different or turn off your headers, you can do so by just simply clicking on each one and seeing what it does. Maybe we're reading our data the other way, last name, or first column. We can also quickly change the color of our table..
Not only that, but if you remember before, I told you that it locks our headers to the top. So if I scroll through my data, you have to make sure you're clicked into your table. I can now say, what's this? Oh, it's the pay rate, because it locks our headers to the column. Now I know that this is the form extension, location, and I can scroll back up. Now another thing about tables is that anytime you create a table, you do get those table design tools. If you click away from the table, you may not see the table design tools up top..
You need to essentially click on the table to see those tools available to you, and then you can click on them. Those are known as contextual tabs. Thanks. If you don't like the table, you can always convert it back to a range by clicking on convert to range. You can name your table if you want to. You can remove duplicates from your table, insert slicers, which is another type of filter, or even do external table data report. So you can export this information to, let's say, a SharePoint list, or a diagram, Invisio..
One of my favorite things about a table, though, Is the total row. If you go to the table design tab and you click on total row, it will add a row to the bottom of your data that totals up the pay rate. But there's so much more that we can do in analyzing a table. Not only can we just look at the total, but if we click on it, we could click the little drop down and choose to look at the minimum or the maximum or maybe the sum. And not only can we do it for the pay rate, but we can do it for any of our columns here. So maybe I want to count how many higher dates I have, and I'll.
Posts Related:
- Visão Geral Menu RIBBON Planilha Login Excel VBA
- SWOT Analysis template *Free download* powerpoint presentation #stayhome learn #withme
- Excel Today - Microsoft Planner Office
Select count, and it looks like 37. So that's really nice, or I could say done again.
We can analyze our data, and once we're done analyzing the data, we can turn off the total Another thing about tables that is important is that they expand. So anytime you want to add a new entry here, it will automatically expand the table for you. This helps especially if you're dealing with tables or any other analysis for your data sets. It will automatically expand the table and include that new data..Our next topic we're going to be talking about is Flash Fill. It's one of my favorites. Now I know it sounds like autofill, which follows a pattern. So Flash Fill actually does the same thing. It follows a pattern. depending on what example we give it. For instance, if I see here, we have the full name like Eric or Ruben or Brooks, and I just simply want to grab that first name from the full name and split this entire column into two. Well, the old way of doing it used to be that we would select the data, we would go over to the data tab, and we would use text to column..
Now, I would open up this wizard, and you'd have to go through a bunch of steps here, and Look at the limiters and things like that. But with Flash Fill, we don't have to worry about any of that. We just give an example of what we want. In this case, what do I want? The first name, Derek. So I type in Derek, I press enter, and I'm now telling Excel that's what I want to pull from that first column. I can then go to the Data tab, and inside of the Data tab, we can head over to the Data Tools command group, and you'll see this flash fill with the little lightning bolt..
If you give that a click there, it's going to now fill in the rest following the example you wanted, which is the first name. So if I click on that little lightning bolt, how amazing is this? It knows to pull the first name. Now we could have done that, or we could have just used a keyboard shortcut instead. I can right click, press enter, and the keyboard shortcut for Flash Fill is going to be the control key. And if you ever want to remember that, you can go to the top and hover over.
Flash Fill and it'll even tell you Flash Fill, parentheses, Control E. So now that I wrote Derek, I press Enter. I can now just do Control E and look at that. And I can do it for the last name too. Give the last name what you want, press Enter, Control E. Now not only can we just pull things apart like this, but we can create our own examples. Maybe, for instance, I want the last name, comma, first initial, or maybe I.
Want the last name, comma, first name. It's really up to you. I'll do last name, comma, space, and I'm going to do the first initial. I'm going to press enter and use control D. And there we go. As long as you give it an example of what you want, it will then be able to follow that. It'll say, oh, we see what you're doing, Joe. You're splitting the last name, putting a comma, and then taking the first character from the first name. We'll do it for all of them for you. One of my favorite things is that Flash Fill can also recognize the.
Difference between letters and numbers. Let's say we have model numbers, and we're deciding that we want to split them. So I'm going to take the AB from the letters, press enter, and use ctrl E. You'll see it doesn't matter how many characters long the letters are. It could be 2 or 3 or 1 or 5. It will know the difference. We'll just put in the numbers, 1, 2, 9, 3, press enter, ctrl E, And it'll pull the numbers as well. Not only that, we can do that for the company..
Let's say we have these email addresses here, and we just want the company name. So, I'll type in the company. Example. Press enter. Control E. And there we go, fake mail, sample of mail, and it will pull it straight from after the at symbol. So it knows exactly what we're trying to do, pull after the at symbol, but before the period. Sorting allows for us to organize our data in a certain order..
And what we're going to talk about is how sorting works. In order for sorting to work, you need two things, a header and also data. So let's say over here that I have a list of names. I'll just put name, myself, couple other names as well. If I have a list of names, there's no difference between the header and also the data. Well, if I go to sort this, it'll actually include name into this list of names here..
So let's say we go over to the data tab and we go over to the sort and filter
Command group, and then I'll choose the quick A to Z to sort it alphabetically. Okay. If I use that right now, I'll click on one of the names, click A to Z, it's also going to sort in our header. And the reason it's doing this is because they don't know the difference between just our headers and our data. So we have to actually make a difference. So I'm going to undo that. And I'm just going to simply make any type of difference to this..And I'm going to choose to bold it. Once I bold it, now if I go to data, and I sort this A to Z, you'll see it works perfectly, or even Z to A. Because now it knows the difference between the actual data and the header. Now that we know a little bit more about that, and I'll delete the tile real quick, let's actually sort our data. So I'm going to come over here, and let's sort our data by last name. So I'll click on any of the last names, it doesn't really matter which one you choose, and we'll go to data tab, and inside of the data tab, in the.
Sort and filter command group, Simply choose A to Z, if you want it to be alphabetically A to Z, or Z to A. I'll choose A to Z. And now we're sorting all of our data by last name. Now it is important that you have some sort of original order. And the original order for me was ordering it by employee ID. If you ever need to order it by employee ID again, just click on one of the employee IDs and then choose A to Z. You'll notice that now it says sort smallest to largest or lowest to.
Highest because now it knows that we're selecting on a numerical value, a number. If I click A to Z, it goes back to the original order with John Smith in the beginning. Not only can we sort a to z or z to a, but we can sort by numbers by just simply clicking on a number. So when I hover over, it'll say sort a to z. But if I'm on a number and I hover over, it'll say sort smallest to largest..
Now let's say that I also want to sort this and a multi level sort instead of just doing a single level sort. Most of you would think, well, let's say I want to sort by last name and then location. Can I just simply click on last name A to Z, location A to Z? It looks like it's working, but it's actually not working the way we want. If you want to do a multi level sort, We're going to actually have to use the sort button instead of the A to Z or Z to A. This will open up a dialog box that allows us to sort in the orders that we need to..
So if you click on the sort, it's going to pop up this dialog box. It's going to ask you what you want to sort by. Well, I want to sort by last name. It's going to ask you how you want to sort it. I want to sort it on whatever the cell value is. You can also do cell colors, font colors, even conditional formatting icons. But most of the time we'll use cell value, and we can choose what the order is, A to Z or Z to A, and I want A to Z. I also, after I sort by last name, then want to sort by location..
So I'll click Add Level, choose Location, A to Z. Then I'll click OK. And now we're actually sorting by last name, then by location. The last type of sorting I do want to showcase is known as a custom level sort. Now the custom level sort is great when you want to customize your order. For instance, let's say that I want to sort this by day off. Well, right now I can't sort it by day off because if I use A to.
Z, it just does it alphabetically. Or if I do Z to A. It just does it once again, alphabetically. So what I need to do is figure out how can I make it run from Sunday to Monday? Well, in that case, you're going to click on your sort button. Once again, the same one we use for multi level sorting. And once you click on it, it'll pop up this dialogue box. You'll choose what you want to sort by. I want to sort by day off. So I'll select day off and we know that A to Z doesn't work..
Z to A doesn't work. So the next thing would be a custom list. Now you'll see here that when you click into custom list, you'll already have a couple of them pre created. I actually created my own as well, like this Monday through Sunday, or go is cool. But you should have these ones that Microsoft pre created for you. And we have the one that we need, Sunday through Saturday. If you ever need to create a new one, just click on new list.
And just write out your entries. Like, if I want to sort this by building, I could do building three, press enter, building or building one, press enter, and then building two. And then you can just click add and it will add it as a new custom list. But we want to sort by day off, Sunday through Saturday, so I'll click the one that already exists and then click OK. Click OK. And there we go. Look how awesome this looks..
And always remember, if you ever want to go back to the original order, just simply click into the employee ID and do A to Z. And it will bring you back where John Smith is first. The sorting function allows for us to sort our data in ascending or descending order based on values in one or more column. What I really like about the sorting function is that you do not have to affect the original data source. So let's say that we have our data here, some simplistic data..
We have the region we sold them, who our sales rep was, what product we sold, and also how many units we sold. Now what I want to do is create my headers. It's going to be the same headers that we have on our original table. You can just copy and paste them over here. And I'll click into F2 and we're going to start with the sort function. So let's put an equal sign. We'll then type out sort. We'll then tab on our keyboard..
We'll open up our parentheses for us. And now it's going to ask us for four arguments. Three of those arguments are going to be optional. And then the first argument is, well, what are we selecting? What are we looking at? And we're looking from A2 to D7. We'll select our data, put a comma, and it's going to ask us what is the sort index. It's basically saying, do you want to sort by region, sales rep, product, or units?.
And which column does that fall under? Now, instead of saying column A, B, C, or D, it's asking for an index, which is a numeric value, a number. So I want to sort this by product. So I'm going to just count one, two, three, four. So now I know that I'm sorting by the product column, which is going to be three. Okay. If I was going to sort by region, we would put a 1 there, or sales rep 2, or units 4. So, I'll just put a 3 because I want to sort by product column. I'll then put my comma. Don't ask me do I want to sort in ascending order or descending order. Ascending order being a 1, or descending being a negative 1..
I'll choose ascending. I can then just press enter, and there we go. It's going to spill the results over into my other cells, and I'm now sorting this by product. Apple, then banana, then grape, then pear. And if I ever want to change which column I'm sorting by, I can always double click in TAF2, and just change the either, ascending or descending order, or the index. Maybe I want to sort this by sales rep, so I put a two, and then maybe.
I want to sort this in descending order, so I do a negative one, press enter, and there we go. When we got the sorting function, we also got what was known as the sort by function, and the difference is, is that the sorting function allows for us to sort an entire data set. Where the sort by function allows us to sort only a certain amount of our data by another column. For instance, let's say that I wanted to sort this entire data set from sales rep and sales amount, and I want to sort this based off of bonus..
I can't just use my original sorting function. If I tried to, this is what would happen. If I do my sort function, it'll ask me to select the array. I'll just select these two. And then it's going to ask me what I want to sort by. I want to sort by bonus, so that's 1, 2, 3. And what order? Ascending. And when I press enter, I'm going to get an error. Because I need to select all the data using the sorting function. But if I use the sort by function, this will work perfectly..
I'll do equals, sort by, press tab to open the parentheses. Then task us our array. What are we looking at? From A2 to B8. Put our comma. And what do we want to sort by? Well, we want to sort by C2. C8. And then we put our comma, and what do we want to sort it in? Ascending or descending? Let's do ascending. So I'll put a 1. When I press enter, it works perfectly..
And this is because it's taking our data, which is just these two, and sorting it by this one column here. So even though the sort function wouldn't work in this case, the sort by function will work perfectly. In this data set, we have our date and we have a bunch of foods that we sell and then how many quantity we've sold on that date. But what happens if I just want to see only what food I'm selling? Well, then I'm going to have to take a look. Apples, oh, apples again, okay..
Oranges, that could take too long. So what Excel did was they made a function known as the unique function. And the unique function removes duplicate values from a range or an array of data. So we can quickly find out what types of food we're selling and remove all those duplicates without affecting our original data set. So I'm going to put this anywhere. You can put it anywhere as well. I'll click into, let's do E3. I'll do equals, and I'm going to start typing out unique. You'll see it's right here..
It'll even tell you that it returns the unique values from a range or an array. I'll press tab to open the parentheses. And all you have to do is just select which column you want to use. I'm going to select from B2 to B17. I'll then press enter and it will return just a unique value of each. So now I know I sell apples, oranges, bananas, strawberries, blueberries, grapes, and cherries. What I really like about this unique. function here is that we also have in the unique function a couple of other.
Arguments here that are optional. For instance, we can choose bicolumn, which will return unique columns, or it can return unique rows, Or we can do exactly once where we can return items that appear exactly once or return every distinct item. Maybe I want to see, is there anything that I've sold only one time in this list? So I'll do true. And when I press enter, it tells me I only sold oranges one time. The rest, I've sold apples a couple of times, I've sold.
Bananas a couple of times, strawberries, but oranges only once. So I probably need to market oranges a little more. So Unique can work two ways. It can only return things that you're looking at one time in a list that appears only once in a list. Or you can use it to pull out unique values in the list. Filtering data allows you to only view the rows in the dataset that you want to see. Let's say that we're looking at this pair company payroll information dataset and I just want to simply see how many staff I have at my department..
In order to do this, I first need to turn on the filters. And we can turn on the filters by going over to the data tab and inside of the data tab, we're going to head over to the sort and filter command group, and we're going to click on the filter button. It is important that you are selected on your headers. You don't want to select, let's say, J1, because if you do that and you click filters, well, it'll say it can't apply. Instead, I'm going to click on, let's say, D3. And if I click filter, then it will place my filters nicely.
On each one of my columns. Now I can quickly go over to the department column, click on the drop down, and you'll see that right away we can start to store our data, or we can use text filters. Or we can just simply choose what we want to see out of our different categories here of departments. And I just simply want to look at sales. So I'll click the drop down and I can either deselect all of them and then click sales or deselect development, research, and staff..
So that only sales is selected. Once I do that, I'll click OK, and there we go. I'm now looking at all of my sales staff. Now, there's a couple of ways to know that a filter's on, but there's one sure way. If I'm looking at, let's say, the column, you'll see here that they're all just little drop downs, except for the department. You'll see a little funnel there, and that's how I know that I'm filtering for my department column. Or if I take a look at the rows, the rows are hiding certain rows..
7, then it goes to 10, then it goes to 16, then 18, then 23. Certain things are being hidden that don't meet the criteria of SAIL. But the surest way to know that there's a filter on, It's if you go over to the sort and filter command group, the only time that this clear button will be available will be when you're filtering for something. If it was grayed out, that means there's no filters on it. So if I click on clear to clear all the filters, it clears everything out and now you'll see that it's grayed out, meaning you have no filters on right now..
Now there's many ways to filter. We can either filter one column at a time, or we can filter many columns. Maybe, for instance, I want to see how many development staff do I have in Connecticut. So I'll go to Division, only select Connecticut, click OK. And then I'll go to Department. And only select development and click. Okay. And now I'm only looking at my Connecticut development staff..
Now at this point, we're already analyzing the data a certain way we're filtering for it, but if I did want to see how many employees do I actually have, you could just count them, of course. Or if you select the data, you'll see at the bottom, towards the right hand corner here, it'll tell you that we have nine cells that are filled with data. This is a quick way to analyze your data. So, just by selecting, let's say, the word Connecticut, it'll tell me that Connecticut is run nine times. So now I know I have nine staff..
