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.
Similar Readings
- Conditional Formatting with Data Bars Based on Another Cell in Excel
- [Fixed]: Conditional Formatting in Data Bar Percentage Not Working 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.
