Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
If you spend a lot of time working in Excel, you'll 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, if you track your company's sales by month, you can have columns representing the year, the quarter - in other words, January, February, March is quarter number one; April, May, June is quarter number two; and so on - then the individual month, and the sales for that month. A row would indicate a particular set of values.
So, for example, for the year 2009, quarter number 1, the month of January, you can have sales of just over $406,000. I'm going to take a moment to describe the characteristics of this data list. Then I'll show you why creating an Excel table makes managing your data lists easier. This table has four columns and, including the headers, 25 rows. It represents two years worth of data. You'll notice that there are no gaps. In other words, there are no blank rows. That's important because if you create an Excel table, you can't have any gaps, any empty rows in the data list.
Also, you'll notice that there is no extraneous data, data that is not part of the list, to the right, beneath, or to the left of the data list. In fact, the data list is against the left edge of the worksheet. The reason that I don't have an extraneous data there is because if I were to create the Excel table, then that data would be included, even though it isn't supposed to be part of the table. So now let's say I create a table. To do that, I click any cell in the data list, and then I click the Tables tab on the Ribbon, click the New button, and then I can either create a table with headers, which in this case I do have, or a table without headers.
In this case, I want to create a table with headers, so I click that option, and Excel creates my table. Now I'll click a cell to get rid of the selection, so you can see it more clearly. It looks the same as before, but in this case, there are some very important differences. For example, if I want to summarize the data in the Sales column, I can add what's called a total row. When I click any cell in the table, and then I'm on the Tables tab, I can check the Total Row box, and Excel automatically adds a total row. And you can see that the total row contains a cell, which is the sum of the values above it.
But now let's say that I want to add a row to my table. How do I do that? The easiest way to add a row to your table is to click the last cell that contains actual data - in other words the cell above the total row - and then press the Tab key. When you do, Excel creates a new row in your table. You can then put in your new entry. So let's say its 2011, quarter number 1, the month is January, and sales were $500,000. When I press Return, instead of creating a new row, Excel simply moves down to the next cell in the worksheet.
You can also see that Excel updated the Total to include the new value of $500,000. Now let's say that you wanted to find your average sales instead of your total sales. You can change the summary operation by clicking this arrow here, and then selecting the new function that you want to use. Right now, it's Sum. If I wanted to change it to Average, I can move up to Average. Click it. You'll see that my average sales are in the order of $322,500. If I want to change it back, I can press Command+Z to undo my last operation.
If I want to get rid of the total row, I can uncheck the box, and it goes away. I've already shown you how to add a row to an Excel table, but what do you do if you want to add a column? Well, to add a column to an Excel table, you click in the first empty cell to the right of the Excel table, and then you type in the name of the column that you want. So let's say that we have transactions, in other words the number of individual sales. When I press Return, Excel adds that column to the table. I'll move it out, so you can see the header properly. You can start adding data into that column.
If you want to delete a column in the table, it's like deleting a column in a worksheet, except it's a little different. If I wanted to delete the entire column in the worksheet, I would move the mouse pointer over the column header, select it, and then delete it. In this case, what I want to do is delete the table column. So I move the move the mouse pointer down, and you'll see that it changes to a different arrow. It's downward-pointing, but in this case, it has a vertical bar across the top. That indicates that you're working with an Excel table. You can then click the column, Ctrl+ Click, point to Delete, and Table Columns.
Excel Tables help you summarize your data effectively, and make it easy to add new values to your collections. Later on in this course, I'll teach you how to format your tables and work with your table data more effectively.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 82559 Viewers
80 Video lessons · 133822 Viewers
52 Video lessons · 67108 Viewers
59 Video lessons · 52936 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.