Comfortable learning about organizing and prepping data, I would like to dive into inserting pivot tables, how to use tables to do that. What to do. If your data sets are not connected, how to relate them together, maybe you have a product table and then you have a sales table and you wanna find out the price of each product. Well, you have to do some functions and you have to do some prepping, some data mining there, uh, to extract the data from one table and merge it to another table. And the X lookup will actually help us do that. I do wanna talk about some tools..
Like building the pivot table, naming it, how to update values within it, how to filter, how to change the number formats to currency, remove decimal places, how to set those as defaults in this new version, we can actually set the pivot table layout that we've chosen as a default. I do wanna spend some time today. We do have, we have quite a bit of time and a lot of good topics. I wanna spend some time focusing on pivot charts more and more as time evolves. I've noticed the information that we have to grasp is becoming larger and we need these visuals to understand it quicker. So using pivot tables and pivot charts together is a powerful combination to speed up your browsing and analysis of reports..
There is more, it's not just this. Once we get comfortable building pivot tables, I wanna talk about the different layouts and styles that you have. The custom settings to designing the pivot table. And then at the very end, I want to talk about these very fancy features that, uh, I consider advanced features that you might wanna know about when building pivot tables, something known as a calculated field. Sometimes there's certain columns that are in your data set, or that I should say are not in your data set that you need inside of your reports..
Well, you can build columns using calculations. You can also build and can join items into groups within your fields. None of this making sense yet you don't know what a pivot table is. It will, by the time we get to this portion of the course now, towards the end, I would like to do a demonstration of how to add power pivot to Excel. It's a special added, uh, it comes included. We have to turn it on. And I would like to spend some time talking about the data model, where we can load several tables. Into a data model and use relationships and build custom columns..
Watch videos without any ads, uh, I, I recommend to sign up for learn it. Anytime that's actually learn. It's dedicated online training subscription service. I'll put the link for that inside the description. If you would like more information on it. Now, keep in mind, if even if you have any questions that you want answered by me or another instructor that you've seen a video with at our channel, don't forget to join offsite. Offsite is a learn community that we put together. It has thousands of members now, and you can actually join and ask any questions.
And even sign up for any free events on Wednesdays and Fridays, our office hours. We call them now my last little message for everybody as always, if you do look inside the description, you will notice that there is an exercise file for you to download this course. And most courses here, they're hands on. Meaning throughout the video, you're gonna hear me say, Hey, pause the video, try this out yourself. And in order to do these pretty advanced functionalities with these data sets that I'm showing you, you will be needing that exercise file that I'll be providing inside the description of the video..
I'm pretty excited. Hopefully you are too grab some snacks, grab a cup of coffee, depending on what time it is. And let's begin with Excel 20, 21 pivot tables. Oh, right. I went ahead and opened up the workbook. That's inside the description and I'm actually on the very front of the workbook and this new refreshed user interface. And I'm looking at a particular sheet known as creating tables, which is actually a list of my employees here that I have tracking certain things.
Like the location they're located in the higher dates, the pay rates. Now what we are actually looking at here, I'm gonna add a little text box. There's a little special name. It's okay. If you don't remember these names, but it's known as a well defined list. That's what we're looking at. This list is a well defined list. And if you ever do have a well defined list, it's great idea to convert them to tables for extra features that we can use in analysis..
What do I mean by a well defined list? Well, my list has headers. Now, these headers are unique compared to each other, besides that our headers and data are unique. And what I mean by that is I don't have a piece of data that matches my header. I don't have a header called ad. I don't have a header called Joe or a header called location or building one..
Posts Related:
The headers are unique compared to the data.
Now the third property that makes this a well defined list is the fact that this has a complete data set. I don't have any blank columns that wouldn't make it a complete data set. Cause I have a blank column and it actually ruins a lot of things. When you're playing around with data..Like if you were to try to highlight the data, even you hit control in the letter a it won't even highlight the extra data cuz it thinks that the data stops. I mean the same thing here. Let me delete this here and I'll do a row. I'll just hit delete on the road. It's the same thing. If I hit control a, it's gonna think that the dataset stopped here. So having a complete data set is something ideal. Not only for us to build this table that we're gonna talk about in just a second, but just for maneuvering around the data, inserting the data, highlighting the data, things of that nature..
Now it looks like I have all three of these properties. I'm in pretty good shape. I have all three of the properties to make it a well defined list. And now if I actually want, I can actually insert a table, which is a great starting base, great starting base for something that we're gonna learn about today tables, cuz we use the table tool to create pivot tables. So I'm gonna simply grab this entire data set and by grab, I just mean. Put my cell address box inside of it. And inside of my insert tab here,.
I can actually create a table. There's an entire command group devoted to this called the tables command group. And if I want, I can use the actual onscreen button, but I can also use the keyboard shortcut control T and let me hit control T here. As long as my cell ad response is inside the data, you'll pick up the surrounding data set. It's a complete data I'm gonna hit. Okay, here and now that I've done this, I've created my first table,.
Which is a great, great starting base for building pivot table reports. Because we can summarize this data in a report. We'll get there. Let's first talk about organizing data. Now there is something I wanna point out to everybody when you do have a table selected, or if you have your cell address box inside of the table, Excel in all versions will give you a tab in this new version. They call it the table design tab. And when you click on it, it's gonna show you a set of tools that you get.
To use to help analyze this data. So one of the most basic things of course that we can do is change the style of the table. I'm gonna make it green to match Excel. Since we're talking about it. And then another very important thing that I like to do is just to name my data inside the ties command group. We have an area called table name, and I'm just gonna give this a name. I'm gonna call it T employees..
There we go. So before we start talking about what tools are provided when organizing our data into this table here, I do want you to catch up a bit. I want you to insert a table. I also want you to use the table design tab and just pick your favorite color from the table styles. And then afterwards, just give your table a name. Now, if you're wondering why we name these things is cuz we end up using these as source data for our pivot tables. And having names for them are easy to swap data sources, or if you want,.
You can navigate to the data source by using the name box in Excel. We talked about that in the Excel beginner course, the name box there has the name of all your tables, or you can hit the dropdown and select it. Go ahead and pause the video, create the table practice by giving it a color and a name and play around your name box before coming back here. And we'll talk about two or three things that I like to do at this table before moving on and talking about how to extract additional data from data sets and then how to sort the data and how to learn, how to use these new functions.
That have been released to sort data. Very interesting stuff there. We'll be playing around with tables quite a bit throughout this session, but this is just the starting introduction to them. All right. Hopefully you had a little fun playing around and inserting the table and then giving it a name and changing the style. But now that we've played around and got a little familiar with the fact that this table designed to have appears, whenever you have a cell selected inside the table, I wanna talk about, about two or three tools that you might wanna know about when using it..
So what's the benefits of this table. Now, one tool that I wanna mention that we have available, that we
Also have for our reports for our pivot tables is something called a slicer inside the table design tab. There's a tool called slicers and it allows us to filter the data set much more visually than we originally would have. If we used a list, I'm gonna go ahead and insert a slicer to filter. Let's say my buildings in my location column..This will now gimme an on screen clickable filter option where I can filter my data set. And if I need to, I can clear the filter and if I want, I can actually select the slicer and a new little tab appears called slicer. Cuz now that I have the slicer selected, it'll show me some tools that I can use to customize it. And something that I do with my slicers is I adjust the columns if I need to..
And I can adjust the width from these handles and the height if I need to. And these slicers can be moved around and located in different areas of the spreadsheet. I'm just gonna insert a few extra columns at the top. Of the spreadsheet to add my slicer there. So we have a nice little filter tool that we can use. Second if I go back to my table and I get the tab again, besides that.
Slicer, I also have something called the total row in the total row, allows me to do high level calculations for each one of these columns. I can aggregate them on the bottom of the data set. I can find out the average pay rate. I can find out how many employees I have and if I want with the combo of having a slicer and that I can go ahead and see the different variations from building to building..
So the total row is another benefit. Now, the third thing that I get before I actually even build my report is the fact that this data set has a filter dropdown down. Each column will automatically give me a filter dropdown that actually will show me I'm gonna click on the dropdown, an area that I can sort each column will talk about sorting next and an area to where I can actually filter each column. Now, if I don't wanna use this, or I wanna hide this from users, I can always turn that off, but I can turn it back on..
Cuz the table provides me with the filter button. We'll call it. So these are some benefits that the table provides. We will be learning about this tool inside the table design tab later on in the session, known as summarize with pivot table, one of the most important tools there, or I can build a report out of this data, but let's still talk a little bit about organizing data. I wanna spend some time focusing on how we can extract additional data from columns that already exist. Using some very common functions that we have access to go ahead and pause.
The video, practice, these benefits before moving on, and I'll be going onto the data mining sheet next. Now, a quick topic I want to touch on is the fact that you can actually use Excel's function library to pull more data, or I should say mine more data since I am on the data mining sheet. Out of existing data. So I'm looking at another sheet here and it's called the books and beyond Inc and the payroll for a certain date, I actually used a function here. The now function equal.
Now returns the current date and time formated as a date and time, it has to have an open and closed per, uh, but nothing inside of it. Uh, but it'll give you the current time from your computer. That's a good one. But as you can see, I do have some columns that are blank here, but I just wanna get your brain juices flowing and how I can actually fill them out using my Excel function library. I actually have the hour, the hours worked for each employee here. I can determine whether or not an employee's full-time or part-time using the set of functions..
One of them is called. If I also have an employee's hire date, I can do a yearly analysis of how many employees I've hired and onboarded. If I extract the year of hire, and if I want, I can conjoin things in, well, we should call them can Kainate strings. And I can put the month and year together as a string, let's start out by filling out the full-time part-time column here by using my function library. Uh, I'm actually gonna access a function known as the, if function in Excel..
And once you see the name of the function you want, you can just click tab and the, if function allows us to basically check if there is a logical test, which is something that can evaluate to true or false, a comparison to two cells. And then if that ends up being true, we can say a statement or do something. If it ends up being false, we can say a statement or do something. And in this case, I'm gonna go ahead and check. If this hour worked is greater than or equal to 40..
And if they so happen to be, I'm gonna return the string full-time Ft. And the reason why I called it a string is cuz you have to put it in quotes. It's a character string. They call it. And if it ends up being false well, that implies that they're part-time PT. I'm gonna close my parenthesis for good practice. And instead of hitting enter, I'm gonna hit control. Enter that way. The cell address box will remain on the same cell and I can double click my auto fill, handle that tiny little square to complete the remainder of.
The columns or the row I should say. I can also do a function known as year, but this time they actually require something inside the parentheses here. Unlike our, now I actually have to give it a serial number, which is a cell formatted as a date, and it will simply extract the year portion of it. And I can go ahead and use my auto fill to extract all the years. If I do need to extract the month, I can always use the month function..
And this Amper percent is a really cool trick to con contaminate text strings together. So I'm gonna put month and a space. I can also add a little dash in between this space. Let's do that. And I'm gonna do the year. And I'm gonna select the same date again. So it's gonna get the month slash year and I can go ahead and use my auto fill handle to get all them there for myself. So now I have three additional columns that I can work with in my reporting.
Needs, practice these formulas. Let me go to my formulas tab and let me turn on show formulas, just so you can get a little sneak peek of those formulas there. And, uh, go ahead and give these a try. If you would like pause the video, I'll be here when you're back. We're gonna talk about a very interesting topic next sorting and these newer ways that we can sort data sets by using functions coming up next. So I'm over here in the workbook and I'm on the third worksheet called custom sorting..
Where looking at the employee master list, that's actually missing the column. So this is not a complete data set. Remember that conversation we were having on the creating table sheet about being a complete data set. This is not well, we can make it one using a pretty cool feature that I like to showcase here, known as flash fill, which can be found in the data tab. And it's pretty quick. It has a keyboard shortcut control E but essentially it reduces that situation.
Where you have to constantly build columns out of preexisting columns. Let me explain, I already have the first name and last name in a separate column. So if I give Excel just one single example in the full name column. And I run up to my data or data tab. However you wanna prefer to pronounce it. I actually have a command group called data tools. And one of the data tools that I have access to is flash fill, which I did mention the keyboard shortcut is control E and once you give it.
One pattern and then you go to a new empty cell, you can actually trigger flash fill to do the rest for you. I'm gonna use control E as my shortcut. I know, I know it's magic, but go ahead and try that out. Pause the video if you would like, but I'm gonna continue talking here about sorting now that we have a complete data set. Cause sometimes before you want to rebuild a report out of data, you want to have it organized and sorted a certain way. Now, I'm sure we all know that inside of the data tab, we have a command.
Group known as sort and filter, which allows us to do an alphabetical sort or a larger to smaller sort using the a to Z and the Z to a option. And as long as you're inside of a column, you'll go ahead and sort all corresponding data related to that column. So notice how all the rows with it also got swapped. However, there is something I wanna mention that we can do that allows.
Us to sort based off multiple priorities, where we can have one column first sorted, and then within that column have another column sorted. And then within that column have another column sorted. And within that column, have another column sorted, multiple levels, multiple priorities of sorting. That's actually using this big sort option kind of looks like a spinning casino slot machine there, the Z to a and a to Z option. And when you click on it, it'll actually give you a big old sort window that.
Allows you to add multiple levels. Let me sort this data set based off the day off so that all the people's day off is in order. I'm gonna switch the sort by full name to day off. And the cool thing about this is, is under order. Excel has something known as a custom list. When I click on this custom list here, it'll gimme an option to choose the fully spelled out days to sort it with Sunday on top, as opposed to Friday, if we would've chose alphabetical, I can go ahead and add in another level..
And then after it sorts that I can have it sort, the locations, the buildings. Alphabetically I can add in a third level. And if I wanted to have these sorted in a different way, don't get me wrong for my buildings. I can always build a custom list. Maybe I want building two to show up first and then building three and then building one. That's just how I want it sorted. Yeah..
Now when I do this, this is me building in a list. It does have to be spelled correctly. It has to match the spelling. If I sort it with one, two and three, it'll come out in that order. If I sort it with three, two, and one, it will come out in that order, even if I sort it with two and then three. And then one in the middle or one at the end. It's out of order. If I hit ad here, I now have a sort order that I can follow by building.
Two, three, and building one here for the final sort inside of the buildings. I also wanna sort the zones or let's do actually the last names and I'll do these three levels of sorting here. So it's first gonna get all my Sunday, people put 'em together and then it's gonna do this next sort here. I'll do a different color for it. Notice how it starts with building two and then three. And then one that's how I chose my sort order. And then the last name here as well..
So remember this takes priority. So even though there's a Bellwood here in building, whereas at building one, building two and three take priority. So this list is kind of skewed the last name. Always remember, you can edit this. You can always switch the order. Maybe you wanted the last name first. You can move it up a level and then the location. And now we have our Sundays here and our last names in perfect order as well within the Sundays. But then you'll notice our buildings might be kind of skewed..
Cause it's the last priority that we cared about. Pretty interesting stuff here. This has been around for quite some time, the sort function, the sort tool, the command, but they did release a sort function that we have available. Now inside of Excel, I would like to spend some time talking about it. Now, what we can do here is use a particular function and this is known as an array function to actually sort a particular column of data. So if I want, I can do the entire data set by running a sort and.
I can choose the entire data. And then I can choose something known as a sort index that's choosing what column I wanna sort by the first, second, third or fourth. And in my case, I'll do the fourth column to sort by the unit sold and descending order, which is negative one, so that I can see the most sold item at the top. When I close this parenthesis and hit enter, it will spill into the other cells and build me an array. They call it of the data. That's newly sorted, where I can go ahead and see my region sales rep..
This is pretty cool, right? Product and units. Pretty fancy method. I, you can also do one column in this example here that I had preloaded, I just did the single units column here. I highlighted only the units, but it also works with the entire array. They call it pretty fancy, pretty fancy. There's also a sort by function, a sort by that they've released. And if you want, you can sort by another column in the data set..
So I can highlight this entire name and age column, and I can sort it by the ages and I can do it in descending order to have the oldest people on top. And if I close my parenthesis here, it'll again, bleed and spill. They call it spill. Into these surrounding cells in the order we looked for. I mean, you could even do this with more than one criteria, kind of like we did with the custom sorting, what if I wanted to sort first by age and then by region..
So I can do that by using the sort by, and I can highlight the entire data set. And the first one, I wanna sort it by the age in a descending fashion. And then right after it's like, all right, what else do you wanna sort it by? I'm gonna do it based off the region in a, a ascending fashion with a one I hit enter here..