Create an Excel table

- [Instructor] If you spend a lot of time working in Excel you will end up summarizing and maintaining data lists. A list is a set of columns, with each column containing a different category of information, and rows where each row represents a full set of values. For example, you could have a list of your company's products where the columns represent the product name, price, and so on, and each row contains a complete set of the details for an individual product. In this movie, I will show you how to move beyond basic data lists by creating Excel tables.

My sample file is the ExcelTables workbook, and you can find it in the chapter three folder of your exercise files collection. In this workbook I have a data list that shows sales by month for the year 2017. It's also broken down by quarter. I could create formulas to calculate values from this data list, but if I add data, for example a row for January 2018, then I would need to either change the formula or relocate them so I can put in the data.

I can get around all that and get some other benefits as well by creating an Excel table. I'll start by clicking any cell in my data list, and you notice that there's no extraneous data around it, no extra values. And then, with a cell in the data list selected, I will press Command + T. Doing so displays the Create Table dialog box. I could have also clicked the Format as Table button on the Home tab of the ribbon. I'll note that the data range is correct, A1 to D13, and also that my table does in fact have headers.

With all that in place, I can click OK, and Excel creates my table. It also displays the Table contextual tab of the ribbon. This table comes with a number of benefits. The first you'll note, over here on the left of the Table contextual tab, is that this table has a name. So the same way that a named range identifies a range of cells, a table identifies a set of data, and each of the column heads can be used in formulas to summarize the data.

So let's say that for the table name for this table, instead of calling it Table 1, I will delete and say MonthlySales and Return. So now that is the name of my table and how I will refer to it. One great way to summarize data within a table is to add a Total row. On the Table contextual tab, there is a Total Row check box. Go ahead and click that, and you will see that it appears at the bottom of the table and that there is a summary operation in the bottom right corner, the rightmost cell.

So for sales, I had over 5.6 million for this year. If I want to change the summary operation, I can click cell D14 in this case, click the down arrow that appears, and select another operation. In this case I won't, but I could display it or I could turn it off entirely. You can also do the same thing for other cells in the Total row. So if I wanted to find, for some reason, the total of the years, I could click cell A14, click the down arrow, currently it says None.

I could make it Some or another value if I wanted. In this case I don't so I'll click away. One great thing about tables is that if you add a new row of data it gets added to the total and other formulas automatically. So let's say that I click cell D13, which is the last cell in the table, and I press the Tab key. That creates a new row and it is within the table. So I'll type 2018 for year, quarter one, then January for the month.

And then for Sales, I'll just say 300,000. And now the total in D15, which is the Total row, should go up to 5.9 million. And when I press Return, it does. Note that because I pressed Return instead of Tab, Excel moved the active cell down one row instead of creating a new table row like it does when you press Tab. If you want to delete a row from a table, you can do that. Just move the mouse pointer over the left part of the row that you want to delete.

And you can see here that I have a right-pointing black arrow. Hold down the Control key and click, and then point to Delete, and then say Table Rows. I can, of course, undo that by pressing Command + Z if I want to. Excel tables help you summarize your data effectively and make it easy to add new values to your collections. I strongly recommend using them.