Excel For Commercial Real Estate: Custom Cell Formatting

Yes, you can typically get around customizing a cell, but often at the cost of increased error input, transcription error, and time.

So if it takes only a minute of your time to become a custom cell expert, consider it a very generous tradeoff.

Most of us are aware of the standard available formats as seen below, but what if we want something commercial real estate specific? How can we label our cells and keep the numerical functionality?

Something to remember: By default, Excel left-aligns text strings and right-aligns numerical values. Furthermore, attempting to add text to a numerical value, for instance adding “SF” to the end of a value, automatically converts the value to text, making it unusable in formulas and calculations.

Below is what happens when we try to use “1800 SF” in a formula.

We receive an error as Excel only recognize “1800 SF” as a text value!

The question becomes: how do we keep 1800 as a numerical value, while keeping the “SF” formatting?

Here’s the four-step process to custom cell expertise. Remember we’ve provided practical excel application, as always, here: Custom Cell Formatting.

Step One: Right click on the cell you wish to format (in this case A4) and select Format Cells.

Step Two: In the Category window, at the bottom, click Custom.

Step Three: Select from a number of pre-formatted starting points, in our case we want to format a number with commas to separate thousands with an inclusion of “SF” at the end to denote Square Feet. Our starting point will be “#,##0”.

Syntax: #,##0 = a numerical value with commas to separate every three values, and if zero, show “0”.

Note, if formatted as “#,###” without the “0”, excel would display a zero as blank (or nothing) rather than “0”.

Step Four: Now that “#,##0” has been selected in the “Type:” box, manually add “SF” after “#,##0” as seen below. This will now format 1800 as “1,800 SF” and recognize it as a numerical value (capable of calculation), rather than text.

As a quick check, if the value is right-aligned you’ve got a number!

You’re sure to impress your neighbors with excel format manipulations not contained in the standard available functions, and more importantly, you’ll save yourself time and errors, which you’ll consider invaluable in long run.

Be sure to download our interactive guide to custom cell formatting, including examples, homework, and a little extra credit!

Join us by subscribing for free email updates and follow us on Twitter.