{"id":40919,"date":"2013-04-10T12:33:25","date_gmt":"2013-04-10T18:33:25","guid":{"rendered":"https:\/\/www.biggerpockets.com\/renewsblog\/?p=40919"},"modified":"2021-03-16T10:05:08","modified_gmt":"2021-03-16T16:05:08","slug":"2013-04-10-table-excel","status":"publish","type":"post","link":"https:\/\/www.biggerpockets.com\/blog\/2013-04-10-table-excel","title":{"rendered":"Help, My Contacts List is in Excel! (Using Microsoft Excel Tables)"},"content":{"rendered":"

Microsoft Office\u2019s most used applications are: Word, Access, PowerPoint, OneNote, Publisher and Outlook.\u00a0 One of the most used applications is Excel.\u00a0 Real estate investors need an application that can analyze numbers.\u00a0 Excel is well suited to that purpose.\u00a0 Because Excel is so widely used, at times we expect it to go beyond \u201cnumber crunching.\u201d<\/p>\n

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

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

Starting with Excel 2007, Microsoft provided all us diehard Excel table users a solution.\u00a0 Excel now has a \u201cstructured table feature\u201d.\u00a0 My Contacts List can now happily reside in Excel, where it was created and used daily.<\/p>\n

What is a Table?<\/h2>\n

What is a table?\u00a0 A table is just a storage place for information that is grouped by similar items.\u00a0 My Contacts List items include; names, addresses, phone numbers, dates and comments.\u00a0 In Excel each business contact\u2019s information is listed across a row.\u00a0 The Excel columns separate the contact\u2019s items.\u00a0 Columns and rows, no wonder I used Excel.\u00a0 For those familiar with database terminology, the columns are the \u201cfields\u201d and the rows are the \u201crecords.\u201d<\/p>\n

Using my Contacts List as an example, here\u2019s a portion of that list below.<\/p>\n

\"contact<\/a><\/p>\n

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

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

Related:<\/strong> Excel Functions Standing upon Bones<\/a><\/p>\n

How to Create a Table<\/h2>\n

To create an Excel table is easy.\u00a0 Using the Contacts List as an example, here are the steps to turn the list into a table.<\/p>\n

    \n
  1. Choose where to start the table.\u00a0 Row 1 of our example contains the items or fields that we will use.<\/li>\n
  2. Make sure that the items in Row 1 are in definable categories.\u00a0 Notice that the first two items combined could be called a \u201cname\u201d category.\u00a0 I could have just used one item called \u201cName\u201d.\u00a0 Using a \u201cName\u201d item would make it difficult to sort on the last name of a contact.\u00a0 By breaking the \u201cName\u201d item into two items; Last Name and First Name, all I need to do is sort on the Last Name item.<\/li>\n
  3. Select one of the items in Row 1.<\/li>\n
  4. From Excel\u2019s menu, choose Insert \u00e0 Tables \u00e0 Table.<\/li>\n
  5. In the \u201cCreate Table\u201d dialog, check \u2018M<\/span>y table has headers.\u2019\u00a0 Notice that Excel automatically scanned, then selected all the nearby cells. [ =$A$1:$E$21 ]<\/li>\n<\/ol>\n

    \"create<\/a><\/p>\n

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

    An Excel Table<\/h2>\n

    The Contacts List is now an Excel table.<\/p>\n

    \"Excel<\/a><\/p>\n

    An Excel table has some important features.<\/p>\n