Conditional formatting as a warning system

Define conditions for important project information or key figures so that the respective field or row is highlighted (e.g. colored) when the condition occurs. This function is particularly suitable for:

• Project controlling as an early warning system for critical projects.
• For optimal structuring of the project list

Add simple formatting

Simple formatting compares the value of a field with a stored reference value (condition). In the following example, we want to add a red background to the projects in the project list whose actual expenses are above a given value.

1. We start from the project list. On the Start tab, click Conditional formatting.
2. The Format Rule Manager opens. Here you can set a new rule for formatting your project list on the New Rule button.
3. Clicking on New Rule opens another window called New Formatting Rule. Here, select Format only cells that contain this as the rule type.
4. In the rule description, select whether you want to format according to a specific cell value or data within a specific range. For our example, we will choose the first option Cell Value.
5. In the drop-down menu of the field to the right of it, specify the condition. For our example, we choose Greater than. Another field appears in which you can enter the corresponding reference value, for our example, 5000.
6. In the Format area, you define, among other things, with which color the cell/row should be filled, e.g. red.
7. Once you have completely defined your formatting rule, confirm it by clicking OK.
8. You are now back in the Format Rule Manager. Here you will find checkboxes to apply the rule formatting to the entire line and to stop the rule. Here you can also select the category to which the rule is to be applied. For our example, select Total Expenses (ACTUAL).

Advanced formatting

Advanced formatting allows the mathematical calculation of a value, which can then be compared with a reference value. Several fields can serve as variables for such a calculation. In the following example, we will check whether the actual expenses of a project exceed the planned expenses.

1. We start from the project list. On the Start tab, click Conditional Formatting.
2. The Format Rule Manager opens. Here you can specify a new rule for formatting your project list on the New Rule button.
3. Clicking New Rule opens another window called New Formatting Rule. Here, select Use a formula to determine the cells to format as the rule type.
4. In the editing of the rule description you can create the desired conditions and link them using the operators "and", "or", "not and" and "not or".
5. Add a new condition on +. For our example, first select Sum expenses (ACTUAL). For the comparison operator, select greater than. As the comparison object, again select Total Expenditures (Planned). By clicking on the icon in the comparison object field you can switch between self-defined values and automatically generated values of other categories.
6. As with the simple formatting, you can now define in the Page panel in the Format area how the projects whose actual expenses are greater than the planned ones are to be highlighted. Afterwards, confirm your new formatting rule with OK.
7. You are now back in the Format Rule Manager. Here you will find checkboxes to apply the rule formatting to the entire line and to stop the rule.
8. In the project list, all projects that fulfill the condition are now highlighted.