Here is a great approach to create ordinal numbers, which is dymanic and VBA macro-free. It applies Excel conditional formatting to the required data array. The ordinal number formatting will preserve the integer properties of the relevant data array, and will require a number of rules to be created.
Existing restrictions with Format Cells
Currently MS Excel is unable to perform custom cell formatting for ordinal numbers. In some cases, financial modellers prefer to maintain cells in integer format, such as the ability to performing Excel-related calculations or other statistical analyses on an array of data.
The customised value of Conditional Formatting in Excel
Conditional formatting is often overlooked by financial analysts during financial model development. It is a highly customiseable formatting tool, which can be applied in a variety of formatting ways.
Hence conditional formatting is the perfect tool to manage, the varying cell format requirements of ordinal numbers.
Practical business planning and financial modelling value of Conditional Formatting
The following spreadsheet and YouTube video show the practical value of conditional formatting, which will help to further enhance the aesthetic and intuitive value of a financial model.
Strategize Financial Modelling – Ordinal number format.xls
[youtube]http://youtu.be/7dfFJAPllKM[/youtube]
As discussed in the video, each ordinal number variation will require a separate conditional formatting rule. For instance, integer value “1” will require a rule for the cell format #,##0″st”, integer value “2” will require a rule for the cell format #,##0″nd” and so forth.
Important practical considerations with Conditional Formatting
As the above video highlights, it is vital to exercise strong attention to detail when establishing the conditional formatting of ordinal numbers. The conditional formatting rules must be uniform in the referencing of the cell range.
Financial model users must understand only the formatted range of cells will be impacted; any cells outside of the range will not reflect the ordinal number formatting. The conditional formatting will need to be copied across to these external cells.