 And once again, thank you everyone for coming out to our Excel Tables Training by CND Rylander. CND, I'm passing you the presenter now. Thank you so much. Thank you everybody for coming to Tables Training. Tables is sort of a non-obvious topic. When I first heard about tables, I thought, well, you know, tables are, let me just quickly click on show my screen. In Excel is in a table. So what is tables all about? Well, tables is actually a feature that they introduced a couple versions back that allow you to have a lot of different benefits. One of them, I don't know if you've used auto filter in the past when you click on a column and you click on the filter and you get these little filter buttons so that if you only want to see, let's say, you only want to see ice cream, you can come to the top here, deselect all and say I only want to see ice cream. Well, normally you would have to do that kind of filtering yourself, but auto filter you'll see will, excuse me, tables, the tables feature, will automatically put auto filtering on. That's one of its benefits. Another benefit, I don't know if you've ever tried to format a table with a certain appearance, for instance, maybe every other row, you want to have be banded so that you have color in every other row, making it easier to read. You can do that, I'm going to click on Format as Table here for a second and show you what that looks like, this every other row banded. The thing is though, if you do that and then you want to add another row, if it's in non-table format, guess what, you get to reapply the formatting over and over again. Even moving a cell, if you move a cell, you'll see that you've disrupted the look of what you've got going. Also if you have a non-table, so let's say I add this column, which is a popular thing to do, you add a column, and so you do generally an equal sum, you're aware that equal sum will allow you to add a column, so I'm going to do that here and then I'm going to add that auto filter again and I'm going to say once again that I, oops, wrong place for my auto filter, I want to do ice cream again. First my total is 7,000 for January, so I'm going to just do ice cream again and it's now not even showing my total like it should, but if it did, it would still show 7,000 even though I've only got 2,000 showing, and that's because it will show hidden values, even though they're not being displayed, they're going to show hidden values, so you're going to see that when we create a table, if we hide values or if we filter values, you're going to see that this changes, it's no longer going to be 7,000 because that wouldn't make sense, if you only saw ice cream you would want it to say 2,000, so that's another benefit. So there's just a ton of different benefits to tables and rather than telling you all of them up front, we're going to go ahead and now start to create a table, although if you have your hand out in front of you, on page 1 it has a section right at the top that tells you all of the neat things that you can do with a table and all of its benefits. So we're going to start out by creating a table. To create a table, let me first of all take this filter off again. So to create a table, all you need are rows that have no blank rows in them, so you want to have rows all next to each other, just like this, and columns again all next to each other. You don't want blank rows, you don't want blank columns, but as long as everything is together then you can click anywhere in the table that you'd like and there's two ways of creating a table. One is to go to the insert tab and this is still on page 1 in your handout. To go to the insert tab and when you go to insert, notice there is something called the table. So I'm going to click on table and notice that just by virtue of me having been in the table, Excel takes the best guess as to what the range of this table should be and it thinks it should be A4 through M12. Notice also that there are dollar signs in front of the A and the 4 and the M and the 12, meaning that it's an absolute area. It's going to point absolutely to those to A4 through M12 and not move at this point and then it also gives you a choice of does your table have headers. Well, my table does. That's really important to know whether or not these should be included as part of the data or not. So in this case my table does have headers. So simply by clicking on OK, so all I did was insert table and simply by clicking on OK it will make this a table. Now notice it's selected a particular look to the table. You don't have to continue that look, but that's the one that it just chose automatically for you if you go through the insert table function. Let me show you another way of creating a table. So I'm going to hit undo. Another way is on the home tab. Notice over here there's an option called format as table. And what format as table would allow you to do is if you click on that you can decide at this moment what your table should look like. So a second ago you saw that this was sort of the one that was picked for you automatically, but if you want you can pick a different one. Let's say this one. And the same kind of box comes up. The first one was insert table. This one's format as table, but the same thought process. Click on OK. And so now you have a table with a little bit different look. But it still has all the exact same benefits of the other table. Now I'm going to go back to the other table only because the other table had that, the look that I kind of that banded look to show you how different having a table versus creating your own table will be. So let's go ahead and do this with a banded look. So we've created a table two different ways. One was to click anywhere in the data and go to insert table. One was to click anywhere in the table and go to home and format as table. Either way we've gotten this table. So the first thing I promised you was that you would get filters without having to apply them yourself. And that is true right? Here we have what are called auto filters, which if I just wanted to choose ice cream, I could just click on this down arrow once again, click on deselect, select all, click on ice cream, click on OK, and now I just get to see ice cream. So that's one benefit of tables and that's on page two. When you have a table, let me move this out of the way a little bit here. When you have a table, notice that even had I gone through the insert table route, if I wanted to change the look of the table, all I have to do is go over here to what's called the table styles gallery and change the look to anything I want. So regardless of what Excel does originally to the look of your table, this is how easy it is to change the look. I'm not even clicking on these, I'm just hovering over them and so you can decide prior to even selecting a look, you can see what it's going to look like in this auto preview over here. But what I wanted to show you was, when you are in the table, notice you have a table tools tab, design tab that appears. When I click outside of the table, notice it goes away. But when you are in the table, you have a lot of support to do a lot of different options right here in this table tools design tab. One thing you can do if you want is you can give your table a name. Now this is the ninth table that I have in my workbook, which is why it's called table nine, but I can actually click in here and change the name if I'd like to. So you've seen that it gives you the auto filters. Now let's say you'd like to add a total row. Now normally you would click here, type in the word total and then probably come over here, highlight this area and click on auto sum if you wanted to, right? And etc. So you do all of this on your own. Well, you don't have to do that. When you're in the table, again if you're looking at the table tools design tab, notice that there is an option called total row. Notice also when I point to it that it gives you this little pop up here that says control shift T would accomplish the same thing as clicking in this total row check box. If I click in the total row check box, notice that it automatically creates a total row for me. Not only does it create a total row, notice it has the two little lines which is in general in accounting. You have the double line above and the single line below a total, which it does. It types in the word total. It makes it bold. It did all that for you. And then if you come over here you see there is a total already in there. Okay? Now we can either copy that across or if you come into any of these cells, notice that any of these cells will have a dropdown to the right of them. Just by clicking in it you get this dropdown. Let's see what happens when we click on it. Look at this. You can select any of these functions which are the most used functions or you can go to more function and select any function you'd like to go into this cell. Now in general we add rows of numbers and so I'm going to use the sum function. Okay? So I'm going to click on sum. But I want you to see that even though I clicked on the sum function, notice that up here it doesn't say sum. Why is that? Well remember earlier I told you that if you do put sum in there equal sum it would show, I believe this was 7,000, oh it obviously is 7,000, but it would always show 7,000. Do you really want it to always show 7,000? In other words, if I come in here and once again I only click on ice cream, do I want it to show 7,000 or do I want it to show 2,000? Okay? So that's why they use, even though it says sum, in these tables they use this function called the subtotal function. And this subtotal function has two arguments to it, two pieces to this function. The first one, 109, says do a sum function, the function that you're used to. And the second part says do a function that adds January. So this is the data portion. Notice this says Jan here. And so what it's doing is it's saying sum Jan. Now what if you say well how do I even know that 109 means sum? Well first of all I have it in your handout, but let's say you don't have your handout with you. Let's come down here to a blank cell and type in equals subtotal right here. I'm just clicking on it. I'm going to double click on equals subtotal. Notice that just by typing equals sub and double clicking on subtotal it shows me what my options are and the function number beside it. So if I look down here do you see that the function number for sum is 9? Now a second ago it wasn't 9, do you remember it was 109? Let's keep going down. And do you see here's another one, sum is 109. So that's odd. I've got two sums. What's the difference? Well again in your handout it'll outline the differences so you don't have to memorize this. But the difference is that 109 will ignore any values that are hidden where 9 will include any values that are hidden. What does that mean? It means if I actually go and hide one of these rows, not just filter, filtering and hiding are two different things right? I'll show you the difference in a minute. But filtering and hiding are two different things. But if I hide a row do I want to see the amount in that hidden row do I want to see that in this total or not? That's the question. If you want to see that hidden value then you want to use 9. If you want to ignore the hidden value you want to use 109. And so Excel thinks that most of the time if you're not seeing the value right here in front of you that you would not want to include it in this total. And that's why it defaults to 109 instead of 9. Because what if you print this out? How confusing would it be to the person seeing this if this said 1000 and 1000 and this for some reason said 3900 because there is a hidden value of 1900 somewhere. So it does give you the option of being able to see hidden values by changing this 109 to just a simply a 9. But that's what the difference is. Any questions on that? This subtotal I know is something that you're going to need to get used to because all your Excel lives I'm sure you've just been using the equal sum and it's worked great for you. The problem is when you start filtering or hiding cells it doesn't work as well for you. And so that's why this subtotal function was created. All right. I'm going to go back to showing everything. And I like this function. I want it to go all the way across. So most of you know that in the bottom right-hand corner notice this little box in the bottom right-hand corner and when my mouse pointer when I hover over that box it goes from being what I call a fat plus or a cross to a thin plus. And that means you're on that fill handle that will allow you to just drag across and fill that formula or function all the way across. So that's how easy it is to create a total row. Okay. If you decide later hey I don't want to see the total row if I go back to my design tab notice that I can take the check mark out of total row and it goes away. But look at this. This is very cool. If I click it again it comes right back fully filled out. You don't have to do it again. So you can hide and show it without having to recreate it. Okay. Now if I want to add a total column let's say over here I'd like to add a total column. Look how easy this is. If I type in total over here and press enter notice that it automatically fills out that formatting and includes this column in your table. Okay. Now because it did this for you it does have an auto correct dropdown that you can click on if you don't want that automatic table expansion. You can say undo or stop expanding this auto expansion. I like it so I'm leaving it. Okay. Now let's say what I want to do is I want to sum across this row or all these columns. I click here and you guys are used to doing auto sum. I'm just going to auto sum. Now even though it is a sum function because notice that it still looks different than what you're used to. Notice that first of all it says I'm going to add table nine. Now had you changed the name of the table earlier you would see the table name here and then it says I'm going to add January through December. Now doesn't that make a lot more sense to add January through December than just to say B5 through M5. Okay. You may wonder how does it get these names and if you look across the top do you see Jan Febmar. I don't know if you've used range names in the past. Normally you have to create them yourself but these are considered range names and instead of you having to create them it identified what the January range of numbers were, what the February range of numbers, what the March range of numbers are and so instead of you having to say B5 through M5 or interpret that it actually uses the range names in this function for you. Now look at this is equally impressive. Look what happens when I press enter. It automatically filled that down to all of the rest of the rows. It knew that if you did one total that you probably wanted that total for every single other cell. Now if you want a total in this bottom right cell you can drag it down this way or you can drag it across if you want to. You don't have to though but if you want to cross foot you can do that. Okay. So so far what we've seen is it automatically expands for you. The table automatically expands with columns. What about expanding with rows? Well it does that as well. There's several different ways that you can make this table expand down. One of them do you notice in the lower right hand corner there's this little kind of an arrow looking thing. If I come here and I drag this down, notice I can drag down. I can also drag across if I want more columns but I have to only go one direction at a time. It'll either increase the number of rows or the columns. You have to do one direction at a time. I'm going to increase the number of rows and let go. Notice that it was smart enough to move the total row down and not add more rows after the total row. It was smart enough to put your totals in here just waiting for the data. It was smart enough to format your entire table all the way down. Okay. Simply by dragging this lower right corner. I can also drag it back up if I decide that I don't want to have it. Now I have to get rid of these but I can make tables either longer or smaller. Whatever I want to do I'm going to undo the back so that you can see another way of increasing a table. One is if I'm in the bottom right hand cell of the table which is over here, the bottom right hand cell of the table, not in the total row but in the table because I don't want to be in the total row. I press Tab. Do you see how it adds another row for me? Once again I can go, excuse me, to the bottom right hand cell, press Tab. I can just continue to add data and it will continue to add rows for me. Okay. And over here once again since it's adding Jan and not just B5 through B12 it's going to, that range will include all these new rows for me. Okay. In fact let's test it to see if that really works. I'll type in ice cream, come over here, type in 500 and notice how it added 500. So I don't have to worry does this really reflect these newly added rows for me or not. Okay. So that's another way to add rows. In addition if you're not seeing your total row I'm going to go ahead and take away the viewing of the total row. Notice once again if I'm in the bottom right corner and I press Tab it's going to add a row or if I come down here and I just type in ice cream or any other value as soon as I enter it's going to create another row. So incredibly easy to add rows and columns and retain everything that you've got going on in the table. Okay. Let's get rid of that. Alright. Remember I told you earlier that if I use 109 and I hide rows that the hidden rows won't be in this total. So let's go ahead and try that. To hide a row let's hide this 1000, this ice cream row. So I'm going to right click on ice cream and I'm going to come down and so excuse me I didn't right click on ice cream I want to make sure I'm not confusing you. When you're trying to highlight a row you need to be on the row header. So I need to be on the number nine. You need to right click on the number nine and come down here and hide it. Do you notice how I'm going now from 8 to 10 and I'm not seeing that thousand in this number right? Okay. So now instead of having nine I'm going to change this excuse me instead of having 109 I'm going to change it to nine and even though my thousand is still hidden notice that it's giving me seven thousand. Even though you're not seeing that extra 1000 what I've got here is only six thousand. So that's what I was trying to tell you with 109 versus nine and that one will show hidden values. If you use nine it will include the hidden value and if you use 109 I'm going to hit undo right now then if you use 109 it will not include the hidden value. Okay so really important to understand that and if you look back at when I showed you the subtotal command over here it's not just nine and 109. Do you see this is one for average and one oh one for average. Two for count and two oh two for count. So all of those have the ability to include or not include hidden values. Okay so I'm going to highlight this area right click and I'm going to unhide. So I get my ice cream back. Any questions on anything so far? Are you seeing some of the value to having your data in a table? Well if not we're going to continue and show you more value. You know how frustrating it is when you're in a normal I'm going to go to a different page here. When you're in a normal table like this and you scroll down look what happens as soon as I scroll beyond my headings. Do you see how now I don't remember which one of these is June? Which one of these is July? Impossible to know because it's scrolled up off the top and some of you know about the function called freezing pains. Freezing pains allows you to keep this from happening. It'll keep the header row on top. Well in working with tables you don't have to do a freeze pain because look what happens as I scroll down when I'm in a table. Instead of these going up off the top they will actually go up into this header area so that instead of ABC you're now seeing Jan-Fed Mar. And notice all the dropdowns are still there allowing you to sort and do all of those things but I didn't have to freeze my pains and these stay visible. It's another benefit to a table. Now I do want to warn you to be careful when I first started using tables. I would click off the table and when you click off the table notice you're no longer seeing those up at the top. You do need to be somewhere in the table in order to see those table headings. So if you click off they're gone but you click back in as long as you stay in the table you will see them and you do not have to use freeze pains. Another benefit with using these tables, this table feature, if you look on the table tool design tab, is this ability to remove duplicates. Have you ever had a table that you just said gosh I wish I could get rid of all of the duplicates? When you have a table like this it's super simple. All you have to do is click anywhere in the table, click on remove duplicates, and then you have to decide what is going to determine whether two rows are duplicates. Are you going to remove them just because let's say both of them say ice cream? If so, then you should only have product checked. If you want to make sure that they don't get removed unless every single column is identical, so ice cream, 1000, 2000, etc., then you're going to want to leave all the columns checked. Does that make sense? So this is saying which columns have to be identical in order for me to consider this a duplicate and does my table have a header row, which it does. So I don't have two identical columns, two identical rows. So what I'm going to do is I'm going to unselect all of these and I'm going to say that if I've got two of the same product type that I wanted to consider that a duplicate row and I want it deleted. So I'm going to do that and then I'm going to click on OK and it's going to say that two duplicate values were found and removed. So remember I had two ice creams and so I had two of something else as well. I don't remember which I could look, but anyway. And six unique values remain. So I click on OK. So they are gone. They got deleted for me. Now if at this time I decide, oh, I really don't want those gone, I can always duplicate undo and bring them back. But notice I had two ice creams and here we go. We had two cherries. Okay. So again, all we have to do is click on Remove Duplicates and it went away. Okay. All right. We've talked about everything on page 7 and page 8. Oh, if you decide to do any sort of other formulas, for instance, let's say I wanted to see what I made in December and subtract it from what I made in November. So I'd end up with 100, right? So I want to see the net value of December minus November. If you do it the normal way, if you say equals, my December, right, minus my November, notice again, when you use this pointing method, instead of it saying equals m5, which this is, minus l5, it's a lot easier to see that, hey, I'm just taking the December amount and subtracting the November amount and getting I should still get 100. And when I press Enter, notice that it thinks that you want that all the way down. So I could put net something like net December minus November or whatever it is I want to do, but that's what it would, that's what it would look like. Notice it also expanded my table for me. I'm going to just hit Undo because I don't want that extra column, but that's how easy it is to work with the table, but I did want to show you that in a table it uses these range names to accomplish what you've done in the past probably using cell addresses. Okay, all right. Now let's say you've created this table and for whatever reason you don't want it to be a table anymore. You wanted to go back, you liked this formatting, you did it to get the formatting, but now you want it to be the good old fashioned, just plain table that you normally have in Excel if you don't use this function. Then again in the Design tab, there's a thing called Convert to Range. If you click on Convert to Range it's going to ask you, do you want to convert this table to a normal range? And if you say yes, then all of the features that you had are now gone. So if I scroll up now off the screen, notice that I don't have that sort of Freeze Pains kind of feature. My table won't grow like it used to so it's going to be now just a regular range as opposed to a table. I'm going to hit Undo because I don't want it to be a regular range, but right here is where it allows you to do that. All right. Now if you want to select part of this table, one of the ways of doing it is notice when I'm at the top of this, the top row and I sort of bring my mouse pointer slowly down onto the top row until I have this sort of fat black down arrow. If I then click, do you notice how it highlights, it selects that row in my table? Okay. So that's, excuse me, that column, I said row but I meant column. So that's a way to select a column. Now if I'm over here on the left side of the table and I click, notice how it also selects just the table row. If I'm over to the left on the number 5 it selects the row all the way across my workbook, but if I'm here in the table, do you notice how it's just going to select my table? Which is what I want. Generally speaking you want to work with just your table. Okay. Now notice as I drag down I can again highlight just the table as opposed to going all the way across. And that's another benefit of the tables feature is to work just with a range that you want to have work together. Okay. Now you've seen me resize the table a couple of different ways. One of them was in the bottom right hand corner dragging this. One of them was being in the bottom right hand corner here and pressing tab. Another way to resize the table is right here. You can click on resize table and instead of having the bottom be N13 I can have it, I can either type in let's say N15 or something like that or I can click up here and just click wherever I want it to end. Okay. And then hit enter. Well, except it erased the rest of my formula which is not what I wanted. So let's try that one more time. So let's say I want N15, hit enter and notice how it expands that way so you can use this resize table to expand as well. Many different ways of doing the same thing. Okay. I showed you one way to select things in a table was again to get this black arrow or to come over to the left. But you can always just right click anywhere in a cell in a table and go to select and you can say table column which is what we did by getting the flat black arrow or table row which you saw a second ago or entire, oops, I said entire table but I didn't actually click on that one. So select table column data. So that's table column data, table row and entire table column. Notice the difference between column data and column, entire column, one stopped at 900 and the other stopped all the way down at 600. So we can get that same effect by if we have this black arrow here, if we click once, we just get the data we click again and we get the entire column. So you can do that either way either by right clicking or by clicking two times to get the entire column. Okay. So those are some of the different things that you can do with a table. In addition though if you want to, let's say create a we call it a chart or to have special formatting like conditional formatting on your table if you highlight the table data do you notice and generally when you're doing charts or doing conditional formatting you don't include the total column or the total row when you highlight that you get a little quick analysis pop up. If I click on the quick analysis pop up, notice that it allows me to do things like formatting which I was just talking about a second ago. This is conditional formatting. So for instance if you wanted color scale formatting to show you lower numbers versus higher numbers or data bars within the cells or icon sets which are showing you lower numbers, higher numbers, more medium numbers or you want to see numbers then it looks like greater than 1500 that sort of thing or top 10%. So these are all different formatting conditional formatting things that you can do simply by using this little quick analysis tool. You can also click on charts and decide that yes I'd like to make a chart of this. Here's my Cherry's ice cream cones and nuts. So you can decide which chart really quickly. So there's just all kinds of things you can do different totals if you want to switch out your totals. This is a count. I don't know if you've used the count function before but the count function shows you not the value of each of these but the fact that if you added how many rows there are that would be 1, 2, 3, 4, 5, 6, 7. So it shows you 7 when I go to count because it's showing you that 7 of these rows all have values in them. If I delete the entries like the number 700 then this would show 6. So it shows how many non-blank cells you have. So this allows you to change that total to being anything you want on here. Tables allow you to insert a pivot table if you want and spark lines allow you to create spark lines so that I don't know if you had spark lines taught you in the past. But this quick analysis tool is an amazing tool that allows you to work very quickly with your data without having to really know how to do any of this simply just sort of pointing and deciding which thing you want. Okay. So that is our tables feature and I'm sure since it's so new there are probably questions on it so I'd like to open it up now to if you have some questions on this feature. So there's a quick question here. Does Excel decide which of the duplicate rows to delete? Excel doesn't. You do. So let's go over that again. So I'm going to click anywhere in the tables feature in the table excuse me and I'm going to click on remove duplicates. Okay. So remember when I unselected all these checks and I clicked on product. So then it's saying okay if any of the products are exactly the same then delete that row. So I had two ice creams but or no I guess I don't have two ice creams anymore. I have two cherries so it would delete one of the cherries. Now you may ask well which cherry. Well you know that it could be either one of the cherries. And that's why it's asking you are you really going to make that decision based just on product which is cherries and it's just going to delete one of them. Or are you going to require more things be identical in order to say it's a duplicate. So if it's not good enough that it's cherries if you also want to make sure that hey they're both 800, they're both 900, they're both 1000 and this really does look like a complete duplicate. It is a complete duplicate. Then if I say select all then it's only going to remove the row if the entire row is identical. So it's up to you to decide how much of the row needs to meet that duplicate status before being deleted. If you don't and that's why they start out with everything being selected is because to be a true duplicate right you need to have all of those columns selected. If you do anything less than all then you're really kind of leaving it up to excel to decide which one gets deleted. You don't really care you're just saying hey if there's two identical ones delete one. So this truly is meant to delete duplicate values. So probably everything ought to be duplicate but it's up to you whether or not that's the case. I hope that clarifies that. Okay so for charts can you copy and paste them somewhere? Not the table but rather a chart? Yes. Okay so this is just a chart question in general or in relation to a table? I am not sure. Okay well so let's just quickly create a chart so we've got our quick analysis tool and let's go to charts and let's do a column chart or whatever chart. So we've got this and I don't know if you're trying to take this and cut it. I mean you can just drag it somewhere so I just hit cut and then paste and now it's down here so definitely you can cut and paste to some place if you want to. But you can also if you want to when you have a chart you may, let me get rid of I don't have enough room to show. Okay so when you're on a chart you can also click on move chart and when you say move chart you can move chart to a new sheet because that's often what people want is they either want it on the same sheet with their data or they say you know what when I print the chart I only want to print this chart and so I'd like to move it onto its own sheet and it's called it chart one but I can call yeah chart one but I can call it whatever I want I can rename it but if I click on okay then notice it gets moved to its own sheet. Okay notice down here it says its own sheet and if you decide again you don't like it you can move chart and you can say move it back to whatever sheet you'd actually like it to be on. So yes you can copy and paste yes you can move a chart if you want to to its own sheet whatever you want to do with that. I love the tables feature because it has so many of those items that I always use for instance in a table like this I would almost always freeze pains, I would almost always have auto filter on, I would almost always be pulling my hair out to have my formatting because personally I really love this format of I'm going to go to insert table again of every other line being colored like this because it makes it so easy when you print out to have your eye follow across a particular line. You know used to be people would take a ruler and they would hold it sorry they would hold it as they move down their sheet of paper and they don't need to do that anymore with this these line features so the ability to come here and then just start a new row and not have to worry about reapplying that formatting over and over and over again is such a joy and the ability to have this total row be calculated for you and not just that but let's look at some of these other things. You can decide to have special first column formatting simply by clicking on first column notice it bolded that whole first column which a lot of people like to do. Last column as well if I want to have last column bolding notice that it did that. Notice that if I want to have banded columns instead of having to highlight every other column to put bands down the columns like this all I had to do is click banded columns maybe I want to get rid of banded rows I can get rid of banded rows so this here allows you to do so many things so quickly without having to do each row or each column one at a time. If I don't like the filter buttons I can just uncheck it and I don't need to have it. If I don't really have a header row I can uncheck it and don't need to have it. So this the ability to work with this is just so much fun and not only that but notice also on the table design tab I don't know if you've ever worked with slicers before but slicers are just another way of filtering data since we have a little extra time I can introduce you to with slicers. So one quick question before slicers or something to think about right after them is what are the biggest mistakes that people make with tables or things that you often have to troubleshoot for people? Oh wow that's a good question. I would say the biggest mistake, it's hard to do too many things wrong with tables but the most difficult thing for people to get used to is this use of nomenclature using the range names like Jan, Bev, Mar as opposed to using cell addresses so that's a difficult thing for people to get used to but the other thing that happens that I would say troubleshooting wise would be in using these totals because it's so new to people to using this subtotal function, it makes them extremely uncomfortable so what they would do is they would see the subtotal function and they would take it out and they would replace it with the some function which is something that they feel comfortable with so they would do something like that and if it does say some as we saw this total will not be reflective of what you want to have in there and so I'd say that would be the biggest thing but really other than that they're reasonably bulletproof. Have you had any issues with people doing things that have not been good? So few people, to be really quite honest, so few people use the table function because they don't even know it's there that I haven't had as much occasion to troubleshoot it. I wish I had because it would mean people were using it more. It's also a great tool to use with pivot tables. Many people use pivot tables and as you know pivot table sizes don't grow with you as your data grows you keep on having to restate what the dimensions are for your table size, your input data size and then refresh your data, right? Well by using tables tables do automatically expand so that data will be reflected in your pivot table so that's another really good use of tables. So hopefully that helps but yeah not too many mistakes can be made I don't believe. You have to work hard at that. So are there other questions before we go to Slicers? That's all the questions so far. Okay so Slicers really are notice if I point to Slicer it says use a Slicer to filter data visually. Whoops and it went away. Okay Slicers make it faster and easier to filter table, pivot, pivot charts, etc. And so you may say well why do I need a Slicer because really isn't this the same as a Slicer isn't it just a filter and yes it is a very visual way of filtering data. So let me show you. So I'm going to click on Insert Slicer and what it's saying here is all of these things can be filters so you can filter on the product column or the Jan column or the Feb column which you kind of already knew because that's why there are filters on all of them right? But let's just pick a few. Let's just do hey we want let's have four Slicers, okay? So I picked just four random ones. Click on OK and these little boxes come up and you may say well why are they on top of one another? Well they're just doing it in sort of a cascading fashion for display but you can move them anywhere you want just drag on this top portion so I can move it out of my way so I can see what's happening. And so now you'll see how fun these things are. If I only want to see ice cream instead of clicking on the down arrow selecting deselect all and clicking on ice cream all I need to do in my Slicers click on ice cream boom. Everything but ice cream is gone. If I want to do sugar cones I can click on sugar cones. Now what if I want to do ice cream and sugar cones? Can I do that? Well let's see what...wait a minute let's go back to here we go we have now cherries and sugar cones and ice cream and waffle cones so I can click on all of them or as many as I want. If I don't want to have just one or if I want to clear it I can clear the filter entirely by just clicking on clear filter but this is what allows you to multi-select if you want. So if I just want something I can multi-select or choose to have a single selection. So this is a single selection and if I click up here I can multi-select and clear. Now what if I want so I'm going to multi-select and I'd like to see cherries and I'd like to see sugar cones or excuse me I'd like to not see cherries and sugar cones. I've got those deselected. I'm seeing cones, ice cream, I'm seeing what's left. Now what if I want to see just those within January 900? I've clicked on January 900, notice I've added that filter. And notice that it's showing me ice cream nuts both as being sort of I say light blue and that's because it's saying ice cream and nuts in January don't have 900. So it's just a really interesting visual way of quickly being able to filter and see what does and doesn't have the criteria that you're looking for. I'm going to get rid of this and get rid of that and now I've got all my data again. So let's click on 900 and see what it does to my product area. I'm just going to click on 900 and so it's showing me that cherries, ice cream, and nuts in January have no 900 value but cones, sugar cones, and waffle cones do. So you can look over here to see that that's true or you can just see simply by what it's eliminated over here. Now if you want to get rid of any of these all you have to do is click on whichever one you don't need anymore and press delete and the slicer goes away. Click, delete and the slicer goes away. And you can bring back the slicer simply again by going to the design tab inserting slicer and picking as many as you want. So it's just another way of doing a filter that is more visual than doing it this way because this way you don't really quite know what is it filtering on unless you actually click on each of these individual areas and see what it's filtering on. While we're here let's just see what all the different things are that you do have to choose from. Notice that these don't just filter they allow you to sort if you want from smallest to largest or largest to smallest. You can also sort by color or do a custom sort if you want to create your own sort. Okay. So you can sort by color you can filter by color so if you actually color code your different rows you can do that. And then you can also have number filters if this is a numeric field or otherwise it will say text filters if it's not. And you can also search for things. So you can search for let's say anything that starts with 7-0 or whatever you want so you can do a search. So this is actually an incredibly powerful tool this auto filter dropdown. So another quick question here, if your cells are all text can you use the slicer to identify which cells have a particular word? Well yeah, so this is a column of all text right here and that was one of the things we did slice on, right? So we went to product and clicked on OK and did ice cream and so absolutely. Now this is limiting to being an exact match. What I mean by that is if I not only had ice cream but I also had ice cones then instead of using this because it would do ice cream, well you could do multiple select and do ice cream and ice cones and that would work just fine. But ice, there's no way to do ice to hit both because it's exact. Well that would be a true statement using the slicer. Coming over here though that's a perfect thing of you could do a search of ice and then it would get ice cream and ice cone. So you know and also this text filter you can say begins with, so you can say begins with ice or ends with cone. Oh well actually we can do that, see we have sugar cones and waffle cones. So let's text filter and we'll say ends with cone and click on OK and now we got everything that has cones in it. So this is just such a cool little thing. I do want to make sure though that when you do these guys here that you're a little careful with the equals. Many people think that if I want to find anything that has sugar cones and anything that has waffle cones I could do anything that has sugar cones and anything that, and of course now my mouse pointer oh here it goes, and anything that has waffle cones. However if you do it this way you will get nothing because nothing equals sugar cones and waffle cones. Really important if you have equals here and equals here this has always got to be an or. So it either equals sugar cones or it equals waffle cones because none of these equals them both at the same time. So be careful people think hey because I want this and that it should be and but no it's that it equals this or that. So if I do that now I get something. Otherwise if I did the and I would have gotten nothing. Any other questions? That's all on the questions so far. If you have any questions on anything else Excel related feel free to ask those as well since we have a couple of extra minutes here. But that is the tables function and the spicer function. Let me make sure that there was nothing else that I missed over here other than summarizing the pivot tables, removing duplicates. I think we covered it. So if there are no other questions I want to thank you so much for coming and hopefully you will add this to your repertoire because it really is a very useful, very easy function with this design tab built in for you. It makes it incredibly simple to create, to change, to convert back if you want to, to add splicers, all of those things and to really make a very pretty little looking table that's incredibly functional and that will expand and contract. So we've got two last quick questions that just came in. One is can you demonstrate how to find values by conditional formatting and then sort or filter by color? Find values by conditional formatting. Oh, okay. So if I have this data well we'll just do one column and then I go to my conditional formatting which is the very first one and I don't know if you want just a particular value or whatever but if I click on greater than I can say if I want to find only those values greater than 900 let's say so it values both my thousand and then you can decide how you want it to look or you can do a custom format if you want to save all these different choices so that would be a greater than but you can also find if you only want to do conditional formatting which is on your home tab and you want it to equal you can do that you can create a new rule and do anything so on this conditional formatting notice that I can say greater than less than between two values equal to is I guess what you're asking for because you said a particular value so let's go ahead and click on equal to so if we only want those that are equal to 850 and we want it filled with light red fill and dark red text or green or whatever it is that you decide click on okay oh I did 850 didn't I that was not too smart because there aren't any that are 850 so let's do that again and we'll do equal to 800 oh there we go even without clicking okay it's already showing us and so it's just found those two values is that what you are looking for I'll assume yes so and then I'm going to go ahead and highlight we just got the answer back to yes that covered their question okay I'm also going to highlight these two rows and I'm going to make them yellow and then I'm going to click on this down arrow and I'm going to filter by color I only have one color so maybe that's not a good thing let's do another row two rows and let's make it a different color pretty gross but that's okay so now I sort by color and so or filter by color so let's say I only want to do the yellow rows I only see yellow rows or I can come back here and filter by color and see red rows or my last choice is filter by color and see no fill so everything that isn't one of those colors so that's how you can filter by color and then you can also sort by color which brought my yellow up to the top because I said I wanted to have yellow sort I wanted it sorted by yellow and this is sorted by red and yellow so you can sort and or filter by color so some people have found that very handy any other questions I think our last question will do a good job of wrapping this up which is will you be offering a class on pivot tables and I hope to be able to answer that to say that we are about to do our survey for what topics people are interested in for the webinar series for next year that will be coming out on the LSM TAP email list and we will be putting together the training schedule for the next year here in late December early January if that is a topic that people are interested in definitely give us that feedback at the end of the webinar here you will also get an email or you'll be asked to as you exit to do a very short five question two minute survey on this training if there are other topics you would like to see Sandy cover please put those into the survey and we will take that into consideration as we do our training series next year so I would like to say a huge thank you to Sandy for coming out and doing another training for us