Microsoft Excel is the dominant tool for commercial real estate financial modeling. It is standard issue for any new finance analyst because it is both easy-to-use and flexible. As a result, Excel has a broad userbase, which provides many resources for refining your skill set. Still, users routinely overlook three important tools that can increase productivity and improve usability. These three tools straddle modeling and advanced Excel programming, but you can start using them easily.

The three Excel gamechangers below are an implementation of the don’t repeat yourself (DRY) programming principle. Proper implementation will allow you to scale your analysis and distribute financial models more widely without fear of corruption.

Lookups

Spreadsheets allow you to store information in tabular format and retrieve it on another worksheet. In this way, they act as a quasi-database. Import functions extend its use as a useful tool for offline research and manipulation by transforming Excel data for a production database. However, a cash flow model is different than a database because each line is an active computation.

Your cash flow model resembles a database because you’ll often need to pull out results for a variety of calculations. For this, you need to get the value in a cell based on its row and column address. Net operating income to calculate size of a loan for refinance, for example.

The easiest way to get a specific cell would be to have a hard reference (e.g. =B3), but this makes changes time consuming and error prone. A scalable model requires a DRY approach, where manipulations can be made in one place and reflected throughout the workbook.

VLOOKUP and HLOOKUP

VLOOKUP and HLOOKUP are the most basic tools for matching against a vertically- or horizontally-oriented table, respectively. These are ideal for simple tables containing information like occupancy and ADR figures. However, they break down when you manipulate the table by adding rows or columns.

These functions are also memory intensive, which means they’ll slow down your workbook more than is necessary. Therefore, use VLOOKUP and HLOOKUP sparingly in your advanced Excel programming.

INDEX MATCH

INDEX takes three inputs – lookup range, row address, and column address – and outputs the figure at the row and column of that range. MATCH also takes three inputs – criteria, range, and accuracy – and outputs a number that tells where the criteria is in that range. You may be able to tell – MATCH gives the addresses and INDEX gives the data.

INDEX with MATCH is the best way to perform lookups in Excel. The combination allows you to flexibly search for data across an arbitrary and discontinuous set of rows and columns.

INDEX MATCH is very effective when looking for a figure that occurs on a specific date, like the T-12 NOI for modeling a refinance. HLOOKUP must span all the rows between the date and NOI, which presents a limitation when you want to manipulate those rows. However, INDEX targets these rows independently by separating the criteria match from the lookup range.

Follow these steps to practice:

1. Create two rows with months (A1:E1) and figures (A2:E2)
2. Enter a month in cell A3 that is also in your first row
3. Enter the following in to cell B3: =INDEX(A2:E2,1,MATCH(A3,A1:E1,1))
4. Manipulate cell A3 to see how B3 changes

Add a few rows after your row of months, and you’ll quickly start to understand how powerful the INDEX MATCH combination can be. This example is a replacement for HLOOKUP, but you can do the same to replace VLOOKUP by moving the MATCH function to the second INDEX input.

SUMIF

Every good analysis requires you to summarize data into an easy-to-consume format. The end user usually doesn’t need to see the granular assumptions in the heart of your analysis. The SUMIF function allows you to roll-up these assumptions into a single figure.

For example, a monthly cash flow model helps neutralize seasonality in your underwriting. However, most investors are only interested in annual returns. Use SUMIF to roll-up selected monthly cash flow figures into annual or quarterly figures.

Note: You can only use SUMIF across one row at a time. Use additional SUMIF functions to sum across multiple rows, even if they are adjacent.

Conditional Statements

A conditional statement takes a hypothesis as input and returns true or false as its output. If-then is the common representation in advanced Excel programming and real life. For example, if my car doesn’t start then I can’t drive it.

Excel’s IF function allows you to build simple and complex logic into your financial models by conditioning the result based on a set of criteria.

You may want to trigger an equity distribution only if there will be $200,000 remaining in your capital account. In this case, you can set up a logical rule that states if the capital account is greater than $200,000 then distribute the excess cash. Otherwise, do not distribute anything.

The IF function takes three inputs – testing criteria, result if true, and result if false – and outputs the results based on success or failure of the criteria.

Macros and VBA

Whenever you find yourself doing the same task repetitively, consider whether the computer can do it more effectively for you. This is possible using macros in most cases, and you can start with macros easily by recording the task.

Select View > Macros > Record Macro… from the Excel ribbon.

This will open a dialogue box to name your macro and provide a description. Excel will start logging every keystroke and mouse click once you hit OK. Stop recording by pressing the stop square in the bottom left corner of your Excel window.

Recorded macros are an effective way to perform simple tasks. However, they do everything you do during that recording session. It may be worthwhile learn how to step into the macro and make changes to the code rather than record another macro to correct your mistakes.

Visual Basic is a programming language used across the Microsoft ecosystem. It is based around a variety of instructions that tell the computer what to do. This is known as control flow, which implements conditional statements (IF, THEN, and ELSE) and loops (DO and FOR) to expand the functionality of your simple macros.

Effective use of lookups and macros already sets you on the track to be proficient with Excel, but the next level in advanced Excel programming is creating macros from scratch using Visual Basic for Applications (VBA). This will set you free to make Excel do things that you never thought were possible with a simple spreadsheet.

Excel/VBA is a very forgiving environment to learn basic programming. The combination also gives you access to a skill set that can enhance productivity by multiples.

Spend some time solving simple problems. Recorded macros are a great way to get your feet wet. These give you code snippets to build into a control flow. Iterate through a series of rows and perform a task based on a set of criteria in one of the cells, for example.