Excel Tips for Private Equity: Custom Formatting

What is Custom Formatting?

Custom formatting is a feature in Excel that allows you to create and add custom number formats to your data. This allows you to display data with additional descriptors without hardcoding and jeopardizing the integrity of your model. With custom formatting, Excel will still be able to read numbers for function calculations but display them in the chosen format.

Custom Formatting vs. Preset Formatting

While custom formatting provides you with added flexibility when presenting data, it should not always be the default formatting option. The benefit of custom formatting is its ability to neatly display data with added descriptors without risking the accuracy of your calculations. However, if the preset formatting options work for your data then they should be used. Preset formatting options can satisfy many situations and include general, number, percentage, date, time, currency, accounting, scientific, fraction, and text.

How is it Used in Real Estate?

At Top Shelf Models, we implement custom formatting into our models when additional descriptors will help to identify and comprehend the data more easily. For instance, we use custom formatting in our distribution waterfall models to display “Limited Partner to First Hurdle (X%)”, while only pulling the specific percentage. This way, we can display the percentage and update it quickly without having to manually enter the descriptive text each time. Additionally, custom formatting can be used for calculations per unit, per foot, per square foot, etc.

Example

For example, what if we needed client phone numbers displayed properly?

Here we have 3 clients and the phone number for each. These are currently in the general format, and with more clients, we would not want to enter the dashes in manually for each.

Instead, we can create a custom format for cells E6 through E8. We simply select the cells, go to format cells, select custom, and set our format to a telephone number as seen above.

Finally, we link the cells in column E to the cells in column D and it will display the data in our custom format.

---

About the Author

Eric Bergin is the founder of TSM. He realized that there was a need for real estate financial models that were more than just generic templates. He wanted to create a personalized product for his customers that would ensure success for them and their company. Please reach out to him if you have any questions regarding discounted cashflows or if he can help you with your modeling needs.