Excel table data storage and organization - Microsoft Excel Tutorial

- [Instructor] Now as we start building dashboards in Excel, we're gonna wanna keep our data organized. Excel provides us an efficient data structure called an Excel table. Now this is a regular table of data, but Excel actually calls this a range. An Excel table is a feature in Excel. Now I don't want you to click on it yet, but I'll just show you on my Insert tab, You see it says Table. So Microsoft likes to call these Ctrl+T tables and that's what I will mean when I talk about an Excel table, a Ctrl+T table. And you will see in a few seconds why we call it that. But to really understand the difference between a range and a table, let's go ahead and do a normal workflow that we would complete if we were trying to understand how many records were in this dataset. So I'm gonna start on cell J2 over here and I'm gonna type in equals, and I'm going to use the Count All function, which is the CONTA for Count All. Then I'm going to select cell A2. And looking at my keyboard, I will do a Ctrl+Shift down. All right, so that's just gonna select the continuous region. I will close that off with the right parentheses and then I will hit Enter like that. So we see there are 43 records in this table and we are using a formula Count All. Now I want you to notice that A2 through A44 does not tell us anything about this data. So put your cursor or your selector anywhere within this continuous data region. Then from on the ribbon, click on Insert and click Table like that. So now we are going to apply a Ctrl+T table so named, because you can also hit Ctrl+T to get to it. So make sure my table as headers is checked and then go ahead and click okay. Now we've applied a table. You can see the name of the table as table one. We're gonna keep it like that for now. So now go to J6, I'm gonna type in equals, I will do a Count All, so C-O-N-T-A left parentheses. And in this case I will type in Table 1, left square bracket. so this opens up the table with that left square bracket. I will select Order Date from here. So I just double clicked it, right square bracket will close it. You can see it automatically selected this region. And then I will close it with the right parentheses like that and hit Enter. So one of these is more semantic than the other. As we build dashboards in Excel, we will prefer using tables. Now let me show you some cool things tables can do. I'm gonna start in cell G1 and I will type in Total like this and hit Enter. Now you'll notice that there's a cell lightning bolt over here, and what this did was automatically expand that table. So now the total column can be referred to in a formula like I did with Order Date. The other thing I can do is in cell G2, I will type in Equals I will select cell E2. So you can see at units, that means at this row, multiplied, that's a shift+8 to get the asterisk, by the unit cost. So at unit cost like this, watch what happens if I go ahead and hit Enter. It automatically populates this data all the way down the side. Now one final thing we can do, you can go ahead in cell A45. So it has to be a 45, the leftmost cell adjacent to the bottom. We will type in today's date, whatever it is for you. So I will type in today's date on my end. Now I'm just gonna hit Tab and you'll see it automatically populated a little anchor here down below. So it shows us that it automatically increased the table size, and under rep, I'll just put my name and then I'll hit Enter. And if I scroll up, you'll see here under the record count, I didn't have to change the formula, but the table automatically grew. Now you'll notice above in the formula-based option it did that as well. But in earlier versions of Excel, it used to not do that. So depending on your version, it may not do that. But we always wanted to do it. So we will always prefer using an Excel table.