Manage data using Excel tables - Microsoft Excel Tutorial

- [Man] Your first step in creating a dashboard in Microsoft Excel should be to store your data in the most efficient and flexible manner possible. In Excel, that often means you should use Excel tables to summarize, store, and combine your data for use in dashboards and other presentations. In this movie, I will demonstrate how to create and work with Excel tables. My sample file is 01_01_ExcelTable and you can find it in the chapter one folder of the exercise files collection. In this workbook, I have a data list and it has several important properties, all of which you need for an Excel table. The first thing to know is that each row contains a discrete set of information about a particular entity that is summarized by the list. So for example, if you look at row number two, you'll see that it summarizes sales data for bath products in the month of January for the year 2020 that happens to be quarter number one. In other words, the first three months of the year, and sales were $939.69. Each row below that row gives a different piece of information. For example, in row three, we have the same month with sales, for gift baskets. So each row contains a complete set of facts and each of the columns contains one of those facts. So we have the year in column A, quarter in B, and so on. You don't really need to have column labels such as year end quarter to create an Excel table, but it is very helpful. If you don't have them, Excel will simply add its own, such as column one, column two, and you'll need to change it later. So it's best to put 'em in from the start. As I scroll down, you'll also notice that there are no blank lines. In other words, there are no gaps in this data list. And also that there's no extra data either to the right, below, or to the left. If you look to the left, you'll see the edge of the worksheet and all the cells to the right of my data list are also blank. And there was nothing below it, either. If there were data to the left, right, or below, then that data would be included in the table and it wouldn't make any sense. Right, so with all that in mind, let's go ahead and create our Excel table from this data list. I'll scroll back up to the top and I'll click any cell within the data list. And then on the home tab of the ribbon, I'll go to the styles group and click format as table. And from here, you can select a style. I'll just select light, which is at the top left, the create table dialogue box appears, and it asks where the data is and it has identified the proper range. You can see it outlined in the worksheet. And also the, my table has headers boxes checked. So that's good. I'll go ahead and click okay. And Excel has created my table, and displayed the table design contextual tab on the ribbon. One of the first things that I do when I create a table is change its name. If you look on the table design, contextual tab of the ribbon, all the way to the left, you will see that the table name box contains the name Table One. The next table that I create would be called Table Two and so on. That's accurate, but not informative. It doesn't tell me anything about the data. So in this case, I will click in the table name box and change it to monthly sales. With that change in place, if I refer to this table in a formula, I'll see that the table contains monthly sales which is much better than simply saying that it's table number one. There are other ways that you can work with a table on the table design tab. So I'll just click away to release the selection. For example, you can have table style options where you can have banded rows. Right now that box is checked. So you can see that we go gray, white, gray, white. If I were to clear banded rows, then everything is white and there's no banding. And if I check the box again, it comes back. And one other item that is very useful is the total row. So I'll go ahead and click to check that box. And at the bottom, a total row has been added. So this is part of the table, but it doesn't affect the data. And you can see that the total of all sales summarized in this table were $1.7 million plus a little. You can also summarize other table columns. So for example, if you wanted to calculate the number of rows, which given the data they have, is really the only sensible calculation you could create. You can click inside of the total row for another column, click the down arrow and select any of the functions that are displayed, or more functions to have a broader selection. So in this case, I'll click count, and I see that there are 72 rows, which makes sense. I have three categories and monthly data for two years. So 3 times 12 is 36, times 2 is 72. If I wanted to change the summary operation for sales, I could click that cell. Click its down arrow, and select another value. In this case I won't, because you've already seen me do it once. And that's it. Excel tables are very powerful on their own, but they're even more powerful when you combine them with pivot tables.