Want a quick way to view and interpret data directly in the tables without resorting to graphs or charts? You can add color bars, icons or graphics that reflect the meaning of the data in each cell to get an “at a glance” view of your data.
Conditional formatting means that the formatting applied to the cells changes dynamically based on conditions you specify. Excel provides several options for graphic and color formatting that make reviewing your sheet more efficient.
Use Data Bars, Color Scales and Icon Sets
- Select the cells to format.
- Click the Conditional Formatting button in the Styles group on the Home tab to open the Conditional Formatting menu.
- Select one of the Rules options:
- Data Bars: Shades cells with a gradient based on their comparative values (higher numbers have longer bars).
- Color Scales: Shades cells with colors based on their comparative values (one color for low values, one for high and—optionally—one for mid-range).
- Icon Sets: Adds an icon to cells based on their comparative values by as many as five measurements.
Create Custom Rules from Scratch
Use this method when none of the preset rules and formats meet your conditional formatting needs.
- Select the cells to which you want to apply conditional formatting.
- Open the New Formatting Rule dialog box by selecting New Rule from the Conditional Formatting dropdown box in the Styles group on the Home tab.
- Select the type of rule to use as a template from the Select a Rule Type: pane.
- Adjust the rules and formatting options that appear in the Edit the Rule Description pane.
- Click the OK button to apply the rule.
Hot Tip: See Highlight Cells Based on Specific Criteria to learn how to remove and edit conditional formatting rules.