How to Set a Rule in Excel

By Shea Laverty

i Courtesy of Microsoft

Conditional formatting can speed up spreadsheet analysis, as it applies visual changes to the sheet according to preset rules or conditions. For example, if you are calculating employee work hours, you can apply a conditional formatting rule to display in red employee timetables that lead to overtime. By adding conditional formatting rules, you can make a spreadsheet digestible at a glance.

Starting a New Rule

Step 1

Launch Excel and open the spreadsheet document you wish to format.

Step 2

Select the cells you wish to format.

Step 3

Click "Conditional Formatting" from the Styles group on the Home tab and select "New Rule."

Step 4

Select a rule type. At this point, the following sections discuss formatting based on the rule type you select.

Format All Cells Based on Their Values

Step 1

Select a format style. You can choose 2-Color Scale, 3-Color Scale, Data Bar and Icon Set.

Step 2

For 2-Color Scales, set the types for each end of the scale and assign the actual values for each end and a corresponding color for each.

Step 3

For 3-Color scales, set the types for each point in the scale and assign values and colors for each point.

Step 4

For Data Bars, set the type for the minimum and maximum values and assign actual values for each. Then configure the bar appearance based on fill-type, borders, colors and bar direction.

Step 5

For Icon Sets, choose an icon style, then select icons for the high, mid and low values. Configure the rules for each icon's appearance by determining when it is greater than or equal to a specified value, and the value type to be used.

Step 6

Click "OK" to apply the rule.

Format Only Cells That Contain Certain Values

Step 1

Select a metric for formatting. The available options are "Cell Value," "Specific Text," "Dates Occurring," "Blanks," "No Blanks," "Errors" and "No Errors."

Step 2

For Cell Value, select a value modifier and target values. Click the "Format" button to select a format for targeted cells.

Example: "Cell value between 1 and 10"

Step 3

For Specific Text, select a modifier for the target text, then define the target text. Click the "Format" button to select a format for targeted cells.

Example: "Specific text containing examples"

Step 4

For Dates Occurring, select a time period. Click the "Format" button to select a format for targeted cells.

Example: "Dates Occurring Next Month"

Step 5

For Blanks, No Blanks, Errors and No Errors, click the "Format" button to select a format for the targeted cells.

Step 6

Click "OK" to apply the rule.

Format Only Top or Bottom Ranked Values

Step 1

Select whether the rule will apply to the top or bottom values on the sheet.

Step 2

Enter the target range. If you're looking to calculate top or bottom percentage instead of direct values, check the "% of the selected range" check box.

Example: Top 100

Step 3

Click the "Format" button to select a format for targeted cells.

Step 4

Click "OK" to apply the rule.

Format Only Values That Are Above or Below Average

Step 1

Select the desired averaging metric for the selected cell range.

Step 2

Click the "Format" button to select a format for targeted cells.

Step 3

Click "OK" to apply the rule.

Format Only Unique or Duplicate Values

Step 1

Select either "duplicate" or "unique" for the selected cell range.

Step 2

Click the "Format" button to select a format for targeted cells.

Step 3

Click "OK" to apply the rule.

Use a Formula to Determine Which Cells to Format

Step 1

Enter the formula you wish to use to determine what cells to format.

Step 2

Click the "Format" button to select a format for targeted cells.

Step 3

Click "OK" to apply the rule.

References

Tips