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.
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:
- B2: Enter text string
WorkArea. Apply a Bold font.
- Select cells B2 through D2. Apply a Fill Color to the selection.
- B3: Enter text string
- D3: Enter text string
True. This will default to a Boolean TRUE
- D3: Name the cell 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:
- Select cells B6 through D8, then Merge and Center the selection. Home > Merge & Center
- The merged cells are now identified by the upper left cell, B6 as shown in the Name Box.
- Apply a border. Home > Font > Borders > Thick Box Border
- Colour the border. Home > Font > click the Font Dialog Box then Border > Color > Green, Accent 6, Darker 25%. (See figure 2).
- Colour the background. Home > Font > Fill Color > Green, Accent 6, Lighter 60%
- B6: Enter the formula
=IF(OutOfData=TRUE,"Out of Data","")as shown in figure 3.
- Enlarge the font. Home > Font > Font Size > 18
To create the OUT of DATA alert range conditional setting:
- 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 Datafrom the value_if_true argument in the IF function in item 6. Then select the
Light Red Fill with Dark Red Textproperty. See figure 4.
- The OUT of DATA alert module is now complete
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.
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: Sunday 9th of October 2016 - 04:49 PM, [Australian Eastern Standard Time (EST)]