Color coding data in Excel tables can be a great way to visualize and highlight important information. Using conditional formatting, you can automatically color cells or entire table rows based on the values they contain. This allows you to easily see patterns, trends, and outliers in your data at a glance.
In this post, we’ll walk through the steps for coloring Excel tables by value. We’ll cover:
- Creating a table in Excel
- Applying basic conditional formatting rules
- Color scales for visually representing data
- Data bars and icon sets for at-a-glance insights
- Using formulas for more advanced conditional formatting
- Tips for effectively using color in tables
Follow along below to learn how to leverage the power of color formatting in Excel tables for better data visualization and analysis.
Create a Table in Excel
Before applying any coloring, you’ll first want to convert your Excel data into an official Excel Table. Here are the steps:
- Select your data set, including column headers.
- Go to the Insert tab and click the Table button.
- Check the My table has headers box in the Create Table dialog box.
- Click OK to create the table.
Creating an Excel Table applies some automatic formatting and provides access to Table-specific features like conditional formatting. It also maintains the table structure even when adding or removing data.
Apply Basic Conditional Formatting
The most straightforward type of conditional formatting is where you manually specify the formatting rules. For example, make all values greater than some threshold red, all values less than another threshold green, and so on.
Here are the basic steps:
- Select the cells/table you want to format.
- On the Home tab, open the Conditional Formatting dropdown and select New Rule.
- In the New Formatting Rule dialog, select the type of rule you want to create.
- Specify the formatting details like font, fill color, borders, etc.
- Click OK to apply the rule.
For example, to highlight any cells in a table greater than 100, you would:
- Select the full table.
- New Conditional Formatting Rule > Format only cells that contain.
- Set Cell Value to Greater Than, Value to 100.
- Format desired fill color like red.
- OK to apply.
You can create multiple rules to build up the desired formatting. Just be sure to think through precedence if rules overlap.
Use Color Scales for Visual Comparison
Color scales are a good option for visually representing relative values in table data. Low to high values are assigned colors ranging from red to yellow to green. This makes it easy to see higher and lower values at a glance.
Steps to apply a color scale:
- Select table cells to format.
- New Conditional Formatting Rule > Color Scales > desired scale.
- The color scale will be applied automatically.
A 2-color scale grades from red to green. A 3-color scale adds yellow in the middle. The more colors, the finer the gradient from min to max values.
You can customize the colors and percentile thresholds as needed. Just keep colors visually distinct and meaningful for your data.
Use Data Bars for Quick Visual Cues
Data bars are horizontal bars directly applied to each cell, with the bar length proportional to the cell’s value. Like color scales, they allow quick visual comparison of relative values.
To apply data bars:
- Select the table cells.
- New Conditional Formatting Rule > Data Bars > desired options.
- Data bar colors, lengths, gradients, etc. will update automatically.
Data bars work best with positive values on a meaningful scale. You can customize details like fill color, borders, direction, and negative value handling as needed.
Use Icons for At-a-Glance Insights
Icon sets display a small graphic in each cell representing the cell’s value. A common use is applying “stoplight” style icons to highlight values falling in low, medium, and high buckets.
Here is how to apply icon set conditional formatting:
- Select the table cells.
- New Conditional Formatting Rule > Icon Sets > select an icon style.
- Set thresholds between icons as desired.
- The icons will populate automatically.
Icon sets work great for quick visual insights on categorical data. You can customize the icons and threshold values to fit your data ranges.
Use Formulas for Advanced Formatting
For more complex conditional formatting, you can apply rules based on formulas. This allows unlimited flexibility to format exactly the cells you want.
To use formulas in conditional formatting:
- Select cells to format.
- New Conditional Formatting Rule > Use a formula to determine which cells to format.
- Enter the desired formula in the field.
- Specify the formatting to apply.
- Ensure formula evaluates to either TRUE or FALSE.
- OK to apply the rule.
For example, coloring cells red where values are above the table’s average:
- Select the full table.
- Use a formula: =C2>AVERAGE($C$2:$C$100)
- Set Fill color to Red.
- OK to apply.
The conditional formatting will apply only where the logical test evaluates to TRUE.
Tips for Effective Use of Color
When applying color conditional formatting to Excel tables, keep these tips in mind:
- Use highly contrasting colors – light vs dark provides separation.
- Avoid red/green together as 10% of men are colorblind.
- Consider color meaning – red for bad, green for good, etc.
- Don’t go overboard – 1-3 colors usually sufficient.
- Test colors against gray backdrop – formatting should stand out when printed.
- Order rules logically – precedence matters if overlap.
- Explain coloring in legends for clarity.
- Make sure formatting highlights most relevantvariation.
By following best practices with colors, you can create more meaningful and effective conditional formatting in Excel tables.
Example Tables
Here are some example tables demonstrating how to color code data by values in Excel:
Color Scale Table
| Product | Revenue |
|---|---|
| Product A | $10,000 |
| Product B | $20,000 |
| Product C | $30,000 |
| Product D | $50,000 |
| Product E | $80,000 |
This table uses a red-yellow-green color scale to highlight high and low revenue values.
Data Bar Table
| Month | Sales |
|---|---|
| January | $50,000 |
| February | $80,000 |
| March | $110,000 |
| April | $140,000 |
| May | $180,000 |
This table uses red data bars to visually represent sales trends over time.
Icon Set Table
| Employee | Rating |
|---|---|
| John | Excellent |
| Jane | Good |
| Jim | Average |
| Jack | Poor |
This table applies star icons to highlight performance rating buckets.
Advanced Conditional Formatting Options
Excel provides several additional options for advanced conditional table formatting:
- Format all cells based on their values – Apply formatting based on cell ranges and percentiles.
- Format only top or bottom ranked values – Highlight top or bottom n values or percent.
- Format only values that are above or below average – Compare to mean.
- Format only unique or duplicate values – Highlight distinct or repeated values.
- Use a formula to determine which cells to format – Custom formula-based rules.
These options enable nuanced conditional formatting tailored to your specific data set. You can combine multiple advanced rules together for even more customization.
Managing Conditional Formatting Rules
When applying multiple conditional formatting rules, it helps to manage them systematically. Here are some tips:
- Give each rule a descriptive name under Manage Rules to track precedence.
- Check the Show Formatting Rules for This Table box under Table Style Options to view currently applied rules.
- Edit rules anytime by selecting Manage Rules and choosing the rule to modify.
- Clear formatting but keep rules by selecting Clear Rules > Clear Rules from Entire Table (preserve formatting).
- Start over by selecting Clear Rules > Clear Rules from Entire Table.
Taking the time to properly structure and document your conditional formatting rules will make them much easier to understand, modify, and troubleshoot.
Troubleshooting Color Formatting
Here are some common issues that can occur when working with colored tables in Excel:
- Overlapping rules lead to unexpected formatting – Carefully order by precedence.
- Rules stop applying correctly when new data added – Verify table references are absolute.
- Colors print differently than displayed – Check colors against gray backdrop.
- Colors appear less distinct when projected – Use high contrast colors.
- Too many colors applied – Limit to 1-3 colors with clear meaning.
- File size rapidly increases – Remove duplicate or unused rules.
- Formatting is slow to calculate – Switch to conditional formats like color scales.
Troubleshoot your specific issues and fine-tune your conditional formatting for optimal Excel table colors.
Sharing Color Formatted Tables
To share a color formatted Excel table with others, you have a few options:
- Send the entire formatted workbook – Recipients see tables and formatting.
- Export table formatting – Save just the table styles to apply to other books.
- Copy paste values only – Removes formatting but maintains data.
- Print table with colors – Ensure colors are optimized for grayscale.
- Take screenshot – Quick way to share visualization.
When sharing workbooks, be mindful that recipients will need the source file to modify conditional formatting rules or underlying data.
Conclusion
Color coding your Excel tables by value is a great way see your data in a whole new light. Conditional formatting rules give you precise control to highlight trends, outliers, thresholds, top/bottom values, and more. Use color intentionally to draw insights from your data through visualization.
The key is applying these techniques in a way that enhances understanding for you and your audience without becoming overwhelming. A bit of color goes a long way in Excel tables!
