Using Tables in Excel

Microsoft Office’s most used applications are: Word, Access, PowerPoint, OneNote, Publisher and Outlook. One of the most used applications is Excel. Real estate investors need an application that can analyze numbers. Excel is well suited to that purpose. Because Excel is so widely used, at times we expect it to go beyond “number crunching.”

The three Microsoft Office applications I tend to use on a daily basis are: Excel, Word and Access. Number crunching, document creation and editing and database features comprise the major part of my day. I feel very comfortable with all three applications, yet I found myself creating my business Contacts List in Excel.

Sure, I could have exported my Contacts List from Excel into Access. After all, Access is the application for relational databases and their underlying tables. My Contacts List is a very simple storage of names, addresses, phone numbers, dates and comments. Access is such a large and powerful application, I felt it would be an over-kill for my “little” Contacts List. Plus, I just want to stay within Excel if possible.

Starting with Excel 2007, Microsoft provided all us diehard Excel table users a solution. Excel now has a “structured table feature”. My Contacts List can now happily reside in Excel, where it was created and used daily.

What is a Table?

What is a table? A table is just a storage place for information that is grouped by similar items. My Contacts List items include; names, addresses, phone numbers, dates and comments. In Excel each business contact’s information is listed across a row. The Excel columns separate the contact’s items. Columns and rows, no wonder I used Excel. For those familiar with database terminology, the columns are the “fields” and the rows are the “records.”

Using my Contacts List as an example, here’s a portion of that list below.

The abbreviated Contacts List contains five items or fields; Last Name, First Name, City, State and Office Phone. The Contacts List shows twenty contacts or records. Of course this is not my real Contacts List. The real list has many more fields and rows. For this article we will use the shortened list above to show some of Excel’s basic table features.

Let me make a side note at this time. You may have heard or used an Excel table called a “Pivot table.” This post is not about pivot tables. Briefly, pivot tables are used with large amounts of raw data. A pivot table will organize the totals from that raw data and place the totals into a table. An example would be all the data of foreclosure homes in California. A pivot table would organize all the raw foreclosure data. You could rearrange the pivot table to summarize the data based on counties in California. Or rearrange the pivot table to summarize the data based on home owner gender, age or some other demographic.

How to Create a Table

To create an Excel table is easy. Using the Contacts List as an example, here are the steps to turn the list into a table.

Choose where to start the table. Row 1 of our example contains the items or fields that we will use.
Make sure that the items in Row 1 are in definable categories. Notice that the first two items combined could be called a “name” category. I could have just used one item called “Name”. Using a “Name” item would make it difficult to sort on the last name of a contact. By breaking the “Name” item into two items; Last Name and First Name, all I need to do is sort on the Last Name item.
Select one of the items in Row 1.
From Excel’s menu, choose Insert à Tables à Table.
In the “Create Table” dialog, check ‘My table has headers.’ Notice that Excel automatically scanned, then selected all the nearby cells. [ =$A$1:$E$21 ]

That was easy. What would happen if there was not a Row 1 of items? Excel would still create a table of the nearby cells. Instead of the Row 1 items as the column headers, Excel would automatically create column headers as; Column1, Column2, Column3, etc. After the table is created, you could go back and rename the automatic column headers to more descriptive headers.

An Excel Table

The Contacts List is now an Excel table.

An Excel table has some important features.

Excel tables contain only two elements; column headers and rows. Excel tables do not support row headers.
Upon creation, all tables have some basic formatting. Notice the column headers in our example have a dark blue background with white lettering. Alternating rows of the table have a light blue fill.
The column headers have a clickable drop down menu.
When scrolling down a table with a large number of rows, the column headers stay at the top. This is true even if there are rows above the table, as long as a cell has been selected inside of the table.
Rows and column headers can be taken out of a table by clicking and dragging the small triangle at the lower right corner to resize the table.

Filtering and Sorting a Table

The two features that make tables useful are:

Sorting – organizing your table alphabetically or numerically based upon the content of a column header.

Filtering – display only certain rows in the table based on a specific criteria.

Sorting and filtering features are easy with an Excel table. Remember the drop-down menu for each column header? The drop-down menu is where you will access these two important features.

To do an ascending sort on the Last Name column header, just click the drop-down menu and select ‘Sort A to Z’. Other options include descending sorting from Z to A and Sort by Color. Sort by Color is meant to be used in conjunction with conditional formatting. Highlighted cells based upon a certain criteria are brought to the top of the table using Sort by Color.

If you only need to see the contacts from Colorado, click the drop-down menu for the State column header, choose Text Filters, then Contains… and type ‘ME’ in the Custom AutoFilter dialog. Only the Colorado contacts would be displayed.

Table Calculations

I have added four more column headers to the table, monthly fees from January to April. The monthly fees from January will be increased by 5% each month. Time to get back to what Excel does best, number crunching.

In a normal worksheet, not a table, the formula for cell G2 would be = $F2 + ( $F2 * 5% ). To apply the 5% increase to the rest of the contacts in the list, you would drag the formula down to row 21. That is the standard worksheet style of applying a formula across rows.

The Contacts List is a table, not a normal worksheet. Excel uses dynamic table calculations. Formulas within a table are automatically extended through the rows or columns within the table. To demonstrate this dynamic calculation feature, go back to the 5% monthly increase in fees.

For the first contact in Row 2, in cell G2, build the formula by clicking on cell F2 plus F2 times 5%. The resulting formula in the table is; =[@[Jan. Fees]]*5%+[@[Jan. Fees]].

After clicking the [ ENTER ] key, the dynamic table calculation of the 5% increase is extended down through the rest of the contacts. No selecting or dragging required to populate the cells, as you would in the normal worksheet. Just enter the formula and press the [ ENTER ] key, dynamic calculation takes over. The newly revised table with dynamic calculations is below.

The previous example showed dynamic calculation to a new column. How would dynamic calculation work if a new contact or row was added? Add a new contact with the last name of ‘Oliver’ in row 22. Dynamic calculation extends the formula to cell G22. All you would need to do is enter the fees for January to produce the calculated fees for February.

Excel tables will not replace relational database applications such as MS Access or SQL Server. Faced with large amounts of data that could be grouped into separate relatable tables, full database applications are the proper tools. If you want to stay within Excel, the structured table features you find could meet or exceed your needs for organizing and analyzing your data.

Note By BiggerPockets: These are opinions written by the author and do not necessarily represent the opinions of BiggerPockets.