Out of data alert

Create an Out of Data alert using conditional formatting


The data set for the Stock Analyser project may include companies that have listed or delisted during the analysis period. Alerting the user to situations when the analysis window includes Out of Data events adds a power boost to the Stock Analyser functionality.


In the xlfAnimatedWorksheets shown in figure 1, an Out of Data switch in cell D3 triggers content and format changes of an alert range in B6:D8. Note: Assume that Page > Layout > Themes > Theme > Office is set, because the colours in this module are from that Theme.


Out of Data
Fig 1: [Out of Data] - xlfAnimatedWorksheets


1. Create the OUT of DATA switch


The switch is simply a place holder for the return value of logical functions elsewhere in the workbook.


To create the OutOfData switch:

  1. B2: Enter text string WorkArea. Apply a Bold font. Home > Font > Bold

  2. Select cells B2 through D2. Apply a Fill Color to the selection. Home > Font > Blue, Accent 5, Lighter 80%

  3. B3: Enter text string OutOfData

  4. D3: Enter text string True. This will default to a Boolean TRUE

  5. D3: Name the cell OutOfData. Formulas > Defined Names > Define Name > Name OutOfData


2. Create the OUT of DATA alert range


In this module, the alert range is blank when data is available. However, the blank range has a coloured background to deter users from inadvertently overwriting the cell formula. To enhance the alert range, its size is increased by merging some cells and then applying a format.


To create the OUT of DATA alert range default setting:

  1. Select cells B6 through D8, then Merge and Center the selection. Home > Merge & Center

  2. The merged cells are now identified by the upper left cell, B6 as shown in the Name Box.

  3. Apply a border. Home > Font > Borders > Thick Box Border

  4. Colour the border. Home > Font > click the Font Dialog Box then Border > Color > Green, Accent 6, Darker 25%. (See figure 2).


    5. Format Cells
    Fig 2: Format cells - border colour

  5. Colour the background. Home > Font > Fill Color > Green, Accent 6, Lighter 60%

  6. B6: Enter the formula =IF(OutOfData=TRUE,"Out of Data","") as shown in figure 3.


    7. Alert Default
    Fig 3: The out of data IF statement in cell B6

  7. Enlarge the font. Home > Font > Font Size > 18

To create the OUT of DATA alert range conditional setting:

  1. Select cell B6: Apply a conditional format. Home > Styles > Conditional Formatting > Highlight Cell Rules > Text That Contains > Format cells that contain the text: Enter the text string Out of Data from the value_if_true argument in the IF function in item 6. Then select the Light Red Fill with Dark Red Text property. See figure 4.


    2. Text that Contains
    Fig 4: Conditional format - for "Text That Contains"

  2. The OUT of DATA alert module is now complete

3. Comments


A cell can have more than one conditional format. To manage the condition format rules, for the Current Selection or This Workbook, click - Home > Styles > Conditional Formatting > Manage Rules > Conditional Formatting Rules Manager in figure 5.

Text that Contains
Fig 5: Conditional formatting - rules manager

Finally, the Out of Data switch in the range OutOfData needs to be linked to the a set of logical formulae. Normally, IF, AND or OR, and possibly NOT are all that is necessary.


  • A copy of the Excel file for this module: outofdata.xlsx
  • This example was developed in Excel 2013 Pro 64 bit.
  • Revised: Saturday 25th of February 2023 - 09:37 AM, [Australian Eastern Standard Time (EST)]