Excel Coloring Formula

Buzz

Ngày cập nhật gần nhất: 15/12/2025

Content

Excel Coloring Formula Guide

To set up cell coloring in Excel as desired, you need to know how to use Conditional Formatting and the coloring formula in Excel. This function helps you format based on the values of cells in the range you selected, determining which cells to color for you.

Conditional Formatting is a feature in Excel that helps you format data strings in cells, values in terms of color, background patterns, borders... When you apply this feature, it creates the usual formatting rules for cells in terms of color, text, and number display. However, when we remove Conditional Formatting, the default formatting of cells will be restored.

Learn Excel coloring formula

Excel Coloring Formula Guide

To use this feature, on the Home tab, under Styles, click Conditional Formatting.

At Conditional Formatting, you will have the following options:

- Highlight Cells Rules: Color cells with special values such as: highest, lowest, equal to, or between a range...
- Top/Bottom Rules: Color cells in the top 10, top 10% highest, lowest, or average group...
- Data Bars: Create corresponding bar charts with values in cells.
- Color Scales: Color correspondingly with values in cells.
- Icon Sets: Create icons corresponding to values in cells.
- New Rules: Allow creating new Rules as per your requirement.
- Clear Rules: Clear Rules in the selected cells or the entire sheet.
- Manage Rules: Open Conditional Formatting Rules Manager window, allowing you to create, delete, or edit Rules.
These are the options in Conditional Formatting, but this article will only focus on the coloring options: Highlight Cells Rules, Top/Bottom Rules, and New Rules.

1. Highlight Cells Rules

Here you have the following options:

- Greater Than...: Color values greater than ...
- Less Than...: Color values less than ...
- Between...: Color values in the range from ... to ...
- Equal To...: Color values equal to ...
- Text that Contains...: Color cells containing the character ...
- A Date Occurring...: Color values entered at a specific point in time.

- Duplicate Values...

2. Top/Bottom Rules

Here you have the following options:

- Top 10 Items...: Color the top 10 cells with the highest values.
- Top 10 %...: Color the top 10% cells with the highest values.
- Bottom 10 Items...: Color the bottom 10 cells with the lowest values.
- Bottom 10 %...: Color the bottom 10% cells with the lowest values.
- Above Average: Color cells with values greater than the average of the range.
- Below Average: Color cells with values less than the average of the range.

3. New Rule

When selecting New Rule, the New Formatting Rule window that appears will provide you with formulas to create new Rule formats as follows:

- Format all cells based on their values: Format all cells based on their results
- Format only cells that contain: Format only cells containing the values you specify.
- Format only top or bottom ranked values: Format only the highest or lowest values
- Format only values that are above or below average: Format values above or below the average.
- Format only unique or duplicate values: Format duplicate or unique values.
- Use a formula to determine which cells to format: Format cells based on your formula.
To clarify, follow the detailed instructions below:

Format all cells based on their values

In the Edit the Rule Description section, we can customize as follows:

In the Format Style section, you have 2 options for coloring: 2 colors or 3 colors.
In the Type section, you can choose the format:

For the smallest and largest value format: Select Lowest Value and Highest Value.

For formatting as a number, date, or time value: Select Number and enter your value.

For formatting as a percentage: Select a value from 0 to 100.

For formula formatting: The formula you choose must return a clear result such as a number, date, or time value.

For percentage group formatting: The reasonable value is from 10 to 90. Values in the same percentage group will have the same color.

In the Color section, choose the color for the smallest and largest values.

After selecting, press OK to apply the new color formula to your data as shown in the example below.

Format only cells that contain

- Cell Value: Color based on the value of the cell.

+ between: Color values between a certain range.
+ not between: Color values outside a certain range.

+ equal to: Color cells with a value equal to a certain value.
+ not equal to: Color cells with a value different from a certain value.
+ greater than: Color cells with a value greater than a certain value.
+ less than: Color cells with a value less than a certain value.
+ greater than or equal to: Color cells with a value greater than or equal to a certain value.
+ less than or equal to: Color cells with a value less than or equal to a certain value.

- Specific Text: Color based on specific characters.

+ containing: Color cells containing specific characters.
+ not containing: Color cells not containing specific characters.
+ beginning with: Color cells beginning with specific characters.
+ ending with: Color cells ending with specific characters.

- Dates Occurring: Color based on the time value occurring in the spreadsheet.

- Blanks: Color empty cells.
- No Blanks: Color non-empty cells.
- Errors: Color cells with errors.
- No Errors: Color cells without errors.
Format only top or bottom ranked values
- Top: Color the top ranked values.
- Bottom: Color the bottom ranked values.

In the empty cell next to it, enter the number of values you want to color.

% of the selected range: Color the percentage values within the range you choose to color.

Format only values that are above or below average

Color cells based on the average value of the selected range:

Format only unique or duplicate values:

Color cells based on value:

- duplicate: identical within the selected range.
- unique: not identical within the selected range.

Use a formula to determine which cells to format:

Use a formula to format the cell you want to color. For example, using a formula to color alternate rows in Excel, alternating colors will help the data display correctly as required by the task or assignment.

In addition to the Format all cells based on their values, to select colors for the remaining Rule formats, do the following:

Step 1: In the Preview, open Format.

Step 2: Open the Fill tab. Here, select the color you want to fill the cell with, then press OK.

After selecting the format and color for the Rule, click OK to apply the format. In the example below, I chose to color 3 cells with the highest values above the average.

I got the result as shown below.

Edit applied Rule attributes

To change the applied Rule, do the following:

Step 1: Open Conditional Formatting, select Manage Rules.

Step 2: In the Conditional Formatting Rules Manager window, select the Rule you want to edit, then click Edit Rule.

Step 3: In the Edit Formatting Rule window, customize the color formatting as needed, then click OK to save the changes.

Step 4: Click OK to apply the edited Rule.

And here is the spreadsheet after changing the Rule.

Delete a created Rule in the spreadsheet

To delete a created Rule, open Conditional Formatting, select Clear Rules, you will see the following 2 options:

- Clear Rules from Selected Cells: Delete Rules from the selected cells.
- Clear Rules from Entire Sheet: Delete all Rules in the spreadsheet.

Choose the option that suits you, the Rule will be deleted immediately depending on your choice. In the example below, I chose to delete all Rules in the spreadsheet.

Above is the article on coloring formulas in Excel by Mytour. Hopefully, after this article, you can easily format Rules to create the necessary coloring formulas for your work.

Frequently Asked Questions

What is the main purpose of using Conditional Formatting in Excel?

The main purpose of Conditional Formatting in Excel is to enhance data visualization by applying specific formatting rules based on cell values. This allows users to quickly identify important trends and outliers in their data, making it easier to analyze and interpret information effectively.

How do you access the Conditional Formatting feature in Excel?

To access Conditional Formatting in Excel, go to the Home tab on the ribbon. Under the Styles group, you will find the Conditional Formatting option. Clicking this will reveal a menu with various formatting options that you can apply to your selected cells.

What types of rules can be applied using Highlight Cells Rules in Excel?

Highlight Cells Rules in Excel allow users to color cells based on specific criteria. Options include formatting cells greater than, less than, or equal to a certain value, as well as formatting cells that contain specific text or duplicate values. This feature aids in quickly identifying key data points.

How can you delete a created Conditional Formatting rule in Excel?

To delete a created Conditional Formatting rule in Excel, navigate to the Conditional Formatting menu and select 'Clear Rules.' You can choose to clear rules from selected cells or from the entire sheet, which will immediately remove the specified formatting.

What options are available under the New Rule feature in Conditional Formatting?

Under the New Rule feature in Conditional Formatting, users can format cells based on various criteria, such as formatting all cells based on their values, unique or duplicate values, or using formulas to determine cell formatting. This flexibility allows for customized data presentation.

How do you format cells based on their values using Conditional Formatting?

To format cells based on their values, select the desired range and choose 'New Rule' in Conditional Formatting. Then, select 'Format all cells based on their values' and specify the criteria and color formats in the dialog box to apply the desired formatting.

Mytour's content is for customer care and travel encouragement only, and we are not responsible.

For errors or inappropriate content, please contact us at: [email protected]