Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Conditional Formatting Data Bars Different Colors

Typically, Excel Conditional Formatting formats data bars in a single color. However, in some cases, users may need to display Conditional Formatting data bars 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.

Dataset-Conditional Formatting Data Bars 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.


Download Excel Workbook


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

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

Formula-Conditional Formatting Data Bars Different Colors

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

New Rule

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

Choose the Use a formula to determine which cell to format rule under Select a rule type.

Type the below formula under Edit the Rule Description command.

=F6>=75%

Click on Format.

Edit Formatting Rule

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.

Format Cells

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

Edit Formatting Rule

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%)

Other Formulas

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

Color applied-Conditional Formatting Data Bars Different Colors

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.

Data Bar

🔺 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.

Final Depiction

Read More: [Solved]: Data Bars Not Working in Excel (3 Possible Solutions)


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.

REPT function-Conditional Formatting Data Bars Different Colors

🔺 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.

Insert Symbol

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.

Formula

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

Final Depiction-Conditional Formatting Data Bars Different Colors

Read More: How to Remove Data Bars in Excel (3 Simple Ways)


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)

Deviation from Average-Conditional Formatting Data Bars Different Colors

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

Data Bar insertion

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

Contrast i Data Bar Depiction-Conditional Formatting Data Bars Different Colors

Read More: How to Add Solid Fill Data Bars in Excel (2 Simple Methods)


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

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo