Changing the color of cells in Excel based on their values is a useful way to visualize data and highlight important values. This can be done through conditional formatting, which allows you to apply formatting to cells that meet specific criteria.
Use Color Scales
The easiest way to quickly color cells based on their values is by using a color scale. This automatically shades the cells with a color gradient that goes from a min color to a max color. For example, you could shade low values red and high values green.
Here are the steps to apply a color scale:
- Select the cells you want to format
- On the Home tab, go to Styles and click on Conditional Formatting
- Select the Color Scales option and choose the color scale you want
This will automatically color the selected cells with a gradient from red to green or whichever colors you chose. You can customize the color scale to use 2 or 3 colors and change the min/max values.
Use Color Rules
For more customized coloring, you can apply rules that color cells a specific color if they meet a condition. For example, color values greater than 50 green, values less than 20 red, etc.
Here are the steps to create a color rule:
- Select the cells to format
- On the Home tab, open Conditional Formatting and select New Rule
- Select Format only cells that contain
- Set the rule criteria (value greater than, less than, between, etc)
- Choose the fill color to apply
- Click OK to apply the rule
You can create as many rules as needed to color cells different colors for different value ranges. This provides more control than color scales.
Use Data Bars
Data bars are horizontal bars that fill each cell proportionally based on the cell’s value. The longer the data bar, the higher the value. They provide a quick visual length-based indicator of values.
To apply data bars:
- Select the cells to format
- On the Home tab, open Conditional Formatting and select Data Bars
- Pick a data bar fill style and colors
Data bars are useful for quickly identifying higher and lower values just by bar length. You can combine them with color rules for additional visual indicators.
Use Icon Sets
Icon sets display icons in each cell based on the cell’s value. Low values get red down arrows, mid values get yellow sideways arrows, and high values get green up arrows. This conveys value ranges instantly using visual icons.
To apply icon sets:
- Select the cells to format
- On the Home tab, open Conditional Formatting and pick Icon Sets
- Choose an icon style to apply
Icon sets make scanning for high, mid, and low values quick and easy. You can combine them with color rules for additional highlighting.
Sample Uses
Here are some common ways to use conditional formatting to color cells by value:
- Color code profit/loss – Green for profits, red for losses
- Highlight top performers – Green for top 10 salespeople
- Flag unreasonable values – Red for values outside expected range
- Temperature heat map – Color code low to high temps
- Inventory alerts – Red for low stock items
Coloring by rules gives you tons of flexibility. You can combine colors, icons, and data bars to create informative visual indicators.
Tips
- Pick easily distinguished colors – Hard to tell shades can confuse
- Don’t overdo it – 1 or 2 formats is often clearest
- Be consistent – Use same rules/colors to mean same things
- Test rules – Adjust criteria to highlight the right values
- Label colored cells – Helps identify what color means
Conclusion
Conditional formatting with color scales, data bars, icon sets, and color rules provides a simple way to highlight values and improve Excel data visibility. Just remember to choose clear colors and icons and don’t over-format. Used properly, coloring by rules can help quickly convey value differences, outliers, and trends.
With some thoughtful rules, you can transform your Excel data from boring to colorful and insightful. Condional formatting brings data to life!
