Conditional Formatting Data Bars Different Colors

We have a dataset that depicts the ongoing project status in percentage. We want conditional formatting data bars in different colors.

Dataset-Conditional Formatting Data Bars Different Colors

Apply Conditional Formatting Data Bars with Different Colors: 3 Easy Ways

For different colored data bars, the typical insertion of the Conditional Formatting Data Bar won’t work. The typical Data Bar allows users to choose only one shade of color. To display data bars in different or multiple colors, users need to use formulas and functions. The coloring typically indicates the percentages, such as green indicates >= 75%, blue between >= 50% and < 70%, and red >=0% and < 50%.

Method 1 – Using Formulas for Conditional Formatting with Data Bars in Different Colors

Steps:

Find the remainders of work percentages with the following formula in cell G6, then drag the Fill Handle.

=1-F6

Formula-Conditional Formatting Data Bars Different Colors

Select the new range.
Go to Home and Conditional Formatting, then choose New Rule.

New Rule

The Edit Formatting Rule window appears.
Choose the Use a formula to determine which cell to format rule under Select a rule type.
Use the following formula under Edit the Rule Description.

=F6>=75%

Click on Format.

Edit Formatting Rule

The Format Cells window appears.
Click on Fill.
Choose the desired Fill color (i.e., green for >=75%).
Click on OK.

Format Cells

Excel returns to the Edit Formatting Rule window.
Click on OK.

Edit Formatting Rule

Add a New Conditional Formatting Rule for another 2 formulas. Replace each formula with the below formulas under Edit the Rule Description and use a different fill color.

=AND(F6>=50%,F6<75%)

=AND(F6>=0%,F6<50%)

Here’s an overview of the Rules Manager.

Other Formulas

Here’s the result.

Color applied-Conditional Formatting Data Bars Different Colors

Select the formatted range.
Go to Conditional Formatting and New Rule. Select the options as depicted in the image below and click OK.

Data Bar

Applying the Data Bar Format Style displays values as colored data bars.

Final Depiction

Method 2 – Using the REPT Function to Display Different Colors Data Bars

Steps:

Insert the following formula into G6, then AutoFill the column.

=REPT("█",F6*10)

The formula repeats the solid black block based on the value in the F cell.

REPT function-Conditional Formatting Data Bars Different Colors

If you want to insert the symbol manually, go to Insert > Symbols (in the Symbols section). Choose Arial as Font, and Block Elements as Subset. Find a Full Block.

Insert Symbol