I'm going to go ahead and keep on with this original pattern. Dot last name@pairco.com for Sean Willis. I'm going to go ahead and just start typing Sean and check this out. XL actually identified the pattern as I was typing it. I repeated the pattern one time and it immediately made the assumption that I was going to be replicating it all the way down the column. And it was right. I can now tap the enter key and it will automatically fill in all of those values for me..
This is called FlashFill and it's an incredibly powerful tool. It's one of those things that once you know it exists, you can never go back. Go ahead and try that for yourself in the first cell. Go ahead and type in first name dot last name@pearco.com. And then once that's been up and running, go into the next cell and begin to replicate and watch Excel, go to work. Go ahead and try that and then come back. So let's take a look at what we've done here. As we're scrolling down the dataset, you can see that we've completed.
The last remaining empty column. What we've now done is we've actually created a well-formed list. What does that mean? Well, formed list, cause it's not just an aesthetic thing. A well-formed list is content that's grouped together in near proximity with no fully blank rows or fully blank. That allows us to manipulate all of the data all at once without having to highlight it. Let me give you an example, go ahead and place your cursor anywhere inside the department column in years past, if we wanted to sort.
The department column, we'd have to highlight the entire data set first. And in fact, many of you are still under the impression that you have to highlight everything in order to sort, it, it turns out you haven't had to do that in quite some time. In 2007, when Microsoft introduced well-formed lists, they didn't really tell anybody. So let's take a look, having placed our cursor and say the department column, we're going to need to find our sort tools. Now our software tools can be found here on the home tab, in the sort and filter dropdown..
Once again, well-formed lists are only any mass data. That's close together in proximity with no fully blank rows or fully blank columns. Let's try it with the division column. Go ahead and place your cursor inside the division column. And once again, go ahead and press a to Z. And there you go. Just like that. It's sorted the division column and it's sorted the entire dataset along with it. Once again, maintaining the horizontal record. Go ahead and try that for yourself and then come back. So there you go, because we had a well-formed list set up here. We didn't have to highlight the dataset in order to sort it..
All we had to do was place our cursor inside the column sorted and the entire data set followed. And that was because of that well-formed list set up. However, let's say I introduced a fully blank column into the mix here. Now, column F is completely empty. What were to happen if I placed my cursor here in the last name column and tried to store it that way, you'll notice that this side of the data set sorted. However, this side did not the reason being is these are now two separate well-formed lists because of this fully blank column..
So it's important to keep that in mind as we're going throughout this process here, because fully blank, rows and columns will make things difficult for you when it comes to trying to organize things. Now that we've seen, we can use single level sorting, meaning we just sort by one column value or another, let's say I want to go a bit further. I've sorted by the division column, but now within each division, I'd like to organize based on last name. I'd like to just sort the employees by last name, as you can see here,.
That's not really the case right now, here in Connecticut, we've got Colbert and chin already. We're off to a terrible start and then rich and then Cafaro and then a bell. But if I were to just place my cursor in the last name column and click a to Z, why wouldn't that work well? Because I'd lose my sort in the division column in order to compound sort, I'm going to need to turn to my big sort tool found here in the sort and filter bar. Now, cursor placement doesn't matter here when we're using the big sword. So go ahead and just click the big sword button and you're going to.
Get this box for my Xcel veterans. This is a pretty familiar view. This used to be the only way that you could sort within a dataset. Now we see that we've got our current sort already in place. We're sorting based on division, based on the values of the cells alphabetically. Okay. But now I'd like to tell Excel to hold onto this. And add an additional level of sort within that. So let's go ahead and click that add level button to add.
Posts Related:
An additional level of sword. Now that I've added that additional level of sword, I'm going to go ahead
And dictate what that then by will be. You'll notice. Now it no longer says sort by, it says then by indicating that we are going to have one dominant level of sword division, our then by will be the last name field, meaning that each division will have its own organized list of last names based on the values of the cells. Alphabetically, go ahead and click..Okay. To lock it in. And there you go. We're now sorted in division, but within each division, we are now organized by last name. Pretty cool. Go ahead and take this opportunity to pause the video and try this for yourself. Let's take a look at another list set here, here in this particular data set. I've got a list of names still, but this time I've also got days off. So let's say I'd like to organize these individuals based on their day off, because I'm going to place my cursor inside the column, just like I did before. And I'm going to click that a to Z button..
Woof, what's wrong with this? What did they do? The expectation was that when I click that a to Z button, it was going to sort the days of the week, based on the order of the days of the week, Sunday, Monday, Tuesday, Wednesday, but it didn't instead, what did it do? It sorted it alphabetically, but no one and I mean, no one orders the days of the week alphabetically. So how do we get Excel to look at these days of the week? The same way we do, interestingly enough, Excel doesn't really know that Sunday.
Comes before Monday comes before Tuesday. We actually have to tell it in order to tell it, we're going to need to jump into our big sort tool. So go ahead and find the big sort tool and give it a click. Once again, we get our big sort tool and we see here that the sort we initiated is still their day off based on the values of the cells alphabetically. So where did we go wrong? The order someone's going to click on that and surely there'll be an easy option, right? Well, not so much. I see a to Z, Z day, but neither of those are going to do it for me..
And there's only one option left and it's custom list. Go ahead and click on custom list. You're going to get this box right here. This is a list that's pre-made by Microsoft. It's got the abbreviated days of the week, the fully spelled out the abbreviated months of the year and the fully spelled. Xcel actually has to be told that this is the order of certain things. And in fact, you can tell Excel to create its own order of other things in this case. However, we've already got what we need the fully spelled out days of the week..
It's important to note that if you don't choose the fully spelled out one in this instance right here, and you try to use the abbreviated, it's not going to work because Excel doesn't know that sun means Sunday or that Monday means Monday. So you have to ensure that the custom list you're choosing matches the content you're trying to organize, having selected that custom list. I'm going to click okay. Day off based on the values of the cells Sunday, Monday, Tuesday, and I'm going to click. Okay. And there you go..
The days off are now organized, based on an actual order. Go ahead and try that for yourself. Once again, the way we got there was by going to the big sort tool and changing the order to a custom list, we then selected the fully spelled out days of the week. Clicked. Okay. And locked it in one more time by clicking. Okay, go ahead and try that for yourself and then come back. So there you go..
You see that we were able to use sorting in order to organize this data in a way that makes sense to us. This enables us to find data more easily. For example, if I'm trying to find somebody whose day off is on Wednesday, I now have an expectation that they will be somewhere in the middle, because I know the order of the days of the week now with that having been said, sometimes sorting just isn't enough. It's nice that we were able to organize things a little bit more. So, but like with Wednesday, if I'm trying to find an individual here, I still have to go and scroll through all of this content that I don't need..
It would be nice if I could just hide all of that information and tell I actually needed that information. And that's where a tool called filtering comes into play. Filtering is an incredibly powerful tool that allows us to give specific conditions for content that should, or shouldn't be visible in order to work with filtering. We're going to need to actually turn the filter tool on. It's going to be found inside the data tab, inside the sort and filter command group. Find the big button that says filter and ensuring that your cursor.
Is somewhere inside the dataset. Give it a click, just like that.
The dropdown arrows appear in each individual column. Notice we didn't have to highlight the entire data set in order to turn filtering on yet again, because we had that well-formed list set up. We didn't have to, it knew that we were trying to enact change across this entire dataset. And so by just having our cursor inside. It allowed us to implement filtering across the entire set.With these little arrows having appeared. We now have some options available that weren't available before. Let's say for example, I'm trying to find all the individuals who work in my development department. Well, first things first I have to identify which of these columns contains the development value. And I see that it's here, the department column, somebody use the dropdown arrow specific to the department column, and I'm going to get this view, which provides some basic sorting tools. But down here at the bottom is where we're really going to be focused..
Each individual checkbox represents a unique value inside the column. So we can see here that there are four separate unique values inside the department, column development, research, sales, and staff, all of them are currently checkbox, meaning all of them are currently visible. So if I'd like to have it set so that I only see development, I'm going to need to de-select all of them and re select development having done that. I'm now going to click. Okay. And just like that, a lot of things have happened..
So let's break this down. The first thing you'll notice is that the down arrow now has a filter icon. Additionally, you'll notice that the row numbers have changed a little. 1, 2, 3, 8. I'm no mathematician, but I'm fairly confident that that's not the numbers that were there before. You also notice that from eight down to 50 here, they're all colored in blue, indicating that these are rows we filtered for. We asked for these, if you're seeing a record inside of this field that you didn't ask for, you need to check your parameters to make sure you're.
Filtering for the appropriate data. Last indication that we're filtering is in the status bar. We are currently looking at 24 of 94 records. Not bad. Go ahead and try that for yourself. Once again, the way we got to this point was by turning on the filter tool, which can be found here inside the data tab. Next, we clicked on the dropdown arrow and the column that we'd like to filter. And lastly, we selected the value we wanted to see and D selected all the ones we wanted hidden..
Go ahead and try that for yourself and then come back. So that was one level of filtering and that's great, but now I'm left with all this information still. Now I'm looking inside the development department, but let's say the individual I know for sure works in Connecticut. I'd like to filter this down so that I'm only seeing people who work in the Connecticut development department. Can I do that? Can I possibly filter from multiple columns at the same time. Absolutely. You can go ahead and click on the down arrow and division and in the same.
Process, we're going to de-select all and re select Connecticut and click. Okay. So now I'm down to two filter fields. What I'd like to do now is just organized the existing individuals. What's the best way to organize any lists that have names. Well, my last name, can I sort while I'm filtering sure. By clicking on the last name dropdown, you can use the sort a to Z or Z to AA tools..
However, don't let this stop you from using the tools up here as well. This is simply a convenient location for them. You still have access to all the same tools that we've talked about in the past. Go ahead and sort the last name alphabetically. And there you go. You've now filtered by two distinct columns and then sorted a third. This is called multi-level filtering and it allows you to find specific values even when there's 94 records. Pretty cool. Go ahead and take this opportunity to catch up with me..
Once again, we sorted by last name, filtered for Connecticut in the division column and filtered for development in the department column. Go ahead and try that and then come back. There's a lot of filtering going on when you're done filtering and you'd like to bring back the original dataset. It's really easy jump into the data tab and find the clear button. This will clear all existing filters in the entire worksheet. You can also clear individual filters by clicking on their individual dropdown.
Arrows and selecting the clear filter button using either of these methods. Go ahead and clear all of the existing filters. Now it was great that we were able to filter for specific values, but we're not always going to be so lucky as to search for very specific things. Sometimes we're looking for more of a range of things. For example, here in the benefits column, let's say I'd like to find all employees who have retirement benefits. Now, given the way this column is set up, retirement can be mixed in with dental and health insurance as well..
So I'd like to find all instances of retirement without having to go and specifically select every single check box that has an RNN. How can I do that? Well, instead of checking the boxes, what I can do is use the search box to search for a specific value inside of a cell. Meaning that if any cell even kind of meets that criteria, it will bubble it up for me. So here in the search box, in the benefits column, I'm going to filter for the letter. And you'll see that it automatically finds all of the unique values that.
Contain the letter R from here. All I have to do is click. Okay. I didn't even have to check the boxes by typing in the box. It checks them for me and clicks. Okay, there you go. I'm down to 49 of 94 records. So these are all my employees that have retirement benefits. What I'd like to do now is find all employees who work less than 35 hours, because I'd like to let them know they are now eligible for additional retirement benefits that otherwise were only previously.
Available to full-time employees. Now, how do I find individuals who are less than 35 hours? Because there's quite a few individuals. Are we really going to go through an individually check box? Every single value that's less than 35? No, that would be too much work. Instead. We're going to filter for a specific range using the number filters tool. Once again, this is inside the hours column and it's called numbers filters..
Go ahead and find that number of filters tool. And which one of these do you think we're probably going to use? We're trying to find all employees who are less than 35 hours. Here we go, less than go ahead and find less than and give it a. And we see here, the custom auto filter box, where we can dictate what rows get shown based on certain conditions. In this case, we want to show rows where ours are less than 35. Go ahead and do that and click okay. And just like that, we're down to 11 of 94 records, not bad..
And we did all of that searching for parts of a cell, not necessarily specific values, just like in other instances, we can now sort. So if I were to sort by hourly rate now from smallest to largest boom, there you go. Go ahead and take this opportunity to try this for yourself. Once again, we searched inside the benefits column using the search bar for the letter R indicating retirement benefits. We then filtered using the numbers filters tool. It's a search for individuals who work less than 35 hours..
And lastly, we sorted the hourly rate column from smallest to largest. Go ahead and take this opportunity to try that and then come back. Good. Let's go ahead and clear that filter. The next thing we're going to do is we're going to turn off the filter tool by turning off the toggle. We turned on previously. Now that we've talked a little bit about sorting and filtering, it's great that we were able to do all of that. And in fact, it was really made possible because of that well-formed list..
We talked about well-formed lists are great. However, there are additional tools out there that allow us to work with this data, manipulate it sorted and filter it in a more simplified manner. It's not to say that there are better versions of these tools, but they're easier to use in some circumstances when we've organized this dataset in a more structured way, I'm talking about tables. Now I know the confusion that arises here in these situations, tables, surely this is a table..
It looks just like the tables. We in certain side of Microsoft word documents, right? Well, it's not a table yet. It's just a list, a well-formed list, but just a list. So when that having been said, we need to make it a table. Now, the determination between what is and is not a table is based on one single criteria inside of Excel. We have to tell it it's a table, that's it. So let's do that. Placing your cursor anywhere inside the dataset, find the insert tab..
Here inside the insert tab, you'll see a series of options beneath inside the tables command group. We've got pivot table, which we'll be talking about later. Recommended pivot tables, still talking about it later and tables. We're going to go ahead and use that one. So once again, ensure your cursor is somewhere inside the dataset and give that table button to click. You'll notice that it automatically highlights everything. This is one last time, because this is a well-formed list. We didn't have to highlight everything. Excel knew. We were trying to enact change to the entire dataset..
Having ensure that everything is selected. We're going to go ahead and verify that our table has headers in this case. Numb first, last employee ID. And so on our table does have headers. So we're going to leave that box checked and click. Okay. And just like that, we now have a table complete with a set of tools that weren't available before the table tools design tab..
This is an example of a contextual tool tab that only appears when we're in an environment that requires it. Notice what happens when I click away from the table, I'm going to click away and you'll see that the table tools, design tab disappears. I'm now going to click back inside the table and you'll see that that tab appear. Go ahead and take this opportunity to catch up with me once again, the way we got to this point was by navigating to the insert tab, ensuring that our cursor was somewhere inside the dataset and then clicking the table button..
Once you verify that all of the data has been selected click. Okay. And there you go, go ahead and try that and catch up. Welcome back. So we've formatted this as a table. Let's take a look at some of the tools that are made available when we format as a table. First things first, you'll notice that we have filtered drop down arrows yet again. So it's nice to know that those automatically come with it. Of course, we don't have to use those inside the table tools, design tab..
You'll notice that we have a series of checkboxes. We can actually turn off the filters. If we want to simply uncheck the box. Now, as we're going through this, there's a lot of really cool things that can be done with these tables. And you'll notice that many of them aren't necessarily exclusive to tables. They're just more consolidated. Table styles is one thing that's unique to Dale's. It's the ability to change the visual aspect of everything with inside the dataset instantly by expanding on the table styles drop down in the bottom right corner. You'll see that we have a series of visual options by hovering over them. You'll get a live preview of what these styles might look..
In this case here. Hmm. I'm digging the orange. I think I'm going for orange this time around once you find a style that you like simply click to lock it in to the left, as we've seen, there are some check boxes. If you're not sure what a checkbox does, just uncheck it or check it. The hetero does exactly what it seems like it's going to do. It turns off or on the hetero. The first and last column visually indicate that there is significance in either the first or the last column by changing the visual styling..
Now, depending on the style you use from the table styles menu, it will either bold the text or alternatively, it will change the filter style or alternatively, it will change the film style of each column. So keep that in mind, abandoned rows and columns are those alternating rows and columns that we see here. Many people have been doing that manually for as long as they've been using Excel. However, by formatting is a table..
You don't need to simply check or uncheck the box to turn on banded, rows or columns. This is simply for readability in this case here. However we're reading the data left to, right? So banded rows is a more appropriate. The last tool that we have in the checkbox is the total row notice currently in row 98. There's nothing. However, when I check the total row, suddenly there's a whole new row introduced and it's automatically summed up the gross pay the last column, but we're not limited to just this last column..
In fact, we can do anything. Let's say we'd like to find the average number of hours worked. I'm going to place my cursor in the total row, underneath the hours column. And you'll notice that I get a little dropdown arrow, simply click on the drop-down arrow. And in this case, I'd like an average. So I'm going to click average boom. The average employee is full-time working nearly 36 hours. Let's say I'd like the average gross pay. There you go. Maybe I want that max gross pay. Whoever's getting paid the most in this case, the highest paid person is getting.
Paid $1,008 versus the smallest 85. So it's interesting to see how without typing, because we formatted as a table using the total row, we were able to do basic arithmetic. Pretty cool. Go ahead and take this opportunity to try out some of those table style options and then come. Welcome back. Let's go ahead and turn off that total row. Now, simply by unchecking additional tools available inside the table tools design tab, we have the ability to export our data into Vizio or to SharePoint..
We also have the ability to summarize with pivot tables, which we'll talk about later. Insert slicers. Also coming later, we also have access to the remove duplicates tool. Now it is important to note that the remove duplicates tool is also available to well-formed lists. This is non-exclusive to tables, but this is a great opportunity to talk about it. When it comes to working with data, there are plenty of instances where we might accidentally have duplicates, whether it's because we re imported the same database multiple times, accidentally copied and pasted one time,.