Formatting Text in an Excel Template

There are a bunch of ways you can format text (i.e. bold, color, etc) in an RTF template but how about an Excel template?  You can not format text the same way in an Excel template as you did in an RTF template.  You have to resort to the native functionality of Excel.

The problem that I had was to have red text in a cell whenever another data element was blank (black when not blank).   A problem with Excel is that you cannot refer to another data element unless it is in the cell like you do in an RTF template. Another words that data element has to be brought into the spreadsheet.  The column can be optionally hidden.

So lets say we have two data elements called EFFECTIVE_DATE and END_DATE.  Whenever EFFECTIVE_DATE is null then we want to make END_DATE with red text.

END_DATE will be assigned to column J.  Because whenever a data element is referred to in Excel it has to be brought into the spreadsheet.  EFFECTIVE_DATE will be assigned at the end which is this case is column AW.

Of course the assignments are made in this manner using Define Name in Excel











Now to format the END_DATE when EFFECTIVE_DATE is blank.  Click on the Conditional Formatting icon from the Home menu while your cursor is sitting in cell J2.  Then click New Rule, then select the last rule type as shown then click OK.  Enter a formula that returns a boolean value of True or False.  In this case the function ISBLANK() returns True or False. Click OK.

The INDIRECT function points to a particular cell  by using the cell reference string.

The ROW() function in the formula returns the row number the cursor is in.  As the data is filled in the rows, the row number dynamically increases.  So the J column data elements will correspond to the AW data elements.

This solution is more of an exercise in the use of Excel  then anything else.