Conditional Formatting Data Bars Different Colors

Get FREE Advanced Excel Exercises with Solutions!

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.

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.


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

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,

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

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

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


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.


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

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo