How to Color Code Your Analysis Output Numbers in Excel

Excel is a powerful tool for data analysis, but sometimes it can be hard to see the patterns and trends in your numbers. One way to make your analysis output more visually appealing and easier to understand is to use conditional formatting to color code your numbers based on certain criteria. Conditional formatting is a feature that allows you to apply different formats, such as colors, fonts, icons, or data bars, to cells that meet specific conditions. For example, you can use conditional formatting to highlight the highest or lowest values in a range, or to show the difference between actual and budgeted amounts.

In this article, we will show you how to use conditional formatting in Excel to color code your analysis output numbers based on some common scenarios. We will also explain how to create custom conditional formatting rules using formulas, and how to manage and edit your existing rules.

How to Apply a Quick Conditional Formatting Color Scale

One of the easiest ways to color code your numbers in Excel is to use a predefined color scale. A color scale is a type of conditional formatting that applies a gradient of colors to your cells based on their values. For example, in a green, yellow, and red color scale, you can specify that higher value cells have a green color, middle value cells have a yellow color, and lower value cells have a red color. This can help you quickly identify the best and worst performers in your data set.

To apply a quick conditional formatting color scale, follow these steps:

Select the cells that you want to apply the formatting to by clicking and dragging through them.
Go to the Home tab on the ribbon and click Conditional Formatting in the Styles group.
Move your cursor to Color Scales and choose one of the 12 options in the pop-out menu. You can see a preview of each option as you hover over it, as well as the cells that you have selected highlighted with each option.
Click on the option that best suits your data and preference.

You have now applied a color scale to your data in just a few clicks. You can see how the colors change depending on the values in your cells.

How to Create a Custom Conditional Formatting Color Scale

If none of the predefined color scales match your needs, you can create your own custom conditional formatting rule using a color scale. This gives you more flexibility and control over how your data is displayed.

To create a custom conditional formatting color scale, follow these steps:

Select the cells that you want to apply the formatting to by clicking and dragging through them.
Go to the Home tab on the ribbon and click Conditional Formatting in the Styles group.
Choose New Rule from the drop-down list.
In the New Formatting Rule window that opens, select Format All Cells Based on Their Values at the top.
In the Edit the Rule Description section at the bottom of the window, choose 2-Color Scale or 3-Color Scale from the Format Style drop-down list. The main difference between these two styles is that the three-color scale has a midpoint, whereas the two-color scale only has minimum and maximum values.
For each point (minimum, maximum, and optionally, midpoint), choose the Type from the drop-down list. You can pick from Lowest/Highest Value, Number, Percent, Formula, or Percentile. The Lowest Value and Highest Value types are based on the data in your selected range of cells, so you don’t have to enter anything in the Value boxes.
For each point (minimum, maximum, and optionally, midpoint), choose the Color from the drop-down list. You can pick from one of the standard colors or use More Colors to select a custom color using RGB values or Hex codes.
You can see a preview of your color scale at the bottom of the window. If you are happy with the result, click OK to apply the conditional formatting to your cells.

You have now created a custom conditional formatting color scale for your data.

How to Use Conditional Formatting with Formulas

Another way to color code your numbers in Excel is to use conditional formatting with formulas. This allows you to apply different formats based on complex criteria that are not available in the predefined options.

To use conditional formatting with formulas, follow these steps:

Select the cells that you want to apply the formatting to by clicking and dragging through them.
Go to the Home tab on the ribbon and click Conditional Formatting in the Styles group.
Choose New Rule from the drop-down list.
In the New Formatting Rule window that opens, select Use a Formula to Determine Which Cells to Format at the top.
In the Edit the Rule Description section at the bottom of the window, enter your formula in the Format Values Where This Formula Is True box. The formula should return TRUE or FALSE for each cell in your selected range. You can use any valid Excel formula that references other cells or values.
Click Format to open the Format Cells dialog box where you can choose the format that you want to apply to the cells that meet your formula criteria. You can select from the Number, Font, Border, or Fill tabs to customize your format.
Click OK to close the Format Cells dialog box and then click OK again to close the New Formatting Rule window and apply the conditional formatting to your cells.

You have now used conditional formatting with formulas to color code your numbers in Excel.

How to Manage and Edit Conditional Formatting Rules

Once you have applied conditional formatting to your cells, you may want to change, delete, or copy your rules. You can do this using the Conditional Formatting Rules Manager.

To access the Conditional Formatting Rules Manager, follow these steps:

Select any cell or range of cells that has conditional formatting applied to it.
Go to the Home tab on the ribbon and click Conditional Formatting in the Styles group.
Choose Manage Rules from the drop-down list.
In the Conditional Formatting Rules Manager window that opens, you can see all the rules that apply to your selected cells or to the entire worksheet. You can use the Show Formatting Rules For drop-down list to switch between different scopes.
To edit a rule, select it from the list and click Edit Rule. This will open the Edit Formatting Rule window where you can change the rule type, criteria, or format as described in the previous sections.
To delete a rule, select it from the list and click Delete Rule. This will remove the rule and its format from your cells.
To copy a rule, select it from the list and click New Rule. This will open the New Formatting Rule window where you can create a new rule based on the existing one. You can then change the rule type, criteria, or format as desired.
To change the order of rules, select a rule from the list and use the Move Up or Move Down buttons to adjust its position. The order of rules is important because Excel applies them in sequence from top to bottom. If two or more rules conflict with each other, only the first one will take effect.
To clear all rules from your selected cells or worksheet, click Clear Rules and choose Clear Rules from Selected Cells or Clear Rules from Entire Sheet.

You have now learned how to manage and edit your conditional formatting rules in Excel.

Conclusion

Color coding your analysis output numbers in Excel can help you make your data more attractive and understandable. You can use conditional formatting to apply different formats based on various criteria, such as color scales, formulas, or predefined options. You can also create custom rules, edit existing rules, or manage multiple rules using the Conditional Formatting Rules Manager.

We hope this article has helped you learn how to color code your numbers in Excel using conditional formatting. If you have any questions or feedback, please let us know in the comments below.

¹: https://support.microsoft.com/en-us/office/use-conditional-formatting-to-highlight-information-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f ²: https://www.howtogeek.com/734938/how-to-apply-a-color-scale-based-on-values-in-microsoft-excel/ ³: https://breakingintowallstreet.com/kb/excel/how-to-color-code-in-excel/ ⁴: https://www.laptopmag.com/articles/conditional-formatting-excel-color-code-cells

How to Color Code Your Analysis Output Numbers in Excel