Conditional Formatting Tab in the New View Window
In addition to using the Filters tab to create views that display specific data, you can use the Conditional Formatting tab to format columns based on specific conditions. This feature helps you visually identify rows within the column that match specific criteria. You can use different formats, such as background fill, font color, icons, and data bar, to customize the visual identification. You can format up to 10 columns in a sheet and use up to 5 format conditions for each column.
You can also apply conditional formats at the cell level, which lets you identify amounts or decimal-based values that match specific conditions. For example, you can use conditions to highlight amounts where the Total Cost Incurred is greater than the Forecasted amount for specific cost codes. By using a condition such as Background Fill & Color, you can access your custom view and immediately identify where total costs have exceeded what was expected (forecasted). Or you might want to highlight approved contracts that exceed $500,000 or are valued at less than $50,000, which lets you visually identify the approved contracts with high or low (or both) values.
When creating conditions, be aware of the following:
- You can only apply conditions to columns and fields that you have permission to access. Hidden, restricted, and deleted columns are not shown. If a column becomes available after you create a view and set up conditional formatting, you must update the view to add the previously restricted or hidden column.
- If a column that you can view when you create a condition subsequently becomes hidden or restricted, or is deleted, the system automatically updates the information shown to you when you select the view or its settings. Similarly, if the column becomes visible to you again because it is no longer hidden or restricted, the system automatically updates your customized view and settings. However, if you update and the customized settings before the column display is reinstated, the system permanently removes the applicable column from the customized view; you must manually update the view to add it.
- If multiple conditions of a column are true, the conditions are applied in the order in which you created and applied them; that is, the condition that you specified first is applied first, and so on.
- If none of the conditions are met, the display of the Cost Sheet remains unchanged; no message is displayed that indicates no conditions were met.
- If you intend to print the Cost Sheet or export it to Microsoft Excel:
- You must enable support for background graphics in your browser before printing the sheet.
- Formatting is not retained during an export to Microsoft Excel, so viewing the conditions in Excel is not supported.
To create a condition:
- Click + Add Column.
- Select a Column.
The list contains all columns available to you in the Default view. Hidden, restricted, and deleted columns are not shown. Conditional Formatting is applied to the selected column.
- Select a Format Type.
- Click Add Format Condition (the plus [+] sign) to specify the elements for which you want to use different formatting.
- Do the following:
- Choose a Data Element. This list contains all columns available to you in the Default view. Restricted and hidden columns are not listed. The validation process to determine whether the condition is met is processed on the selected column.
- Choose a Condition. The conditions listed are based on the type of data element (DE) selected. For more information, see Condition options.
- Choose a Value. Depending on the type of DE, choose a value that the query condition must meet. If you chose a Condition such as is empty, the Value field does not apply.
- Choose a Format. The options in this list depend on the selected Format Type.
- If you chose Background Fill & Color or Font Color, select the applicable color. The listed colors are the only options available; they cannot be customized for use.
- If you chose Data Bar, Format displays a read-only Data Bar selection. Data Bar assumes that the associated Column contains numeric values, including negative values. A Data Bar is not displayed for summary records or totals. After the Conditional Format is applied, the selected Column is replaced with data bars whose length is proportional to the percentage value with regard to the maximum value. The values are printed inside their respective bars. Negative values are represented by red bars.
- If you chose Icon Set, Format displays a read-only Icon Set selection that uses a fixed representation. Positive values are reflected by a green Up Arrow, negative values by a red Down Arrow, and zero values by a yellow Horizontal Arrow.
Note: Because you can create multiple conditions for the same column, conditions are applied in the order in which you create them; after the first condition is satisfied, the remaining conditions are ignored.
- To add additional columns, click Add Column again, and repeat the preceding steps.
Last Published Monday, October 7, 2024