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!