The Best Excel Tips For Commercial Real Estate Professionals
As a commercial real estate professional, Microsoft Excel is an important part of your workday. Despite its frequent use, it’s crazy all the tricks and shortcuts you probably don’t know exist. A common response after showing people some of them are “no way, I never knew I could do that” or “that’s awesome, I could have saved so much time if I knew I could do that”.
Below I describe a few of my favorite and most useful Excel tricks and shortcuts. Keep in mind this is not a comprehensive list, just a few of the ones I find extremely easy and useful that I think commercial real estate professionals can benefit from using. Plus, these are great tips to utilize if you are cleaning up your data prior to importing into our commercial real estate CRM software.
NOTE: The examples shown contain sample screen shots from Microsoft Excel for Office 365. If you have a different version of Excel, then there could be some slight differences however, the same basic shortcuts should apply.
How To Freeze The Top Row
The ability to freeze the top row so that the headings always stay in view, no matter how you scroll through the worksheet can be extremely useful.
On the main menu click ‘View’
Click ‘Freeze Top Row’
Scroll down to the rest of the worksheet. The result is, Excel automatically adds a dark grey horizontal line to indicate that the top row is frozen.
How To Create A Basic Excel Drop Down Filter
Adding a drop-down filter to your spreadsheet is an easy way to see the values in a specific column. When an Excel filter is added to the header row of a spreadsheet, a drop-down menu appears in each cell of the header row. They can be applied in different ways to improve the performance of your worksheet. You can filter text, dates, and numbers. You can even use more than one filter to further narrow your results.
Begin with a worksheet that identifies each column using a header row
Highlight the columns you would like to filter
Select the Data tab and press Filter (it is the icon that looks like a funnel)
There will now be a filter on each highlighted cell
Tip: Using a filter is a great way to review and clean-up data prior to importing it into a commercial real estate CRM software, like ClientLook.
How To Split A Column of Data
Excel has developed a mind of its own. If you need to separate first and last names (or part names and numbers, or any other data) into separate columns, Flash Fill makes it easy.
Enter the first name or other value in the column next to your data and press Enter to advance to the below cell.
Now start typing the next name. Flash Fill will show a list of suggested names.
If it looks good, just press Enter to accept the list.
Tip: To continue typing without using suggested names, press Escape.
Now enter a last name in the next column, and press Enter.
Start typing the next name, press Enter, and you’re done.
Capitalization
Flash Fill is case sensitive which means if you enter a lower-case last name, for example, all the last names follow suit.
Tip: If you don’t like a result, just press Escape, and continue typing without using suggestions. You can also use Flash Fill to change the case of text.
More about Flash Fill
Your data doesn’t have to be names. Flash Fill works with any data you need to split into more than one column, or you can simply use it to fill out data based on an example. Flash Fill typically starts working when it recognizes a pattern in your data. However, Flash Fill may not always start filling out your data. It works best when your data has some consistency. The more consistency the better it will work.