What's a well-defined list and how can you insert a table from that well-defined list a table is a tool that was invented in 2007 it's nothing new but it allows us to filter data sets sort data sets calculations remove duplicates a ton of great tools when analyzing data if you are using a table and we're going to spend that good chunk of time in module one learning about that in module two the summary based if functions.
I want to learn about the if function the sumif and average if and i also include the new functions sort filter and unique that got released with the office 2021. now module three we're going to dive into a little bit of charts learning about the recommended charts tool what charts are available how to edit them and if you need to insert something called a sparkline which is a single chart that fits in a tiny little cell of a spreadsheet that.
Way you can have many of them now module 4 module 2 and module 4 are some of my favorites of the class module 4 we're going to learn about pivot tables how can i get a large amount of data and how can i summarize that large amount of data and build a report out of it how can i build a visual for that report known as a pivot chart and how can i format the report by changing the data types the number formats adding in slicers.
And changing the summarize values a lot of good stuff i do want to mention that i try to make these sessions hands on after i show a topic i would probably ask you to pause the video and to quickly practice because there is an exercise file that you can download it's probably in the description there now if you are enjoying these videos before we get started be sure to subscribe and to hit the like button.
Now as always don't forget pause the video and download the exercise file before getting started now that being said let's slowly begin so i'm inside of the excel workbook that provided for the course and i'm looking at a particular worksheet the very first one it's called creating tables and right now what i'm actually looking at here is is there's a special name for it.
Right now what we're looking at is called a list and a list simply holds values in columns and there's usually a header for the column describing the values it's holding now there is a way to get a list and enhance it and make it a well defined list now you might be thinking what's the point of making it a well-defined list.
Because if you have a well-defined list you can insert a table so let's talk a little bit about what makes a list a well-defined list there's actually three things that makes this list well-defined the first thing is that the list has headers now the headers have to be unique you can't have duplicate headers and i have a header for each one of my columns besides your headers name needing to be unique you also have headers.
The headers and the data need to also be unique so i can't have a header called joe or a header called at because it needs to be unique compared to the data now the third thing that i need is a complete data set which i actually have and a complete data set just doesn't have any empty or columns so it won't have any empty columns here.
Or it won't have any empty rows it's okay if it has a blank cell here or there that's that's okay but a complete data set just doesn't have any blank columns or rows now if we actually have all of these things all three of these pieces we have a well-defined list and we actually have one in front of us here so that means we can get the well-defined list and convert it to a table.
Posts Related:
If i put my cell address box inside of this list here this green box they call it the cell
Address box i can actually go over to my insert tab and i can choose to insert a table now i do want to mention something i like using keyboard shortcuts so as long as you're inside of the table or inside of the list we should call it right now.You have the opportunity to use a shortcut to insert the table you can simply hit ctrl t or if you're on a mac command t now when you do this it's automatically going to add these marching ants i've made up that word entirely around the entire list this indicates that it's grabbed the entire data set and it's also acknowledging the fact that you have your own custom headers and if you didn't excel would make them.
For you but they wouldn't be too descriptive it would just be called column one column two column three column four and so on and so forth let me hit ok here so i've successfully built a table it's in here and there's something i want you to know when you're inside of a table like when your cell address box is inside the table excel is going to give you access to this new tab it's called the table design tab.
And it's not always going to be there if you move your cell address box like outside the table it's going to remove this table design tab for you thinking that you no longer need to use the command groups within it because all of these are primarily focused on the table here so if i go back notice how it's not there because my cell address box isn't in the right area.
But if i click back in i'm back in business now what i like to do when i create tables is i like to give the data a name we can name our data sets inside of the table design tab we actually have a properties command group the properties command groups allows us to give it a single word name for every data set that we create every table we create now you might be wondering what's the point of that.
Well you can use this data set for a pivot table or you can use this name to navigate across the workbook if you're on a separate sheet excel actually has something known as the name box which has a drop down arrow and it'll show you the names of every named data set you have if you'd like to select one you can click on the drop down hit table it'll instantly highlight the data set for you and take you to the sheet that.
Holds that content or that data so it's also a navigation trick that's just one benefit of using a table now besides that it also lets you this is another benefit i'm going to go over to this next command group called tools it allows you to filter in a much more visual way in fact there's something called slicers that allow us to filter data sets and if you want to filter your building.
Numbers you can go into the table design tab and insert a slicer for the locations inside that column now this slicer is just like the table to where it's going to give you a special tab if you have it selected i typically use this tab to adjust the look of my slicer i like to adjust the look by adding in multiple columns spreading out the buttons a bit let me add in a two.
Columns maybe three and just spread out the buttons a bit a little bit longer there and i'm also just going to make this really thin so i can fit it at the top and here let me just add in a few extra rows i'm going to insert a few extra rows here just so i can add the slicer to the top now this was invented in 2013 nothing too new but if i select one of these options here it'll actually filter my data set.
Pretty cool right and if i clear the filter here it'll go back to original i can also turn on a
Multi-select i want to do a building three and two comparison so that gives us a nice new filter tool that i have available known as a slicer when using tables there's more to it though there's more to it let me insert a row into the table i'm just going to right click and insert a row and i'm just going to copy.This entire row so that way i can have a duplicate the table also allows me to remove duplicates from a from a table a data set the table design tab has another tool called remove duplicates and if i click on that it's actually going to let me search the entire data set i can also search a single column and if that column has a duplicate it will remove all related data so i'm going to search my employee id.
Columns if i hit ok it's going to let me know that it found one duplicate value and removed it and there's 37 values that remain so besides these three tools there's also a tool that does calculations for you inside of the table design tab moving on from tools i'm going to go.
Over to stable style options i also have this tool called the total row and if i turn it on it's going to add a new row to the bottom of the table that i can select a cell from and i can aggregate the column above maybe finding a count of how many employees i have and it looks like i do have 37. i'm going to find the average pay rate as well now if i want i have the opportunity to.
Filter this and i can see my total row will also filter the calculations that i have quick way to do calculations for a table a good old total row there now there is another fifth benefit i mean there's a ton i'm just giving the most important ones here inside the table design tab i can also choose a different style for my table whether that be a light color scheme a.
Medium color scheme or a dark color scheme i even have the opportunity to build my own custom table styles by choosing the color of the header row the first row the second row and all the details the font colors one by one you know save it for me to use in all of my workbooks i'm going to switch this here i'm going to switch it over to a medium green just to mimic the excel look.
And now we've just learned about table styles with the click of a button i just drastically changed the look of my table so there's a lot there's a lot here with tables you can give your data sets names remember we did that from the properties command group you can also insert filter tools you can remove the duplicates you can turn on the total row and you can pick a color set for the table a style.
Before you do move on and move over to the conditional formatting sheet where you can highlight certain values based off conditions or criterias and even add icon sets i want you to practice getting this well-defined list and converting it to a table and practicing these five things that we just discussed don't forget to come back there's a lot more in store for you all right i'm inside of the conditional.
Formatting worksheet it's the second worksheet of the workbook that i provide for the course if you don't have it be sure to download it but i'm actually looking at a list it's a well-defined list again and it's simply actually tracking looks like some houses what town it comes from the listing price the square footage the bedrooms and the bathrooms now to narrow down my house hunt excel actually offers me a set of tools.
To highlight certain cells based off conditions and criterias i can also add icon sets this tool is one of those tools that help us style up spreadsheets it's known as conditional formatting let's go down the list and add some conditional formats this data set starting out the listing prices i actually want my listing prices to be.
Between 100k and 150k that's what i'm looking for so if i actually select the column i can select the column by hitting ctrl spacebar at the same time control spacebar selects the whole column you can also click on the letter i can actually go over to my conditional formatting tool and under highlight cell rules i have an option to highlight cell.
Values between a range i can do greater than less than equal to but i'm going to do between for this one and i'm just going to put 100 000 and i said 150 there so i wrote it down so don't forget and i'm also going to go ahead and change that to a green fill with a dark green text there and i'll hit ok you can also do it for text.
I'm going to hit ctrl spacebar in this column here and maybe i always want to i want to narrow down my search and i want to highlight all the towns that are jamesville all the houses that are located in the town so if i go over to conditional formatting and under highlight cell rules i have an option to choose equal to or text that contains i'm going to do an equal to there and i'm going to type in.
Jamesville as you can see it instantly picks up all the jamesville items there and if i go ahead and change that to a green fill and i'll hit ok i can also add color scales so if it's green the square footage is going to be above thousand it's yellow it's going to be between a thousand.
And one nine nine and if it's red i want it to be a thousand or lower so i can actually do the same thing but i can select here i'm going to click on the letter here i'll go to conditional format and i'll choose a color scale there's green yellow red color scales but it doesn't really let us pick the values we want to use unless we change the settings for it.
So if i select this again i can go to conditional formatting and i can manage the rule that i just created if i choose to edit the rule a window will appear that allows me to choose my lowest value the lowest value no let's change that to a number if it's a thousand or lower it's going to turn to red my midpoint i want it to be a number is a thousand and one it's going to be.
Yellow if it hits 2000 or up it's going to be green so the minimum number 1000 the midpoint 1001 that's going to start turning yellow the maximum two thousand i can also change this to just two colors if i needed to let me hit okay here it's gonna drastically change the rubric that i have it's like a lot of green options.
A lot of green options if you went to yellow there i don't think anything went to red nothing went to red there let's add in some red i'm going to go to conditional formatting manage the rules edit the rules i'm just going to change it to where maybe if it's 1700 it's going to be red it's because i saw a few 1600s in there oh one second and then this one's going to be 1800 will be yellow.
Cool we got a few this one let's see a bigger breakdown i have two more you can also add icon sets maybe they want a conditional format that chooses an icon for each bedroom like wouldn't it be nice to have these maybe these boxes here however though these boxes won't perfectly align to the numbers.
I have to edit the rule by going to manage rule with the column selected and if i choose to edit the rule i can actually choose to change the type this is going to choose what i use to choose the box i'm going to choose a number so i can pick a number that determines what box is assigned and if it's a greater than or equal to four it's gonna do four boxes if it's greater than or equal to three it's gonna do the three boxes if not it's gonna do two.
If it's one bedroom and if it's less than zero it's going to do zero now when i hit ok it's going to change the rubric to match those settings that i applied i can also do it for bathrooms i'm going to do conditional formatting and look i choose an icon set with just four because i've changed the settings for my first one it picked up the settings for my other one as well i do admit usually when i use conditional formatting i highlight the top row.
And from there i go over to my data tab and the data tab has something called a sort and filter command group to where i can turn on the filter item and i can actually filter based off these conditional formats i can sort by it or filter i filtered the listing prices i'm going to filter the town the square footage is very filter maybe i only want the four bedrooms there.
And maybe i want only three bathrooms so i have quite a few filters applied here and if i ever need to clear these filters again i can always go and clear the filters directly from there all this does in reality is show and hide certain rows much rather use a slicer and a table to do that but it works just as good before moving on everybody.
I do want you to spend some time and i want you to practice filling out at least three you don't have to do every single column but at least three conditional formats and hopefully i hope you choose the ones that need to be edited by actually going to the home tab and then afterwards managing the rule don't forget to come back when you're done we're going to start talking about some summary based if functions in the next video another interesting function that we.
Have available in our function library will solve quite a few problems for us like for example here i'm looking at the pair company sales from february of 2021 and it's actually my job to figure out who met the monthly goal now i can sit here and one by one do it manually that's a yes that's a no that's a yes oh my bad i meant no.
And mistakes like that can happen i can use a function to logically determine whether or not the value should be yes or the value should be no i can also then give a bonus based off that value if it's yes they get a bonus if it's no they don't in order to do this i have to use a function inside of excel's function library known as the if function in fact it's very simple to fill out.
It's just ask for a logical test an optional argument called value if true and another optional argument called value if false a logical test is simply a comparison of something something that can be determined as true or false .
And if it so happens to be true the value if true argument will be output and if it ends up being false you'll see whatever you've put inside the value of false argument to be the output of the cell so let me fill this out here if i put an equal sign and i type in the letter i i can use my function library i'll just hit tab to fill out the rest of it now my logical test here is a comparison now.
For this cell if it's greater than or equal to the monthly goal that i have when i hit comma it allows me to add in the value if true which for the value of true i'm just going to put the word yes in in its quotes excel has to have words in quotes they're known as strings and if not i want to put in its other set of quotes my value false is going to be no now if i close this parentheses and hit enter.
