Easy Excel Conditional Formatting for Excel Dashboards
Conditional formatting tool is more flexible than other formatting tools such as alignment and color. Conditional formatting uses one’s own formula to boost important information provided in spreadsheet. The process enables a user to manage commands that in-built rules cannot. In conditional formatting, once given conditions are met, specified format is obtained. Excel conditional formatting enables a user push formats in spreadsheets more than their common uses.
Excel Formulas for Conditional Formatting Based on Cell Value
Pre-defined conditional formatting mainly format cells based on values that a user specifies or on their own values. Data bars, icon sets and other rules are available on conditional formatting tab. There is a need to use excel formulas whenever one wants to apply conditional formatting. The excel formulas can be applied in a cell or entire row with a value of a single cell. The conditional formatting feature can be found on Home tab under Styles group.
To create excel formula for conditional formatting you can:
1. Select cells or column to format. But in cases where you want to apply formatting in entire row, you can select several columns or entire table. Addition of more data can be done by converting cells into a table using Insert tab then go to Table, or by selecting some empty rows below one’s own data.
2. Click Conditional Formatting and then select New Rule.
3. Under the New Formatting Rule, select Use a formula tab to determine which cells to format.
4. Then at the corresponding box the formula can be entered.
5. To choose a custom format select the Format tab.
6. To play with options like font, style or fill color one can switch the Font, Border, and Fill tabs. Also one can choose a preferred color from RGB or HSL charts under More Colors button and click Ok.
7. Once the preview part displays desired format, the Ok button can be pressed to save the rule. In cases where results are not satisfactory, one may click Format and start editing.
When editing a conditional formatting formula it is advisable to always press F2 button, then using arrows navigate to the needed place within the formula. In adding certain cell F2 is pressed second time then the cell clicked.
Formulas to Compare Values
There are a number of ready to use rules available in excel to format equal to, less than, or greater than values. However when one wants some columns or entire rows conditionally formatted, he/she may use analogous formulas.
AND and OR Formulas
The AND and OR functions are basic conditional formatting formulas used in excel. The AND and OR functions are used to format excel table based on two or more conditions. This formula works with both text values and numbers. In the AND and OR function one may use 2, 3 or more conditions.
Formatting for Empty and Non-empty cell
To format empty and non-empty cells, select under a New Formatting Rule then select Format only cells that contain button and choose either Blanks or no Blanks part. In excel conditional formatting, cells may appear empty. In cases where one wants returns of an empty string but does not want such cells to be treated as blanks the following formulas can be used:
=isblank(A1)=true and isblank(A1)=false.