Excel isn’t the best way to keep structured data, but sometimes, it’s all you’ve got. And there are some benefits to storing listing information in Excel versus your CRM app. It’s difficult if not impossible to migrate your listing data from a CRM app. If you’ve got it in Excel, however, you can migrate it to a database or import to aggregators.
It helps if you design a database friendly spreadsheet. Unlike in Excel where everything is on one sheet, databases will have separate tables for property, listing, comparable and company/contact data. These would all be linked so you wouldn’t necessarily have to repeat the same data in each table. And you wouldn’t have the same columns in all tables. No need for listing agent in the property table for example.
You actually can use Excel as a “faux” database with separate sheets – one for properties, listings, etc… Using MSQuery, each sheet is treated as a table. You’ll need a “matching” field to join on – usually a ID for the row (not Excel’s default row numbers, though). Then, you can select the fields you want from each sheet/table and they’ll be returned in a new sheet. You’ll need to do some prep work and know a little SQL to do this but not as much as you think. Here’s a step by step guide.
If that sounds like too much work, don’t worry. You can still create a single spreadsheet in a database friendly way so you can migrate without a lot of headaches.
–Data standards for all your data is important but it’s critical for the address field. Most all property/listing matching is done by aggregators on the address field as is property mapping. It’s really a poor choice since everyone seems to have their own way of entering the address. Many times I’ve seen the same listing in aggregator databases with address variations. So don’t let that happen to your data. Create standards and stick to them.
–OSCRE standards try to bring some sense to the address field by breaking it up into street number, street name, directional, street suffix/prefix…and on and on. But that’s overkill and it still doesn’t address the myriad variations.
–You can use post office abbreviations as is typically the case for municipal database but they look awful/some are confusing (like PH for Penthouse…). I say, just make sure the address is mappable. Enter it in Google or Bing maps. Then use the format that’s returned.
–Add an alternate address field for streets that could be known by their local name or route name. Remember to specify if it’s a national (US or sometimes “I”) or state (use the 2 letter state code…) route, e.g., US 1, NY 90. County Routes, Rural Routes, Old Routes, etc.. can be tricky to get right. Keep checking the maps until you get it right.
—Grab the longitude/latitude of the property. Most aggregators don’t take that yet, but you’ll be glad you have this data for other purposes.
–Don’t confuse physical address with mailing address. They’re not always the same.
–In smaller markets, often the municipality, usually a Hamlet, is more commonly known than the post office “city.” Agents are sometimes left with a difficult choice – get a mappble address but use a city that locals won’t be searching for. Aggregators don’t take this into account at all but sometimes Google will get it right – you type in the hamlet as the “city” and it returns the address with the post office “city.” Adding a municipality field and type is important in smaller markets.
–I can’t stress the importance of getting the address right, so make sure that checking for property addresses is your first step before entering a new listing. If you’re up to it, create a separate sheet for property addresses – and give each a unique ID. Enter that property ID in your listing sheet then use a Lookup function to fill in the street address, city, etc… that matches that unique property ID. (More info on lookup here.) You can also add “enduring” data here instead of in your listing sheet – longitude/latitude, building SF, municipality and so on – that can be copied over using a lookup.
–It’s essential that you create a unique ID for each listing. Pre-fill the listing ID or use a VBA macro.
–Use the keyboard left/right arrow keys to move horizontally from cell to cell. Using tab/enter only works vertically.
–Can’t stand using the arrow keys? Copy your column headings, paste, then use Paste Special to “transpose” them in a single column on another sheet. Enter your data next to the column names, then select all the data, copy and paste (using Paste Special/Transpose) into your listings sheet.
–You can create forms for data entry using VBA. Without proper coding, however, you’ll lose the auto-complete feature – where you start typing and Excel starts to “guess” what you want to type based on previous cell entries. (You’ll also lose aut0-complete if you use the tip, above.) It’s worth it to add this feature particularly for the address field which you want to keep standard/free of address variations. A discussion of how to add that here.
–No VBA experience/coders around? Excel’s data validation tool will work for many cases, particularly fields with commonly selected items – property type, listing agent, lease type etc… You’ll create lists of allowed entries for each field so that when you click on the cell to make an entry, the list appears. This way you can standardize how these fields are filled and remove the chance of spelling errors/stray data getting into fields.
–Create Y/N columns for “sort” fields like exclusive listing, business sale, investment sale, etc… instead of lumping this type of information in a misc. text field. When migrating to a database, these “flags” are converted to bit fields – on or off (1, 0).
–Pre-fill certain columns, like country or state, using the copy/paste feature. You can always over-write a cell if you need to.
–Add a “track” field to identify properties/listings you’re going to use for certain types of market data reports (available SF by neighborhood, market area, use, etc…, period to period absorption). You won’t, however, be able to calculate vacancy rates since in cases when a property has more than one listing, the building SF will be counted multiple times (remember that if you’re creating a Pivot table from your data…). Use Excel’s Advanced Filter on the address field to show only unique instances of the address.
–Add “comped” to your listing status field (usually active, pending, off-market) as an easy way to flag listings that have been sold or leased. Sort by this flag to easily find comps (and add some more fields for comp data) or move these to another spreadsheet if you want.
–Use Excel’s filter feature. Select the column titles, then select FILTER. Each column title will have a “dropdown” so you can sort or select values “in place” instead of using Excel’s sort function. You’ll still need to use the sort function for multi-field sorting, e.g., by exclusive AND listing agent.
–Add hyperlinks to related flyers and photos. Select the cell, the INSERT –> Hyperlink. Browse to the location (your local or network drive – or a web server…and then don’t move them!), then select the photo or flyer file. Excel will create the URL (path) to your file. Clicking on it in Excel will open the file in its native application (e.g., photos in your photo editor).
–You can add a thumbnail image, too, but inserting it into a cell/resizing a large photo will make your Excel file huge not to mention messy. Put one in as a “pop-up” instead using Excel’s “comment” feature. Resize/optimize your photo (200 pixel width max, 96 dpi) and then follow these instructions to add to the comment box.
Usually the only contact information in a listing spreadsheet is the agent name…or initials. But for data imports to aggregators, you’ll need more than that. Add listing agent(s) and other brokerage information in their own columns. You can use a drop down list (data validation) for your agents or area brokers or rely on Excel’s autocomplete to help you out. Searching through previously entered contacts and copying/pasting works, too.
I’ve created a sample spreadsheet if you want to take a look. For columns, I used LoopNet data entry forms as a guide. Didn’t include everything – and I added others that I thought were important. It also includes a how to on using Excel’s data validation tool. As you’ll see, it’s quite unwieldy – 71 columns! So in the near future, I’ll cover designing a database for your CRE data.
Photo from amazon.com