All right. Delving deeper into week 4. I'm going to show you here how we can create something known as a table, an Excel table. These are extremely useful when you're working with large datasets. I'm working once again with this file called cookies that we used a lot in the past. So I've got the seller, the buyer, the cookies that they ordered, the boxes, the quantity of boxes, and the price. We worked a lot with this, we did a lot of different things on this dataset. Now I'm going to show you how we can convert this into something known as an Excel table. We can do this a couple of ways. The easiest way is to go up here to the Home tab and you can Format as Table. This will allow you to specify the preference that the table format that you'd like, and I like to use this green over here. Another way to do this is you can go to Insert Table right here. That does not allow you to specify the format of your table, the color and then finally, you can do Control-T to make it into a table. So I'm going to go up here to the Home tab, Format as Table. Let's make it into this nice green. It asks you to select where is the data for your table and if you just have a single cell selected in a contiguous block of cells, it's going to detect that area and then make sure if you have headers, there you have this clicked and then you can go ahead and press Okay. So this is our table. It's grouped together as one individual object more or less. So I can go up here and rename this. So I'm going to rename this just 'cookies'. So you can rename the name of that table if you'd like. Then if you type into the Name Box 'cookies', is going to select that data. So it's very similar to naming an array. The difference is that you can add very easily, you can add rows to this table. So if I do Tab, you see that it has added an entire row, and if I go to the end of that next row and I press Tab again, is going to add in another row. So let me go ahead and just do Control-Z a couple of times. I did show you in a screencast how you can do this using the Name Manager. You can make a named array adapt in a dynamic fashion when you add different rows. But converting it to an Excel table really simplifies things. Another nice feature of these Excel tables, if you go up here to Table Design, there's a couple different options here. One thing I like to do is add in a total row and so it adds in this row, that's a different row then all of the entries. So if I'm on the end of the last row here and I press Tab, it still adds a row before that total row, but you can select what you want to display. Maybe the price. We don't really need a total for the price, so I'm going to select None. Maybe we want a total quantity, so you can sum. There's all sorts of different things that you can play around with, but I'm just going to put in a sum. So we can show that we have sold 46 boxes. Another really nice thing about these tables is if I add a sales column, you see when I press Enter, it automatically added that column to the table. So if I go back into the Name Box and I type in 'cookies', it's referring to that entire contiguous block of cells. When you are working in these tables, it's easy to perform calculations. You also notice that when I click on cell D2, it's got this different look to that variable that you haven't seen before. These are known as structured references. So that's referring to that entire column. So I can multiply that by the price and because tables you're oftentimes doing column-wise stuff, by default, it's going to copy that formula all the way down. By the way, I'm going to go ahead and format all of this to currency. I showed you this in some previous screencast. I can, at least on Office 365, we can do sort unique of the seller array over here, and so I get a list of all the unique sales people. If you don't have that, you can copy this and then remove duplicates and I showed you how to do that in a previous screencast. I'm also going to have a summary here of the cookie sales. I'm going to do the same thing. Sort unique of our cookies here and we've got a list there. Worst case, you can just type those in manually. So what we're going to do is we're going to calculate total sales for each of these salespeople and this I have gone through in a previous screencast as well. So we're going to sum. Our range criteria is over here and our criteria is the salesperson there, and the summing range is the sales. So that's a little bit of a review and then we can drag this down and convert that to your total sales by each of the sales people. We can do the same thing over here. Maybe we want to get a summary of how many boxes have sold. Now the nice thing about the table here is I can add a row. You can press Tab key to add a row. Alternatively, you can go in the middle here and you can right-click and you can do insert a table row above. I'm just going to do the one at the end and now I can add in another sale and when I add in that new sale, it automatically updates my summaries over here. Now the nice thing about tables is I can add a row by clicking on that last one and pressing Tab and maybe we have a brand new seller, Henry, and when I added that, it automatically updated my list up here. It will update in a live fashion. So this is how you can make an Excel table. If you're managing large sets of data, it's very useful. You can easily add rows and remove rows, and it just makes things a lot easier to work with. One more thing I forgot to mention is when you create an Excel table, you've got these filters that have already been added. So if I wanted to just filter out Dana's sales, then it'll do that. One last thing related to these tables. If you decide that you don't want it to be formatted as an Excel table anymore, you can always just click somewhere in your table, go up to Table Design, and do Convert to Range and it'll ask you to Confirm, and then you've got this back as just a typical range. If you want to remove the formatting, you would have to do so. So we can go up here to Cell Styles and Normal. So hopefully, you learned a little bit more about using tables in Excel. Thanks for watching.
