Chart & Visualization Issues

How to Create Three-Color Data Bars Based on Values in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

The user wants to format Excel data bars to display specific colors based on distinct value ranges (orange for values below 14, yellow for 14 through 18, and green for values above 18).

Product: Microsoft Excel
Device & OS: not provided
Scenario: Applying advanced conditional formatting to a dataset to visually categorize numbers into three specific colored data bars based on custom threshold values.
Observed behavior: Excel does not provide a single built-in conditional formatting rule to assign multiple custom colors to data bars based on value tiers.

Before you start

Verify whether your workbook needs to be shared with other users, as legacy shared workbooks do not fully support VBA macros, making helper columns the safer approach.

Solution 1Recommended

Use Helper Columns with Conditional Formatting

Since a single conditional formatting rule cannot apply multiple data bar colors, you can separate your data into three helper columns and apply an individual data bar rule to each.

This method is highly recommended for collaborative environments because it relies entirely on native formulas and conditional formatting, ensuring it works perfectly in shared workbooks and updates dynamically.

Create helper columns

Insert three new columns next to your original data column. Label them Orange, Yellow, and Green to represent your value tiers.

Insert IF formulas

Use an IF formula to populate each column based on your conditions. For example, in the Orange column, use a formula like =IF(A2<14, A2, ""). Do the same for Yellow (=IF(AND(A2>=14, A2<=18), A2, "")) and Green (=IF(A2>18, A2, "")).

Apply the first data bar

Highlight the data in your Orange helper column. Navigate to the Home tab, click Conditional Formatting, hover over Data Bars, and select an orange fill.

Configure the remaining rules

Repeat the previous step for the Yellow and Green columns, applying yellow and green data bars respectively. Hide the original data column if you only want to display the colored bars.

Compatibility Advantage: This formula-based approach ensures your visualizations remain intact even if the document is shared or opened in web-based spreadsheet editors.

Solution 2

Use a VBA Macro to Assign Colors

Write a VBA script to evaluate each cell's value and programmatically assign a specific color to the data bar without needing extra columns.

Seamless Data Visualization

Format Data Easily with WPS Spreadsheet

Easily analyze and visualize your data ranges using the powerful built-in conditional formatting tools in WPS Spreadsheet. You can effortlessly set up helper columns and apply vibrant, highly customizable data bars.

1. Open your data: Launch WPS Spreadsheet and open your existing dataset.
2. Prepare your columns: Create helper columns for your specific value ranges (e.g., <14, 14-18, >18).
3. Access Conditional Formatting: Select the cells in your first helper column, navigate to the Home tab, and click Conditional Formatting.
4. Apply Data Bars: Select Data Bars from the dropdown menu and pick your preferred color.
5. Customize rules: Click 'More Rules' to fine-tune the bar appearance, then repeat the process for the remaining columns.

Fully compatible with Microsoft Excel (.xlsx) formats and conditional formatting rulesIntuitive Conditional Formatting interface for rapid data visualizationLightweight application that runs smoothly and quickly on Windows, Mac, and Linux

microsoft office alternative - wps office

Frequently Asked Questions

Is it possible to use one rule for multiple data bar colors in Excel?

No, Excel's built-in data bar rule only supports a single color gradient or solid fill per formatting rule. To display different colors based on distinct value tiers, you must use workaround methods like helper columns or VBA scripting.

Why should I avoid using VBA macros for shared workbooks?

If a workbook is shared using Excel's legacy sharing features, macros often will not function correctly or cannot be edited. Additionally, many organizations disable macros by default for security reasons, meaning other users might not see your customized colors.

Can I use a PivotChart instead of colored data bars?

Yes. A PivotChart allows you to group your data and apply different colors to specific series or categories automatically. This can serve as a robust and highly visual alternative to in-cell conditional formatting.

Excel & Data Problems