Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
In Excel 2003 Microsoft introduced the list, which enabled you to manage tables of data more efficiently. In Excel 2007, these list objects became Excel tables, which are much more useful when performing financial analysis in Excel. Now, on this worksheet, you will see that I have the beginnings of a data table, where I have the date, category, detail, income, expense and balance columns, and then I have entries within those columns and this is what you might think of as an Excel table. But an Excel table is actually a specific object that you create within Excel, and as opposed to this data, which is in list format, the Excel table gives you other capabilities that you can use to analyze your data. It makes it much easier to use and is a real addition I think to Excel 2007.
So how do you create an Excel table? Well, to do that, you click any cell in the data that you want to include in the table, and then on the Home tab, click Format as table, and select a table style. Excel indicates the data it will include in the table. That looks right, and then it says that your table has headers. It does. You have the date, category, detail and so on. You can click OK, and it creates the table. So here I have my starting balance of $250,000, and the second row has rent that I received for rent of unit L203 in the sum of $2,500, and you can see here that I have my running balance of $252,500, and that includes the previous balance, in cell F2, the income in cell D3, and any expense in cell E3.
When I want to add a new row to the table, I just click the last cell in the last row, and press the Tab key, and it gives me a new row to enter my data. And you will also notice that Excel extended the formula into the new row. So if I were to receive another rent payment, also on the 5th, for unit L205, for the sum of $5000, then that formula takes over, and that includes the income. And just for completeness I'll show an expense, say that I pay my mortgage on the office building. So that is an expense, call it $25,000, and the Excel table updates my running balance.
Now if you want to find a total of the values in a particular column, say for example, you wanted to find the total of all of your incomes in the table. To do that, you can add what is called a total row to the table. So you'd go up to the Table Tools Design tab that appears anytime you are editing a table, and then in the Table Style Options group, click Total Row, and the total row appears at the bottom of the table. Now you will notice that we have a total of all of your balances, which doesn't make any sense.
So to get rid of it, you just click the cell, click the down arrow, and then select None. So there is no summary there, but if you want to get a total of all of your income over a particular time, you click that cell, click the down arrow, and then select Sum, and that gives you the total of $2500 and $5000, which is $7500. You can do the same here for your expenses, click Sum, and it gives you the sum of all of your expenses. Excel tables help you summarize your data effectively and provide a base for more advanced analysis using Pivot Tables, which I'll show you in the next few lessons.
Get unlimited access to all courses for just $25/month.Become a member
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.
Your file was successfully uploaded.