Customize Excel Conditional Formatting
Excel Conditional Formatting already lets you format cells based on the value of those cells or the value of the formulas in those cells (see our conditional formatting tutorial for more details). Now we’ll show how you can customize these features so you (and others) can quickly scan your spreadsheet and determine at a glance what the data means based on the way each column, row, cell, or range is formatted.
Here’s how to find the customization options. In any spreadsheet, choose Home > Conditional Formatting New Rule. The New Formatting Rule dialog window opens.
Notice the first panel: Select a Rule Type. We’ll go through the options in turn:
- Format all cells based on their values
- Format only cells that contain
- Format only top or bottom ranked values
- Format only values that are above or below average
- Format only unique or duplicate values
- Use a formula to determine which cells to format
For this tutorial we’ve created a simple spreadsheet showing the sales figures for a team over the months of January through April.
Format all cells based on their values:
Colour Scales
Highlight column B (Jan Sales Totals) and choose Home > Conditional Formatting > New Rule.
Select the first option, Format all cells based on their values.
In the lower panel, Edit the Rule Description, there are four options under Format All Cells Based on Their Values.
Under Format Style, select 2-Color Scale or 3-Color Scale. You can customize the Minimum and Maximum or Minimum, Midpoint, and Maximum values, respectively.
Under Type > Minimum, Midpoint, Maximum, select Lowest, Highest, Number, Percent, Formula, or Percentile based on how you’d like to see the numbers in your database grouped.
Choose a value for the Number, Percent, Formula, or Percentile.
Choose the Colors, and then click OK.
Format cells based on values and a colour scale.