We're also going to look at some of Xcel's conditional functions and even pivot tables, which are one of my favorite outlook tools. The neat thing about most of these tools is they're all really pretty easy to utilize. So please join us on this journey now, as always, if you're enjoying these videos, Please like, and subscribe. And if you're looking to earn certificates and watch videos without ads, sign up for learn at any time, our dedicated online train, subscription service, check the link in the description for more information. Now, if you have any questions you want answered by one of our awesome instructors, please join our offsite community..
The link is in the description as well. And as always, if this course has exercise files, you'll find them in the video description. Hi, everyone in this session, what we want to do is look at how we can get Excel data, ready to become a table. Now, right here, I have a very simple data set. And if you'd like to try this out, if you go into the practice folder and open up customer data, dash one a you can follow along, but you'll notice that this data is very simple..
It's really not ready for anything. If I look at it, for example, there are no column headers. Also, it doesn't really help me know what the data is. So we want to look at how we can prepare this data before we actually make it into a table. One of the first things I need to do is add column headers at the top. It's also really important that if I do have titles at the top of my data, I separate those titles where the data set, uh, by a couple of rows, but I just want to put in some column headers. So I'm going to come up here to the top, right? And then insert you new blank row..
This row will become my column header. Now why are column headers so important? The reason they're so important is that they help to represent all the data underneath the column header. So if I don't know what column is, I need to figure it out so that I can appropriately name my columns. Now you'll see that this data is actually about customers and different things that they have purchased. So I'm coming in and I'm naming again, the different columns based on the data type underneath. Now, this brings up another important topic. You'll notice that as I name these, all the data underneath.
Is the same type of data. For example, here I have a phone column, as you can see here, it's not going to be, for example, the date that someone purchased something, this is the phone column, and this right here is going to be, if there was a commission page, Now, these are just normally formatted columns at this point. The other thing I'm going to do is take that column and I'm going to put it in bold now, very simple step, but again, it will become very important when we start getting our data ready to turn into a table. So again, get your column headers in there, and that will help you be.
Even creating a pivot table in Excel, because what often happens when you have a blank row in your data is that Excel cannot analyze past that point. It's kind of like a breaking point in your data. And the other thing that can be also bad is to have redundant data. This could be where one or two, again, cells in your data have the same information per column, or it could be happening multiple times. So we're going to check this data set for both of those things. Now keep in mind that if you want to try this. Uh, go into our practice files and open up customer data and you can try out this..
The first thing we're going to do is remove the blank rows. Now there are many ways to do this, but one of them, if you're just dealing with entire blank rows, like you see here in my data, I have lots of those. You can actually search for blank, rows or blank cells in your data. So I have clicked in my data and then I'm going to open up the go-to box. This is simply control G. Which stands for go-to. Now, normally we use this to go to specific cells, but in this case, I need it to help me locate a special item, which is going to.
Include any cells that are blank. Now, in my case, I just have entire blank rows. If you have blank cells, this will also select those. So be careful with that when I click on, okay, you'll let us, what it's done is in my data. It's highlighted where they're blank rows, and we can see that as I scroll through the data now, really simple way to get rid of those rows is to go inside one of the selected rows, right? And then go down to delete it's right beneath insert, and it's going to delete all those blank rows out..
Now, it's also really important to decide how they should be removed. I'm going to ask it to shift the existing cells up and you'll let us, this is going to downsize my data, which is exactly what I wanted it to do because those blank rows will impact the analysis. Once I apply my table style. Now, the second thing we mentioned is to remove any duplicates inside the data set. Now I could go through and check for that kind of stuff, but it would take forever. And this is not a very long data set. It's only about 61 rows down. So I'm going to let Excel do that for me again..
It's a great idea to already have column headings when you do this, because it helps Excel to recognize what type of data is located in each column. Also, we're going to click inside one of the columns of data. I'm not going to want to click over here in column K for example, because there is no data. So after I click inside my dataset, I'm going to go up to the data ribbon tab. There's a lot of great tools to help you with analysis on the data ribbon tab of Excel. And if you come over to the right hand side, you'll notice there's a data tools group. And this group of buttons is all about helping get your data ready for analysis..
One of the tools that I have here is a great tool called remove duplicates.
And I'm going to go ahead and click on. Now, right here, I'm seeing my different column headings. And this helps again, Excel to recognize the data underneath. It's going to look through all of them for redundant data. You'll notice it's telling me that it's found four different duplicate values. That means I've got up to four different rows of data that are being duplicated. I'm going to remove those and notice that if I now look at the data, I'm only got 57 rows of data, or I should say 56 because we have our.Column headers and it's cleared out that redundant data because when I start to analyze things, I don't want duplicates inside my dataset as always. We'd love to have you continue to follow us on this journey. So if you want to try it out, remember to go to the practice files and open up customer data dash two, two B, and you can try it out. Hi, everyone. I'm almost ready to make this Excel data into a table. But before I do that, there's one more thing that I want to check. You'll see right now in my data that I have a column, a that's the last name and a column B..
That's the first name. And I'd really like first and last name to be combined before I apply a table style to my daily. Now, this can be a little bit tricky. There are formulas that you can use. There's also actually a tool inside of the data group, but I want to show you a newer tool called flash fill that makes this into a breeze. It's actually pretty fun. The first thing I'm going to do is I'm going to come into my data and remember, if you want to follow along and try this on your own, go into.
The practice files folder and open up the customer data and you can do it, but I'm going to insert a new blank row for this new combined column. I'm going to come right here and right click on column C and just insert my new blank column. I'll widen it out just a little bit, and I'm going to just call it Nate. Again, it's a combination of last name and first name. Now, the thing that's critical when you use this tool it's called flash fill is that you type the entries exactly as they're spelled to the left..
So if they're misspelled, you need to misspell them in this case, I'm using people's names. So it's important that as I start typing in the first name, I type it in exactly as it appears to the left. Now, as soon as I come in and start typing in the next name, Microsoft Excel recognizes that I'm creating a pattern and it says, Hey, can I help you with that? Now if I hit enter, boom, it already feels all the information in for me, but let's pretend it doesn't. Now this could be for two reasons. Number one, I may have misspelled Mirial, or FlashFill just doesn't click. If it doesn't, you can actually force FlashFill to do that. I'm going to come up to my data ribbon tab and on the data ribbon tab in the data.
Tools group, I'm going to look for the button, has a lightning bolt next to it. This is the flash fill button. I'm going to click on that and you'll notice then it will force the FlashFill. There's also a keyboard shortcut for FlashFill, which is control E as in elephant. There's no connection between that. FlashFill that's just the keyboard shortcut. They've applied to it. Notice if you hover on the button, it will give you that shortcut. But again, notice now I have combined those two fields to the left. FlashFill we'll both add those together..
It will separate them apart. It's a really fun tool to play with. Now for me, I can go ahead and delete the two columns to the left because I won't need them anymore. And now my data set is ready for me to apply my tables. Hey everyone. Welcome back. My data and Excel is finally ready for me to apply what is called an Excel table or a table style. Now, what exactly is a table style? A table style does provide formatting, but more importantly, it allows you to analyze.
The data inside of that table with things. Sorting filtering even quick calculations. So the formatting is an added bonus to me, but it's the analysis of tables that really make them a powerful tool. It's almost like you're making your Excel data into a little mini database. Now we've spent some time preparing this data and remember, if you want to try this out on your own, go into the practice files and open up customer data dash four eight, and you can follow along, but I'm now ready to apply my table style. Now, the first thing I need to do is click anywhere within my. 'cause if I'm clicked over here in column M that's a blank.
Cell, there's nothing in it. And I can't really apply a table style to that. I want to do it to the current data. So I'm going to click inside of my data set, and then I'm going to come up to my home ribbon tab. These are a pretty popular tool, and that's why they are located right smack dab in the middle of the home ribbon. You'll see that if you come over directly to the right of conditional formatting, we have the format as table button. Now, when you click on this button, you get a huge pop down of all these little colored squares..
These actually represent examples of the formatting that will be applied to your table.
I'm going to go ahead and just pick one of them. You can always pick a different table style if you don't like the look later on, but you click on the particular format you want to add. Then you're going to see this create table box. Pop-up. Now, one thing to notice is that it actually shows you the range of your table. This is why having blank rows can be a no-no because Excel can.Actually select the correct range. If you do have blank rows, you're more than welcome to just highlight the correct range by left dragging over that range with your mouse. But again, depending on the size of your data, that could be quite big. So you could also type it in the dollar signs does represent that it is an absolute reference. Now, my table has headers. What's up with that. That means that you do have column headers at the top of your table. And this is also important because it helps make the table style really easy to be created..
So I'm going to click on, okay. And boom. When I click inside of one of my cells, I now have my table style. Now let's take a, just a moment and point out a couple of the great features that you get. As soon as you apply that to people's styles, one of them is you get this alternating row color. This is great. When you're trying to read down again, a list of data, and this is where the formatting of the table style comes into play. Additionally, at the top, you've got these little silver arrows. When you click on these, these are built in auto sorting and filtering for your Microsoft Excel table..
The other great thing about table styles is they're really easy to turn off, but we'll talk about that in just a few minutes. So remember, try this out on your own by going to the practice. Hey, everyone. I'm ready to take a table style that I've applied to my data in Microsoft Excel and actually start using some of the built-in tools that are part of the table style experience. So as always, if you want to follow along on your own and try this. Go to the practice files folder and open up customer data dash five eight..
Now inside my data inside my table style, I've got these great Otto and sorting filtering arrows at the top of each of my columns. I want to start by using these and show you a few of the great tricks that are inside of them. Now, first of all, there's, built-in sorting. So when I come to any of the columns, I'm going to go to my bookings column in this case and click right on the arrow. You're going to see that I have at the very top an ascending sort or a descending sort, I'm going to go ahead and do a descending score. And you'll notice that this again, takes all of the information, sorts it by robe based on..
That particular column notice the little arrow, how it points down to indicate that there's a down sort going on. If I come to another column, for example, the destination column and do a sort on this one, you'll notice that now it's going to sort by that column and the previous sort gets cleared out. So what do you do if you need to sort by more than one column at a time, this is sometimes called a multi-phased sort for this. I'm actually going to click inside my table style, but I'm going to go up to the data ribbon tab and actually use the sort and filter group. This is great..
When you need to do two sorts, two levels of sorts. I should say, I'm going to come to the source. And again, it's great because it recognizes that I have a table style so I can come in and you'll notice it continues to show the first sort that I had turned on. But now I'm going to add a second level where, when there's a tie in destination, I'd also like it to sort based on the bookings. And I can decide if I want that sort to be from smallest to largest or from largest to smallest. So let's see if that works. I'm going to click on. Okay. So what I'm going to see here is when there's a tide in the destination..
When I come over to bookings, it's going to sort those rows again in ascending order. And I can see that as they come down. For example, I see Duluth here. And when I look at the bookings, I can see that they are sorted all. In ascending order. And again, notice the two different arrows that you have next to each one, representing the type of sort that's been done. Now let's go ahead and take a peak at filtering. Filtering is a little bit different, right? Because we are just focusing on specific types of data inside of our style for this one, I want to come into destinations again and click on the arrow..
You let us here at the bottom that I have a list of all my destinations. If I on check them and then I come in and just tick off the ones I want to see. You'll notice that it's going to, again, just show me the data points that meet the criteria I set, which in this case is all my destinations that start with the letter B notice next to the auto sorting and filtering, arrow, a little funnel representing that particular type of filter that I've applied to turn the filter off. I think like here on the little filtering, arrow, and come in and say clear filter from there's also seen clearing options up in the data ribbon in the sort and filter group..
Now, what if I would like to look for any locations or destinations in this point that start with the letter? Rather than coming in and actually clicking on all the different arrows, I'm going to come up to the search box and actually type in M with what we call a wild card, which is an asterix. This goes through the list of my destinations and finds any of them that start with the letter M notice here, how it's already selected them for me and is showing them to me when I click on, okay. Now it shows me just the destinations that fit that wildcard criteria..
So there's a lot of tricks like this that can really help you to optimize the sorting and filtering inside of your table style. Remember to turn the filtering off, I'm going to go back to the little auto sorting and filtering arrow, and just click on clear filter from now. I'm back to my full list. Welcome back everybody. I want to be able to show you a great tool inside of a Microsoft Excel table that allows you to take filtering and actually apply some of your own custom criteria to it..
It's called a custom auto filter. So what we're going to do for this is I'm going to start by coming in and just sorting my destinations in ascending order. But we're going to go ahead and just update it really fast, but I want you to see, as in my data, I actually have the city of Fargo, but it's been designated by different like east, west, north, south. And when I do my filter, I want to make sure that all the Fargo's come back now for this, I'm going to come up to the destinations auto sorting and filtering arrow and come down..
Directly above where I can search for a specific field to filter for. I have this thing called text builders. These are super cool. What Excel does is it recognizes that the data type in this column of my table is taxed. And then based on that, if you look over here on the right, it gives me different kinds of criteria that I can filter for based on the types of data. So, because it's texts, you'll notice that I have everything from equals two does not equal. It begins with that's very text centric, isn't it or even contains..
So in this case, I'm the say any field in this column that contains the word Fargo. It's not case sensitive in this case, it's just based on again, the text I'm going to click on. Okay. And based on that, it returns all of my locations that have the word Fargo inside of them. This is a filter. So notice the light blue highlight over here on the left to represent my rows that are filtered. And it turned that filter off. I'm actually going to come up to the data Reuben tab this time and.
Go to the sort and filter group. And there's a clear option. Then let us the funnel with the red X on it that will clear any filters in my table. Note, if I do a filter on a column that is not text-based, maybe that's a number for that. I'm going to come to my amount of sales column. I'm going to click on the auto sorting and filtering arrow and come in and in the same location. But this time it's not text, it's looking for number filters..
So you'll see here I come down that Excel updates, the type of filter it will do for me based on the type of data in the column. So since this has a column of numbers, That's the type of those hers I get. So I'm going to come in this time and I'm going to pick that. I would like to filter for anything that is greater than or equal to 1500. I'm not going to include dollar signs or decimals because all of my values don't have decimals in them. I'm going to click on. Okay. And we'll see here that I'm only seeing, again, those value points.
That are 1500 or above in this case that it turned that off. I can now go back to again, the auto sorting and filtering arrow and say clear filter from amount of sales and it turns off that's another great tool. That's part of a table style and Excel. Now, as always, if you want to try this out, go to the practice files and open up customer data dash six eight, and you can try applying some custom auto filters on here. Hey everybody. Welcome back to our intro to Excel data analytics. We want to take a table style and update its formatting, and also.
See if you have the options that we can change within the table style. So here I have, again, my customer data file open. Remember in the practice files folder. If you go in and open up customer data dash seven eight, you can try this out. When you apply a table style, you're going to get a new contextual ribbon up in the ribbons. It's going to be on the right hand side of any existing ribbons in Excel, and it's called table design. You'll only see this ribbon when you're clicked inside your table. So if you click in a normal cell outside the table, it will tell you..
Now with this ribbon, I can use some of the options to help adjust the format of my table style. For example, you're going to see on the right a table style options group on this ribbon and it's check boxes. So for example, I can come in and I can uncheck hetero. Notice that my row one that had my column headers in it turns off. If I check it again, they come back. It's just a way to minimize how much distraction there is in the table. You also have this alternating row color, which I'm a big fan of because it helps your eyes horizontally read data down..
But notice if I uncheck banded rows, it turns off. I also have an option to do banded columns, which is again, for someone that's reading from right to left or left to, right. Then you also have the first column. It puts everything in the first column of your table, under the hetero in bold. You also have a last column that does the same thing. And then in addition to. Filter button. You'll notice here that allows us to turn off the auto sorting and filtering arrows at the top of each of our columns. Now this total row, we're going to talk about in a minute, but I want you.
To also see that with table styles. When you come over here at the same group of options that we use, when we first applied, the table style are here. So if you don't like the format of the first table that you applied, you can come in and select a different style and it will turn it on in your table and notice that this is something where I can live preview by just hovering over the different table formats. And it will apply that particular format to my table. I don't even need to click, I'm going to want to click. Once I find a style that I liked that I want to apply because when I click,.
Then it applies that format to my table. So again, lots of great options to update the look of the table style so that it's easier for you to work with the data insights. Hey, everybody. Welcome back. I'm less able to have some formulas in my table that I've created in Microsoft Excel. And the problem you have with adding formulas is that every time you add new data to your table, especially if it's a spreadsheet that's going to continue to be used and new data will be put into it..
Those formulas have to get adjusted. So instead, Microsoft has created a tool called a total row, which is a way to automatically turn formulas on at the bottom of your table and turn them off when you don't. Now, first of all, notice that if I come to the bottom of my table, it's not very long. There's only about 57 rows and there's nothing at the bottom, but I'd like to have some formulas, for example, that could, for example, tell me how many bookings I have do a count of bookings. Also, I have an amount of sales column that I would like to see a total four. And then right here, I also have, for example, even commissions,.
I'd like a count on that. So I could manually put those formulas in, but instead if there's something faster, like the total row, I'd like to use that the first step is for me to click in my table so that the tools that are available to me to use with the table will be available to me on that ribbon. Then I'm going to come up to the table style options box and get a checkoff total row. It's just a small check box. Now, the bottom row under my table data notice row 58 here has a new piece of information in it that says total. And then the very last cell of that one..
Already has what looks like a formula of some sort in it. So if I click on that, I'm going to see a small arrow appear next to the total that I'm seeing there. I'm not quite sure what it is, but if I click on it, it shows me that Excel is already thinking that I'd like to do a count of the commission paid column. And that is exactly what I intended. So it kind of read my mind though. It didn't now right here in my amount of sales column, I'd like to see an average. So if I click right there, I can come in and I will see again, a certain list of formulas, including a average count min-max and there are a few more, it's.
Not all the formulas, but a good number. And I'm going to pick average. So this would be the average of my amount of sales. And then from my bookings column, I'm going to come in and just do good old some. So I can see a sum of all my bookings. Now over time, it's new rows of data. Get put into my table. It's very easy for me to uncheck the total row, turn it off. And then the next time I need it, I just recheck it and it turns back on. So it's a great tool to use when you need, and then turn it off when you don't need it. Now, as always, we'd love to have you try this out. So go to the practice files, open up customer data dash eight, eight,.
And you can try using the total row. Hi, everybody. Welcome back. I wanted to take a Microsoft Excel table and actually add new data to it. This is common because your tables often are what we call live spreadsheets, meaning they're continually being updated with new data. So how do I do that? Well, first of all, if I come to the very bottom of my table, one of the options is just to add new rows. But the important thing is to make sure that the new rows are part of the existing table style format..
And you'll see if I come right here to the very last cell of my table, that if I hit the tab button, it will automatically add a new row. In addition to that, I can also come right here to the bottom right corner, rest my mouse pointer on the very bottom right corner. And you'll see that I get a little double-headed arrow, one that points up and an arrow that points down. If I drag this, as I hold down my left mouse button, it will add more new rows to the bottom of my table for me to add new data to, in addition to that, I can also right click on row inside of my table. And notice if I say insert, it will insert a new blank row with the same.
Formatting as the surrounding rows and columns of my table for a new column, I can right click on one of the columns of the table and say insert. And again, I get the same thing. So the important thing is that I'm inserting these new columns and rows and they have the same format as the existing table does. Now, if I delete them out again, I want to make sure that as I. I'm not deleting anything that has data in it. Otherwise I will lose data within my table. Same as normal cells. Now the final thing we want to focus on is how do I turn a table style off without losing my data?.