Typically, Excel Conditional Formatting formats data bars in a single color. However, in some cases, users may need to display **Conditional Formatting data bars** in different colors. Formulas entwined with data bars, repeating block symbols using the **REPT **function, and contrasting data insert data bars with different colors.

Letâ€™s say we have a dataset that holds ongoing project status in percentage. We want conditional formatting data bars in different colors.

In this article, we demonstrate formulas laced with the **Data Bar **feature, the **REPT **function, and the plain **Conditional Formatting Data Bar** feature to display different multiple colored data bars.

**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. Because the typical **Data Bar** feature allows users to choose only one shade of color in its depiction. To display data bars in different or multiple colors, users need to use formulas and functions. Also, the coloring indicates the **percentages,** such as **green** indicates **>= 75%**, **blue** between **>= 50%** and **< 70%**, and **red >=0%** and **< 50%**. Follow any of the methods below to conditionally format data bars in different colors.

**Method 1: Using Formulas to Conditional Formatting Data Bars with Different Colors**

First, users have to apply **Conditional Formatting**, assigning multiple formulas to display cells filled with colors. Then, inserting data bars from opposite directions results in multicolored data bars.

**Step 1:** Find the rest of the work percentages using deduction. Type the following formula in cell **G6,** then drag the **Fill Handle**.

`=1-F6`

**Step 2:** Highlight the range, then go to **Home** > **Conditional Formatting** > **New Rule**.

**Step 3:** The **Edit Formatting Rule** window appears. In that window,

- First, choose the
**Use a formula to determine which cell to format**the rule under**Select a rule type**. - Next type the below formula under
**Edit the Rule Description**command.

`=F6>=75%`

- Finally, click on
**Format**.

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

**Step 5:** Excel then returns to the **Edit Formatting Rule** window. Again, click on **OK**.

**Step 6:** Now, repeat **Steps 2** to **5** for another **2** formulas. Just replace each formula with the below formulas under **Edit the Rule Description**. Then click **OK**.

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

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

🔺 Clicking **OK**, fills the cells with the respective colors, as shown in the screenshot below.

**Step 7:** Afterward, select the range, then move to** Home** > **Conditional Formatting** > **New Rule**. The **Edit Formatting Rule** window appears. In the window, select the options as depicted in the image below. Finally, click **OK**.

🔺 Applying **Data Bar Format Style** whitens the cell fill colors, thus displaying values as different colored data bars. The final data bar representation looks like the following picture.

**Read More: **Conditional Formatting with Data Bars Based on Another Cell in Excel

**Method 2: Using REPT Function to Display Different Colors Data Bars **

**The REPT function** inserts the same character up to a given time. The syntax of the function is **REPT (text, number_times)**. Therefore, inserting a block character at a certain time may result in a data bar. Afterward, applying different font colors turns those data bars into data bars with different colors.

**Step 1:** Insert the below formula into the cells within the range.

`=REPT("â–ˆ",F6*10)`

Here â–ˆ is the** text**, and** F6*10** is the** number_times**.

🔺 To insert the symbol, go to** Insert** > **Symbols** (in the **Symbols** section). After that, choose **Arial** as **Font**, and **Block Elements** as **Subset**. Click on **Full Block**, then** Insert**.

**Step 2:** For formula insertion, repeat **Steps 3** to **5** for each formula. The formulas are the same as in the previous method. After inserting all the formulas, click **OK** as shown in the below image.

**Step 3:** Return to the worksheet and you’ll see all the cells get inserted with different colored data bars.

**Method 3: Displaying Deviation from Average in Data Bars with Different Colors**

Sometimes, users just need to display the contrast among values. Positive and negative values colored with different data bars help users represent the nature of their status. Therefore, deviations from average values can be represented with different colored data bars.

**Step 1:** Write the below formula in cells within the range to find the deviation values from the average.

`=F6-AVERAGE($F$6:$F$11)`

**Step 2:** After highlighting the range, go to **Home** > **Conditional Formatting** > **Data Bars** > Select **Light Blue Data Bar** (in the **Solid Fill** section).

🔺Â In a moment, Excel inserts the data bars in different colors, displaying the existing contrasting values as depicted in the following picture.

**Download Excel Workbook**

## Conclusion

In this article, we demonstrate **Data Bars** laced with formulas, the **REPT **function, and the **Data Bar** feature to conditionally format data bars in different colors. Use any of the methods to insert data bars with multiple colors. We hope this article offers enough clarification to remove any confusion regarding different color data bars. Comment if you have further inquiries or have anything to add.

## Related Articles

- How to Add Data Bars in Excel
- How to Add Solid Fill Data Bars in Excel
- How to Add Blue Data Bar in Excel
- How to Use Data Bars with Percentage in Excel
- How to Define Maximum Data Bars Value in Excel
- How to Remove Data Bars in Excel
- [Solved]: Data Bars Not Working in Excel
- [Fixed]: Conditional Formatting in Data Bar Percentage Not Working in Excel

**<< Go Back to Data Bars | Conditional Formatting | Learn Excel**